Return and sql tuple descriptions are incompatible

Started by Hengky Liwandouwalmost 12 years ago8 messagesgeneral

Jump to latest

Hengky Liwandouw

hengkyliwandouw@gmail.com

almost 12 years ago

Hi,

I have the following crosstab command :

Select * from crosstab($$select produkid, warehouseid,onhand from vwtest
order by 1,2$$)

as t (produkid VARCHAR, warehouseid integer)

vwtest :

CREATE OR REPLACE VIEW public.vwtest (

produkid,

warehouseid,

onhand)

SELECT dt.produkid,

dt.warehouseid,

sum(dt.awal + dt.ttlmsk - dt.ttlklr) AS onhand

FROM (

SELECT tblstockawal.kodebarang AS produkid,

tblstockawal.warehouseid,

sum(tblstockawal.qty) AS awal,

0 AS ttlmsk,

0 AS ttlklr

FROM tblstockawal

GROUP BY tblstockawal.kodebarang, tblstockawal.warehouseid

UNION

SELECT tbltransaksi.kodebarang AS produkid,

tbltransaksi.warehouseid,

0 AS awal,

sum(tbltransaksi.masuk) AS ttlmsk,

sum(tbltransaksi.keluar) AS ttlklr

FROM tbltransaksi

GROUP BY tbltransaksi.kodebarang, tbltransaksi.warehouseid

) dt

GROUP BY dt.produkid, dt.warehouseid;

The crosstab command didn't work with error : Return and sql tuple
descriptions are incompatible.

I have tried to change productid type to text and warehouseid to float8 and
the problem still not solve.

What is wrong with the command ?

Thanks

David G. Johnston

david.g.johnston@gmail.com

almost 12 years ago

In reply to: Hengky Liwandouw (#1)

Re: Return and sql tuple descriptions are incompatible

Hengky Lie wrote

Select * from crosstab($$select produkid, warehouseid,onhand from vwtest
order by 1,2$$)

as t (produkid VARCHAR, warehouseid integer)

The crosstab command didn't work with error : Return and sql tuple
descriptions are incompatible.

I have tried to change productid type to text and warehouseid to float8
and
the problem still not solve.

What is wrong with the command ?

Go read: http://www.postgresql.org/docs/9.3/interactive/tablefunc.html
"F.36.1.2. crosstab(text)" again and then, looking at the data you are
passing to the crosstab function, see if you can determine the correct
number of columns that need to be declared in the "as t (...)" section. The
answer is likely not "2" since one of those is a row name and if you only
have a single warehouse a crosstab seems pointless...

Note that even if "warehouseid" is an integer it is going to be effectively
converted to text since the values of the categories all become column
labels...

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Return-and-sql-tuple-descriptions-are-incompatible-tp5801414p5801450.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hengky Liwandouw

hengkyliwandouw@gmail.com

almost 12 years ago

In reply to: David G. Johnston (#2)

Re: Return and sql tuple descriptions are incompatible

Hi,

I have read the instruction, change warehouseid to text, spend several hours
to test many option but it doesn't help.

This is the new query :

SELECT *
FROM crosstab(
'SELECT produkid, warehousename, onhand
FROM vwtest
ORDER BY 1,2')
AS ct ("Produkid" text, "Store1" text, "Store2" text, "Store3" text);

Vwtest itself is a view with 3 column output: produkid(text),
warehousename(text), onhand(integer)

CREATE OR REPLACE VIEW public.vwtest (
produkid,
warehousename,
onhand)
AS
SELECT dt.produkid::text AS produkid,
tblwarehouse.warehousename::text AS warehousename,
sum(dt.awal + dt.ttlmsk - dt.ttlklr)::integer AS onhand
FROM (
SELECT tblstockawal.kodebarang AS produkid,
tblstockawal.warehouseid,
sum(tblstockawal.qty) AS awal,
0 AS ttlmsk,
0 AS ttlklr
FROM tblstockawal
GROUP BY tblstockawal.kodebarang, tblstockawal.warehouseid
UNION
SELECT tbltransaksi.kodebarang AS produkid,
tbltransaksi.warehouseid,
0 AS awal,
sum(tbltransaksi.masuk) AS ttlmsk,
sum(tbltransaksi.keluar) AS ttlklr
FROM tbltransaksi
GROUP BY tbltransaksi.kodebarang, tbltransaksi.warehouseid
) dt
JOIN tblwarehouse ON tblwarehouse.id = dt.warehouseid
GROUP BY dt.produkid, tblwarehouse.warehousename;

The error still the same.

I really have no idea

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of David G Johnston
Sent: Friday, April 25, 2014 2:20 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Return and sql tuple descriptions are incompatible

Hengky Lie wrote

Select * from crosstab($$select produkid, warehouseid,onhand from vwtest
order by 1,2$$)

as t (produkid VARCHAR, warehouseid integer)

The crosstab command didn't work with error : Return and sql tuple
descriptions are incompatible.

I have tried to change productid type to text and warehouseid to float8
and
the problem still not solve.

What is wrong with the command ?

Note that even if "warehouseid" is an integer it is going to be effectively
converted to text since the values of the categories all become column
labels...

David J.

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Return-and-sql-tuple-descriptions-ar
e-incompatible-tp5801414p5801450.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Joe Conway

mail@joeconway.com

almost 12 years ago

In reply to: Hengky Liwandouw (#3)

Re: Return and sql tuple descriptions are incompatible

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 04/25/2014 12:19 AM, Hengky Liwandouw wrote:

I have read the instruction, change warehouseid to text, spend
several hours to test many option but it doesn't help.

Please send a complete, self-contained test case which reproduces your
error. By that I mean CREATE statements for tables and view involved,
COPY statements with sufficient sample data, and your failing query. I
realize you have sent bits and pieces of this, but it should be
together in one spot. Then we can easily cut and paste to reproduce
the error, and determine the issue.

In other words, help us help you.

Joe

- --
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.14 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQIcBAEBAgAGBQJTWm+0AAoJEDfy90M199hlpfQP/2wpUJcAj2Z6+uOAbqJoBYDJ
JY/9oj/dkuySAqL4qWQSLqNZf8nwPNXpst9exQs4Sf6GQa+j47AamWy+G1++J/x8
HRe76VslP1r7vBY4x0kx7a3U8KkcfrPlZAOLVjpFDjvcALPMVgy8AfHRLWTSOmQt
VmjKe1t/k1Znb8buAAUILs+1Q0c81hihv7AGTUZQL/oxCBBmNPpmZPHFR3ZO3NOm
wfmLP/U/lHGd2ev/d9DShi0Q2LKyPQu4Q/WIsc6z+eThd991rP3TIlhKl79snzxv
XgFEQRP2DeRgW/S/43gguIWb53Wimfj0RN6mJZN9DFJO72kAyin0Cs2BDKN3kHbD
D8UkE9Qjnj7UqZzIrZ3+XLLmG04KNmFl9H1HxD8xPudyO4DG4gXkbsvSQ9US0ThG
ZOk5ipOmUupjlgEis9W1QvDlc8jqJo5AbYLPAQ7S9cd9ougKDsD7YUeQdSwQod+H
ls2u2JqFfdewsb5YupSGlvY6k5SVchz+SD8JSlYXRG4yqiLivHwudwsSADaQAJyj
YZiLduHoXKjnPKASpqATTtfSvXdKxbJLJZg9Gf5CgLIlIl30tQjMRQ/iFfPq2jTD
rK8p+AKQrVoHD8/7T1rkBfLa9Vp3r/BqyYz3OgSLcHhnZg5lanfSK0jwKKNSGIoV
2sSy4zVI9+ioNL+lUggN
=JHtf
-----END PGP SIGNATURE-----

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hengky Liwandouw

hengkyliwandouw@gmail.com

almost 12 years ago

In reply to: Joe Conway (#4)

Re: Return and sql tuple descriptions are incompatible

Joe,

Thanks to give me the right direction to get help. Okay, here is the detail.

CREATE table test (id SERIAL, produkid TEXT, warehousename TEXT, onhand
INTEGER);

COPY test (id, produkid, warehousename, onhand) FROM stdin;
1 2791404000014 OFFICE 10
2 2791404000021 STORE2 10
3 2791404000014 STORE2 45
\.

select *
from crosstab('select produkid, warehousename, onhand from test order by
1,2')
as ct (row_name text, categori_1 text, categori_2 text)

Result : ERROR: return and sql tuple descriptions are incompatible

Hope to get solution for this.

Hengky

-----Original Message-----
From: Joe Conway [mailto:mail@joeconway.com]
Sent: Friday, April 25, 2014 10:23 PM
To: Hengky Liwandouw; 'pgsql-general General'
Subject: Re: [GENERAL] Return and sql tuple descriptions are incompatible

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 04/25/2014 12:19 AM, Hengky Liwandouw wrote:

I have read the instruction, change warehouseid to text, spend
several hours to test many option but it doesn't help.

In other words, help us help you.

Joe

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Joe Conway

mail@joeconway.com

almost 12 years ago

In reply to: Hengky Liwandouw (#1)

Re: Return and sql tuple descriptions are incompatible

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

(resending to the list as well for the sake of the archives)

On 04/25/2014 08:53 AM, Hengky Liwandouw wrote:

Thanks to give me the right direction to get help. Okay, here it
the detail.

CREATE table test (id SERIAL, produkid TEXT, warehousename TEXT,
onhand INTEGER);

COPY test (id, produkid, warehousename, onhand) FROM stdin; 1
2791404000014 OFFICE 10 2 2791404000021 STORE2 10 3 2791404000014
STORE2 45 \.

select * from crosstab('select produkid, warehousename, onhand
from test order by 1,2') as ct (row_name text, categori_1 text,
categori_2 text)

Result : ERROR: return and sql tuple descriptions are
incompatible

Great -- thanks.

First of all, I suspect you do not want to use this form (the single
argument form) of the crosstab function. See:

http://www.postgresql.org/docs/9.3/interactive/tablefunc.html

Specifically:
"The crosstab function produces one output row for each consecutive
group of input rows with the same row_name value. It fills the output
value columns, left to right, with the value fields from these rows.
If there are fewer rows in a group than there are output value
columns, the extra output columns are filled with nulls; if there are
more rows, the extra input rows are skipped."

- - and -

"The main limitation of the single-parameter form of crosstab is that
it treats all values in a group alike, inserting each value into the
first available column. If you want the value columns to correspond to
specific categories of data, and some groups might not have data for
some of the categories, that doesn't work well."

Therefore I would be willing to bet what you really want is something
like:

select *
from crosstab
(
'select produkid, warehousename, onhand
from test order by 1,2',
'select distinct warehousename from test order by 1'
)
as ct (produkid text, office int, store2 int);
produkid | office | store2
- ---------------+--------+--------
2791404000014 | 10 | 45
2791404000021 | | 10
(2 rows)

- - or -

select *
from crosstab
(
'select warehousename,produkid, onhand
from test order by 1,2',
'select distinct produkid from test order by 1'
)
as ct (warehousename text, p_2791404000014 int, p_2791404000021 int);
warehousename | p_2791404000014 | p_2791404000021
- ---------------+-----------------+-----------------
OFFICE | 10 |
STORE2 | 45 | 10
(2 rows)

The second SQL provides the "category". It is generally useful to run
this from your app first, get a list of the actual categories based on
current data, and then have the app write the crosstab query
dynamically and execute it. In that case the last example might
actually be written as:

select *
from crosstab
(
'select warehousename,produkid, onhand
from test order by 1,2',
'values (2791404000014),(2791404000021)'
)
as ct (warehousename text, p_2791404000014 int, p_2791404000021 int);
warehousename | p_2791404000014 | p_2791404000021
- ---------------+-----------------+-----------------
OFFICE | 10 |
STORE2 | 45 | 10
(2 rows)

HTH,

Joe

iQIcBAEBAgAGBQJTWpBQAAoJEDfy90M199hlezsP/0cXv+ES1G68HXG2zONgZNT7
fcF665ZlofNYfKmzAy9KH9OuIeyGVO3tAd2So+xv42hZKca/M4FKVDd9bRqLlRZO
56ecfBGQIHFAQPyHk1pz4cddpHT+3NU5Ub40R/E+VoY6XkdBcaHRGEPMtDNoMm51
qHQpDV9vTsYAc3QZTamnBQ5IyPl1m2QUEeUq4y7IX69I9J/4+D8nprSzJm2ILHVX
RBjIydSZRjL8RabYcHA32HWX4zJJwekdybutgVXws3HBP8YkJcsV/flsqAu6kO0M
24V2lqA/aQbG2ggID3I99CE8MVNWpybV+vLEUUfwMMTPql/4GQF5GdlaZD9kkfp5
vGj9kHQIrW67JyoEAck/Nv0rjlwFAKXFqMi2XNR5MCfXIqJhKmIwk7jXTEE1jkP3
s6VCcvQTTGUtqZyyGq1bqCQ7/d71G4VAthxrYLz/ZajJ8N2HIfbcvB9LbAS4Qig7
i401yCzaGo9SuALKWN7pRZzI6aJZrcBshbvBSqFZQdWNlpbbaDoPwEgQ3MLT+7Xp
NdFF8HokrwNFQ416cUYVfi84s0whSzjHt4iZprUxhYCesvZgDnE8LUlJhz7aeiBD
3Xi+YDE9MqDrQPYNP5wXu2TsNTXQzyol7jo2fpvRbjs6T2B40Jc+SERGr2gUPrpF
bK4ejASGoW7z6uJUJwWd
=WSMX
-----END PGP SIGNATURE-----

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Import Notes

Reply to msg id not found: 000c01cf609e$867186c0$93549440$@com

rob stone

floriparob@gmail.com

almost 12 years ago

In reply to: Hengky Liwandouw (#5)

Re: Return and sql tuple descriptions are incompatible

O
n Fri, 2014-04-25 at 23:58 +0800, Hengky Liwandouw wrote:T
hanks to give me the right direction to get help. Okay, here is the
detail.

CREATE table test (id SERIAL, produkid TEXT, warehousename TEXT, onhand
INTEGER);

COPY test (id, produkid, warehousename, onhand) FROM stdin;
1 2791404000014 OFFICE 10
2 2791404000021 STORE2 10
3 2791404000014 STORE2 45
\.

select *
from crosstab('select produkid, warehousename, onhand from test order by
1,2')
as ct (row_name text, categori_1 text, categori_2 text)

Result : ERROR: return and sql tuple descriptions are incompatible

Hope to get solution for this.

Hengky

-----Original Message-----
From: Joe Conway [mailto:mail@joeconway.com]
Sent: Friday, April 25, 2014 10:23 PM
To: Hengky Liwandouw; 'pgsql-general General'
Subject: Re: [GENERAL] Return and sql tuple descriptions are incompatible

On 04/25/2014 12:19 AM, Hengky Liwandouw wrote:

I have read the instruction, change warehouseid to text, spend
several hours to test many option but it doesn't help.

Please send a complete, self-contained test case which reproduces your
error. By that I mean CREATE statements for tables and view involved,
COPY statements with sufficient sample data, and your failing query. I
realize you have sent bits and pieces of this, but it should be
together in one spot. Then we can easily cut and paste to reproduce
the error, and determine the issue.

In other words, help us help you.

Joe

I think that Joe wanted to see the CREATE TABLE DDL for tables
tblstockawal and tbltransaksi.
If you "SELECT * FROM public.vwtest" does the query return expected
data?
If any of these columns contain null (tblstockawal.qty,
tbltransaksi.masuk, tbltransaksi.keluar) then the result of any SUM is
null. This means that vwtest.onhand will be null.

Regards,
Robert

CREATE OR REPLACE VIEW public.vwtest (
produkid,
warehouseid,
onhand)
AS
SELECT dt.produkid,
dt.warehouseid,
sum(dt.awal + dt.ttlmsk - dt.ttlklr) AS onhand
FROM (
SELECT tblstockawal.kodebarang AS produkid,
tblstockawal.warehouseid,
sum(tblstockawal.qty) AS awal,
0 AS ttlmsk,
0 AS ttlklr
FROM tblstockawal
GROUP BY tblstockawal.kodebarang, tblstockawal.warehouseid
UNION
SELECT tbltransaksi.kodebarang AS produkid,
tbltransaksi.warehouseid,
0 AS awal,
sum(tbltransaksi.masuk) AS ttlmsk,
sum(tbltransaksi.keluar) AS ttlklr
FROM tbltransaksi
GROUP BY tbltransaksi.kodebarang, tbltransaksi.warehouseid
) dt
GROUP BY dt.produkid, dt.warehouseid;

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hengky Liwandouw

hengkyliwandouw@gmail.com

almost 12 years ago

In reply to: Hengky Liwandouw (#1)

Re: Return and sql tuple descriptions are incompatible

Joe, that is exactly what I want.

Could you please give more detail example for this crosstab ? I have
warehouse and product table like this :

CREATE TABLE tblwarehouse (
id integer NOT NULL,
warehousename character varying(20)
);

COPY tblwarehouse (id, warehousename) FROM stdin;
2 OFFICE
3 STORE2
\.

CREATE TABLE tblproduct (
id serial NOT NULL,
produkid text,
warehouseid integer,
onhand integer
);

COPY tblproduct (produkid, warehouseid, onhand) FROM stdin;
2791404000014 2 10
2791404000021 3 10
2791404000014 3 45
\.

I need crosstab query to display record from tblproduct like this :

PRODUKID | OFFICE | STORE2 | TOTAL
---------------+--------+--------+ ------
2791404000014 | 10 | 45 | 55
2791404000021 | 0 | 10 | 10

The crosstab warehouse column name is taken from tblwarehouse so when end
user add warehouse, crosstab column name will change automatically. And also
each row has total qty.

Please give detail command for this.

Thanks in advance !

-----Original Message-----
From: Joe Conway [mailto:mail@joeconway.com]
Sent: Wednesday, April 30, 2014 5:43 AM
To: Hengky Liwandouw
Subject: Re: [GENERAL] Return and sql tuple descriptions are incompatible

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 04/25/2014 11:55 PM, Hengky Liwandouw wrote:

The last question : how to have automatically column header from
other table ? say from select distinct warehousename from test
order by 1 ?

If I understand your question, the best thing is to run the query
"select distinct warehousename from test order by 1" in your
application first, and then use the result to build a crosstab SQL
string. Then execute the crosstab SQL string with a second query.

Joe

iQIcBAEBAgAGBQJTYBz6AAoJEDfy90M199hlybAP/0wfDVvJmvGcXK9lP0w+1vxR
A+Snl/E5MjUd3p9yTjBqP7MjDOgg467cn5gg+q7dtpya/jyED4Db78rn/G03ZqVK
2IVhaXQgD6p91w/s+zexdB7UBC3BxGzk/IMf3E93tlsZuBUk15x98jhY4FHl9Wgw
++luWY05pxnuluvmjwvc3e2PM99Re8EIw83KuiLzSYgChCvremz1uJi6hd0GDXme
iSmxhgn9blSL5hqJNsYWn0Ch0ga87T380HLOgFgnA4e9afE/8QU8kqHtLt+J9mKF
RgzvG2+cPCtlDmjTEWWbznKa+m54VmSnwwLjndU1JOsr4NEh4X5lv8Ahx6yh/BI0
PsoyU/DLrCJcXp263nUuGKbid+PRbRecpX5abX+fP/dfHPNqiw5ECFVpFMiZ35ug
5BqxJPX7hJAapwGp7QBKT9aFCtpuKFRkanywO19lgQC8MVXpRZH+/fADbzYrLc+d
v/9u6r4Qhxn7ltEjz7pU85EgZqYLw4j4fXRr1fZseN3+HXZpRVaBGC8JOyPE6Buc
p75tlgL7E6XXLNJsoY6RTqAcs3SmPgBBrmAfaP3etbpeHlZUBQMx9Xs2lOXWatn+
Uw3whFjJF1Wl8P+l5Bc49Yyerxj+d1Yb7Z3KOpLglOHi4K8hNu4knaeR1DiOs/4n
GLf3JS+5qijpX0aOndVK
=5sxK
-----END PGP SIGNATURE-----

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Import Notes

Reply to msg id not found: 53601CFA.4030206@joeconway.com