Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2010
    Posts
    1

    Unanswered: SQL / Oracle Decode help needed

    I need to convert an oracle query to SQL server which doesn't have a DECODE function.

    SELECT TMP_STORE.TRANSDATE, SUM(TMP_STORE.AS_SALES_CT), COUNT(*), (DECODE(COUNT(*),0,0,SUM(TMP_STORE.AS_SALES_CT)/COUNT(*)))

    FROM TMP_STORE
    WHERE TMP_STORE.TRANSDATE IN ('03-Jan-10', '04-Jan-10', '05-Jan-10', '06-Jan-10', '07-Jan-10', '08-Jan-10', '09-Jan-10')
    GROUP BY TMP_STORE.TRANSDATE


    Can anyone tell me how to change this query to run in SQL server?

    Thanks,
    Marcy

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Very likely someone can. Some of the posters on here know PL\SQL & T-SQL. I don't know PL\SQL.

    Please could you explain what decode does? If you can do that there will be a lot more people able to help you.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    CASE WHEN COUNT(*) = 0 
         THEN 0
         ELSE SUM(TMP_STORE.AS_SALES_CT)/COUNT(*) AS avg_sale
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Decode = replace

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by PMASchmed View Post
    Decode = replace
    That's plain wrong.
    decode is Oracle's old way of implementing the SQL standard CASE expression (as r937 has shown)

    @r937:
    I think you are missing an END in your expression:
    Code:
    CASE WHEN COUNT(*) = 0 
         THEN 0
         ELSE SUM(TMP_STORE.AS_SALES_CT)/COUNT(*) END AS avg_sale

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    He usually leaves that as an exercise for the interested reader ;-)

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by MCrowley View Post
    He usually leaves that as an exercise for the interested reader ;-)
    either that or i make my own share of mistakes, eh

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by shammat View Post
    That's plain wrong.
    decode is Oracle's old way of implementing the SQL standard CASE expression (as r937 has shown)

    @r937:
    I think you are missing an END in your expression:
    Code:
    CASE WHEN COUNT(*) = 0 
         THEN 0
         ELSE SUM(TMP_STORE.AS_SALES_CT)/COUNT(*) END AS avg_sale
    It's not "just plain wrong", it would work fine with a single item to "case".
    Last edited by healdem; 01-21-10 at 16:54. Reason: removal off offensive, unecessary comment

  9. #9
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by PMASchmed View Post
    It's not "just plain wrong", it would work fine with a single item to "case".
    Then show me how to use replace for the following:
    Code:
    CASE 
       WHEN some_col < 0 THEN -1
    END
    And please watch your language!
    Last edited by healdem; 01-21-10 at 16:55. Reason: removal off offensive, unecessary comment

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    REPLACE(REPLACE(CAST(some_col AS BIT), 1, -1), 0, some_col))
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by pootle flump View Post
    Code:
    REPLACE(REPLACE(CAST(some_col AS BIT), 1, -1), 0, some_col))
    But it doesn't return a numeric value, so you'd need another cast.

    But this "obfuscating competition" is becoming fun

    What about this one:
    Code:
    CASE 
       WHEN some_col < 24 AND other_col > 42 THEN third_col * 17
    END

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Who needs CASE?
    Code:
    USE tempdb
    GO 
    
    SELECT 
            cases   =   CASE 
                           WHEN some_col < 24 AND other_col > 42 THEN third_col * 17
                        END
          , replaces=   REPLACE(REPLACE(some_col, low.number, 'pootle') + REPLACE(other_col, high.number, 'flump'), 'pootleflump', third_col * 17)
    FROM
            (
                SELECT  id          = 1
                      , some_col    = 0
                      , other_col   = 43
                      , third_col   = 1
                UNION ALL
                SELECT  id          = 2
                      , some_col    = 25
                      , other_col   = 43
                      , third_col   = 1
                UNION ALL
                SELECT  id          = 3
                      , some_col    = 0
                      , other_col   = 42
                      , third_col   = 1
                UNION ALL
                SELECT  id          = 4
                      , some_col    = 25
                      , other_col   = 42
                      , third_col   = 1
            ) AS some_table
    LEFT OUTER JOIN 
            dbo.numbers AS low
    ON  some_col    = low.number
    AND low.number  < 24
    LEFT OUTER JOIN 
            dbo.numbers AS high
    ON  other_col   = high.number
    AND high.number  > 42
    EDIT - removed a snippet of code I used for debugging....
    Last edited by pootle flump; 01-22-10 at 10:57.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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