Home » SQL & PL/SQL » SQL & PL/SQL » Tricky SELECT Statement
Tricky SELECT Statement [message #190090] Tue, 29 August 2006 04:46 Go to next message
aarontan78
Messages: 63
Registered: August 2005
Member

Hi all,

I have a tricky SQL statement below:

SELECT field_x FROM TABLE B WHERE field_y=0;
will generate result = ABC. ABC is a field IN TABLE A

However WHEN I try TO USE the below query:
SELECT (SELECT field_x FROM TABLE B WHERE field_y=0) FROM TABLE A;

it gives me a different result (All records is ABC), compare to the below query result that I want:
SELECT (ABC) FROM TABLE A;

Please advise.
Thanks.

Re: Tricky SELECT Statement [message #190093 is a reply to message #190090] Tue, 29 August 2006 04:49 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
That's the predicted result.
SELECT (SELECT b.column1 FROM b WHERE column2=0)
FROM   a
The query above will perform a full scan of table a. For each row it will perform the select from b.

MHE
Re: Tricky SELECT Statement [message #190241 is a reply to message #190093] Tue, 29 August 2006 19:50 Go to previous messageGo to next message
aarontan78
Messages: 63
Registered: August 2005
Member

Thanks for the reply.
However, this is the problem I got.

The query you give will give me result like:

SELECT 'column1' FROM a

instead of:

SELECT column1 FROM a

The query I want is: SELECT column1 FROM a

Thanks.
Re: Tricky SELECT Statement [message #190284 is a reply to message #190241] Wed, 30 August 2006 01:04 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You will need dynamic SQL for that. You cannot use variables as table- or columnnames.
Re: Tricky SELECT Statement [message #190285 is a reply to message #190284] Wed, 30 August 2006 01:06 Go to previous messageGo to next message
aarontan78
Messages: 63
Registered: August 2005
Member

Thanks.
How to use dynamic sql?
Fyi, I use this statement in oracle form.
Re: Tricky SELECT Statement [message #190287 is a reply to message #190285] Wed, 30 August 2006 01:12 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I would start at reading the manuals.

Be aware though, dynamic sql can very easily lead to a non-performant, non-maintainable application if not used with care.
Very very often the use of it either is not necessary, of is the result of 'suboptimal' design-decisions.
Re: Tricky SELECT Statement [message #190322 is a reply to message #190090] Wed, 30 August 2006 04:00 Go to previous messageGo to next message
jai_o7
Messages: 3
Registered: August 2006
Location: Mumbai
Junior Member
Hi,

Can u tell me the Column common in both the tables (I mean primary key)?

I think it isn't a big deal then....
Re: Tricky SELECT Statement [message #190332 is a reply to message #190322] Wed, 30 August 2006 04:21 Go to previous message
aarontan78
Messages: 63
Registered: August 2005
Member

Thanks.
For the time being I haven't put any PK.
The statement:

SELECT field_x FROM TABLE B WHERE field_y=0;

will only yield one record, I think it doesnt matter much.
Previous Topic: Sending data from Forms and reports to MS-Word
Next Topic: Query
Goto Forum:
  


Current Time: Tue Dec 06 00:04:05 CST 2016

Total time taken to generate the page: 0.12014 seconds