Re: Return and sql tuple descriptions are incompatible - Mailing list pgsql-general

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