[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Query formaton

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

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

649 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