1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

CASE Statement in the Where Clause.

Discussion in 'SQL PL/SQL' started by Farshad Javadi, Aug 9, 2011.

  1. Farshad Javadi

    Farshad Javadi Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    Hello To All,

    I am creating "G0001", "G0002", "G0003", "G0004", and "G0005" columns using the case statments in the SELECT clause. The value of these columns are 'Y' or ' '. These code work correctly without any error.

    Now I need in the WHERE clause if the value G0001 or G0002, or G0003 is 'Y' then I have the following condition: contact.ext_CMSEligibleInd = 1 and if the vlaue of G0004 or G0005 is 'Y' then I have the following condition contact.ext_CMSEligibleInd = 0.

    What should I do? Do I need a new CASE statement in the WHERE clause this time?
    Can you please help me how to code this part?


    Thanks very much,
    Farshad Javadi
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You need to post your code so we can see what you're doing; you also need to describe better what it is you want to do as it isn't clear from this post.
     
  3. neullson

    neullson Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Yeah, at least provide us Code for Creating the Table and your own code to solve this problem. Then tell us your problem clearly. So we can analyze it.

    Thanks,
    Neullson
     
  4. Farshad Javadi

    Farshad Javadi Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    Here is the code respectfuly:

    Code (SQL):
    SELECT DISTINCT
      contact.ext_CMSEligibleInd,
      incident.id,
      ADJref_fcc.region_code AS "RegionCode",
      ADJTL_FccCode.Typecode AS "FCCCode",
      AdjUsr.Ext_PrimarySectionCode AS "SectionCode",
      AdjUsr.Ext_PrimaryAdjusterCode AS "AdjusterCode",
      ADJAssoc.lastname||', '||ADJAssoc.firstname AS "Adjuster",
      Claim.ClaimNumber "Claim#",
      Contact.lastname||', '||Contact.firstname ||' '||suffix.name "Claimant Name",
      (CASE WHEN (Contact.taxid IS NOT NULL OR Contact.ext_SSNStatus = '10004')  THEN 'Y' ELSE 'N' END) "SSN exists ?",
      (CASE WHEN Contact.dateofbirth IS NOT NULL THEN 'Y' ELSE 'N' END) "Date of Birth exists ?",
      (CASE WHEN Contact.gender IS NOT NULL THEN 'Y' ELSE 'N' END) "Gender",
      (CASE WHEN contact.ext_cmsclaimnumber IS NOT NULL THEN 'Y' ELSE 'N' END) "HICNFlag",
      TRUNC(Claim.LossDate) "DOI",
      MIN(trunc(exposure.createtime,'DDD')) OVER (PARTITION BY exposure.id)  AS "Monitoring Since",
    --New Code start here
      (CASE WHEN (contact.ext_CMSEligibleInd = 1 OR contact.ext_CMSEligibleInd IS NULL) THEN
            (CASE WHEN ( tl_CostCat.TypeCode IN ('ABI','BID','CSL','LBI','QBI','QUB','RBC','RBI','SBI','UBA','UBI','UBR','UBX','UIA','UIM','UIR','UIX','UMB','UNI','UNM','USL','XBI') )
                  THEN (CASE WHEN exposure.closedate IS NOT NULL
                             THEN ( SELECT MIN(rpt_year||'/'||quarter||'Q') FROM ref_cmsreportingdate WHERE reportdate >= exposure.closedate+45 )
                             ELSE 'None'
                        END)
             
                  ELSE (CASE WHEN (tl_CostCat.TypeCode IN ('ADE','ADI','NAM','NAR','NDA','NDW','NEA','NEF','NEI','NEM','NER','NES','NEW','NAA','NAM','NAR','NAS','NAW','NDM','NDR','DBA','DBD',
                                                'DBF','DBH','DBI','DBL','DBS','DBW','NDB','NDF','EMB','NBA','NBD','NBF','NBL','NBR','NBS','NBW','NBM','FNL','LER','NCA','NCC','NCI',
                                                'NCM','NCR','NCS','NCW','NPC','NPL','NPM','NPR','MEB','QME','MED','QME','QNB','NBA','NBC','NBD','NBF','NBI','NBL','NBR','NBS','NBW',
                                                'QNB','NBM','NCM','NCR','NPA','NPC','NPI','NPL','NPM','NPR','NPS','NPW','RMD','NWL','DBC') )
                             THEN (CASE WHEN Claim.lossDate IS NOT NULL
                                        THEN ( SELECT MIN(rpt_year||'/'||quarter||'Q') FROM ref_cmsreportingdate WHERE reportdate >= Claim.lossDate+45 )
                                   ELSE 'None'
                                   END)
                        END)
             END)
               
                                   
            ELSE 'None'
       END) AS "CMS Report due ",
     
    --  The following lines find Geico-Errors for G0001-To-G0011
      CASE WHEN ((Contact.taxid IS NULL OR contact.ext_ssnstatus IN ('10002', '10003', NULL)) OR ((Contact.taxid IS NOT NULL) AND EXISTS (SELECT * FROM ccx_SYS_ISOInvalidValueInfo ISOInvalid
                                              WHERE ISOInvalid.EXT_INVALIDDATAVALUETXT = contact.TaxID AND ISOInvalid.EXT_ISODATAVALiDATIONTYPE IN (SELECT ID FROM cctl_ext_isodatavalidationtype WHERE TypeCode = 'N') ))) THEN ( 'Y' )
          ELSE ' '
          END "G0001",
     
      CASE WHEN ((Contact.DateOfBirth IS NULL) OR ((Contact.DateOfBirth IS NOT NULL) AND EXISTS (SELECT * FROM ccx_SYS_ISOInvalidValueInfo ISOInvalid
                                              WHERE ISOInvalid.EXT_INVALIDDATAVALUETXT = TO_CHAR(contact.DateOfBirth) AND ISOInvalid.EXT_ISODATAVALiDATIONTYPE IN (SELECT ID FROM cctl_ext_isodatavalidationtype WHERE TypeCode = 'N') ))) THEN ( 'Y' )
          ELSE ' '
          END "G0002",
         
      CASE WHEN ( ((TO_CHAR(Contact.gender) IS NULL) OR (TO_CHAR(Contact.gender) = 'UNKNOWN')) ) THEN ( 'Y' )
          ELSE ' '
          END "G0003",
         
      CASE WHEN ( ((incident.ext_NoFaultInsuranceLimitAmt IS NULL) OR (incident.ext_NoFaultInsuranceLimitAmt = 0)) AND contact.ext_CMSEligibleInd = 1 )  THEN ( 'Y' )
          ELSE ' '
          END "G0004",
         
      CASE WHEN ( (NOT EXISTS (SELECT * FROM ccx_ICDCodeContainer WHERE ccx_ICDCodeContainer.InjuryIncidentID = incident.id)) AND contact.ext_CMSEligibleInd = 1 ) THEN ( 'Y' )
          ELSE ' '
          END "G0005",
         
      CASE WHEN ( ((incident.ext_ExternalInjuryCauseCode IS NULL) OR (incident.ext_InjuryCauseClassCode IS NULL)) AND contact.ext_CMSEligibleInd = 1 ) THEN ( 'Y' )
          ELSE ' '
          END "G0006",
         
      CASE WHEN (  (NOT EXISTS( SELECT * FROM cc_bodypart bp WHERE bp.IncidentID = incident.id AND bp.ext_medicalconditiontype IS NOT NULL AND
                             EXISTS (SELECT * FROM ccx_InjuredBodyPart WHERE ext_BodyPartDetailsID = bp.id  ))) AND contact.ext_CMSEligibleInd = 1 ) THEN ( 'Y' )
          ELSE ' '
          END "G0007",
         
      CASE WHEN ((Contact.firstname IS NULL) OR ((Contact.firstname IS NOT NULL) AND EXISTS (SELECT * FROM ccx_SYS_ISOInvalidValueInfo ISOInvalid
                                              WHERE ISOInvalid.EXT_INVALIDDATAVALUETXT = contact.firstname AND ISOInvalid.EXT_ISODATAVALiDATIONTYPE IN (SELECT ID FROM cctl_ext_isodatavalidationtype WHERE TypeCode = 'M') ))) THEN ( 'Y' )
          ELSE ' '
          END "G0008",
         
      CASE WHEN ((Contact.lastname IS NULL) OR ((Contact.lastname IS NOT NULL) AND EXISTS (SELECT * FROM ccx_SYS_ISOInvalidValueInfo ISOInvalid
                                              WHERE ISOInvalid.EXT_INVALIDDATAVALUETXT = contact.lastname AND ISOInvalid.EXT_ISODATAVALiDATIONTYPE IN (SELECT ID FROM cctl_ext_isodatavalidationtype WHERE TypeCode = 'M') ))) THEN ( 'Y' )
          ELSE ' '
          END "G0009",
         
      --CASE WHEN ( ((trunc(SYSDATE, 'ddd') - trunc (contact.dateofbirth, 'ddd'))/366 > 65) ) THEN ( 'Y' )
        --  ELSE ' '
          --END "G0010",
         
      CASE WHEN ( ((Contact.ext_CMSClaimNumber IS NULL) OR ((Contact.ext_CMSClaimNumber IS NOT NULL) AND EXISTS (SELECT * FROM ccx_SYS_ISOInvalidValueInfo ISOInvalid
                                              WHERE ISOInvalid.EXT_INVALIDDATAVALUETXT = contact.ext_CMSClaimNumber AND ISOInvalid.EXT_ISODATAVALiDATIONTYPE IN (SELECT ID FROM cctl_ext_isodatavalidationtype WHERE TypeCode = 'N') ))) AND contact.ext_CMSEligibleInd = 1 ) THEN ( 'Y' )
          ELSE ' '
          END "G0011",
     
      Exposure.id   AS ExposureID,
      tl_CostCat.TypeCode AS Reservline,
      expotype.name AS ExposureDescription,
      NVL(SUBSTR(ExposureText.Text, 94), 'None') "CMS Error Code",
    --  NVL(SUBSTR(ExposureText.Text, 104), 'None') "CMS Error Description"
      CASE WHEN ( ( (SUBSTR(ExposureText.Text, 94) IS NOT NULL) AND ( (contact.ext_CMSEligibleInd = 1) OR (contact.ext_CMSEligibleInd IS NULL)) ) ) THEN 5
           ELSE (CASE WHEN ( (Contact.ext_CMSEligibleInd = 1) AND ( ( ((incident.ext_NoFaultInsuranceLimitAmt IS NULL) OR (incident.ext_NoFaultInsuranceLimitAmt = 0)) AND contact.ext_CMSEligibleInd = 1 ) OR ( (NOT EXISTS (SELECT * FROM ccx_ICDCodeContainer WHERE ccx_ICDCodeContainer.InjuryIncidentID = incident.id)) AND contact.ext_CMSEligibleInd = 1 ) OR ( ((incident.ext_ExternalInjuryCauseCode IS NULL) OR (incident.ext_InjuryCauseClassCode IS NULL)) AND contact.ext_CMSEligibleInd = 1 ) OR (  (NOT EXISTS( SELECT * FROM cc_bodypart bp WHERE bp.IncidentID = incident.id AND bp.ext_medicalconditiontype IS NOT NULL AND
                             EXISTS (SELECT * FROM ccx_InjuredBodyPart WHERE ext_BodyPartDetailsID = bp.id  ))) AND contact.ext_CMSEligibleInd = 1 ) OR ( ((Contact.ext_CMSClaimNumber IS NULL) OR ((Contact.ext_CMSClaimNumber IS NOT NULL) AND EXISTS (SELECT * FROM ccx_SYS_ISOInvalidValueInfo ISOInvalid
                                              WHERE ISOInvalid.EXT_INVALIDDATAVALUETXT = contact.ext_CMSClaimNumber AND ISOInvalid.EXT_ISODATAVALiDATIONTYPE IN (SELECT ID FROM cctl_ext_isodatavalidationtype WHERE TypeCode = 'N') ))) AND contact.ext_CMSEligibleInd = 1 ) ) ) THEN 3  
                      ELSE (CASE WHEN ( (Contact.taxid IS NULL OR contact.ext_ssnstatus IN ('10002', '10003', NULL)) OR ((Contact.taxid IS NOT NULL) AND EXISTS (SELECT * FROM ccx_SYS_ISOInvalidValueInfo ISOInvalid
                                              WHERE ISOInvalid.EXT_INVALIDDATAVALUETXT = contact.TaxID AND ISOInvalid.EXT_ISODATAVALiDATIONTYPE IN (SELECT ID FROM cctl_ext_isodatavalidationtype WHERE TypeCode = 'N') )) OR (Contact.DateOfBirth IS NULL) OR ((Contact.DateOfBirth IS NOT NULL) AND EXISTS (SELECT * FROM ccx_SYS_ISOInvalidValueInfo ISOInvalid
                                              WHERE ISOInvalid.EXT_INVALIDDATAVALUETXT = TO_CHAR(contact.DateOfBirth) AND ISOInvalid.EXT_ISODATAVALiDATIONTYPE IN (SELECT ID FROM cctl_ext_isodatavalidationtype WHERE TypeCode = 'N') )) OR ((TO_CHAR(Contact.gender) IS NULL) OR (TO_CHAR(Contact.gender) = 'UNKNOWN')) OR (Contact.firstname IS NULL) OR ((Contact.firstname IS NOT NULL) AND EXISTS (SELECT * FROM ccx_SYS_ISOInvalidValueInfo ISOInvalid
                                              WHERE ISOInvalid.EXT_INVALIDDATAVALUETXT = contact.firstname AND ISOInvalid.EXT_ISODATAVALiDATIONTYPE IN (SELECT ID FROM cctl_ext_isodatavalidationtype WHERE TypeCode = 'M') )) OR (Contact.lastname IS NULL) OR ((Contact.lastname IS NOT NULL) AND EXISTS (SELECT * FROM ccx_SYS_ISOInvalidValueInfo ISOInvalid
                                              WHERE ISOInvalid.EXT_INVALIDDATAVALUETXT = contact.lastname AND ISOInvalid.EXT_ISODATAVALiDATIONTYPE IN (SELECT ID FROM cctl_ext_isodatavalidationtype WHERE TypeCode = 'M') ))) THEN 1 END)
                  END)
                                             
      END AS Stage,
      contact.ext_CMSEligibleInd
    FROM
      -- Exposure/Reserve Line information
      cc_Exposure Exposure
      JOIN cc_incident incident ON Exposure.INCIDENTID = Incident.ID
      JOIN Cc_Claim Claim ON incident.claimid = claim.id
      JOIN cctl_exposuretype expotype ON exposure.exposuretype = expotype.id
      JOIN cc_ReserveLine   ReservLine ON Exposure.Id = ReservLine.ExposureId AND ReservLine.Retired = 0
      JOIN cctl_CostCategory   tl_CostCat ON ReservLine.CostCategory = tl_CostCat.Id AND tl_CostCat.Retired = 0
      JOIN cctl_ext_claimhandlestatustype handletype ON Claim.ext_ClaimHandleStatusType = HandleType.id
      -- Contact information
      JOIN cc_claimcontact Claimcontact ON ClaimContact.claimid = Claim.id
      JOIN cc_contact Contact ON Claimcontact.contactid = contact.id
      JOIN cc_claimcontactrole ClaimRole ON ClaimContact.ID = Claimrole.claimcontactid
      JOIN cctl_contactrole rolename ON rolename.id = Claimrole.ROLE AND rolename.typecode = 'injured'
      JOIN cctl_yesNo Injury ON claimcontact.ext_InjuryInd = Injury.id
      LEFT JOIN cctl_namesuffix suffix ON suffix.id = contact.suffix
      -- ADJ user (Exposure Owner)
      JOIN cc_User ADJUsr ON Exposure.assigneduserid = ADJUsr.id
      JOIN cc_contact ADJAssoc ON ADJAssoc.id = ADJUsr.contactid
      JOIN Cctl_Ext_FccCode ADJTL_FccCode ON ADJTL_FccCode.id = ADJAssoc.ext_fcc
      JOIN ref_fcc ADJref_fcc ON ADJref_fcc.fcc_code = ADJtl_fcccode.typecode
      -- CMS Medicare information --
      LEFT OUTER JOIN ccx_InjuryISOMedicareInfo ISOMedicareInfo ON Incident.ext_injuryisomedicareinfoid = ISOMedicareInfo.id
      LEFT JOIN cc_ExposureText ExposureText ON Exposure.id = ExposureText.exposureId

    WHERE
    --Select ATLAS claims.  Exclude legacy or FNOL-only claims
      Handletype.typecode = 'A'
    --Include only injured parties
    AND Injury.typecode = 'Yes'
    --Restrict parties based on Medicare eligibility and required information
    --Restrict the exposure types to ORM or TPOC
    AND
    (tl_CostCat.TypeCode IN ('ADE','ADI','DBA','DBC','DBD','DBF','DBH','DBI','DBL','DBS','DBW','EMB','FNL','LER','MEB','MED','NAA','NAM',
    'NAR','NAS','NAW','NBA','NBC','NBD','NBF','NBI','NBL','NBM','NBR','NBS','NBS','NBW','NCA','NCC','NCI','NCM','NCR','NCS','NCW','NDA','NDB','NDF',
    'NDM','NDM','NDR','NDW','NEA','NEF','NEI','NEM','NER','NES','NEW','NPA','NPC','NPI','NPL','NPM','NPR','NPS','NPW','NWL','QME','QNB','RMD','RVL')  
    -- TPOC exposures
    OR tl_CostCat.TypeCode IN ('ABI','BID','CSL','LBI','QBI','QU','RBC','RBI','SBI','UBA','UBI','UBR','UBX','UIA','UIM','UIR','UIX','UMB','UNI','UNM','USL','XBI'))
    --and ExposureText.textType in (select id from cctl_exposuretextType where typecode = 'ISOErrorMessage')
    --and ExposureText.text is Not Null
    AND contact.subtype = (SELECT id FROM cctl_contact WHERE typecode ='Person')
    AND exposure.ClaimantDenormID = Contact.ID
    --AND TRUNC(exposure.createtime, 'ddd') <= sysdate-:sqlDayOffset -- For normal operation: :sqlDayOffset = 1 for the day before.
    --AND AdjUsr.Ext_PrimarySectionCode = :SectionCode
    --AND Claim.ClaimNumber = '6094147990101126'
    ORDER BY "RegionCode","Claim#"
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    What, exactly, is this case statement in the where clause intended to accomplish?