Click here to Skip to main content
15,068,988 members
Please Sign up or sign in to vote.
4.50/5 (2 votes)
See more:
i have following store procedure
IF EXISTS(SELECT * FROM MIS_CH_MOL_WORK AS MWork WHERE MC_REP_TYPE=@ReportType
    AND MWork.MC_DISCRIPTION1='WSSTTY Lowbase'
            AND MWork.MC_CLUST_CODE IN (SELECT * FROM [dbo].[SplitCommaString](@ClusterCode)) 
            AND CONVERT(BIGINT,MWork.MC_DATE)>= CONVERT(BIGINT,@FromDate) AND CONVERT(BIGINT,MWork.MC_DATE)<= CONVERT(BIGINT,@ToDate)) 
        BEGIN
    
    SELECT
     [ClusterCode]=MWork.MC_CLUST_CODE
    ,[ClusterName]=Cluster.SEGMENT_TERR_NAME
    ,[Report Type]=MWork.MC_REP_TYPE
    ,MWork.MC_DISCRIPTION1
    ,[TerritoryName]=Territory.SEGMENT_TERR_NAME
    ,[WssTerritory Name]=MWork.MC_DISCRIPTION2
    ,[Root Cause]=ISNULL(ETD.VcRootCauseRemark,'')
    ,[Action Plan]=ISNULL(ETD.VcActionPlanRemark,'')
    ,[Sale Plan For Crnt QTR]=ISNULL(ETD.VcSalePlan,'')
    ,[col1]=''
    ,[col2]=''
    
        FROM MIS_CH_MOL_WORK AS MWork
            JOIN OM_SM_PF AS Cluster ON MWork.MC_CLUST_CODE=Cluster.SEGMENT_TERR_CODE
            JOIN OM_SM AS Territory ON MWork.MC_TERR_CODE=Territory.SEGMENT_TERR_CODE
            LEFT JOIN Exception_Trans AS ET ON ET.VcClust_Code IN (SELECT * FROM [dbo].[SplitCommaString](@ClusterCode))  
                AND VcTerr_Code=MWork.MC_TERR_CODE AND ET.VcReport_Type=@ReportType AND MWork.MC_DISCRIPTION1=ET.VcDescription 
                AND MWork.MC_DISCRIPTION2=ET.VcDescription2
                AND CONVERT(BIGINT,ET.VcDate)>= CONVERT(BIGINT,@FromDate)
                AND CONVERT(BIGINT,ET.VcDate)<= CONVERT(BIGINT,@ToDate)
                LEFT JOIN Exception_TransDetails AS ETD ON ET.Id=ETD.ExceptionId
        WHERE MC_REP_TYPE=@ReportType AND MWork.MC_DISCRIPTION1='WSSTTY Lowbase'
        AND MWork.MC_CLUST_CODE IN (SELECT * FROM [dbo].[SplitCommaString](@ClusterCode)) 
        AND CONVERT(BIGINT,MWork.MC_DATE)>= CONVERT(BIGINT,@FromDate)
        AND CONVERT(BIGINT,MWork.MC_DATE)<= CONVERT(BIGINT,@ToDate)
    END

I have one record which contains value for clustercode but MWork.MC_TERR_CODe is NULL
SO IN IFEXIST it gives me record as clustercode is there but AND VcTerr_Code=MWork.MC_TERR_CODE condition fails
so it dont give me record..can i check territorycode in if condition
Posted

Please check
AND (ISNULL(VcTerr_Code,0)=ISNULL(MWork.MC_TERR_CODE,0))
   
Comments
cid_moossaa 28-Oct-11 11:17am
   
good one
Does this help?

AND (VcTerr_Code=MWork.MC_TERR_CODE OR MWork.MC_TERR_CODE is null)
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900