Home » SQL & PL/SQL » SQL & PL/SQL » Is there a more effective or better way to write this?
Is there a more effective or better way to write this? [message #387517] Thu, 19 February 2009 12:55 Go to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
Hi,

See a small part in the where clause below I am using in an oracle report. Do you feel this is a good way to write this in the where clause or do you feel there can be a more effective way?





AND TO_DATE('01-'||NVL(AAA.PA_PERIOD,'JUL-85'),'DD-MON-RR')  <= '01-'||:PA_PERIOD_TO
                        &P_WHERE_EXPENDITURES





Thanks

Anne
Re: Is there a more effective or better way to write this? [message #387518 is a reply to message #387517] Thu, 19 February 2009 13:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course DDL is useless.
Of course comparing date and string is very smart.
Of course telling us what is "&P_WHERE_EXPENDITURES" is useless.
Of course telling us what is ":PA_PERIOD_TO" is useless.

Regards
Michel
Re: Is there a more effective or better way to write this? [message #387528 is a reply to message #387518] Thu, 19 February 2009 15:28 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
Sorry those are lexical variables for an oracle report. Ignore those.

Anne
Re: Is there a more effective or better way to write this? [message #387536 is a reply to message #387528] Thu, 19 February 2009 16:00 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
well, writing

and 1 = 2


or

and 1 = 1


would be much more effective and faster, if you can ignore all variables.
Re: Is there a more effective or better way to write this? [message #387551 is a reply to message #387528] Thu, 19 February 2009 21:18 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Anne Simms wrote on Thu, 19 February 2009 22:28
Sorry those are lexical variables for an oracle report. Ignore those.

Seems that you misunderstood Michel's point. So, briefly:
- the left side of the comparision - TO_DATE(...) - has DATE data type
- the right side of the comparision - '...'||some_var - has VARCHAR2 data type
It is necessary to make (implicit) conversion to compare them. Much more better would be, if the data types would be the same, so Oracle would not have to do it (= the data type conversion).

I may ignore data types and sample content of AAA.PA_PERIOD column, :PA_PERIOD_TO bind variable and &P_WHERE_EXPENDITURES substitution variable. However without that info, nobody is able to rewrite that condition correctly (because of not knowing what data type conversion(s) would (not) be performed).

[Edit: typo]

[Updated on: Thu, 19 February 2009 21:19]

Report message to a moderator

Re: Is there a more effective or better way to write this? [message #387674 is a reply to message #387551] Fri, 20 February 2009 04:36 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
And even then we might be wrong because we don't know that indexes there are.

For example, in some queries I use an

where function(column) = :bind_variable


which would be nuts in most cases, because it would almost always result in a full table scan. Only when one knows that there is a function based index on that column it makes sense.

So, the only "general" advice would be :

- have the same data types on both sides of the "="
- avoid having functions on the side that is queried from the table.

Previous Topic: insertion
Next Topic: Time difference between two task
Goto Forum:
  


Current Time: Sun Dec 11 00:17:40 CST 2016

Total time taken to generate the page: 0.08999 seconds