Re: CASE? DECODE?

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 19 Mar 2008 20:12:54 +0100
Message-ID: <47e165b7$0$14359$e4fe514c@news.xs4all.nl>

<mtek_at_mtekusa.com> schreef in bericht
news:439a3392-bd48-4a12-b9cd-134e5612074f_at_m44g2000hsc.googlegroups.com...

> On Mar 19, 11:50 am, "Shakespeare" <what..._at_xs4all.nl> wrote:

>> <m..._at_mtekusa.com> schreef in
>> berichtnews:89edba08-889c-4537-93cd-612068fbf54c_at_s50g2000hsb.googlegroups.com...
>>
>>
>>
>>
>>
>> > Hi,
>>
>> > I have the following query:
>>
>> > SELECT c.email || '|' || ca.first_name || '|' || ca.last_name ||
>> > '|' || 'ZACKS' || '|' ||
>> > TO_CHAR(c.date_registered, 'mmddrr hh24:mi:ss') || '|' ||
>> > TO_CHAR(TRUNC(SYSDATE-1),'MM-DD-YYYY') || '|' ||
>> > v.adid || '|' || cd.day_phone line
>> > FROM customer c, customer_account ca, visitor v,
>> > subscr_email_product s, customer_address cd
>> > WHERE TRUNC(c.date_registered) = TRUNC(SYSDATE-1)
>> > AND c.customer_id = ca.customer_id(+)
>> > AND c.customer_id = s.customer_id
>> > AND c.customer_id = v.customer_id
>> > AND c.customer_id = cd.customer_id(+)
>> > AND s.email_product_id = 'HL'
>> > AND email IN (
>> > SELECT email
>> > FROM customer c, customer.subscriptions s, customer.product p,
>> > customer_address ca, customer_account ct, visitor v
>> > WHERE ca.address_type_id = 1
>> > AND S.STATUS=1
>> > AND S.sell_rep_id IN (201, 202)
>> > AND p.produst_id=1
>> > AND TRUNC(start_date) = TRUNC(SYSDATE-1)
>> > AND p.produst_id = s.produst_id
>> > AND c.customer_id= s.customer_id
>> > AND c.customer_id = ca.customer_id
>> > AND c.customer_id = ct.customer_id
>> > AND c.customer_id = v.customer_id (+));
>>
>> > Actually, I need 2 queries, the only difference being that one uses
>> > the IN and the other will use NOT IN.
>>
>> > I would like to combine these into 1 query with a value so I can tell
>> > which query the value came from. So, for example, If I execute this
>> > query could I have something like:
>>
>> > DATA|DATA|DATA|X
>> > DATA|DATA|DATA|Y
>>
>> > Where I can tell by the X or Y whether the record came from the IN
>> > portion or the NOT IN portion? It would make it easier in the coding
>> > as I can just test for the value of X or Y and do whatever......
>>
>> > Regards
>>
>> By using a union, and append a column value 'X' in the first part, and
>> 'Y'
>> in the second part. (I hope I understood your question right).
>>
>> Select <your columns>, 'X' indicator_col
>> union
>> select <your columns>, 'Y' inidcator_col
>>
>> Shakespeare

>
> So, how would that query be structured?
>
>
>     SELECT c.email || '|' || ca.first_name || '|' || ca.last_name ||
> '|' || 'ZACKS' || '|' ||
>        TO_CHAR(c.date_registered, 'mmddrr hh24:mi:ss') || '|' ||
> TO_CHAR(TRUNC(SYSDATE-1),'MM-DD-YYYY') || '|' ||
>        v.adid || '|' || cd.day_phone line || a.stuff
>     FROM customer c, customer_account ca, visitor v,
> subscr_email_product s, customer_address cd
>     WHERE TRUNC(c.date_registered) = TRUNC(SYSDATE-1)
>     AND c.customer_id = ca.customer_id(+)
>     AND c.customer_id = s.customer_id
>     AND c.customer_id = v.customer_id
>     AND c.customer_id = cd.customer_id(+)
>     AND s.email_product_id = 'HL'
>     AND email IN (
>      SELECT email, 'X' stuff
>      FROM customer c, customer.subscriptions s, customer.product p,
> customer_address ca, customer_account ct, visitor v
>      WHERE ca.address_type_id = 1
>        AND S.STATUS=1
>        AND S.sell_rep_id IN (201, 202)
>        AND p.produst_id=1
>        AND TRUNC(start_date) = TRUNC(SYSDATE-1)
>        AND p.produst_id = s.produst_id
>        AND c.customer_id= s.customer_id
>        AND c.customer_id = ca.customer_id
>        AND c.customer_id = ct.customer_id
>        AND c.customer_id = v.customer_id (+)
>      UNION
>      SELECT email, 'Y' stuff
>      FROM customer c, customer.subscriptions s, customer.product p,
> customer_address ca, customer_account ct, visitor v
>      WHERE ca.address_type_id = 1
>        AND S.STATUS=1
>        AND S.sell_rep_id NOT IN (201, 202)
>        AND p.produst_id=1
>        AND TRUNC(start_date) = TRUNC(SYSDATE-1)
>        AND p.produst_id = s.produst_id
>        AND c.customer_id= s.customer_id
>        AND c.customer_id = ca.customer_id
>        AND c.customer_id = ct.customer_id
>        AND c.customer_id = v.customer_id (+) a);
>

No, I don't think it will work...
Actually, I was thinking of

     SELECT c.email || '|' || ca.first_name || '|' || ca.last_name || '|' || 'ZACKS' || '|' ||

        TO_CHAR(c.date_registered, 'mmddrr hh24:mi:ss') || '|' ||
        TO_CHAR(TRUNC(SYSDATE-1),'MM-DD-YYYY') || '|' ||
        v.adid || '|' || cd.day_phone line,
       'X' indicator_col
     FROM customer c, customer_account ca, visitor v,
subscr_email_product s, customer_address cd
     WHERE TRUNC(c.date_registered) = TRUNC(SYSDATE-1)
     AND c.customer_id = ca.customer_id(+)
     AND c.customer_id = s.customer_id
     AND c.customer_id = v.customer_id
     AND c.customer_id = cd.customer_id(+)
     AND s.email_product_id = 'HL'
     AND email IN (
      SELECT email
      FROM customer c, customer.subscriptions s, customer.product p,
                customer_address ca, customer_account ct, visitor v
      WHERE ca.address_type_id = 1
        AND S.STATUS=1
        AND S.sell_rep_id IN (201, 202)
        AND p.produst_id=1
        AND TRUNC(start_date) = TRUNC(SYSDATE-1)
        AND p.produst_id = s.produst_id
        AND c.customer_id= s.customer_id
        AND c.customer_id = ca.customer_id
        AND c.customer_id = ct.customer_id
        AND c.customer_id = v.customer_id (+))
union
     SELECT c.email || '|' || ca.first_name || '|' || ca.last_name ||
'|' || 'ZACKS' || '|' ||
        TO_CHAR(c.date_registered, 'mmddrr hh24:mi:ss') || '|' ||
        TO_CHAR(TRUNC(SYSDATE-1),'MM-DD-YYYY') || '|' ||
        v.adid || '|' || cd.day_phone line,
       'Y' indicator_col
     FROM customer c, customer_account ca, visitor v,
subscr_email_product s, customer_address cd
     WHERE TRUNC(c.date_registered) = TRUNC(SYSDATE-1)
     AND c.customer_id = ca.customer_id(+)
     AND c.customer_id = s.customer_id
     AND c.customer_id = v.customer_id
     AND c.customer_id = cd.customer_id(+)
     AND s.email_product_id = 'HL'
     AND email NOT IN (
      SELECT email
      FROM customer c, customer.subscriptions s, customer.product p,
                customer_address ca, customer_account ct, visitor v
      WHERE ca.address_type_id = 1
        AND S.STATUS=1
        AND S.sell_rep_id IN (201, 202)
        AND p.produst_id=1
        AND TRUNC(start_date) = TRUNC(SYSDATE-1)
        AND p.produst_id = s.produst_id
        AND c.customer_id= s.customer_id
        AND c.customer_id = ca.customer_id
        AND c.customer_id = ct.customer_id
        AND c.customer_id = v.customer_id (+));

I'm sure there are more efficient methods. You could even create a view for the select in the 'in' clause, or use the with ... as construction. This one is simple to read though.

The thing that bothers me in this query is that there is no relationship between the customer record in the main query and the one in the 'in' clause. If there is some kind of default for unknown email addresses (like unknown_at_acme.com ) you will end up mixing up the customers....

Shakespeare Received on Wed Mar 19 2008 - 14:12:54 CDT