I have a statement which inserts into table with difference count between original table and backup data
EXECUTE IMMEDIATE 'INSERT INTO LOG_table (TABLE_NAME,TYPE,COUNT,QUARTER)
SELECT NAME,TYPE,COUNT,QUARTER FROM (SELECT COUNT(*) COUNT,''table_name'' NAME,''A-B'' TYPE,'''||SUPP_QUARTER||''' QUARTER FROM(
SELECT COL1 ,COL2 ... TO_CHAR(TO_DATE(SOMENUMBER,''YYYYMM''),''YYYY"-Q"Q'') AS QT FROM TABLE
MINUS
SELECT COL1 ,COL2 ... TO_CHAR(TO_DATE(SOMENUMBER,''YYYYMM''),''YYYY"-Q"Q'') AS QT FROM BACKUP_TABLE)
UNION ALL
SELECT COUNT(*),''TABLE_NAME'',''B-A'','''||SUPP_QUARTER||''' QUARTER FROM
(SELECT COL1 ,COL2 ... TO_CHAR(TO_DATE(SOMENUMBER,''YYYYMM''),''YYYY"-Q"Q'') AS QT FROM SCRATCH.W_SUPPL_SNAPSHOT_CONTROL_G_DLY_BKP_'||TO_CHAR(LATEST_DATE_VALUE,'YYMMDD') ||'
MINUS
SELECT COL1 ,COL2 ... TO_CHAR(TO_DATE(SOMENUMBER,''YYYYMM''),''YYYY"-Q"Q'') AS QT FROM FROM DW.W_SUPPL_SNAPSHOT_CONTROL_G)' INTO SUPP_QUARTER using QT
;
END;
when i run this i get : PLS-00201: identifier 'QT' must be declared
So my question is how do i insert QT INTO LOG_TABLE.
If I don't do this and pick quarter from select statement itself i would get "not group by statement " which is correct.
Any way i can achieve this ?
Also table only has 60 records
You don't need the INTO SUPP_QUARTER using QT, even if you have defined those variables - and the error suggests you haven't defined QT at least.
The statement doesn't appear to have any bind variables, so there is nothing to supply via using. But you are embedding SUPP_QUARTER in the statement, which is potentially a SQL injection risk, so that should be a bind variable if it's needed, but you seem to be trying not to. And it's an insert statement that doesn't return anything, so there is nothing to put into a variable either; as it's the same variable you're embedding it looks like that might just be confused.
The insert ... select ... syntax doesn't care what the columns are called in the select part, it will use the value that is in the matching position for each column it's inserting. (Using keywords or function names like TYPE and COUNT as column names isn't ideal and is likely to cause confusion at some point...)
When you are forced to use dynamic SQL it's often helpful get a working static version first, then convert it. At the moment
As a static statement with fixed values 'Q1' for SUPP_QUARTER and '230222' for the dynamic part of the table name, and removing ... and an extra from, it would look like:
INSERT INTO LOG_table (TABLE_NAME, TYPE, COUNT, QUARTER)
SELECT NAME,TYPE,COUNT,QUARTER
FROM (
SELECT COUNT(*) COUNT, 'table_name' NAME, 'A-B' TYPE, 'Q1' QUARTER
FROM (
SELECT COL1, COL2, TO_CHAR(TO_DATE(SOMENUMBER, 'YYYYMM'), 'YYYY"-Q"Q') AS QT
FROM DW.W_SUPPL_SNAPSHOT_CONTROL_G
MINUS
SELECT COL1, COL2, TO_CHAR(TO_DATE(SOMENUMBER, 'YYYYMM'), 'YYYY"-Q"Q') AS QT
FROM SCRATCH.W_SUPPL_SNAPSHOT_CONTROL_G_DLY_BKP_230222
)
UNION ALL
SELECT COUNT(*), 'TABLE_NAME', 'B-A', 'Q1' QUARTER
FROM (
SELECT COL1, COL2, TO_CHAR(TO_DATE(SOMENUMBER, 'YYYYMM'), 'YYYY"-Q"Q') AS QT
FROM SCRATCH.W_SUPPL_SNAPSHOT_CONTROL_G_DLY_BKP_230222
MINUS
SELECT COL1, COL2, TO_CHAR(TO_DATE(SOMENUMBER, 'YYYYMM'), 'YYYY"-Q"Q') AS QT
FROM DW.W_SUPPL_SNAPSHOT_CONTROL_G
)
)
The two levels of subquery aren't really needed, and if you want to use the QT value from the inner query then refer to that instead of SUPP_QUARTER in the select list, and add a group-by clause for it, in each branch:
INSERT INTO LOG_table (COUNT, TABLE_NAME, TYPE, QUARTER)
SELECT COUNT(*) COUNT, 'table_name' NAME, 'A-B' TYPE, QT QUARTER
FROM (
SELECT COL1, COL2, TO_CHAR(TO_DATE(SOMENUMBER, 'YYYYMM'), 'YYYY"-Q"Q') AS QT
FROM DW.W_SUPPL_SNAPSHOT_CONTROL_G
MINUS
SELECT COL1, COL2, TO_CHAR(TO_DATE(SOMENUMBER, 'YYYYMM'), 'YYYY"-Q"Q') AS QT
FROM SCRATCH.W_SUPPL_SNAPSHOT_CONTROL_G_DLY_BKP_230222
)
GROUP BY QT
UNION ALL
SELECT COUNT(*), 'TABLE_NAME', 'B-A', QT
FROM (
SELECT COL1, COL2, TO_CHAR(TO_DATE(SOMENUMBER, 'YYYYMM'), 'YYYY"-Q"Q') AS QT
FROM SCRATCH.W_SUPPL_SNAPSHOT_CONTROL_G_DLY_BKP_230222
MINUS
SELECT COL1, COL2, TO_CHAR(TO_DATE(SOMENUMBER, 'YYYYMM'), 'YYYY"-Q"Q') AS QT
FROM DW.W_SUPPL_SNAPSHOT_CONTROL_G
)
GROUP BY QT
That no longer uses SUPP_QUARTER, but that seems to be what you want. You aren't using COL1 or COL2 but I've left them in - you might be in your real query, and if they're projected then you would need to group by those too.
You don't need the column aliases in the outer query, the insert doesn't need them, but you might prefer to leave them there to make it easier to run the query stand-alone.
That's can then be converted to dynamic SQL to add the variable backup table suffix back in; and that would be easier to read and maintain with the alternative quoting syntax:
execute immediate q'^INSERT INTO LOG_table (COUNT, TABLE_NAME, TYPE, QUARTER)
SELECT COUNT(*) COUNT, 'table_name' NAME, 'A-B' TYPE, QT QUARTER
FROM (
SELECT COL1, COL2, TO_CHAR(TO_DATE(SOMENUMBER, 'YYYYMM'), 'YYYY"-Q"Q') AS QT
FROM DW.W_SUPPL_SNAPSHOT_CONTROL_G
MINUS
SELECT COL1, COL2, TO_CHAR(TO_DATE(SOMENUMBER, 'YYYYMM'), 'YYYY"-Q"Q') AS QT
FROM SCRATCH.W_SUPPL_SNAPSHOT_CONTROL_G_DLY_BKP_^' || TO_CHAR(LATEST_DATE_VALUE,'YYMMDD') || q'^
)
GROUP BY QT
UNION ALL
SELECT COUNT(*), 'TABLE_NAME', 'B-A', QT
FROM (
SELECT COL1, COL2, TO_CHAR(TO_DATE(SOMENUMBER, 'YYYYMM'), 'YYYY"-Q"Q') AS QT
FROM SCRATCH.W_SUPPL_SNAPSHOT_CONTROL_G_DLY_BKP_^' || TO_CHAR(LATEST_DATE_VALUE,'YYMMDD') || q'^
MINUS
SELECT COL1, COL2, TO_CHAR(TO_DATE(SOMENUMBER, 'YYYYMM'), 'YYYY"-Q"Q') AS QT
FROM DW.W_SUPPL_SNAPSHOT_CONTROL_G
)
GROUP BY QT^';