?
Solved

Query formaton

Posted on 2012-03-25
2
Medium Priority
?
262 Views
Last Modified: 2012-06-27
Hi,

Please find the attached sql query , which is calling a function.
can I combine the functionaliteis of the function within the query,if so please help me to frame that query.

Regards,
query1.sql
Funct1.sql
0
Comment
Question by:neoarwin
2 Comments
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 37768276
In theory, yes, you can include code from a PL\SQL function directly into a query that calls a function *IF* the function can be reduced to simple SQL.  In this case however, the function includes a cursor loop and some conditional logic based on values in the last record retrieved by the cursor, so no, you certainly can't easily include this logic in a single-level query.

It may be possible to write a "max" subquery that returns just one row instead of a cursor loop, and put that inside of a "case" statement to evaluate what it returns, then put this into your query, but this is certainly not a task for a novice SQL coder!  I'm not suggesting that you are a SQL novice, but this would be a challenge for many advanced SQL developers.  Also, maintenance and/or changes of something this complex could be a problem in the future.
0
 
LVL 21

Accepted Solution

by:
flow01 earned 1200 total points
ID: 37768529
In the use of this function  within this query however you can  cut some wood i think, because so many conditions are handled in the query itself..
However this will mean that the determination of lv_st is coded in 2 places : in the function and in this sql.

Looking more closely at the function there rise questions : as far as I can see I expect the result always to be lv_st = 2.
The count of rows of the group by query in the function never gets 0 (that row won't be found *1) and the notfound exits the loop without altering lv_show_ordr : so lv_show_ordr will always be FALSE and thus lv_st = 2 ?

 *1) Within an outer join is possible to count a column of the optional table (if there are only null values the count will get 0)
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses
Course of the Month15 days, 5 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question