Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2005
    Posts
    116

    Unanswered: If exist command in 'case' statement

    Hi Guys,

    Im facing some problem here, i plan to put an exist command into a 'case' statement. Actually i have a query here..

    Code:
    SELECT J.NAME || '_' || A.NAME as MODEL, 
    case when
    exists (select violationconsequence from rulecontent where 
    rulecontent.rulenameid = E.rulenameid) 
    then (violationconseqence)
    else 'none'
    end vioconsequence
    
    FROM
       TEST.TECHNOLOGYCLASS A
     , TEST.SUBGROUPMODEL B
     , TEST.MODELRULENAME F
     , TEST.RULENAME E
     , TEST.CHECKFORMAT I
     , TEST."GROUP" H
     , TEST.CHECKCYCLE J
     , TEST.MODEL G
     , TEST.SHORTRULENAME D
     , TEST.SUBGROUP C
    WHERE
         (B.SUBGROUPID = C.SUBGROUPID)
     AND (D.SUBGROUPID = C.SUBGROUPID)
     AND (E.SHORTRULENAMEID = D.SHORTRULENAMEID)
     AND (F.RULENAMEID = E.RULENAMEID)
     AND (F.SUBGROUPMODELID = B.SUBGROUPMODELID)
     AND (B.MODELID = G.MODELID)
     AND (G.TECHNOLOGYCLASSID = A.TECHNOLOGYCLASSID)
     AND (C.GROUPID = H.GROUPID)
     AND (D.CHECKFORMATID = I.CHECKFORMATID)
     AND (G.CHECKCYCLEID = J.CHECKCYCLEID)
     order by g.modelid, D.shortrulenameid
    Actually i have a couple of join statmenets here.
    And my idea is to append a case statement just to check each record
    and retrieve the violationconsequence from rulecontent table if it exists,
    otherwise just put 'none' in the field.

    But when i execute, it returns invalid identifier at the following line
    Code:
    then (violationconseqence)
    the reason why im not putting this table to join together with other tables is because if there is not match found in the RULECONTENT Table, it wont return the whole piece of record, using exist, i can still return the entire piece of record except for the violationconsequence if it does not exists

    Is there any way i can eliminate this error, appreciate if someone could guide me here, thank you very very much..

  2. #2
    Join Date
    Feb 2005
    Posts
    116
    hi guys,

    i have found a solution for this, if there is record found, im inserting another
    query in the 'then' keyword, thanks

    Code:
    SELECT J.NAME || '_' || A.NAME as MODEL, 
    case when
    exists (select 1 from rulecontent where 
    rulecontent.rulenameid = E.rulenameid) 
    then (select violationconsequence from rulecontent where 
    rulecontent.rulenameid = E.rulenameid) 
    else 'none'
    end vioconsequence
    the case and exist statement really results in some performance issue, but its ok, as long as it returns to correct results
    Last edited by a1jit; 04-16-06 at 12:02.

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    There is no reason I can see why the outer join to rulecontent wouldnt work unless you can have multiple records for one value of rulenameid in rulecontent,and you may find performance is much better.

    select ... , nvl(violationconsequence,'none')...
    from ...., rulecontent r
    where ... e.rulenameid = r.rulenameid(+)

    Alan
    Last edited by AlanP; 04-18-06 at 07:19.

  4. #4
    Join Date
    Sep 2004
    Posts
    60

    Lightbulb

    a1jit,

    I would suggest use of decode. For checking condition.

    If you want code , I can try rewriting it using decode.

    AD

Posting Permissions

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