Home » SQL & PL/SQL » SQL & PL/SQL » where condition | how to form the condition? (Oracle 9i)
where condition | how to form the condition? [message #366342] Fri, 12 December 2008 21:54 Go to next message
umajava
Messages: 8
Registered: December 2008
Junior Member
I am newbie to PL/SQL. I have a requirement where in I wanted to use the column that I select be used in the where conditition.

e.g. Sample query not real one

select col1 as x, col2 as y, get_value_from_some_package() as z from temptable
where z > 10

In the above query "z" is the alias name of the column that I receive from some external function and I am trying to use it in the where condition.

Oracle gives me an error when I use this way. Is there any other alternative approach to this please?

Thanks
Uma
Re: where condition | how to form the condition? [message #366345 is a reply to message #366342] Fri, 12 December 2008 22:22 Go to previous messageGo to next message
anasjamil
Messages: 28
Registered: April 2008
Location: KARACHI
Junior Member

hi,
i think having clause will work instead of where
as function is used. hope it will help.

regards
anas jamil
Re: where condition | how to form the condition? [message #366349 is a reply to message #366345] Fri, 12 December 2008 22:51 Go to previous messageGo to next message
umajava
Messages: 8
Registered: December 2008
Junior Member
I get an error message "not a group by expression".

I do not have any group by clause used and I do not have such requirement.

Any suggestions please?
Re: where condition | how to form the condition? [message #366350 is a reply to message #366342] Fri, 12 December 2008 22:53 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
I think, HAVING clause will also fail to do this task.

In this situation, you can use correlated sub query like,

select tbl_outer.col1, tbl_outer.col2, 
(select package_val() as col3 from tbl tbl_inner where tbl_inner.pk_col = tbl_outer.pk_col)
from tbl tbl_outer
where col3 > 10

pk_col refers to primary key column

regards,
Delna

[Updated on: Sat, 13 December 2008 01:16] by Moderator

Report message to a moderator

Re: where condition | how to form the condition? [message #366351 is a reply to message #366350] Fri, 12 December 2008 23:04 Go to previous messageGo to next message
umajava
Messages: 8
Registered: December 2008
Junior Member
In below query

(select package_val() as col3 from tbl tbl_inner where tbl_inner.pk_col = tbl_outer.pk_col)

I do not have any column or primary key that I can map with the outer table.

I actually pass one of the column as parameter to the function and the function returns some number value.
Re: where condition | how to form the condition? [message #366355 is a reply to message #366342] Sat, 13 December 2008 00:01 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
I actually pass one of the column as parameter to the function and the function returns some number value.

You confused me now, as the only function in the code does not have any parameter.

Originally I understood it that the function returns the name of the affected column; then you shall compare it with all column names and pick the appropriate column value, something like
WHERE DECODE( select package_val(), 'COL1', col1, 'COL2', col2 ) > 10

If you would call it from PL/SQL (it does not seem to be this case however) and the function would not dependent on the table content (it would return the same value for all rows), you could call it before the executed query and create the SELECT statement dynamically.

[Edit: Added another precondition to the last paragraph]

[Updated on: Sat, 13 December 2008 00:13]

Report message to a moderator

Re: where condition | how to form the condition? [message #366380 is a reply to message #366351] Sat, 13 December 2008 03:21 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
You please describe scenario properly.

Confused It is making me confused
Re: where condition | how to form the condition? [message #366385 is a reply to message #366342] Sat, 13 December 2008 04:10 Go to previous messageGo to next message
anasjamil
Messages: 28
Registered: April 2008
Location: KARACHI
Junior Member

hi,

yeah it will be better to describe it in details.
Making me confused too. Razz

regards,
anas jamil
Re: where condition | how to form the condition? [message #368451 is a reply to message #366355] Sat, 13 December 2008 07:44 Go to previous messageGo to next message
umajava
Messages: 8
Registered: December 2008
Junior Member
I am sorry to confuse you.

The original query is around 600 lines so I couldnt paste it here. Let me rephrase the sql and how it would be

SELECT T1.COL1 A
       T1.COL2 B
       T2.COL1 C
       T2.COL3 D
       T2.COL4 E
       CALL_MY_FUNCTION(T1.COL3, T2.COL5) Z
FROM TABLE1 T1, TABLE2 T2
WHERE T1.COL1 = T2.COL2
AND Z > 10;


The function takes say two values and returns a NUMBER. I get an invalid identifier for "Z" in the where clause.

Is there anyway I can use "Z" in where clause?
Re: where condition | how to form the condition? [message #369634 is a reply to message #368451] Sat, 13 December 2008 08:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't.
Either repeat the expression in both places or use an inline view.

Regards
Michel
Re: where condition | how to form the condition? [message #375718 is a reply to message #369634] Sat, 13 December 2008 10:12 Go to previous messageGo to next message
umajava
Messages: 8
Registered: December 2008
Junior Member
Can you give me an example of inline view please? And how I can apply to my scenario?
Re: where condition | how to form the condition? [message #375719 is a reply to message #375718] Sat, 13 December 2008 10:16 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select * from (select ... from ...) ...

Regards
Michel

[Updated on: Sat, 13 December 2008 10:16]

Report message to a moderator

Previous Topic: Does Selecting From Views Affect Performance?
Next Topic: Challenging Query
Goto Forum:
  


Current Time: Mon Dec 05 13:18:07 CST 2016

Total time taken to generate the page: 0.08021 seconds