DB2 9.7 SQL语法，我在做什么错？ [英] DB2 9.7 SQL syntax, what am I doing wrong?

问题描述

出于某种我无法控制的原因，我试图在过去12个月中简单地提取数据。但是，从本质上讲，要缩小到数据的大小，我必须每天查询一个临时表并从那里进行查询。现在，我是使用DB2编写脚本的新手，但通常不是SQL，因此我尝试了下面的代码（对我来说逻辑似乎很好）。

For one reason or other which are out of my control I am attempting to simply pull data over the past 12 months. However, essentially down to the size of data, I have to query each day into a temp table and go from there. Now I'm a newbie to scripting in DB2, but not SQL in general, so I've tried the code below (the logic seems fine to me).

最初我只是对要生成多少记录感兴趣，但是理想情况下，我想运行第二个SELECT代码。我一直在使用Data Studio，但是我相信要导出数据，我需要通过CLP来运行它。现在我的问题是我丢失了我无法弄清楚的语法中的某些内容，这正在全神贯注。这很可能是非常基本的内容，或者我只是完全错误地这样做了。

Initially I'm was just interested in how many records will be generated, but ideally I'd want to run the second SELECT code. I've been using Data Studio, but I believe to export the data I would need to run this via CLP. Now my issues are that I'm missing something within syntax that I can't figure out and it's doing my head in. It's most likely something very basic or I'm just doing it totally wrong.

如果我尝试执行SELECT COUNT（*）代码，我将得到以下错误：-
这行有多个标记
-适用于Linux，UNIX和Windows的DB2：预期在 FROM之后。
-Linux，UNIX和Windows的DB2：。无效。

If I try the SELECT COUNT(*) code I'm getting these errors: - Multiple markers at this line - DB2 for Linux, UNIX, and Windows: "" was expected after "FROM". - DB2 for Linux, UNIX, and Windows: "." is invalid.

如果尝试其他的SELECT代码，则会出现以下错误：-
适用于Linux，UNIX和Windows的DB2：应为而不是 SELECT MARKET_ID，
SUPER_REGION，
REGION，
MARKET，
POSA，
DEST_ID，
DEST_NAME，
DEST_TYPE，
STAT ...
用于Linux，UNIX和Windows的DB2：，应在 FROM之后。
DB2用于Linux，UNIX和Windows的DB2：，应在 GROUP之后

If I try the other SELECT code I'm getting these errors: - DB2 for Linux, UNIX, and Windows: "" was expected instead of "SELECT MARKET_ID, SUPER_REGION, REGION, MARKET, POSA, DEST_ID, DEST_NAME, DEST_TYPE, STAT... DB2 for Linux, UNIX, and Windows: "," was expected after "FROM". DB2 for Linux, UNIX, and Windows: "," was expected after "GROUP"

我只是不明白。有人可以帮忙吗？之后，SESSION.l12_Dest表也似乎不可用，我无法尝试查看

I just don't get it. Can someone please help? The SESSION.l12_Dest table also doesn't seem to be available afterwards for me to try looking at the table manually.

代码：-

--<ScriptOptions statementTerminator="@"/>

CREATE OR REPLACE PROCEDURE HWW.DM_CHECKLIST()
    LANGUAGE SQL
BEGIN
DECLARE GLOBAL TEMPORARY TABLE SESSION.L12_DEST 
(
    ACTUAL_DATE DATE, 
    MARKET_ID INTEGER,
    SUPER_REGION VARCHAR (100), 
    REGION VARCHAR (100), 
    MARKET VARCHAR (100),
    POSA VARCHAR (100),
    DEST_ID INTEGER,
    DEST_NAME VARCHAR (100),
    DEST_TYPE VARCHAR (30),
    STATUS_CODE SMALLINT,
    VISITORS INTEGER
)
ON COMMIT PRESERVE ROWS NOT LOGGED;
COMMIT;

FOR V_ROW AS 
    SELECT ACTUAL_DATE
    FROM DM.DATE_DIM
    WHERE ACTUAL_DATE 
        BETWEEN (CURRENT_DATE - 12 MONTHS) - DAY((CURRENT_DATE - 12 MONTHS)) DAYS + 1 DAYS 
            AND LAST_DAY((CURRENT_DATE - 1 MONTHS))
    DO           
        INSERT INTO SESSION.L12_DEST
        SELECT  B.ACTUAL_DATE,
                Z.HCOM_SRCH_DEST_PROPERTY_MKT_ID,
                Z.HCOM_SRCH_DEST_PROPERTY_MKT_SUPER_REGN_NAME,
                Z.HCOM_SRCH_DEST_PROPERTY_MKT_REGN_NAME,
                Z.HCOM_SRCH_DEST_PROPERTY_MKT_NAME,
                S.SITE_CNTRY_NAME,
                Z.HCOM_SRCH_DEST_ID,
                Z.HCOM_SRCH_DEST_NAME,
                Z.HCOM_SRCH_DEST_TYP_NAME,
                LZ.STATUS_CODE,
                COUNT(DISTINCT(F.VISITOR_KEY))AS VISITORS
        FROM DM.LODG_DEMAND_FACT F
        INNER JOIN DM.V_HCOM_SRCH_DEST_DIM Z 
            ON F.HCOM_SRCH_DEST_KEY = Z.HCOM_SRCH_DEST_KEY
        INNER JOIN DM.DATE_DIM B
            ON F.LOCAL_DEMAND_DATE_KEY = B.DATE_KEY
        INNER JOIN DM.SITE_DIM S
            ON S.SITE_KEY = F.SITE_KEY
        LEFT JOIN LZ.LZ_HCM_DESTINATION LZ
            ON Z.HCOM_SRCH_DEST_ID = LZ.DESTINATION_INT_ID
        WHERE F.BRAND_KEY = 101
            AND B.ACTUAL_DATE = V_ROW.ACTUAL_DATE
        GROUP BY    B.ACTUAL_DATE,
                    Z.HCOM_SRCH_DEST_PROPERTY_MKT_ID,
                    Z.HCOM_SRCH_DEST_PROPERTY_MKT_SUPER_REGN_NAME,
                    Z.HCOM_SRCH_DEST_PROPERTY_MKT_REGN_NAME,
                    Z.HCOM_SRCH_DEST_PROPERTY_MKT_NAME,
                    S.SITE_CNTRY_NAME,
                    Z.HCOM_SRCH_DEST_ID,
                    Z.HCOM_SRCH_DEST_NAME,
                    Z.HCOM_SRCH_DEST_TYP_NAME,
                    LZ.STATUS_CODE;
END FOR;

--SELECT COUNT(*) FROM SESSION.L12_DEST;

--EXPORT TO C:\TEMP\MARKETS.TXT OF DEL 
SELECT  MARKET_ID,
        SUPER_REGION,
        REGION,
        MARKET,
        POSA,
        DEST_ID,
        DEST_NAME,
        DEST_TYPE,
        STATUS_CODE,
        SUM(VISITORS)
FROM SESSION.L12_DEST 
GROUP BY    MARKET_ID,
            SUPER_REGION,
            REGION,
            MARKET,
            POSA,
            DEST_ID,
            DEST_NAME,
            DEST_TYPE,
            STATUS_CODE;

END @

推荐答案

到在存储过程中使用EXPORT实用程序，必须将其包装在ADMIN_CMD过程中：

To use the EXPORT utility in a stored procedure, you have to wrap it in the ADMIN_CMD procedure:

create procedure dm_checklist()
    language sql
begin

    declare global temporary table results (...) 
        on commit preserve rows;

    -- Steps to build / populate your temporary table

    call sysproc.admin_cmd('export to C:\file.del of del select * from session.results');

end

这篇关于DB2 9.7 SQL语法，我在做什么错？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！