Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2013
    Posts
    3

    Unanswered: If clause in where statement

    Hi

    I am trying to write sql to basically say if c.rel_value IN ('XYZ','ABC') then rel_attr_id = 'A0' else rel_attr_id = 'C1'...bbut to get the rel_value we need the rel_attr_id to be 'A0' to begin with....so my statement currently is...

    select c.client, c.attribute_id, b.dim_value, a.description, c.rel_attr_id, c.rel_value, 'N' as status
    from agldescription a, agldimvalue b, aglrelvalue c
    where b.client = c.client AND b.attribute_id = c.attribute_id AND
    b.dim_value BETWEEN c.att_val_from AND c.att_val_to AND
    a.client = b.client AND a.attribute_id = b.attribute_id AND
    a.dim_value = b.dim_value AND a.language = 'EN' AND
    a.attribute_id = '70' AND c.rel_attr_id = 'A0'

    then I want to say as per above but if the rel_value are in XYZ or ABC then sqap rel_attr_id to 'C1' otherwise keep with 'A0'...does that make sense?

  2. #2
    Join Date
    Oct 2013
    Posts
    3
    I have moved it on a bit but...just the IF statement to get right I think...any ideas?

    Select t.client, t.attribute_id,t.dim_value, t.description,t.rel_attr_id, t.rel_value, t.status
    from
    (select c.client, c.attribute_id, b.dim_value, a.description, c.rel_attr_id, c.rel_value, 'N' as status
    from agldescription a, agldimvalue b, aglrelvalue c
    where b.client = c.client AND b.attribute_id = c.attribute_id AND
    b.dim_value BETWEEN c.att_val_from AND c.att_val_to AND
    a.client = b.client AND a.attribute_id = b.attribute_id AND
    a.dim_value = b.dim_value AND a.language = 'EN'
    AND b.attribute_id = '70' AND c.rel_attr_id = 'A0'
    UNION
    select c.client, c.attribute_id, b.dim_value, a.description, c.rel_attr_id, c.rel_value, 'N' as status
    from agldescription a, agldimvalue b, aglrelvalue c
    where b.client = c.client AND b.attribute_id = c.attribute_id AND
    b.dim_value BETWEEN c.att_val_from AND c.att_val_to AND
    a.client = b.client AND a.attribute_id = b.attribute_id AND
    a.dim_value = b.dim_value AND a.language = 'EN'
    AND b.attribute_id = '70' AND c.rel_attr_id = 'C1') as t
    iF t.rel_value IN ('R1100','R1500') THEN t.rel_attr_id = 'C1' ELSE t.rel_attr_id = 'A0')

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?

    Code:
    SELECT c.client
         , c.attribute_id
         , b.dim_value
         , a.description
         , c.rel_attr_id
         , c.rel_value
         , 'N' as status 
     FROM  agldescription a
         , agldimvalue    b
         , aglrelvalue    c
     WHERE b.client       = c.client
      AND  b.attribute_id = c.attribute_id
      AND  b.dim_value
                    BETWEEN c.att_val_from
                        AND c.att_val_to
      AND  a.client       = b.client
      AND  a.attribute_id = b.attribute_id
      AND  a.dim_value    = b.dim_value
      AND  a.language     = 'EN'
      AND  a.attribute_id = '70'
      AND
       /*  IF   c.rel_value IN ('R1100' , 'R1500')  */
       /*  THEN                                     */
       /*       rel_attr_id = 'C1'                  */
       /*  ELSE                                     */
       /*       rel_attr_id = 'A0'                  */
      (
           c.rel_value IN ('R1100' , 'R1500')
       AND rel_attr_id = 'C1'
       OR
       /*  ELSE                                     */
       (   c.rel_value NOT IN ('R1100' , 'R1500')
        OR c.rel_value IS NULL
       )
       AND rel_attr_id = 'A0'
      )
    ;
    Last edited by tonkuma; 10-22-13 at 09:47. Reason: Replace (XYZ','ABC') with ('R1100' , 'R1500'). Exchange 'A0' and 'C1'.

  4. #4
    Join Date
    Oct 2013
    Posts
    3
    Hi...thanks for the reply...no that won't work as the rel_value will only be XYZ etc if the rel_attr_id = A0

    Hence me doing the UNION and then thinking I could then do an IF clause overall?

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    no that won't work as the rel_value will only be XYZ etc if the rel_attr_id = A0
    I wrote
    Quote Originally Posted by tonkuma View Post
    How about this?

    Code:
    ...
       /*  IF   c.rel_value IN ('R1100' , 'R1500')  */
       /*  THEN                                     */
       /*       rel_attr_id = 'C1'                  */
       /*  ELSE                                     */
       /*       rel_attr_id = 'A0'                  */
      (
           c.rel_value IN ('R1100' , 'R1500')
       AND rel_attr_id = 'C1'
       OR
       /*  ELSE                                     */
       (   c.rel_value NOT IN ('R1100' , 'R1500')
        OR c.rel_value IS NULL
       )
       AND rel_attr_id = 'A0'
      )
    ;
    In that query,
    Code:
       (   c.rel_value NOT IN ('R1100' , 'R1500')
        OR c.rel_value IS NULL
       )
       AND rel_attr_id = 'A0'
    might satisfy "rel_value will only be XYZ etc if the rel_attr_id = A0".

    If you doubt,
    please provide some test data and compare the results from my query and your expected results.

Posting Permissions

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