Horton

Thread

  1. Function in selection?

    Dousak May (Phoebus Apollonus) <phoebus@email.si> — 2003-03-13T18:56:02Z

    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

  2. Re: Function in selection?

    Peter Darley <pdarley@kinesis-cem.com> — 2003-03-13T19:11:05Z

    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

  3. Re: Function in selection?

    Jean-Luc Lachance <jllachan@nsd.ca> — 2003-03-13T19:11:38Z

    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

  4. Re: Function in selection?

    Dousak May (Phoebus Apollonus) <phoebus@email.si> — 2003-03-13T19:17:31Z

    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

  5. Re: Function in selection?

    Dousak May (Phoebus Apollonus) <phoebus@email.si> — 2003-03-13T19:19:24Z

    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

  6. Re: Function in selection?

    Dousak May (Phoebus Apollonus) <phoebus@email.si> — 2003-03-13T19:30:10Z

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

  7. Re: Function in selection?

    Jean-Luc Lachance <jllachan@nsd.ca> — 2003-03-13T20:01:17Z

    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

  8. Re: Function in selection?

    Dousak May (Phoebus Apollonus) <phoebus@email.si> — 2003-03-13T21:51:23Z

    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

  9. Re: Function in selection?

    Jean-Luc Lachance <jllachan@nsd.ca> — 2003-03-13T22:36:48Z

    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)

  10. Re: Function in selection?

    Dousak May (Phoebus Apollonus) <phoebus@email.si> — 2003-03-13T23:01:33Z

    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;

  11. Re: Function in selection?

    Jean-Luc Lachance <jllachan@nsd.ca> — 2003-03-13T23:33:29Z

    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