开发者

Trying to understand a SQL where clause

I am hoping someone can help interpret the last code line (@Active=1....) for this SQL clause:

SELECT DISTINCT LOC_ID
    ,LOC_CODE
    ,ADDR_LINE_1
    ,ADDR_LINE_2
    ,ADDR_LINE_3
    ,CITY
    ,STATE
    ,COUNTRY
    ,POSTAL_CODE
    ,COMPANY
    ,OPERATION_TYPE
    ,PROCESS
        ,ADDR_LINE_1 + ',' +ADDR_LINE_2+ ',' + CITY + '-' + COUNTRY + ' ' + POSTAL_CODE AS ADDRESS
        ,COMPANY + '.' + CENTER + '.' + OPERATION_TYPE + '.' + PROCESS AS Proc_Combo
    ,CASE   WHEN INACTIVE_DATE IS NULL THEN 'Active'
        WHEN INACTIVE_DATE IS NOT NULL THEN 'InActive'
    ELSE 'UNKNOWN' END AS INACTIVE_DATE
    ,CASE   WHEN INSTANCE_ID = 1 THEN 'AP' 
        WHEN INSTANCE_ID = 2 THEN 'GAP'     
    ELSE 'ALL' END AS INSTANCE_ID
FROM HR_Locations
WHERE
    (@STATE IS NOT NULL AND STATE =@STATE OR @STATE='' AND STATE =STATE OR @STATE IS NULL AND 1=1) 
AND
    (@ACTIVE=1 AND INACTIVE_DATE IS NULL OR @ACTIVE=2 AND INACTIVE_DATE IS NOT N开发者_StackOverflowULL OR @ACTIVE=0 AND 1=1 OR @ACTIVE IS NULL)  

I have encountered code like this, only this is not contained in an "IF" block


More easily understood when re-written as

(@ACTIVE=1 AND INACTIVE_DATE IS NULL)
OR (@ACTIVE=2 AND INACTIVE_DATE IS NOT NULL)
OR (@ACTIVE=0 AND 1=1)
OR @ACTIVE IS NULL

The part that seems interesting to me is (@ACTIVE=0 AND 1=1). This will AND the condition @ACTIVE=0 with the value TRUE. It seems to me it might be a typo. I've seen things like (@ACTIVE=0 OR 1=1) to make the condition sort of "optional", but in this case it looks unecessary. Can you try running they query without AND 1=1 to see if the results are the same?


Not sure what the 1=1 is for ...

@ACTIVE=1 AND INACTIVE_DATE IS NULL OR
@ACTIVE=2 AND INACTIVE_DATE IS NOT NULL OR
@ACTIVE=0 AND 1=1 OR
@ACTIVE IS NULL

It appears as though the where clause is validating the @Active flag and the inactive date to make sure that if the status is active then the inactive is null and vise-versa.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜