[SQL] crosstab help
I have a simple table
item_number week_of planned_qoh
-- -- --
5 2012-02-05 30
5 2012-02-12 40
5 2012-02-19 50
where
item_number text
week_of date
planned_qoh integer
I have a function that returns the table as above:
chromasun._chromasun_totals(now()::date)
I want to see
5 2012-02-05 2012-02-122012-02-19
30 40 50
This is what I have tried (although, I have tired many others)
select * from crosstab('select item_number::text as row_name,
to_char(week_of,''MM-DD-YY'') as bucket, planned_qoh::integer as buckvalue
from xchromasun._chromasun_totals(now()::date)')
as ct(item_number text, week_of date, planned_qoh integer)
I get
ERROR: return and sql tuple descriptions are incompatible
What am I doing wrong?
Johnf
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] crosstab help
Hi, the return type of the crosstab must be defined correctly, according to the number of expected columns. Try following (untested): select * from crosstab( 'select item_number::text as row_name, to_char(week_of,''MM-DD-YY'')::date as bucket, planned_qoh::integer as buckvalue from xchromasun._chromasun_totals(now()::date)') as ct(item_number text, week_of_1 date, week_of_2 date, week_of_3 date) Regards, Andreas -Ursprüngliche Nachricht- Von: [email protected] [mailto:[email protected]] Im Auftrag von John Fabiani Gesendet: Freitag, 24. Februar 2012 09:11 An: [email protected] Betreff: [SQL] crosstab help I have a simple table item_number week_of planned_qoh -- -- -- 5 2012-02-05 30 5 2012-02-12 40 5 2012-02-19 50 where item_number text week_of date planned_qoh integer I have a function that returns the table as above: chromasun._chromasun_totals(now()::date) I want to see 5 2012-02-05 2012-02-122012-02-19 30 40 50 This is what I have tried (although, I have tired many others) select * from crosstab('select item_number::text as row_name, to_char(week_of,''MM-DD-YY'') as bucket, planned_qoh::integer as buckvalue from xchromasun._chromasun_totals(now()::date)') as ct(item_number text, week_of date, planned_qoh integer) I get ERROR: return and sql tuple descriptions are incompatible What am I doing wrong? Johnf -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] crosstab help
That worked! However, I need the actual date to be the column heading? And of course the dates change depending on the date passed to the function: xchromasun._chromasun_totals(now()::date) So how do I get the actual dates as the column header? johnf On Friday, February 24, 2012 09:27:38 AM Andreas Gaab wrote: > Hi, > > the return type of the crosstab must be defined correctly, according to the > number of expected columns. > > Try following (untested): > > select * from crosstab( > 'select item_number::text as row_name, to_char(week_of,''MM-DD-YY'')::date > as bucket, planned_qoh::integer as buckvalue from > xchromasun._chromasun_totals(now()::date)') as ct(item_number text, > week_of_1 date, week_of_2 date, week_of_3 date) > > Regards, > Andreas > > > > -Ursprüngliche Nachricht- > Von: [email protected] [mailto:[email protected]] > Im Auftrag von John Fabiani Gesendet: Freitag, 24. Februar 2012 09:11 > An: [email protected] > Betreff: [SQL] crosstab help > > I have a simple table > item_number week_of planned_qoh > -- -- -- > 5 2012-02-05 30 > 5 2012-02-12 40 > 5 2012-02-19 50 > > > where > item_number text > week_of date > planned_qoh integer > > I have a function that returns the table as above: > > chromasun._chromasun_totals(now()::date) > > I want to see > > 5 2012-02-05 2012-02-122012-02-19 > 30 40 50 > > This is what I have tried (although, I have tired many others) > > select * from crosstab('select item_number::text as row_name, > to_char(week_of,''MM-DD-YY'') as bucket, planned_qoh::integer as buckvalue > from xchromasun._chromasun_totals(now()::date)') as ct(item_number text, > week_of date, planned_qoh integer) > > I get > ERROR: return and sql tuple descriptions are incompatible > > What am I doing wrong? > > Johnf > > -- > Sent via pgsql-sql mailing list ([email protected]) To make changes > to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] crosstab help
As far as I know you must define the numbers (and types) of columns and column headers individually for each query or define some custom function... Andreas -Ursprüngliche Nachricht- Von: [email protected] [mailto:[email protected]] Im Auftrag von John Fabiani Gesendet: Freitag, 24. Februar 2012 09:39 An: [email protected] Betreff: Re: [SQL] crosstab help That worked! However, I need the actual date to be the column heading? And of course the dates change depending on the date passed to the function: xchromasun._chromasun_totals(now()::date) So how do I get the actual dates as the column header? johnf On Friday, February 24, 2012 09:27:38 AM Andreas Gaab wrote: > Hi, > > the return type of the crosstab must be defined correctly, according > to the number of expected columns. > > Try following (untested): > > select * from crosstab( > 'select item_number::text as row_name, > to_char(week_of,''MM-DD-YY'')::date > as bucket, planned_qoh::integer as buckvalue from > xchromasun._chromasun_totals(now()::date)') as ct(item_number text, > week_of_1 date, week_of_2 date, week_of_3 date) > > Regards, > Andreas > > > > -Ursprüngliche Nachricht- > Von: [email protected] > [mailto:[email protected]] > Im Auftrag von John Fabiani Gesendet: Freitag, 24. Februar 2012 09:11 > An: [email protected] > Betreff: [SQL] crosstab help > > I have a simple table > item_number week_of planned_qoh > -- -- -- > 5 2012-02-05 30 > 5 2012-02-12 40 > 5 2012-02-19 50 > > > where > item_number text > week_of date > planned_qoh integer > > I have a function that returns the table as above: > > chromasun._chromasun_totals(now()::date) > > I want to see > > 5 2012-02-05 2012-02-122012-02-19 > 30 40 50 > > This is what I have tried (although, I have tired many others) > > select * from crosstab('select item_number::text as row_name, > to_char(week_of,''MM-DD-YY'') as bucket, planned_qoh::integer as > buckvalue from xchromasun._chromasun_totals(now()::date)') as > ct(item_number text, week_of date, planned_qoh integer) > > I get > ERROR: return and sql tuple descriptions are incompatible > > What am I doing wrong? > > Johnf > > -- > Sent via pgsql-sql mailing list ([email protected]) To make > changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] crosstab help
Thanks for the insight! johnf On Friday, February 24, 2012 09:48:03 AM Andreas Gaab wrote: > As far as I know you must define the numbers (and types) of columns and > column headers individually for each query or define some custom > function... > > Andreas > > -Ursprüngliche Nachricht- > Von: [email protected] [mailto:[email protected]] > Im Auftrag von John Fabiani Gesendet: Freitag, 24. Februar 2012 09:39 > An: [email protected] > Betreff: Re: [SQL] crosstab help > > That worked! However, I need the actual date to be the column heading? > And of course the dates change depending on the date passed to the > function: xchromasun._chromasun_totals(now()::date) > > So how do I get the actual dates as the column header? > johnf > > On Friday, February 24, 2012 09:27:38 AM Andreas Gaab wrote: > > Hi, > > > > the return type of the crosstab must be defined correctly, according > > to the number of expected columns. > > > > Try following (untested): > > > > select * from crosstab( > > 'select item_number::text as row_name, > > to_char(week_of,''MM-DD-YY'')::date > > as bucket, planned_qoh::integer as buckvalue from > > xchromasun._chromasun_totals(now()::date)') as ct(item_number text, > > week_of_1 date, week_of_2 date, week_of_3 date) > > > > Regards, > > Andreas > > > > > > > > -Ursprüngliche Nachricht- > > Von: [email protected] > > [mailto:[email protected]] > > Im Auftrag von John Fabiani Gesendet: Freitag, 24. Februar 2012 09:11 > > An: [email protected] > > Betreff: [SQL] crosstab help > > > > I have a simple table > > item_number week_of planned_qoh > > -- -- -- > > 5 2012-02-05 30 > > 5 2012-02-12 40 > > 5 2012-02-19 50 > > > > > > where > > item_number text > > week_of date > > planned_qoh integer > > > > I have a function that returns the table as above: > > > > chromasun._chromasun_totals(now()::date) > > > > I want to see > > > > 5 2012-02-05 2012-02-122012-02-19 > > > > 30 40 > > 50 > > > > This is what I have tried (although, I have tired many others) > > > > select * from crosstab('select item_number::text as row_name, > > to_char(week_of,''MM-DD-YY'') as bucket, planned_qoh::integer as > > buckvalue from xchromasun._chromasun_totals(now()::date)') as > > ct(item_number text, week_of date, planned_qoh integer) > > > > I get > > ERROR: return and sql tuple descriptions are incompatible > > > > What am I doing wrong? > > > > Johnf > > > > -- > > Sent via pgsql-sql mailing list ([email protected]) To make > > changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-sql > > -- > Sent via pgsql-sql mailing list ([email protected]) To make changes > to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] crosstab maybe by case statement
Hi Folks,
I have one that I need your advise on how to construct.
I have the need to create a series of dates starting from a Sunday - one week
apart.
(select ((date_trunc('week', '2012-02-12'::date)::date) +(i+6)) as week_date
from
generate_series(0,84,7) i) as foo
that gives a table of sunday dates starting from '2012-02-12'::date
The dates will be used in the column headings ( I don't know in advance what
those dates will be - because the start sunday can change).
I have written functions to return sum()'s of a field for the week starting
from the sundays returned from the above dates.
getqtyordered(itemsite_id, foo.week_date) -- returns qty order for the week
getqtyalloc(itemsite_id, foo.week_date) -- qty_alloc for the week
... I actually have several of these types of functions.
And of course there is other information retrieved.
What I need to output is the following:
Namedate 1 date2 date3date4 ...
qty order 10820 15
qty_alloc 6 03 50
What I can't figure out is how to get the dates to become my field/column
name.
BELOW IS MY ACTUAL FUNCTION:
CREATE OR REPLACE FUNCTION xchromasun._chromasun_totals(date)
RETURNS SETOF weekly_mpr AS
$BODY$
DECLARE
_passed_date alias for $1;
_start_date date;
_warehous_id integer;
_firm_planned_accum numeric;
_running_avail numeric;
_start_qoh numeric;
_itemsite_id integer;
_olditemsite_id integer;
rec record;
myrecord weekly_mpr;
counter integer;
BEGIN
_running_avail :=0;
_firm_planned_accum := 0;
_olditemsite_id := 0;
_itemsite_id := 0;
counter := 0;
--find Sunday
select the_date from
(select (_passed_date::date) - num as the_date, extract(dow from
((_passed_date::date) - num)) as weekday
from (select generate_series(0,6) as num) as t) as myans where weekday = 0
into _start_date;
for rec in select itemsite_id,item_number, item_descrip1, itemsite_qtyonhand,
itemsite_safetystock,foo.week_date,itemsite_leadtime,warehous_code,
coalesce(vend_name,'NOT ON FILE') as "vendor", coalesce(vend_number, 'NONE')
as "vend_number",
xchromasun._chromasun_getqtyordered(itemsite_id, foo.week_date) as
"qty_ordered",
xchromasun._chromasun_getqtyallocated(itemsite_id, foo.week_date) as
"qty_alloc",
xchromasun._chromasun_getqtypr(itemsite_id, foo.week_date) as "purch_req",
xchromasun._chromasun_getqtyplanneddemand(itemsite_id, foo.week_date) as
"planned_demand",
qtyavailable(itemsite_id, foo.week_date) as "qty_avail",
(select xchromasun._chromasun_getqtyfirmed(itemsite_id, foo.week_date)) as
"firm_planned_orders",
(xchromasun._chromasun_getqtyplanned(itemsite_id, foo.week_date) - (select
xchromasun._chromasun_getqtyfirmed(itemsite_id, foo.week_date))) as
"planned_orders"
from public.itemsite
left join whsinfo on (warehous_id = itemsite_warehous_id)
left join item on (item_id = itemsite_item_id)
left join itemsrc on (itemsrc_item_id = item_id)
left outer join vendinfo on (vend_id = itemsrc_vend_id),
(select ((date_trunc('week', _start_date::date)::date) +(i+6)) as week_date
from
generate_series(0,84,7) i) as foo
where itemsite_item_id in (select item_id from item) and item_type = 'P'
order by item_number asc,vend_number,week_date
loop
counter := counter +1;
_olditemsite_id :=rec.itemsite_id;
IF _itemsite_id <> _olditemsite_id THEN
_itemsite_id := rec.itemsite_id;
_running_avail := rec.itemsite_qtyonhand;
END IF;
_firm_planned_accum = _firm_planned_accum + rec.firm_planned_orders ;
_running_avail = _running_avail - rec.planned_demand + rec.qty_ordered -
rec.qty_alloc ;
myrecord.counter := counter::integer;
myrecord.warehous_code := rec.warehous_code;
myrecord.week_of := rec.week_date;
myrecord.qty_ordered := rec.qty_ordered;
myrecord.firm_planned_orders := rec.firm_planned_orders;
myrecord.planned_orders := rec.planned_orders;
myrecord.item_number := rec.item_number;
myrecord.item_descrip1 := rec.item_descrip1;
myrecord.itemsite_qtyonhand := rec.itemsite_qtyonhand;
myrecord.itemsite_safetystock := rec.itemsite_safetystock;
myrecord.qty_alloc := rec.qty_alloc;
myrecord.qty_avail := rec.qty_avail;
myrecord.planned_qoh := _running_avail;
myrecord.firm_avail := _firm_planned_accum;
myrecord.lead_time := rec.itemsite_leadtime;
myrecord.vend_number := rec.vend_number;
myrecord.vendor := rec.vendor;
myrecord.purch_req := rec.purch_req;
myrecord.planned_demand := -rec.planned_demand;
return next myrecord;
end loop;
return;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION xchromasun._chromasun_totals(date)
OWNER TO postgres;
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
