Re: CASE? DECODE?

On Mar 19, 2:12 pm, "Shakespeare" <what..._at_xs4all.nl> wrote:
> <m..._at_mtekusa.com> schreef in berichtnews: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
> unkn..._at_acme.com ) you will end up mixing up the customers....
>
> Shakespeare

Well, basically I am trying to create a list of customers. The second half of the query are the customers I do NOT want in the first query, and I really do not want to create two separate cursors or something like that. I'm using the last column as the identifier column.

I'm always for more compact code, so anything that would cut some of the code down, so I'm open for that.

Thanks! Received on Thu Mar 20 2008 - 06:51:26 CDT