英文:
Using Execute immediate along with variable
问题
当运行此代码时，出现了PLS-00201: 标识符 'QT' 必须声明的错误。因此，您想知道如何将QT插入到LOG_TABLE中。如果不这样做并且直接从SELECT语句中选择季度，您将获得“不是组合语句”这是正确的。
有没有办法实现这一点？
另外，表只有60条记录。
以下是修复错误的SQL代码：
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,
TO_CHAR(TO_DATE(SOMENUMBER, ''YYYYMM''), ''YYYY-Q"Q"'') AS QUARTER
FROM TABLE
MINUS
SELECT COUNT(*), ''TABLE_NAME'', ''B-A'',
TO_CHAR(TO_DATE(SOMENUMBER, ''YYYYMM''), ''YYYY-Q"Q"'') AS QUARTER
FROM DW.W_SUPPL_SNAPSHOT_CONTROL_G)
INTO SUPP_QUARTER using QT;
END;
这段代码中，我将QT的声明从子查询移到了INTO子句，并修复了TO_CHAR函数的语法错误。这应该能够解决PLS-00201错误，并将QT插入到LOG_TABLE中。请确保QT在执行INSERT语句之前已经声明和赋值。
英文:
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
答案1
得分: 2
你不需要使用 INTO SUPP_QUARTER using QT，即使你已经定义了这些变量 - 而且错误提示表明你至少没有定义 QT。
该语句似乎没有绑定变量，因此没有需要通过 using 提供的内容。但你在语句中嵌入了 SUPP_QUARTER，这可能存在SQL注入的风险，所以如果需要的话，应该将其设置为绑定变量，但你似乎在尝试不这样做。而且这是一条插入语句，不返回任何内容，因此也没有东西可以放到一个变量中；由于它与嵌入的变量相同，看起来可能会让人感到困惑。
insert ... select ... 语法不关心 select 部分的列叫什么名字，它将使用要插入的每列的匹配位置上的值。在列名中使用关键字或函数名，如 TYPE 和 COUNT，并不理想，很可能会在某个时候引起混淆...
当你被迫使用动态SQL时，通常有助于先获得一个工作的静态版本，然后进行转换。目前来看
作为一个具有固定值 'Q1' 用于 SUPP_QUARTER 和 '230222' 用于表名动态部分的静态语句，去掉 ... 和额外的 from 后，它看起来像这样：
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
)
)
两层子查询实际上不是必需的，如果你想要使用内部查询中的 QT 值，那么在选择列表中引用它而不是 SUPP_QUARTER，并在每个分支中添加一个 group by 子句：
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
这不再使用 SUPP_QUARTER，但这似乎是你想要的。你没有使用 COL1 或 COL2，但我把它们留在那里 - 你在你的真正查询中可能需要它们，如果它们被投影出来，那么你也需要对它们进行分组。
你不需要在外部查询中使用列别名，插入不需要它们，但你可能更愿意将它们保留在那里，以便更容易运行独立的查询。
然后，可以将其转换为动态SQL，以添加变量备份表后缀；并且使用替代引号语法会更容易阅读和维护：
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_SN
<details>
<summary>英文:</summary>
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^';
[fiddle](https://dbfiddle.uk/jCuIKAXs)
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论