1 2 Previous Next 15 Replies Latest reply: Apr 2, 2013 2:39 PM by Frank Kulash Go to original post RSS
      • 15. Re: Please Help: query matching string value in WHERE clause
        Frank Kulash
        Hi,
        SMCR wrote:
        Yes, I realized that. For the purpose i am using i will not have any issue with '~'
        Even so, I would not concatenate the names.
        I did however changed it up a little, here is how it looks like:
        SELECT  CUSTOMER_ID, CUSTOMER_FNAME, CUSTOMER_LNAME, DATE_OF_BIRTH
        FROM    CUSTOMERS
        WHERE   (CUSTOMER_FNAME||'~'||CUSTOMER_LNAME, DATE_OF_BIRTH) IN
        (
        (('JOHN~DOE'), (TO_DATE('20130101', 'YYYYMMDD'))),
        (('MIKE~MILLER'), (TO_DATE('20130101', 'YYYYMMDD')))
        );
        What are you selling? I see that some of your customers are only 3 months old!

        You can cut out some of the parentheses.
        That is, when you are comparing tuples, after the IN operator you need 2 sets of parentheses:
        (1) to enclose the entire IN-list, and
        (2) to enclose each tuple
        Instead of the WHERE clause you posted above, you could say:
        WHERE   (CUSTOMER_FNAME||'~'||CUSTOMER_LNAME, DATE_OF_BIRTH) IN
        (
          ('JOHN~DOE',        TO_DATE ('20130101', 'YYYYMMDD')),
          ('MIKE~MILLER',  TO_DATE ('20130101', 'YYYYMMDD'))
        );
        or, what I recommend:
        WHERE   (CUSTOMER_FNAME,CUSTOMER_LNAME, DATE_OF_BIRTH) IN
        (
          ('JOHN','DOE',     TO_DATE ('20130101', 'YYYYMMDD')),
          ('MIKE','MILLER',  TO_DATE ('20130101', 'YYYYMMDD'))
        );
        With only 2 tuples, this is actually less typing than you need with the concatenation approach.

        If you think the extra parentheses make the code more clear, or help you in any way, then keep on using them: they won't give you wriong results or make things inefficient. You should know that they're not needed, however.

        If you want to reduce typing even more, you can use DATE literals instead of TO_DATE:
        WHERE   (CUSTOMER_FNAME,CUSTOMER_LNAME, DATE_OF_BIRTH) IN
        (
          ('JOHN','DOE',     DATE '2013-01-01'),
          ('MIKE','MILLER',  DATE '2013-01-01')
        );
        1 2 Previous Next