SQL Server 2008 - 实施解码

Question

首先，我知道有很多与使用 CASE 代替 DECODE 相关的帖子等，但它们似乎与我的担忧不符。

我正在尝试将 Oracle PL/SQL 过程转换为 SQL Server。该过程动态构建 SQL 语句，并使用 DECODE 函数创建 x-tab。过程如下：

PROCEDURE GET_XFORM_DATALOGS (
    fLOTCODE IN VARCHAR2,
    THEDATA OUT SYS_REFCURSOR) IS

    -- VARIABLE DECLARATIONS

    TYPE loc_array_type IS TABLE OF VARCHAR2(40);  -- array type
    loc_array    loc_array_type;     -- array for test names

    prod VARCHAR2(20);  --  product ID
    step VARCHAR2(20);  --  step ID
    sql_str VARCHAR2(32000);    -- SQL statement


    -- EXECUTABLE CODE

    BEGIN -- executable part starts here

        -- get the test names for the given lot code
        SELECT 
            PT_TESTNAME BULK COLLECT INTO loc_array
        FROM 
            (
                SELECT DISTINCT
                    TESTPARMS.PT_TESTNAME, 
                    TESTPARMS.PT_TESTNUM
                FROM 
                    "PRETEST".PRETEST_LOT@PRS_DBLINK LOT,
                    "PRETEST".PRETEST_MEASURE@PRS_DBLINK MEASURE,
                    "PRETEST".PRETEST_TEST_PARMS@PRS_DBLINK TESTPARMS
                WHERE 
                    LOT.PT_LOTSQ = MEASURE.PT_LOTSQ AND
                    MEASURE.PT_LOTSQ = TESTPARMS.PT_LOTSQ AND
                    MEASURE.PT_TESTNUM = TESTPARMS.PT_TESTNUM AND
                    LOT.PT_LOTID = fLOTCODE
                ORDER BY
                PT_TESTNUM
            );

        -- build the SQL string
        sql_str := '';
        sql_str := sql_str ||   'SELECT ';
        sql_str := sql_str ||   '   PRETEST_LOT.PT_LOTID, ';
        sql_str := sql_str ||   '   PRETEST_LOT.PT_LOCTYPE, ' ;
        sql_str := sql_str ||   '   PRETEST_LOT.PT_TESTDATE, ';
        sql_str := sql_str ||   '   PRETEST_MEASURE.PT_WAFERID, ';
        sql_str := sql_str ||   '   PRETEST_MEASURE.PT_XCOORD, ';
        sql_str := sql_str ||   '   PRETEST_MEASURE.PT_YCOORD, ';

        -- add the decodes for column headings
        FOR i IN loc_array.first..loc_array.last LOOP
            sql_str := sql_str || 'max ( decode ( PRETEST_TEST_PARMS.PT_TESTNAME, '''
                || loc_array(i) || ''', PRETEST_MEASURE.PT_MEAS_VALUE, null ) ) '
                || loc_array(i);
                IF (i < loc_array.last) THEN
                    sql_str := sql_str || ', ';
                END IF;
        END LOOP;

        -- build the remainder of the SQL
        sql_str := sql_str || ' FROM ';
        sql_str := sql_str || '     "PRETEST".PRETEST_LOT@PRS_DBLINK PRETEST_LOT, ';
        sql_str := sql_str || '     "PRETEST".PRETEST_MEASURE@PRS_DBLINK PRETEST_MEASURE, ';
        sql_str := sql_str || '     "PRETEST".PRETEST_TEST_PARMS@PRS_DBLINK PRETEST_TEST_PARMS ';

        sql_str := sql_str || ' WHERE  ';
        sql_str := sql_str || '     PRETEST_LOT.PT_LOTSQ = PRETEST_MEASURE.PT_LOTSQ AND ';
        sql_str := sql_str || '     PRETEST_MEASURE.PT_LOTSQ = PRETEST_TEST_PARMS.PT_LOTSQ AND ';
        sql_str := sql_str || '     PRETEST_MEASURE.PT_TESTNUM = PRETEST_TEST_PARMS.PT_TESTNUM AND ';
        sql_str := sql_str || '     (PRETEST_LOT.PT_LOTID = :fFLOTCODE)  ';

        sql_str := sql_str || ' GROUP BY ';
        sql_str := sql_str || '     PRETEST_LOT.PT_LOTID,  ';
        sql_str := sql_str || '     PRETEST_LOT.PT_LOCTYPE,  ';
        sql_str := sql_str || '     PRETEST_LOT.PT_TESTDATE,  ';
        sql_str := sql_str ||   '   PRETEST_MEASURE.PT_WAFERID, ';
        sql_str := sql_str ||   '   PRETEST_MEASURE.PT_XCOORD, ';
        sql_str := sql_str ||   '   PRETEST_MEASURE.PT_YCOORD ';

        sql_str := sql_str || ' ORDER BY  ';
        sql_str := sql_str || '     PRETEST_LOT.PT_TESTDATE  ';

        -- run the query
        OPEN THEDATA FOR sql_str USING fLOTCODE;

END GET_XFORM_DATALOGS;

我的问题是，这可以在 SQL Server 中实现吗？我似乎无法弄清楚如何使用 CASE WHEN THEN 等根据动态数据创建列标题。

抱歉，如果我问的不清楚。感谢您提供的任何帮助。

BBz

Answer 1

那么使用 CASE 的 Oracle 等效项将是：

sql_str := sql_str || 'max ( CASE WHEN PRETEST_TEST_PARMS.PT_TESTNAME = '''
    || loc_array(i) || ''' THEN PRETEST_MEASURE.PT_MEAS_VALUE END ) '
    || loc_array(i);

因此尝试将其转换为 SQL Server。