Thread: Function in selection?

Function in selection?

From

Dousak "May (Phoebus Apollonus)"

Date:

13 March 2003, 16:56:35

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

Re: Function in selection?

From

Jean-Luc Lachance

Date:

13 March 2003, 17:10:00

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

Re: Function in selection?

From

"Peter Darley"

Date:

13 March 2003, 17:11:07

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

Re: Function in selection?

From

Dousak "May (Phoebus Apollonus)"

Date:

13 March 2003, 17:18:02

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

Re: Function in selection?

From

Dousak "May (Phoebus Apollonus)"

Date:

13 March 2003, 17:20:00

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

Re: Function in selection?

From

Dousak "May (Phoebus Apollonus)"

Date:

13 March 2003, 17:30:41

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

Re: Function in selection?

From

Jean-Luc Lachance

Date:

13 March 2003, 18:00:00

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

Re: Function in selection?

From

Dousak "May (Phoebus Apollonus)"

Date:

13 March 2003, 19:51:55

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

Re: Function in selection?

From

Jean-Luc Lachance

Date:

13 March 2003, 20:35:32

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)

Re: Function in selection?

From

Dousak "May (Phoebus Apollonus)"

Date:

13 March 2003, 21:02:02

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;

Re: Function in selection?

From

Jean-Luc Lachance

Date:

13 March 2003, 21:32:03

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