Solved

Query formaton

Posted on 2012-03-25
2
253 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 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Suggested Solutions

Title # Comments Views Activity
How to free up undo space? 3 50
best datatype for oracle table email creation 8 57
Sybase and replication server 13 39
oracle numeric condition check 4 27
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…
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 …
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 copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

832 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