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!

change values in cycle, pure sql if possible

Discussion in 'SQL PL/SQL' started by ecivgamer, May 20, 2011.

  1. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    Hi all, I've got code, everything works fine for XXX and ZZZ. But I have to change these values manually. How do I change values in cycle? Let's say, XXX can be 1 or 2, ZZZ can be 30.03.2010 or 31.03.2010. Could you please help me improve my code?

    Code (SQL):
    INSERT INTO marco_tmp4
    WITH t1 AS(
    SELECT
    --var_LUAH
    (SELECT nvl(SUM(b.summanow)/100,0) FROM arc_balance b, aaccount d WHERE
    d.baccountid IN (2010,2020,2030,2062,2063,2072,2073,2074,2082,2083,
    2102,2103,2112,2113,2122,2123,2132,2133,2202,2203,2220,2232,2233)
    AND b.arcdate = to_date('ZZZ', 'DD.MM.YYYY')
    AND d.contragentid = 'XXX'
    AND b.corrmode IN (0,2) --bez kor.provodok
    AND d.currencyid = 980
    AND d.id = b.accountid ) var_LUAH,

    --LUSD
    (SELECT nvl(SUM(b.summanow)/100,0) FROM arc_balance b, aaccount d WHERE
    d.baccountid IN (2010,2020,2030,2062,2063,2072,2073,2074,2082,2083,2102,2103,2112,2113,2122,
    2123,2132,2133,2202,2203,2220,2232,2233)
    AND b.arcdate = to_date('ZZZ', 'DD.MM.YYYY')
    AND d.contragentid = 'XXX'
    AND b.corrmode IN (0,2) --bez kor.provodok
    AND d.currencyid = 840
    --and b.summanow > 0
    AND d.id = b.accountid ) var_LUSD
    FROM dual )
    SELECT * FROM t1
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Not sure about the question. Are you talking about the following one ?

    Code (SQL):
    WITH t1 AS(
    SELECT  d.contragentid, TRUNC(b.arcdate), nvl(SUM(b.summanow)/100,0) FROM arc_balance b, aaccount d WHERE
        d.baccountid IN (2010,2020,2030,2062,2063,2072,2073,2074,2082,2083,
        2102,2103,2112,2113,2122,2123,2132,2133,2202,2203,2220,2232,2233)
        AND b.arcdate BETWEEN TO_DATE('30.03.2010','DD.MM.YYYY') AND TO_DATE('31.03.2010','DD.MM.YYYY')
        AND d.contragentid IN ('1','2')
        AND b.corrmode IN (0,2) --bez kor.provodok
        AND d.currencyid = 980
        AND d.id = b.accountid
        grouo BY d.contragentid, TRUNC(b.arcdate)   )
    SELECT * FROM t1