Function in selection?

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

How is Z being joined? What's the condition?

"Dousak May (Phoebus Apollonus)" wrote:

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...

Thanks, I'll try it right away!

May

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:

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:

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

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: