Thread: Return and sql tuple descriptions are incompatible
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)
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;
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
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.
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 ?
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-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
-----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-----
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.
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-----
-----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
- --
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/
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-----
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;
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
- --
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/
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-----