Re: Function in selection?

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(at)postgresql(dot)org
> > [mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Dousak May
> > (Phoebus Apollonus)
> > Sent: Thursday, March 13, 2003 10:56 AM
> > To: pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org)
> --
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster