From: Franco Bruno Borghesi <franco@akyasociados.com.ar>
To: C F <tacnaboyz@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: Re: SQL Help
Date: Fri, 30 May 2003 14:12:30 -0300
Message-ID: <200305301412.31184.franco@akyasociados.com.ar> (raw)
In-Reply-To: <20030530154703.37238.qmail@web20415.mail.yahoo.com>
References: <20030530154703.37238.qmail@web20415.mail.yahoo.com>
If your concern is speed, the thing here is that you will have as many records
as there are in "mytable", most of them (I think) with NULLs for alias1, alias2,
alias3 and alias4. This way, there is no condition to filter any record, so
postgreSQL will do a sequential scan over the whole table.
If you are ok fetching the records that match and assumming that the all the others
don't match, then the following will work:
--column1=column2
SELECT
column3 AS alias1, column4 AS alias2, column5 AS alias3, NULL AS alias4
FROM
myTable
WHERE
column1=column2
UNION
--column6=column7
SELECT
NULL AS alias1, NULL AS alias2, NULL AS alias3, column8 AS alias4
FROM
myTable
WHERE
column6=column7
Of course, you will need the necesary indexes.
If this didn't give you a hint, please post a message with a link to your original
message, so I can get a better idea of what you need.
On Friday 30 May 2003 12:47, C F wrote:
> Hello,
> I already tried this same basic question with no response.... maybe I was
> too wordy. So here it is simplified.... what's the best way to write this
> query? I'm open to using stored procedures, but even then I don't know how
> I would conditionally populate a resultset (refcursor). Notice that in the
> first three cases, the expression is the exact same, only the return value
> is different. This seems inefficient....
>
>
> select
> (case when column1 = column2 then column3 end) as alias1,
> (case when column1 = column2 then column4 end) as alias2,
> (case when column1 = column2 then column5 end) as alias3,
> (case when column6 = column7 then column8 end) as alias4
> from
> mytable
> ;
>
> Any ideas?
> Thanks!
>
>
> ---------------------------------
> Do you Yahoo!?
> Free online calendar with sync to Outlook(TM).
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: franco@akyasociados.com.ar, tacnaboyz@yahoo.com
Subject: Re: SQL Help
In-Reply-To: <200305301412.31184.franco@akyasociados.com.ar>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox