0

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.

1

2 Answers 2

2

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;
/
0

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.