Netezza SQL传递变量名报错求助：DECLARE语句语法异常

Hey there! Let's break down why you're hitting that error and how to fix it.

First off, the root issue is that Netezza doesn't support the SQL Server-style DECLARE variable syntax directly in ad-hoc SELECT statements. Your code uses a syntax that works for SQL Server, but Netezza has its own rules for handling variables. Also, a quick side note: in Netezza, you should use single quotes (') for string/date constants instead of double quotes ("), which was another small issue in your original code.

Here are two common, working approaches to use variables in Netezza:

1. Use Session Variables (Ad-Hoc Queries) #

For one-off queries, you can set a session-level variable and reference it with a colon (:):

-- Set the session variable
SET VAR filter = 'A,B,U,F';

-- Reference it in your SELECT
SELECT :filter FROM YOUR_TABLE WHERE DATE = '2019-10-31';

This works directly in Netezza SQL clients (like Aginity Workbench or nzsql) without needing a stored procedure.

2. Use Variables in a Stored Procedure #

If you need to wrap this logic in a reusable procedure, Netezza uses NZPLSQL (its own procedural language). Here's how your code would look adjusted for that:

CREATE OR REPLACE PROCEDURE get_filtered_data()
LANGUAGE NZPLSQL
AS
BEGIN_PROC
  DECLARE 
    filter CHAR(7);
  BEGIN
    -- Assign value to the variable (note the := operator instead of SET)
    filter := 'A,B,U,F';
    
    -- Use EXECUTE IMMEDIATE for dynamic SQL if needed
    EXECUTE IMMEDIATE 'SELECT ' || filter || ' FROM YOUR_TABLE WHERE DATE = ''2019-10-31''';
  END;
END_PROC;

Then you can run the procedure with:

CALL get_filtered_data();

Quick Extra Tip #

If your actual goal is to filter rows using the values in filter (like WHERE some_column IN ('A','B','U','F')), you'll need to split the comma-separated string. Netezza has functions like STRING_TO_TABLE that can help with that. For example:

SET VAR filter = 'A,B,U,F';
SELECT * FROM YOUR_TABLE 
WHERE DATE = '2019-10-31'
AND some_column IN (SELECT value FROM STRING_TO_TABLE(:filter, ','));

内容的提问来源于stack exchange，提问作者Lonewolf