Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    193

    Unanswered: CASTing a datatype returned by CASE Statement

    I realize that the CASE statement doesn't like different datatypes as return values but if I want to format the "0" in the second WHEN condition below to "000", how do I do that? I have a "Region" that is "000" and would like it to show up that way at the very top of my report. I have the GROUP BY and ORDER BY to work fine, it just shows up as "0" and I can't change it. I realize it is being read as an int but am having trouble with the CAST and where to place it. Thanks again, you guys are great.

    ddave


    SELECT Region =
    CASE WHEN branch_num IN(48,53,78,173,186,198,208,212,257,286,287,317,35 3,398,440,
    478,571,572,610,1069) THEN 44
    WHEN branch_num IN(484,532,841,864,7001,7101,7102,7103,7104,9031) THEN 0
    ELSE 999
    END

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That depends (doesn't it always?) on what you really want. If you want the other regions to show using normal INT formatting, but 0 to be a special case That is one thing, if you want all the region numbers to be zero filled, that is something different. If you want something I haven't thought of yet, then that's probably different too.

    The quick and dirty would be to use:
    PHP Code:
    SELECT Region 
       CASE
          
    WHEN branch_num IN(48,53,78,173,186,198,208,212,257,286,287,317,35 3,398,440,
             
    478,571,572,610,1069THEN ' 44'
          
    WHEN branch_num IN(484,532,841,864,7001,7101,7102,7103,7104,9031THEN '000'
          
    ELSE '999' 
       
    END 
    -PatP

  3. #3
    Join Date
    Feb 2004
    Posts
    193
    Pat,

    Once again, "You da Man!!". It works perfectly. I decided to use '000', ' 1', ' 78', etc. I spent over an hour on it and I knew it was something easy. I mean I don't expect a medal or anything but you can be lost w/o "the little details". Thanks again.

    ddave

  4. #4
    Join Date
    Feb 2004
    Posts
    193
    If I want the format to show the Region field just once, is there a way to do that? My current report has a Region field immediately to the left of BranchNo. Branches are contained within the Regions. I got it to list Region every time I show a record but just in case the manager wants it formatted the way I mention I want to be prepared. The example I was to follow has Region just once.

    This is an example of what I have now:
    Code:
    code:-----------------------------------------------------------------
    Region   BranchNo    OrderNo    ErrorCode1    ErrorCode2    ErrorCode3
      000        478             111               0                  1                  1
      000        478             112               0                  0                  0
      000        478             113               1                  0                  0
      001        610             119               0                  0                  0
      001        610             120               1                  0                  0
    -----------------------------------------------------------------------

    This is an example of what I wish to try:
    Code:
    code:-----------------------------------------------------------------
    Region   BranchNo    OrderNo    ErrorCode1    ErrorCode2    ErrorCode3
      000        478             111               0                  1                  1
                 478             112               0                  0                  0
                 478             113               1                  0                  0
      001        610             119               0                 0                   0
                 610             120               1                  0                  0
    -----------------------------------------------------------------------
    ddave

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What reporting tool are you using? Hopefully this isn't 100% Transact-SQL based, right?

    -PatP

  6. #6
    Join Date
    Feb 2004
    Posts
    193
    Well, I am looking at the data in Query Analyzer but that is a good question. I guess the real answer is that we haven't decided yet. I can use Access though I have to figure out the mechanics which I know won't be difficult. I can even stick it on an Excel spreadsheet as long as it looks good. I say Access because that is "what the others did" but it is not an issue.

    ddave

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It's a presntation issue, and Access is very good at it, and can easily do what your asking....

    I'd love to setup reporting services though...

    Anyone seen it?

    What's the installation like?

    What's the interface?

    Can you use the same box as sql server?

    PS. If they say Crystal...run....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Reporting Services is quite cool, but it is rather complex and it requires Visual Studio to develop reports.

    MS-Access would be beauteous, and would make the formatting, grouping, etc rather simple. I'm not nearly as alergic to Crystal Reports as most folks around here seem to be, but I would STRONGLY advise using Access unless you have another tool of choice.

    -PatP

Posting Permissions

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