Results 1 to 7 of 7
  1. #1
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104

    Unanswered: How to Query this...

    My data looks like this:

    | STORE | ORDER# | AMOUNT |
    | 180 | 12345 | 100.00 |
    | 190 | 12345 | 100.00 |
    | 193 | 12345 | 100.00 |


    I want it to look like this:

    | STORE | ORDER# | AMOUNT |
    | 180 | 12345 | 100.00 |
    | 190 | 12345 | 0.00 |
    | 193 | 12345 | 0.00 |

    I have 2 options:
    (1) Use a cursor and loop.
    (2) My choice of getting MIN(Store) and group by Order# and Amount then join it back and place replace NULL amount with 0.

    Is there a better way?

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Can you give a bit more insight into what real world task this is intended to accomplish? There are certainly ways to do what you describe, but I'm nearly certain that there is a better way than doing a brute force dance. If we understand what you're really trying to do, we can give MUCH better suggestions on how to go about doing it!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    prefer option (2)

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    update [YourTable]
    set Amount = 0
    where Store in (190, 193)

    That will do the trick for you.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Sep 2011
    Posts
    2
    but what exactly do you want to do? Show the ammount for the first register and ignore the rest?

  6. #6
    Join Date
    Sep 2011
    Posts
    75
    Hello,

    You can use case statement to replace null with zero.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jassi.singh View Post
    You can use case statement to replace null with zero.
    replacing null with 0 can also be accomplished with the COALESCE function

    however, there are no nulls in this problem, jassi -- are you sure you didn't intend your sweet little one-liner response for some other thread?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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