Setting a row cell to zero?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Setting a row cell to zero?

  1. #1
    Join Date
    May 2007
    Posts
    10

    Setting a row cell to zero?

    I'm trying to isolate duplicate sale numbers and change the revenue dollar amount for just one of each duplicate sale number found to zero.
    Anyone know how to do this? (Code and data set below)

    Code:
    SELECT dis.name AS
    district_id,
      pat.account_number,
     Substr(pat.Sale_number,1,4) as Sale_Num,
      pat.sale_date,
      revenue_dollar_amount,
    (select COUNT(*)
    from district_tbl dis,
    payroll_accrual_tbl pat
    Where pat.district_id = dis.id
     AND pat.accrual_status_flag = 'P'
     AND '926103' = pat.district_id
    AND pat.sale_number = pat.sale_number
    HAVING COUNT(*) >= 1)
    a
    FROM district_tbl dis,
    payroll_accrual_tbl pat
    Where pat.district_id = dis.id
     AND pat.accrual_status_flag = 'P'
     AND '926103' = pat.district_id
    AND pat.sale_number = pat.sale_number
    25291 1478 15-FEB-07 3952.8 4
    25291 1478 15-FEB-07 3952.8 4
    22485 1479 15-FEB-07 2436.6 4
    22485 1479 15-FEB-07 2436.6 4

    So one 3952.8 would show 0 and one 2436.6 would show 0

  2. #2
    Join Date
    May 2007
    Posts
    10
    nvm...discovered the anwser to my question. If anyone is interested let me know and I can post for you.
    thx again.

  3. #3
    Join Date
    Nov 2000
    Posts
    198
    Can you post the answer?

    Thanks

  4. #4
    Join Date
    May 2007
    Posts
    10

    setting a row cell to zero?

    OCP here ya go:
    The highlighted portions are the main drivers for this to take place.
    In my case I had “Sale_Number” that could occur 2 or more times, so Max(sale_number) would give me one of the two records (this separates the two even though they are the same).
    NOTE: using Min(sale_number) would also work of course.

    Then, in the “inner select” statement just add in your criteria (FROM, WHERE, etc…)
    The case statement at top is where it’s decided to set the value of the duplicate to 0 and the other to the original amount (or whatever you want them to be) based on the COUNT(*) from the inner query. You can add and extend what’s done with other duplicates within the case easily.

    Code:
    SELECT  dis.name AS
            dist_id,
            pat.account_number,
            Substr(pat.Sale_number,1,4) as Sale_Num,
            pat.sale_date,
            case  when inner.sale_count is null or inner.sale_count = 1
                  then revenue_dollar_amount
                  else 0
            end as revenue_outputFROM acc_tbl pat
      LEFT JOIN
                (SELECT   Substr(pat.Sale_number,1,4) as Sale_Num,
                  count(*) as sale_count,
                  max(sale_number) as second_sale_number
                FROM pay_acc_tbl pat
                Where pat.stat_flag = 'P'
                GROUP BY substr(pat.sale_number,1,4)) inner
      ON inner.second_sale_number = pat.sale_number
      JOIN dist_tbl dis
      ON dis.id = pat.dist_id
    WHERE pat.stat_flag = 'A' 
    ORDER by sale_num
    It's a nice little piece of code, hope it helps!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width