Search Postgresql Archives

Re: Return and sql tuple descriptions are incompatible

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxx
[mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of David G Johnston
Sent: Friday, April 25, 2014 2:20 AM
To: pgsql-general@xxxxxxxxxxxxxx
Subject: 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-ar
e-incompatible-tp5801414p5801450.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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



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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux