none
Help filtering out data from a complex query RRS feed

  • Question

  • Hello all,

    I need help filtering out data from this query. I need to show all "33" codes that are in columns Code1, Code2, Code3 and Code4. I also need to filter out all "62" codes in Code1, Code2, Code3 and Code4 columns.

    So if codes are 33 62 33 40, I need to filter out this column.

    23 33 45 56 is ok

    76 12 23 33 is ok

    21 23 33 62 is NOT ok.

    Can anyone help me?

    SELECT Format(DateSerial(Year(Now()),Month(Now()),Day(Now() - 1)),"mm/dd/yyyy") AS ReportDate, DEME.EffDate, DEME.MbrNo, Right(DEME.TraceNum,6) AS CardNum, IIf(Left(DEME.TraceNum,6)="581112","A",IIf(Left(DEME.TraceNum,6)="550849","D","")) AS CardType, Trim(B.FIRST_NAME) & " " & Trim(B.MID_INITIAL) & " " & Trim(B.LAST_NAME) AS PrimaryMemberName, Trim(JO.JT_FIRST_NAME) & " " & Trim(JO.JT_MID_INITIAL) & " " & Trim(JO.JT_LAST_NAME) AS JointMemberName, Format(B.PHONE,"###-###_####") AS PrimaryPhoneNum, B.WORK_PHONE AS WorkPhoneNum, iif(S.SHARE_OPEN_DATE = "" , "" , DateSerial(LEFT(S.SHARE_OPEN_DATE_CC & S.SHARE_OPEN_DATE,4),Mid(S.SHARE_OPEN_DATE_CC & S.SHARE_OPEN_DATE,5,2),Right(S.SHARE_OPEN_DATE_CC & S.SHARE_OPEN_DATE,2))) AS CheckingOpenDate, B.OD_SCORE AS ODPLimit, S.OLP_CARD AS ExtendedCoverageElection, DEME.TranCode, DEME.BalType, DEME.TranAmt, DEME.Interest, DEME.Fee, DEME.ResultBal, DEME.MiscField, DEME.Post, DEME.Code1, DEME.Code2, DEME.Code3, DEME.Code4
    FROM (((SELECT * FROM DEBIT_EXCP as DE WHERE DE.BalType="S 4"AND ((DE.Code1) = "33") OR  ((DE.Code2) = "33") OR ((DE.Code3) = "33")  OR  ((DE.Code4) = "33")     
    UNION ALL SELECT * FROM MAC_EXCP as ME WHERE ME.BalType="S 4" AND ((ME.Code1) = "33") OR  ((ME.Code2) = "33") OR ((ME.Code3) = "33")  OR  ((ME.Code4) = "33") )  AS DEME 
    LEFT JOIN BASICS AS B ON B.MEMBER_NUMBER = DEME.MbrNo) 
    LEFT JOIN JOINT_OWNER AS JO ON B.MBR_KEY = JO.MBR_KEY) 
    LEFT JOIN SHARES AS S ON B.MBR_KEY = S.MBR_KEY
    WHERE (   ((DEME.Code1) <> "62") OR  ((DEME.Code2) <> "62") OR ((DEME.Code3) <> "62")  OR  ((DEME.Code4) <> "62") AND S.SHARE_NUMBER="040");
    

     

    Sunday, July 26, 2015 5:16 PM

Answers

  • As far as I can tell, the condition that at least one column contains 33 is built into the join. To exclude records where at least one column contains 62, you need to use AND instead of OR:


    WHERE ( ((DEME.Code1) <> "62") AND ((DEME.Code2) <> "62") AND ((DEME.Code3) <> "62")  AND ((DEME.Code4) <> "62") AND S.SHARE_NUMBER="040");

    If the Code1 ... Code4 fields can be blank, it's slightly more complicated:

    WHERE ( ((DEME.Code1) <> "62" OR DEME.Code1 Is Null) AND ((DEME.Code2) <> "62" OR DEME.Code2 Is Null) AND ((DEME.Code3) <> "62" OR DEME.Code3 Is Null)  AND ((DEME.Code4) <> "62" OR DEME.Code4 Is Null) AND S.SHARE_NUMBER="040");


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by achurchill Sunday, July 26, 2015 5:52 PM
    Sunday, July 26, 2015 5:25 PM

All replies

  • As far as I can tell, the condition that at least one column contains 33 is built into the join. To exclude records where at least one column contains 62, you need to use AND instead of OR:


    WHERE ( ((DEME.Code1) <> "62") AND ((DEME.Code2) <> "62") AND ((DEME.Code3) <> "62")  AND ((DEME.Code4) <> "62") AND S.SHARE_NUMBER="040");

    If the Code1 ... Code4 fields can be blank, it's slightly more complicated:

    WHERE ( ((DEME.Code1) <> "62" OR DEME.Code1 Is Null) AND ((DEME.Code2) <> "62" OR DEME.Code2 Is Null) AND ((DEME.Code3) <> "62" OR DEME.Code3 Is Null)  AND ((DEME.Code4) <> "62" OR DEME.Code4 Is Null) AND S.SHARE_NUMBER="040");


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by achurchill Sunday, July 26, 2015 5:52 PM
    Sunday, July 26, 2015 5:25 PM
  • Yeah the code fields can have one and the rest blank. So they can look like

    33 45 23 blank

    62 blank blank blank


    I will try it out.
    • Edited by achurchill Sunday, July 26, 2015 5:32 PM
    Sunday, July 26, 2015 5:31 PM
  • Hans thank you very much for your help it worked like a charm.
     I had to filter out the nulls with the second part that you posted.
    • Edited by achurchill Sunday, July 26, 2015 5:53 PM
    Sunday, July 26, 2015 5:52 PM