Home » SQL & PL/SQL » SQL & PL/SQL » Help with a procedure
Help with a procedure [message #303489] Fri, 29 February 2008 09:42 Go to next message
digigeek
Messages: 6
Registered: February 2008
Junior Member
I'm not really new at Oracle, per se, but have never had to do this before:

1) I'm converting some classic ASP pages to .Net with Oracle procedures.

2) The old ASP code, of course, built the SQL queries in the code using VB if statements - adding where conditions and even tables into the query as needed and then passing it as a SQL query string. So, that's pretty normal.

3) My problem is that I need to express this in an Oracle procedure(s) and I'm not sure how since there is ONE major parameter ("view type" - there are 9 "views" - not SQL views, just a UI term in the application) and each one has 6 if conditions that affect its WHERE clause and ORDER BY heavily.

I would hate to have to make 9*6 procedures (54!), one for each "view" and each of its varying conditions. What I want is eiter one proc or 9 (one for each view) where I can somewhat dynamically build the WHERE and ORDER sections.

Here's a brief example:

PROCEDURE GetTest
    (
        pivSalesView IN NUMBER,
        pivName IN VARCHAR2,
        pivPlace IN VARCHAR2,
        pivAge IN NUMBER,
        povResults OUT Refcur
    ) 
AS
BEGIN
    CASE pivSalesView
        WHEN 1 THEN
            -- Fiscal Year and Month
            BEGIN
---
            OPEN povResults FOR
                SELECT
                    A.Name, 
                    B.Place, 
                    C.Age
                FROM 
                    Names A,
                    Places B,
                    Ages C
                WHERE 
                    --- Can I do any sort of IF block here???
                    -- I need to use different WHERE statements based on pivName, pivAge, pivPlace
                -- I need to use alter GROUP BY statement based on pivName, pivAge, pivPlace
                GROUP BY ...
                -- I need to vary ORDER statement based on pivName, pivAge, pivPlace
                ORDER BY ...
    
    --- And another CASE for each of the 9 SalesViews
Re: Help with a procedure [message #303495 is a reply to message #303489] Fri, 29 February 2008 10:29 Go to previous messageGo to next message
digigeek
Messages: 6
Registered: February 2008
Junior Member
Okay, after playing around with this for awhile this morning, I'm getting close using inline CASE and DECODE. I can't seem to get it working on the ORDER BY, though:

SELECT DB_SYSTEM_SEARCH_ID, SEARCH_NM, STORED_PROC_NM
FROM GPS.DB_SYSTEM_SEARCHES
WHERE SEARCH_NM LIKE (CASE WHEN 4<2 THEN '' ELSE '%' END)
ORDER BY (CASE WHEN 1<2 THEN 'DB_SYSTEM_SEARCH_ID' ELSE 'SEARCH_NM' END);


The query always uses default ordering...my ORDER BY has no affect. Does anyone know a way to make this work?

[Updated on: Fri, 29 February 2008 10:31]

Report message to a moderator

Re: Help with a procedure [message #303496 is a reply to message #303495] Fri, 29 February 2008 10:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remove ' in ORDER BY clause.
I assume this is just an example and constants are not really all constants.

Regards
Michel

[Updated on: Fri, 29 February 2008 10:36]

Report message to a moderator

Re: Help with a procedure [message #303498 is a reply to message #303489] Fri, 29 February 2008 10:39 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
You can build a sql query string dynamically and open the ref cursor using that sql query string. Please see the partial code below.


PROCEDURE GetTest
    (
        pivSalesView  IN  NUMBER,
        pivName       IN  VARCHAR2,
        pivPlace      IN  VARCHAR2,
        pivAge        IN  NUMBER,
        povResults    OUT Refcur
    ) 
AS
        sql_query_string  VARCHAR2 (32767);
BEGIN
    -- dynamically build sql_query_string:
    IF pivSalesView = 1 THEN
        sql_query_string := 'SELECT  A.Name, B.Place, C.Age FROM Names A, Places B, Ages C ';
        IF <some condition> THEN
            sql_query_string := sql_query_string || ' WHERE ... ';
        ELSE
            sql_query_string := sql_query_string || ' WHERE ... ';
        END IF;
        IF <some condition> THEN
            sql_query_string := sql_query_string || ' GROUP BY ... ';
        ELSE
            sql_query_string := sql_query_string || ' GROUP BY ... ';
        END IF;
        IF <some condition> THEN
            sql_query_string := sql_query_string || ' ORDER BY ... ';
        ELSE
            sql_query_string := sql_query_string || ' ORDER BY ... ';
        END IF;
     ELSIF pivSalesView = 2 THEN
        ...
     END IF;
 
    -- dynamically open ref cursor using sql_query_string:
    OPEN povResults FOR sql_query_string;
END GetTest;


Re: Help with a procedure [message #303515 is a reply to message #303498] Fri, 29 February 2008 11:49 Go to previous message
digigeek
Messages: 6
Registered: February 2008
Junior Member
Barbara Boehmer wrote on Fri, 29 February 2008 11:39
You can build a sql query string dynamically and open the ref cursor using that sql query string. Please see the partial code below.


PROCEDURE GetTest
    (
        pivSalesView  IN  NUMBER,
        pivName       IN  VARCHAR2,
        pivPlace      IN  VARCHAR2,
        pivAge        IN  NUMBER,
        povResults    OUT Refcur
    ) 
AS
        sql_query_string  VARCHAR2 (32767);
BEGIN
    -- dynamically build sql_query_string:
...





Barbara,

Thanks. I've considered writing it using dynamic SQL, but we'd like to avoid that where possible.

I had another look at all the old code and think I may be able to ditch the dynamic ORDER and GROUP BYs. That would pretty much save the day for me.

James
Previous Topic: Tricky Problem with Connect By
Next Topic: Comparing fields?
Goto Forum:
  


Current Time: Mon Dec 05 02:47:44 CST 2016

Total time taken to generate the page: 0.11867 seconds