?
Solved

Query formaton

Posted on 2012-03-25
2
Medium Priority
?
259 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 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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.
Suggested Courses

743 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