How can i branch based on a columns value in oracle?

Question

I am trying to have a dynamic inner join..on in an Oracle database.

For this purpose, I am getting the table name, and User id which acts as an access filter (return this person's records for example), and a cursor which returns the resulting dataTabe).

There is a slight problem here. The userId which is passed belongs to two different groups - buyers and sellers. So, whenever I want to run my stored procedure and get the needed record I need to know if it's a buyer or a seller id, then have the correct condition applied in my where clause.

To cut it short, this is my half-complete query:

CREATE OR REPLACE PROCEDURE MYDB.GET_DEAL_LIST 
 ( PersonID NUMBER,
 TableName NVARCHAR2,
 BuyerOrSeller INTEGER,
 a_CURSOR OUT sys_refcursor
 )
AS
BEGIN
 OPEN a_CURSOR FOR
 SELECT TBLDEALS.*, TableName.* FROM TBLDEALS
 inner JOIN Tablename on 
 Tablename.ID = TBLDEALS.id
 --Now here I need a branch, something like this:
 --if (buyerOrSeller == 1 )//then this is a buyer
 and personID = TBLDEALS.Buyer_ID
 --else if (buyerOrSeller = 2 ) /then it is a seller and we need to return his record
 and PersonID = tblDeals.Seller_ID 
END;
/

For this I used case like this :

CREATE OR REPLACE PROCEDURE MYDB.GET_DEAL_LIST 
 ( PersonID NUMBER,
 TableName NVARCHAR2,
 BuyerOrSeller INTEGER,
 a_CURSOR OUT sys_refcursor
 )
AS
BEGIN
 OPEN a_CURSOR FOR
 SELECT TBLDEALS.*,TableName.* FROM TBLDEALS
 inner JOIN Tablename on 
 Tablename.ID = TBLDEALS.id and
 case when BuyerOrSeller = 1 then TBLDEALS.Buyer_ID = PersonID
 when BuyerOrSeller = 2 then TBLDEALS.Buyer_ID = PersonID;
END;
/

But it seems to be wrong!( I get a

Error(14,56): PL/SQL: ORA-00905: missing keyword

near equality sign in TBLDEALS.Buyer_ID = PersonID

And I am stuck on how to specify my where condition according to the buyerOrSeller variable.

• Mat: With a variable table name, you have to use dynamic SQL anyway, so just build the right query. (docs.oracle.com/cd/E11882_01/appdev.112/e25519/…)

Answer 1

As per the comment, perhaps something like this:

CREATE OR REPLACE PROCEDURE MYDB.GET_DEAL_LIST 
 ( PersonID NUMBER,
 TableName NVARCHAR2,
 BuyerOrSeller INTEGER,
 a_CURSOR OUT sys_refcursor
 )
AS
 v_stmt_str VARCHAR2(200);
BEGIN
 if BuyerOrSeller = 1 then
 v_stmt_str := 'SELECT TBLDEALS.*,t.* FROM TBLDEALS inner JOIN ' || tablename || ' t on t.ID = TBLDEALS.id and TBLDEALS.Buyer_ID = :PersonID)';
 else
 v_stmt_str := 'SELECT TBLDEALS.*,t.* FROM TBLDEALS inner JOIN ' || tablename || ' t on t.ID = TBLDEALS.id and TBLDEALS.Seller_ID = :PersonID)';
 end if;
 OPEN a_CURSOR FOR v_stmt_str using PersonID;
END;
/

Answer 2

The problem with your case statement is the syntax is wrong.

CREATE OR REPLACE PROCEDURE MYDB.GET_DEAL_LIST 
 ( PersonID NUMBER,
 TableName NVARCHAR2,
 BuyerOrSeller INTEGER,
 a_CURSOR OUT sys_refcursor
 )
AS
BEGIN
 OPEN a_CURSOR FOR
 SELECT TBLDEALS.*,TableName.* FROM TBLDEALS
 inner JOIN Tablename on 
 Tablename.ID = TBLDEALS.id 
 and PersonID = (CASE BuyerOrSeller
 WHEN 1 THEN TBLDEALS.Buyer_ID
 WHEN 2 THEN TBLDEALS.Seller_ID
 END);
END;
/

The above case statement would work, but the better solution is presented by teagles (as there are still problems with the rest of the query).