Solved

Query formaton

Posted on 2012-03-25
2
257 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 20

Accepted Solution

by:
flow01 earned 400 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

689 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