Changing tablename using dynamic sql instead of subquery [duplicate]

Question

I have been stuck on this error for few hours.

I have parameterized this select query

--Original query
SELECT DISTINCT col1 FROM table1;

to this which erroring out ORA-00904: "A"."col1": invalid identifier 00904. 00000 - "%s: invalid identifier"

--Parameterized query
SELECT DISTINCT A.col1 as col1  FROM (SELECT cont_code FROM dt5_campaign_code 
                                    where code_id = &test_cont_id) A;

Nested query outputs

SELECT cont_code FROM dt5_campaign_code 
                                    where code_id = &test_cont_id;

Found a solution here. Please help in translating this for my query.

The identifier used by the nested query is CONT_CODE. If you want the identifier CONT_CODE to become COL1, then you will need to alias that column in the nested query. SELECT cont_code AS col1. — Jason Seek Well
– Jason Seek Well, Commented Nov 21, 2022 at 16:56

MT0 · Accepted Answer · 2022-11-22 09:21:49Z

1

SELECT DISTINCT A.col1 as col1
FROM   (
  SELECT cont_code
  FROM   dt5_campaign_code 
  where  code_id = &test_cont_id
) A;

The sub-query A does not contain a col1 column.

You either want to use cont_code in the outer query:

SELECT DISTINCT A.cont_code as col1
FROM   (
  SELECT cont_code
  FROM   dt5_campaign_code 
  where  code_id = &test_cont_id
) A;

or, alias cont_code to col1 in the inner query:

SELECT DISTINCT A.col1 as col1
FROM   (
  SELECT cont_code AS col1
  FROM   dt5_campaign_code 
  where  code_id = &test_cont_id
) A;

or, get rid of the sub-query:

SELECT DISTINCT cont_code as col1
FROM   dt5_campaign_code 
WHERE  code_id = &test_cont_id;

As a dynamic statement:

DECLARE
  v_table_name VARCHAR2(30);
  v_cur        SYS_REFCURSOR;
  v_str        VARCHAR2(4000);
BEGIN
  SELECT cont_code
  INTO   v_table_name
  FROM   dt5_campaign_code
  WHERE  code_id = 1; --&test_cont_id;

  OPEN v_cur FOR 'SELECT DISTINCT col1 FROM ' || v_table_name;
  LOOP
    FETCH v_cur INTO v_str;
    EXIT WHEN v_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_str);
  END LOOP;
  CLOSE V_cur;
END;
/

fiddle

edited Nov 22, 2022 at 9:21

answered Nov 21, 2022 at 18:17

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

yash agarwal Over a year ago

cont_code should output a table name. Both tables don't have any common columns. I just created a lookup table(dt5_campaign_code) to get the value from a cell i.e. used by outer select query.

yash agarwal Over a year ago

Nested query should o/p a table name which is used by outer select.

MT0 Over a year ago

@yashagarwal Your question does not make that clear and, in that case, you will need to use dynamic SQL.

yash agarwal Over a year ago

Can you please help in some way. I'm not familiar with dynamic sql

MT0 Over a year ago

@yashagarwal No, it will not as you cannot merge SQL and PL/SQL statements like that. (But you can try that yourself and see that it fails.)

|

Collectives™ on Stack Overflow

Changing tablename using dynamic sql instead of subquery [duplicate]

1 Answer 1

11 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Linked

Related