Re: CASE? DECODE?

<mtek_at_mtekusa.com> schreef in bericht
news: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 Received on Wed Mar 19 2008 - 11:50:55 CDT