Professional Web Applications Themes

View syntax question - Microsoft SQL / MS SQL Server

I am trying to create a view in SQL Server 2000. One field of the view depends on the value of any of ten fields, i.e., in the base table that is the source for this view column I need to test which one of the ten fields has a 'Y' (one of them is guaranteed to). I can't figure out how to do this in a 'case' statement; case doesn't allow one to test for more than one column and multiple case statements separated by a comma would produce additional columns in the view. I have been trying to ...

  1. #1

    Default View syntax question

    I am trying to create a view in SQL Server 2000. One field of the
    view depends on the value of any of ten fields, i.e., in the base
    table that is the source for this view column I need to test which one
    of the ten fields has a 'Y' (one of them is guaranteed to).

    I can't figure out how to do this in a 'case' statement; case doesn't
    allow one to test for more than one column and multiple case
    statements separated by a comma would produce additional columns in
    the view.

    I have been trying to do this with an 'if' clause but Query yzer
    keeps telling me my syntax is no good. Example

    select f1,f2,
    f3= (if fielda='Y' then 'FIELDA'
    else if fieldb='Y then 'FIELDB' end if)

    What is the best way to do this?

    TIA -

    jh
    Jared Guest

  2. #2

    Default Re: View syntax question

    you can use a UDF (user defined function), eg:

    select f1, f2, dbo.fxWhichIsYes(fielda, fieldb) from theTable

    regards, -marty nicholson

    "Jared" <com> wrote in message
    news:google.com... 


    Martin Guest

  3. #3

    Default Re: View syntax question

    SELECT F1, F1,
    CASE
    WHEN fielda = 'Y' THEN 'FIELDA'
    WHEN fieldb = 'Y' THEN 'FIELDB'
    WHEN fieldc = 'Y' THEN 'FIELDC'
    END ...
    FROM
    "Jared" <com> wrote in message
    news:google.com... 


    David Guest

  4. #4

    Default Re: View syntax question

    Ack! You may be amused to leasrn that not only I but my boss missed
    the 'END' clause in the docs. This is what I originally did.

    Thanks for the newbie help.

    "David Frommer" <com> wrote in message news:<phx.gbl>... [/ref]
    Jared Guest

  5. #5

    Default Re: View syntax question

    hehe, it happens sometimes. Glad I was able to help

    "Jared" <com> wrote in message
    news:google.com... 
    news:<phx.gbl>... [/ref][/ref]


    David Guest

  6. #6

    Default Re: View syntax question

    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.
     [/ref]
    [sic], i.e., in the base table that is the source for this view column I
    need to test which one of the ten fields [sic] has a 'Y' (one of them is
    guaranteed to). <<

    There is a big difference between a field and a column. This sounds
    like a repeating group and you might want to normalize thils table, so
    you don't have to kludge things like this in the future.
     [/ref]
    them is guaranteed to). <<

    Mind showing the CHECK() constraint you wrote to enforce this? If there
    is no such constraint, then there is NO guarantee (and you cannot put it
    in the application source code). The problem with de-normalized tables
    is that you need a LOT of constraints to enforce business rules.

    CREATE Dumbar (floob_key, yes_col_nbr)
    SELECT floob_key,
    CASE WHEN A01 = 'Y' THEN 1
    WHEN A02 = 'Y' THEN 2
    ...
    WHEN A10 = 'Y' THEN 10
    ELSE NULL END
    FROM Foobar;


    --CELKO--


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

Similar Threads

  1. Question about E4X syntax.
    By rpierich in forum Macromedia Flex General Discussion
    Replies: 7
    Last Post: May 27th, 06:12 AM
  2. syntax question
    By JS in forum PERL Miscellaneous
    Replies: 8
    Last Post: October 3rd, 02:46 AM
  3. Question on syntax...
    By Jonathan Villa in forum PHP Development
    Replies: 1
    Last Post: August 20th, 03:15 PM
  4. [PHP] Question on class syntax
    By Luis Lebron in forum PHP Development
    Replies: 3
    Last Post: August 7th, 07:07 PM
  5. SQL Syntax Question???
    By Kory in forum Macromedia Dreamweaver
    Replies: 2
    Last Post: July 10th, 10:57 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139