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

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;