Re: CASE? DECODE?

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);