Обсуждение: Function in selection?
Hi, I want to make really advanced query and I'm not sure it's
possible... it would be "like that":
select A, B, C, D, E, F from TABLE_X ORDER BY D, C, E
if A==0 AND B!=0 {A=B*Z.TABLE_Y}
if A!=0 AND B==0 {B=A/Z.TABLE_Y}
OK, I don't really have problems with first line :), but how can I tell
him to select like the second and third line are telling it?
is that possible? :/
Tnx,
May
How is Z being joined? What's the condition?
"Dousak May (Phoebus Apollonus)" wrote:
>
> Hi, I want to make really advanced query and I'm not sure it's
> possible... it would be "like that":
>
> select A, B, C, D, E, F from TABLE_X ORDER BY D, C, E
> if A==0 AND B!=0 {A=B*Z.TABLE_Y}
> if A!=0 AND B==0 {B=A/Z.TABLE_Y}
>
> OK, I don't really have problems with first line :), but how can I tell
> him to select like the second and third line are telling it?
> is that possible? :/
>
> Tnx,
>
> May
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
May,
I think this is what you want.
SELECT CASE WHEN A=0 and B<>0 THEN B*Z.TABLE_Y ELSE A END AS A, CASE WHEN
A<>0 AND B=0 THEN A/Z.TABLE_Y ELSE B END AS B FROM TABLE_X ORDER BY D, C, E
Thanks,
Peter Darley
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Dousak May
(Phoebus Apollonus)
Sent: Thursday, March 13, 2003 10:56 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Function in selection?
Hi, I want to make really advanced query and I'm not sure it's
possible... it would be "like that":
select A, B, C, D, E, F from TABLE_X ORDER BY D, C, E
if A==0 AND B!=0 {A=B*Z.TABLE_Y}
if A!=0 AND B==0 {B=A/Z.TABLE_Y}
OK, I don't really have problems with first line :), but how can I tell
him to select like the second and third line are telling it?
is that possible? :/
Tnx,
May
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
On Thu, 2003-03-13 at 20:11, Jean-Luc Lachance wrote:
Z is row from TABLE_Y and should be read on the fly...
> How is Z being joined? What's the condition?
>
>
> "Dousak May (Phoebus Apollonus)" wrote:
> >
> > Hi, I want to make really advanced query and I'm not sure it's
> > possible... it would be "like that":
> >
> > select A, B, C, D, E, F from TABLE_X ORDER BY D, C, E
> > if A==0 AND B!=0 {A=B*Z.TABLE_Y}
> > if A!=0 AND B==0 {B=A/Z.TABLE_Y}
> >
> > OK, I don't really have problems with first line :), but how can I tell
> > him to select like the second and third line are telling it?
> > is that possible? :/
> >
> > Tnx,
> >
> > May
Thanks, I'll try it right away! May > May, > I think this is what you want. > > SELECT CASE WHEN A=0 and B<>0 THEN B*Z.TABLE_Y ELSE A END AS A, CASE WHEN > A<>0 AND B=0 THEN A/Z.TABLE_Y ELSE B END AS B FROM TABLE_X ORDER BY D, C, E > > Thanks, > Peter Darley
I have two problems with that:
-> how can I add SELECT A, B, C, D,..... to SELECT CASE WHEN....?
-> I tried Z.TABLE_Y (row Z of table_y) and TABLE_Y.Z and it just
returns errors... how do I tell him "A * Z from table_Y"? :/
Tnx,
May
> May,
> I think this is what you want.
>
> SELECT CASE WHEN A=0 and B<>0 THEN B*Z.TABLE_Y ELSE A END AS A, CASE WHEN
> A<>0 AND B=0 THEN A/Z.TABLE_Y ELSE B END AS B FROM TABLE_X ORDER BY D, C, E
>
> Thanks,
> Peter Darley
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Dousak May
> (Phoebus Apollonus)
> Sent: Thursday, March 13, 2003 10:56 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Function in selection?
>
>
> Hi, I want to make really advanced query and I'm not sure it's
> possible... it would be "like that":
>
> select A, B, C, D, E, F from TABLE_X ORDER BY D, C, E
> if A==0 AND B!=0 {A=B*Z.TABLE_Y}
> if A!=0 AND B==0 {B=A/Z.TABLE_Y}
>
> OK, I don't really have problems with first line :), but how can I tell
> him to select like the second and third line are telling it?
> is that possible? :/
>
> Tnx,
>
> May
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
You will need an ORDER BY for TABLE_Y.
For example, if you need thte 20th row from Y
SELECT CASE WHEN A=0 THEN B*( SELECT Z FROM TABLE_Y ORDER BY Z OFFSET 20
LIMIT 1) ELSE A END AS A,
CASE WHEN B=0 THEN A/( SELECT Z FROM TABLE_Y ORDER BY Z OFFSET 20
LIMIT 1) ELSE B END AS B,
C, D, E, F
FROM TABLE_X ORDER BY D, C, E
"Dousak May (Phoebus Apollonus)" wrote:
>
> I have two problems with that:
>
> -> how can I add SELECT A, B, C, D,..... to SELECT CASE WHEN....?
> -> I tried Z.TABLE_Y (row Z of table_y) and TABLE_Y.Z and it just
> returns errors... how do I tell him "A * Z from table_Y"? :/
>
> Tnx,
>
> May
>
> > May,
> > I think this is what you want.
> >
> > SELECT CASE WHEN A=0 and B<>0 THEN B*Z.TABLE_Y ELSE A END AS A, CASE WHEN
> > A<>0 AND B=0 THEN A/Z.TABLE_Y ELSE B END AS B FROM TABLE_X ORDER BY D, C, E
> >
> > Thanks,
> > Peter Darley
> >
> > -----Original Message-----
> > From: pgsql-general-owner@postgresql.org
> > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Dousak May
> > (Phoebus Apollonus)
> > Sent: Thursday, March 13, 2003 10:56 AM
> > To: pgsql-general@postgresql.org
> > Subject: [GENERAL] Function in selection?
> >
> >
> > Hi, I want to make really advanced query and I'm not sure it's
> > possible... it would be "like that":
> >
> > select A, B, C, D, E, F from TABLE_X ORDER BY D, C, E
> > if A==0 AND B!=0 {A=B*Z.TABLE_Y}
> > if A!=0 AND B==0 {B=A/Z.TABLE_Y}
> >
> > OK, I don't really have problems with first line :), but how can I tell
> > him to select like the second and third line are telling it?
> > is that possible? :/
> >
> > Tnx,
> >
> > May
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> --
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
One more problem :) I wrote my tables and variables and it looks like that: SELECT CASE WHEN cena=0 AND cenaeur<>0 THEN cena=cenaeur *(SELECT eur FROM devizni ORDER BY eur OFFSET 20 LIMIT 1) ELSE cena END AS cena, CASE WHEN cenaeur=0 AND cena<>0 THEN cenaeur=cena/(SELECT eur FROM devizni ORDER BY eur OFFSET 20 LIMIT 1) ELSE cenaeur END AS cena_eur from oglasi ORDER BY posr, id_regije; cena, cenaeur and eur are all double precision. When I try to execute it, I get following error: ERROR: CASE types "float8" and "bool" not matched I have no boolean in neither table... ok, comparision is binary, but why do I get that error? What can I do to make it work? :/ Tnx, May
There is no need for the assignment in the then clause. Ie. THEN cena=cenaeur ... should be THEN cenaeur ... and THEN cenaeur=cena ... should be THEN cena ... as: BTW, the <>0 part is redondant. SELECT CASE WHEN cena=0 THEN cenaeur*(SELECT eur FROM devizni ORDER BY eur OFFSET 20 LIMIT 1) ELSE cena END AS cena, CASE WHEN cenaeur=0 THEN cena/(SELECT eur FROM devizni ORDER BY eur OFFSET 20 LIMIT 1) ELSE cenaeur END AS cena_eur from oglasi ORDER BY posr, id_regije; "Dousak May (Phoebus Apollonus)" wrote: > > One more problem :) > > I wrote my tables and variables and it looks like that: > > SELECT CASE WHEN cena=0 AND cenaeur<>0 THEN cena=cenaeur > *(SELECT eur FROM devizni ORDER BY eur OFFSET 20 LIMIT 1) > ELSE cena END AS cena, CASE WHEN cenaeur=0 AND cena<>0 > THEN cenaeur=cena/(SELECT eur FROM devizni ORDER BY eur > OFFSET 20 LIMIT 1) ELSE cenaeur END AS cena_eur from oglasi > ORDER BY posr, id_regije; > > cena, cenaeur and eur are all double precision. > > When I try to execute it, I get following error: > > ERROR: CASE types "float8" and "bool" not matched > > I have no boolean in neither table... ok, comparision is binary, > but why do I get that error? > What can I do to make it work? :/ > > Tnx, > > May > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Cool, this works, thanks!! I remembered one more thing... I promise this is the last one :) Both tables have date entries; how can I replace OFFSET 20 with "where date <= date from current record from oglasi"? :/ I know, I know, I'm terrible...sorry :/ Tnx, May > There is no need for the assignment in the then clause. > SELECT CASE WHEN cena=0 THEN > cenaeur*(SELECT eur FROM devizni ORDER BY eur OFFSET 20 LIMIT 1) > ELSE cena END AS cena, > CASE WHEN cenaeur=0 THEN > cena/(SELECT eur FROM devizni ORDER BY eur OFFSET 20 LIMIT 1) > ELSE cenaeur END AS cena_eur > from oglasi > ORDER BY posr, id_regije;
I guess you want the most recent... SELECT CASE WHEN cena=0 THEN cenaeur*(SELECT eur FROM devizni WHERE date <= oglasi.date ORDER BY date DESC LIMIT 1) ELSE cena END AS cena, CASE WHEN cenaeur=0 THEN cena/(SELECT eur FROM devizni WHERE date <= oglasi.date ORDER BY date DESC LIMIT 1) ELSE cenaeur END AS cena_eur from oglasi ORDER BY posr, id_regije; "Dousak May (Phoebus Apollonus)" wrote: > > Cool, this works, thanks!! > > I remembered one more thing... I promise this is the last one :) > > Both tables have date entries; how can I replace OFFSET 20 with "where > date <= date from current record from oglasi"? :/ I know, I know, I'm > terrible...sorry :/ > > Tnx, > > May > > > There is no need for the assignment in the then clause. > > > SELECT CASE WHEN cena=0 THEN > > cenaeur*(SELECT eur FROM devizni ORDER BY eur OFFSET 20 LIMIT 1) > > ELSE cena END AS cena, > > CASE WHEN cenaeur=0 THEN > > cena/(SELECT eur FROM devizni ORDER BY eur OFFSET 20 LIMIT 1) > > ELSE cenaeur END AS cena_eur > > from oglasi > > ORDER BY posr, id_regije; > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html