[SQL] simple join is beating me
hi folks i have the following: select o_ord_date as o_date, count(o_id) as orders from orders where o_de_id in (5,6) and o_ord_date > CURRENT_DATE-'1 month'::interval group by o_ord_date order by o_date desc and select o_act_del_date as o_date, count(o_id) as delivery from orders where o_de_id in (5,6) and o_act_del_date > CURRENT_DATE-'1 month'::interval and o_act_del_date <= CURRENT_DATE group by o_act_del_date order by o_date desc These give me o_date | orders + 2009-07-10 | 4 2009-07-09 | 5 2009-07-08 | 12 2009-07-07 | 5 2009-07-06 | 2 2009-07-03 | 2 2009-07-02 | 7 2009-07-01 | 19 2009-06-30 | 20 2009-06-29 | 28 and o_date | delivery +-- 2009-07-13 |5 2009-07-10 |3 2009-07-09 |4 2009-07-08 |2 2009-07-07 |4 2009-07-06 |7 2009-07-03 |6 2009-07-02 |5 2009-07-01 |3 2009-06-30 |3 How do i get o_date | orders | delivery ++-- 2009-07-13 ||5 2009-07-10 | 4 |3 2009-07-09 | 5 |4 2009-07-08 | 12 |2 2009-07-07 | 5 |4 2009-07-06 | 2 |7 2009-07-03 | 2 |6 2009-07-02 | 7 |5 2009-07-01 | 19 |3 2009-06-30 | 20 |3 -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] simple join is beating me
Howdy, Gary, I have not the database in this computer, so I cannot test the sql I'm sending you, but if you do an outer join won't it result in what you need? Maybe I am not reaching what you want to do... SELECT deliveryQuery.o_date , orders, delivery FROM (/* ur first query here */) ordersQuery NATURAL RIGHT JOIN (/* ur second query goes here */) deliveryQuery ORDER BY deliveryQuery.o_date DESC Tararabite, Oliveiros @Allgarve 2009/7/13 Gary Stainburn > hi folks > > i have the following: > > select o_ord_date as o_date, count(o_id) as orders >from orders where o_de_id in (5,6) and o_ord_date > CURRENT_DATE-'1 > month'::interval >group by o_ord_date > order by o_date desc > > and > > select o_act_del_date as o_date, count(o_id) as delivery >from orders >where o_de_id in (5,6) and > o_act_del_date > CURRENT_DATE-'1 month'::interval and > o_act_del_date <= CURRENT_DATE >group by o_act_del_date > order by o_date desc > > These give me > > o_date | orders > + > 2009-07-10 | 4 > 2009-07-09 | 5 > 2009-07-08 | 12 > 2009-07-07 | 5 > 2009-07-06 | 2 > 2009-07-03 | 2 > 2009-07-02 | 7 > 2009-07-01 | 19 > 2009-06-30 | 20 > 2009-06-29 | 28 > > and > > o_date | delivery > +-- > 2009-07-13 |5 > 2009-07-10 |3 > 2009-07-09 |4 > 2009-07-08 |2 > 2009-07-07 |4 > 2009-07-06 |7 > 2009-07-03 |6 > 2009-07-02 |5 > 2009-07-01 |3 > 2009-06-30 |3 > > How do i get > > o_date | orders | delivery > ++-- > 2009-07-13 ||5 > 2009-07-10 | 4 |3 > 2009-07-09 | 5 |4 > 2009-07-08 | 12 |2 > 2009-07-07 | 5 |4 > 2009-07-06 | 2 |7 > 2009-07-03 | 2 |6 > 2009-07-02 | 7 |5 > 2009-07-01 | 19 |3 > 2009-06-30 | 20 |3 > -- > Gary Stainburn > > This email does not contain private or confidential material as it > may be snooped on by interested government parties for unknown > and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
Re: [SQL] simple join is beating me
Hi Oliveiros, Thank you for this. However, this does not give me what I want. If a date exists where we have orders but no deliveries the row does not appear. I have tried doing a union to link the two selects together, but i still cannot get anything to work. Gary On Monday 13 July 2009 12:45:49 Oliveiros wrote: > Howdy, Gary, > > I have not the database in this computer, so I cannot test the sql I'm > sending you, but > if you do an outer join won't it result in what you need? Maybe I am not > reaching what you want to do... > > SELECT deliveryQuery.o_date , orders, delivery > FROM (/* ur first query here */) ordersQuery > NATURAL RIGHT JOIN (/* ur second query goes here */) deliveryQuery > ORDER BY deliveryQuery.o_date DESC > > Tararabite, > > Oliveiros > @Allgarve > > > > 2009/7/13 Gary Stainburn > > > hi folks > > > > i have the following: > > > > select o_ord_date as o_date, count(o_id) as orders > >from orders where o_de_id in (5,6) and o_ord_date > CURRENT_DATE-'1 > > month'::interval > >group by o_ord_date > > order by o_date desc > > > > and > > > > select o_act_del_date as o_date, count(o_id) as delivery > >from orders > >where o_de_id in (5,6) and > > o_act_del_date > CURRENT_DATE-'1 month'::interval and > > o_act_del_date <= CURRENT_DATE > >group by o_act_del_date > > order by o_date desc > > > > These give me > > > > o_date | orders > > + > > 2009-07-10 | 4 > > 2009-07-09 | 5 > > 2009-07-08 | 12 > > 2009-07-07 | 5 > > 2009-07-06 | 2 > > 2009-07-03 | 2 > > 2009-07-02 | 7 > > 2009-07-01 | 19 > > 2009-06-30 | 20 > > 2009-06-29 | 28 > > > > and > > > > o_date | delivery > > +-- > > 2009-07-13 |5 > > 2009-07-10 |3 > > 2009-07-09 |4 > > 2009-07-08 |2 > > 2009-07-07 |4 > > 2009-07-06 |7 > > 2009-07-03 |6 > > 2009-07-02 |5 > > 2009-07-01 |3 > > 2009-06-30 |3 > > > > How do i get > > > > o_date | orders | delivery > > ++-- > > 2009-07-13 ||5 > > 2009-07-10 | 4 |3 > > 2009-07-09 | 5 |4 > > 2009-07-08 | 12 |2 > > 2009-07-07 | 5 |4 > > 2009-07-06 | 2 |7 > > 2009-07-03 | 2 |6 > > 2009-07-02 | 7 |5 > > 2009-07-01 | 19 |3 > > 2009-06-30 | 20 |3 > > -- > > Gary Stainburn > > > > This email does not contain private or confidential material as it > > may be snooped on by interested government parties for unknown > > and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 > > > > -- > > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-sql -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] simple join is beating me
> From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql- > ow...@postgresql.org] On Behalf Of Gary Stainburn > Sent: Monday, July 13, 2009 9:12 AM > > If a date exists where we have orders but no deliveries the row does not > appear. > I have tried doing a union to link the two selects together, but i still > cannot get anything to work. Try this. Obviously it can be cleaned up, and it's not terribly efficient in it's current form, but you'll get the idea. To be fair, your sample output did not account for this.. You showed one example where one of the two columns could be null, not both. select myDate, orders, delivery from( select distinct o_ord_date as myDate from orders where o_de_id in (5,6) and o_ord_date > CURRENT_DATE-'1 month'::interval union all select distinct o_act_del_date delivery from orders where o_de_id in (5,6) and o_act_del_date > CURRENT_DATE-'1 month'::interval and o_act_del_date <= CURRENT_DATE ) as q1 left join ( select o_ord_date as o_date, count(o_id) as orders from orders where o_de_id in (5,6) and o_ord_date > CURRENT_DATE-'1 month'::interval group by o_ord_date ) as q2 on q2.o_date = q1.myDate left join ( select o_act_del_date as o_date, count(o_id) as delivery from orders where o_de_id in (5,6) and o_act_del_date > CURRENT_DATE-'1 month'::interval and o_act_del_date <= CURRENT_DATE group by o_act_del_date ) as q3 on q3.o_date = q1.myDate Matthew Hartman Programmer/Analyst Information Management, ICP Kingston General Hospital .now. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] simple join is beating me
Oh, I actually thought that it was the behavior you wanted, Gary. On the example you supplied you have an order on June , the 29th and it doesn't appear on the result you showed. You wanted this row do appear as an NULL delivery? Just try replacing the RIGHT JOIN by FULL JOIN. This will cause all dates to appear that either have orders or deliveries, or both. I thought that you needed the days without orders to appear, but not the days without deliveries, as you didn't include June the 29th on your desired result. But it's not serious, If I understand correctly. Just replace the RIGHT by FULL Best, Oliveiros 2009/7/13 Gary Stainburn > Hi Oliveiros, > > Thank you for this. However, this does not give me what I want. > > If a date exists where we have orders but no deliveries the row does not > appear. > > I have tried doing a union to link the two selects together, but i still > cannot get anything to work. > > Gary > > On Monday 13 July 2009 12:45:49 Oliveiros wrote: > > Howdy, Gary, > > > > I have not the database in this computer, so I cannot test the sql I'm > > sending you, but > > if you do an outer join won't it result in what you need? Maybe I am not > > reaching what you want to do... > > > > SELECT deliveryQuery.o_date , orders, delivery > > FROM (/* ur first query here */) ordersQuery > > NATURAL RIGHT JOIN (/* ur second query goes here */) deliveryQuery > > ORDER BY deliveryQuery.o_date DESC > > > > Tararabite, > > > > Oliveiros > > @Allgarve > > > > > > > > 2009/7/13 Gary Stainburn > > > > > hi folks > > > > > > i have the following: > > > > > > select o_ord_date as o_date, count(o_id) as orders > > >from orders where o_de_id in (5,6) and o_ord_date > CURRENT_DATE-'1 > > > month'::interval > > >group by o_ord_date > > > order by o_date desc > > > > > > and > > > > > > select o_act_del_date as o_date, count(o_id) as delivery > > >from orders > > >where o_de_id in (5,6) and > > > o_act_del_date > CURRENT_DATE-'1 month'::interval and > > > o_act_del_date <= CURRENT_DATE > > >group by o_act_del_date > > > order by o_date desc > > > > > > These give me > > > > > > o_date | orders > > > + > > > 2009-07-10 | 4 > > > 2009-07-09 | 5 > > > 2009-07-08 | 12 > > > 2009-07-07 | 5 > > > 2009-07-06 | 2 > > > 2009-07-03 | 2 > > > 2009-07-02 | 7 > > > 2009-07-01 | 19 > > > 2009-06-30 | 20 > > > 2009-06-29 | 28 > > > > > > and > > > > > > o_date | delivery > > > +-- > > > 2009-07-13 |5 > > > 2009-07-10 |3 > > > 2009-07-09 |4 > > > 2009-07-08 |2 > > > 2009-07-07 |4 > > > 2009-07-06 |7 > > > 2009-07-03 |6 > > > 2009-07-02 |5 > > > 2009-07-01 |3 > > > 2009-06-30 |3 > > > > > > How do i get > > > > > > o_date | orders | delivery > > > ++-- > > > 2009-07-13 ||5 > > > 2009-07-10 | 4 |3 > > > 2009-07-09 | 5 |4 > > > 2009-07-08 | 12 |2 > > > 2009-07-07 | 5 |4 > > > 2009-07-06 | 2 |7 > > > 2009-07-03 | 2 |6 > > > 2009-07-02 | 7 |5 > > > 2009-07-01 | 19 |3 > > > 2009-06-30 | 20 |3 > > > -- > > > Gary Stainburn > > > > > > This email does not contain private or confidential material as it > > > may be snooped on by interested government parties for unknown > > > and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 > > > > > > -- > > > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > > > To make changes to your subscription: > > > http://www.postgresql.org/mailpref/pgsql-sql > > > > -- > Gary Stainburn > > This email does not contain private or confidential material as it > may be snooped on by interested government parties for unknown > and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
Re: [SQL] function returning a cursor and a scalar
Surajit Bhattacharjee wrote: > I am new to Postgresql and am trying to write a function which will do a > search and return the first page of results along with the total number > of matches. How can I make my function return a cursor AND a scalar - > can I do the scalar as an OUT param and then make the function > explicitly RETURN a refcursor? Why wouldn't you just use two OUT params? BTW how do you plan on returning the number of matches? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
