Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    67

    Unanswered: Sybase 11.9, make a negitive=0

    I have a query where I want to return 0 instead of a negative number when the sum is less then 0. Any ideas on how to do this?

    Below is the SQL. The field that could go negative is "tothoursot"...

    SELECT xx.paydays AS paydays,
    total = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida y+saturday)
    FROM it_tt_activity aa
    WHERE aa.uid = @uid AND aa.week_date = xx.week_date),0),
    tothoursot = (SELECT ((ISNULL(
    (SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida y+saturday)
    FROM it_tt_activity aa
    WHERE aa.uid = @uid
    AND aa.week_date = xx.week_date),0)
    )
    -(40+(
    ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida y+saturday)
    FROM it_tt_activity m
    WHERE m.activity = "No Pay"
    AND m.uid = @uid
    AND m.week_date = xx.week_date),0))))),
    holiday = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida y+saturday)
    FROM it_tt_activity a
    WHERE a.activity = "Holiday" AND a.uid = @uid AND a.week_date = xx.week_date),0),
    vacation = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida y+saturday)
    FROM it_tt_activity b
    WHERE b.activity = "Vacation" AND b.uid = @uid AND b.week_date = xx.week_date),0),
    ato = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida y+saturday)
    FROM it_tt_activity c
    WHERE c.activity = "Authorized Time Off" AND c.uid = @uid AND c.week_date = xx.week_date),0),
    mato = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida y+saturday)
    FROM it_tt_activity d
    WHERE d.activity = "Management Time Off" AND d.uid = @uid AND d.week_date = xx.week_date),0),
    illness = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida y+saturday)
    FROM it_tt_activity f
    WHERE f.activity = "Illness" AND f.uid = @uid AND f.week_date = xx.week_date),0),
    everythingelse = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida y+saturday)
    FROM it_tt_activity g
    WHERE g.activity NOT IN ("Jury Duty","Military Duty","Holiday","Vacation","Authorized Time Off","Management Time Off","Illness") AND g.uid = @uid AND g.week_date = xx.week_date),0),
    juryduty = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida y+saturday)
    FROM it_tt_activity l
    WHERE l.activity = "Jury Duty" AND l.uid = @uid AND l.week_date = xx.week_date),0),
    militaryduty = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida y+saturday)
    FROM it_tt_activity m
    WHERE m.activity = "Military Duty" AND m.uid = @uid AND m.week_date = xx.week_date),0),
    nopay = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida y+saturday)
    FROM it_tt_activity m
    WHERE m.activity = "No Pay" AND m.uid = @uid AND m.week_date = xx.week_date),0)
    INTO #tsacttemp
    FROM it_tt_activity z, it_tt_paypds xx
    WHERE z.uid=@uid AND z.week_date=xx.week_date
    GROUP BY xx.paydays
    ORDER BY xx.paydays DESC



    Thanks in advance.

  2. #2
    Join Date
    Jan 2003
    Posts
    67

    Ok

    Got cought up in trying to make this happen in one query. I just did an update after the first query where <0 set to 0.

    Sorry.

  3. #3
    Join Date
    Apr 2003
    Posts
    5

    Re: Ok

    Originally posted by timmoser
    Got cought up in trying to make this happen in one query. I just did an update after the first query where <0 set to 0.

    Sorry.
    use CASE in your sql

  4. #4
    Join Date
    Apr 2003
    Posts
    18

    Re: Sybase 11.9, make a negitive=0

    Use the select .... case statement
    Originally posted by timmoser
    I have a query where I want to return 0 instead of a negative number when the sum is less then 0. Any ideas on how to do this?

    Below is the SQL. The field that could go negative is "tothoursot"...

    SELECT xx.paydays AS paydays,
    total = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida y+saturday)
    FROM it_tt_activity aa
    WHERE aa.uid = @uid AND aa.week_date = xx.week_date),0),
    tothoursot = (SELECT ((ISNULL(
    (SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida y+saturday)
    FROM it_tt_activity aa
    WHERE aa.uid = @uid
    AND aa.week_date = xx.week_date),0)
    )
    -(40+(
    ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida y+saturday)
    FROM it_tt_activity m
    WHERE m.activity = "No Pay"
    AND m.uid = @uid
    AND m.week_date = xx.week_date),0))))),
    holiday = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida y+saturday)
    FROM it_tt_activity a
    WHERE a.activity = "Holiday" AND a.uid = @uid AND a.week_date = xx.week_date),0),
    vacation = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida y+saturday)
    FROM it_tt_activity b
    WHERE b.activity = "Vacation" AND b.uid = @uid AND b.week_date = xx.week_date),0),
    ato = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida y+saturday)
    FROM it_tt_activity c
    WHERE c.activity = "Authorized Time Off" AND c.uid = @uid AND c.week_date = xx.week_date),0),
    mato = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida y+saturday)
    FROM it_tt_activity d
    WHERE d.activity = "Management Time Off" AND d.uid = @uid AND d.week_date = xx.week_date),0),
    illness = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida y+saturday)
    FROM it_tt_activity f
    WHERE f.activity = "Illness" AND f.uid = @uid AND f.week_date = xx.week_date),0),
    everythingelse = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida y+saturday)
    FROM it_tt_activity g
    WHERE g.activity NOT IN ("Jury Duty","Military Duty","Holiday","Vacation","Authorized Time Off","Management Time Off","Illness") AND g.uid = @uid AND g.week_date = xx.week_date),0),
    juryduty = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida y+saturday)
    FROM it_tt_activity l
    WHERE l.activity = "Jury Duty" AND l.uid = @uid AND l.week_date = xx.week_date),0),
    militaryduty = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida y+saturday)
    FROM it_tt_activity m
    WHERE m.activity = "Military Duty" AND m.uid = @uid AND m.week_date = xx.week_date),0),
    nopay = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+frida y+saturday)
    FROM it_tt_activity m
    WHERE m.activity = "No Pay" AND m.uid = @uid AND m.week_date = xx.week_date),0)
    INTO #tsacttemp
    FROM it_tt_activity z, it_tt_paypds xx
    WHERE z.uid=@uid AND z.week_date=xx.week_date
    GROUP BY xx.paydays
    ORDER BY xx.paydays DESC



    Thanks in advance.

Posting Permissions

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