1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Set Variable

Discussion in 'SQL PL/SQL' started by ertweety, Jun 13, 2012.

  1. ertweety

    ertweety Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    How do I set a month variable at the top of my code?

    DECLARE CURRENT_MTH NUMBER(4);
    BEGIN

    CREATE TABLE SC_10_ETA_ALL_CATEG AS
    SELECT H.MTH,
    H.VENDOR#,
    H.VENDOR_NAME,
    H.CATEGORY,
    CASE WHEN P.ETA_HIT_YES IS NULL THEN 0 ELSE P.ETA_HIT_YES END ETA_YES,
    CASE WHEN P.TOTAL_ETA_OPPS IS NULL THEN 0 ELSE P.TOTAL_ETA_OPPS END TOTAL_ETA_OPS
    FROM COLBI.EDISP_TOWERS_WITH_CAT H
    LEFT OUTER JOIN SC_9_ETA_TARG_MTH P
    ON H.VENDOR# = P.VENDOR#
    AND P.MON = CAST(H.MTH AS INT)
    AND P.CATEGORY = H.CATEGORY
    WHERE (CAST(H.MTH AS INT)<= CURRENT_MTH)ORDER BY H.VENDOR#, H.MTH, H.CATEGORY;


    CREATE TABLE SC_18_ATA_ALL_CATEG AS
    SELECT H.MTH,
    H.VENDOR#,
    H.VENDOR_NAME,
    H.CATEGORY,
    CASE WHEN Z.ATA_HIT_YES IS NULL THEN 0 ELSE Z.ATA_HIT_YES END ATA_YES,
    CASE WHEN Z.TOTAL_ATA_OPPS IS NULL THEN 0 ELSE Z.TOTAL_ATA_OPPS END TOTAL_ATA_OPS
    FROM COLBI.EDISP_TOWERS_WITH_CAT H
    LEFT OUTER JOIN SC_17_ATA_TARG_MTH Z
    ON H.VENDOR# = Z.VENDOR#
    AND Z.MON = CAST(H.MTH AS INT)
    AND Z.CATEGORY = H.CATEGORY
    WHERE (CAST(H.MTH AS INT)<= CURRENT_MTH)ORDER BY H.VENDOR#, H.MTH, H.CATEGORY;

    END;
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Code (SQL):
    DECLARE current_mth NUMBER(4):=6;
    BEGIN
    CREATE TABLE sc_10_eta_all_categ AS
    SELECT h.mth,
    h.vendor#,
    h.vendor_name,
    h.category,
    CASE WHEN p.eta_hit_yes IS NULL THEN 0 ELSE p.eta_hit_yes END eta_yes,
    CASE WHEN p.total_eta_opps IS NULL THEN 0 ELSE p.total_eta_opps END total_eta_ops
    FROM colbi.edisp_towers_with_cat h
    LEFT OUTER JOIN sc_9_eta_targ_mth p
    ON h.vendor# = p.vendor#
    AND p.mon = CAST(h.mth AS INT)
    AND p.category = h.category
    WHERE (CAST(h.mth AS INT)<= current_mth) ORDER BY h.vendor#, h.mth, h.category;
     
     
    CREATE TABLE sc_18_ata_all_categ AS
    SELECT h.mth,
    h.vendor#,
    h.vendor_name,
    h.category,
    CASE WHEN z.ata_hit_yes IS NULL THEN 0 ELSE z.ata_hit_yes END ata_yes,
    CASE WHEN z.total_ata_opps IS NULL THEN 0 ELSE z.total_ata_opps END total_ata_ops
    FROM colbi.edisp_towers_with_cat h
    LEFT OUTER JOIN sc_17_ata_targ_mth z
    ON h.vendor# = z.vendor#
    AND z.mon = CAST(h.mth AS INT)
    AND z.category = h.category
    WHERE (CAST(h.mth AS INT)<= current_mth) ORDER BY h.vendor#, h.mth, h.category;
     
    END;
     
  3. ertweety

    ertweety Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Doesn't seem to work with Oracle. Doesn't like the create statement after the declare.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No, you're not allowed to directly execute DDL within a PL/SQL block. You didn't ask how to make the block work, you asked how to assign values to a variable. To get this to actually work:

    Code (SQL):
    DECLARE
    current_mth NUMBER(4):=6;
    sqlstmt varchar2(4000);
    BEGIN
    sqlstmt:= 'CREATE TABLE sc_10_eta_all_categ AS ';
    sqlstmt:=sqlstmt||'SELECT h.mth, ';
    sqlstmt:=sqlstmt||'h.vendor#, ';
    sqlstmt:=sqlstmt||'h.vendor_name, ';
    sqlstmt:=sqlstmt||'h.category, ';
    sqlstmt:=sqlstmt||'CASE WHEN p.eta_hit_yes IS NULL THEN 0 ELSE p.eta_hit_yes END eta_yes, ';
    sqlstmt:=sqlstmt||'CASE WHEN p.total_eta_opps IS NULL THEN 0 ELSE p.total_eta_opps END total_eta_ops ';
    sqlstmt:=sqlstmt||'FROM colbi.edisp_towers_with_cat h ';
    sqlstmt:=sqlstmt||'LEFT OUTER JOIN sc_9_eta_targ_mth p ';
    sqlstmt:=sqlstmt||'ON h.vendor# = p.vendor# ';
    sqlstmt:=sqlstmt||'AND p.mon = CAST(h.mth AS INT) ';
    sqlstmt:=sqlstmt||'AND p.category = h.category ';
    sqlstmt:=sqlstmt||'WHERE (CAST(h.mth AS INT)<= '||current_mth||') ORDER BY h.vendor#, h.mth, h.category';
    EXECUTE immediate sqlstmt;
     
     
    sqlstmt:='CREATE TABLE sc_18_ata_all_categ AS ';
    sqlstmt:=sqlstmt||'SELECT h.mth, ';
    sqlstmt:=sqlstmt||'h.vendor#, ';
    sqlstmt:=sqlstmt||'h.vendor_name, ';
    sqlstmt:=sqlstmt||'h.category, ';
    sqlstmt:=sqlstmt||'CASE WHEN z.ata_hit_yes IS NULL THEN 0 ELSE z.ata_hit_yes END ata_yes, ';
    sqlstmt:=sqlstmt||'CASE WHEN z.total_ata_opps IS NULL THEN 0 ELSE z.total_ata_opps END total_ata_ops ';
    sqlstmt:=sqlstmt||'FROM colbi.edisp_towers_with_cat h ';
    sqlstmt:=sqlstmt||'LEFT OUTER JOIN sc_17_ata_targ_mth z ';
    sqlstmt:=sqlstmt||'ON h.vendor# = z.vendor# ';
    sqlstmt:=sqlstmt||'AND z.mon = CAST(h.mth AS INT) ';
    sqlstmt:=sqlstmt||'AND z.category = h.category ';
    sqlstmt:=sqlstmt||'WHERE (CAST(h.mth AS INT)<= '||current_mth||') ORDER BY h.vendor#, h.mth, h.category';
    EXECUTE immediate sqlstmt;
     
    END;
    /
     
     
    ertweety likes this.
  5. ertweety

    ertweety Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Thanks for the help!