CODEWITHSUNDEEP
t-sql
Naveen Medisetti
Naveen Medisetti

Reputation: 1

Regarding If Else in T-SQL

SELECT NM1.nm101,
    CLP.Segment_GUID as CLPSegmentGuid,
    CASE NM1.nm101
    WHEN 'QC' THEN 'Patient'
        WHEN 'IL' THEN 'Insured'
        WHEN '74' THEN 'Corrected'
        WHEN '82' THEN 'Crossover'
    END As type,
    NM1.NM102 As Entity_Type_Qualifier,
    NM1.NM103 As Entity_Last_Name,
    NM1.NM104 As Entity_First_Name,
    NM1.NM105 As Entity_Middle_Name,
    NM1.NM108 As Entity_Identification_Code_Type,
    NM1.NM109 As Entity_Identification_Code
FROM X12_NM1 As NM1 
    INNER JOIN X12_CLP As CLP
      ON CLP.segment_guid = NM1.parent_segment_guid and NM1.Loop_Id  ='2100'

Here if nm1.nm108 = MI then I want the result set with those MI.

Upvotes: 0

Views: 82

Answers (1)

Jonathan
Jonathan

Reputation: 1719

If I understand your question correctly then you are looking for records where only nm1.nm108 = 'MI' if it exists otherwise you want those records excluded

Try something like this

 IF EXISTS(SELECT 1 FROM X12_NM1 As NM1 
                    INNER JOIN X12_CLP As CLP 
                       ON CLP.segment_guid = NM1.parent_segment_guid
                       AND NM1.Loop_Id  ='2100'
                    WHERE nm1.nm108 = 'MI')
 BEGIN
 SELECT NM1.nm101,
 CLP.Segment_GUID as CLPSegmentGuid
 , CASE NM1.nm101
      WHEN 'QC' THEN 'Patient'
     WHEN 'IL' THEN 'Insured'
     WHEN '74' THEN 'Corrected'
     WHEN '82' THEN 'Crossover'
   END As type       
 , NM1.NM102 As Entity_Type_Qualifier
 , NM1.NM103 As Entity_Last_Name
 , NM1.NM104 As Entity_First_Name
 , NM1.NM105 As Entity_Middle_Name
 , NM1.NM108 As Entity_Identification_Code_Type
 , NM1.NM109 As Entity_Identification_Code
  FROM   X12_NM1 As NM1 
 INNER JOIN X12_CLP As CLP
    ON CLP.segment_guid = NM1.parent_segment_guid
    AND NM1.Loop_Id  ='2100'
 WHERE nm1.nm108 = 'MI'
 END
 ELSE
 BEGIN
 SELECT NM1.nm101,
 CLP.Segment_GUID as CLPSegmentGuid
 , CASE NM1.nm101
      WHEN 'QC' THEN 'Patient'
     WHEN 'IL' THEN 'Insured'
     WHEN '74' THEN 'Corrected'
     WHEN '82' THEN 'Crossover'
   END As type       
 , NM1.NM102 As Entity_Type_Qualifier
 , NM1.NM103 As Entity_Last_Name
 , NM1.NM104 As Entity_First_Name
 , NM1.NM105 As Entity_Middle_Name
 , NM1.NM108 As Entity_Identification_Code_Type
 , NM1.NM109 As Entity_Identification_Code
  FROM   X12_NM1 As NM1 
 INNER JOIN X12_CLP As CLP
    ON CLP.segment_guid = NM1.parent_segment_guid
    AND NM1.Loop_Id  ='2100'
 WHERE nm1.nm108 <> 'MI'
 END

Upvotes: 1

Related Questions