Re: CASE? DECODE?

From: <mtek_at_mtekusa.com>
Date: Thu, 20 Mar 2008 05:31:30 -0700 (PDT)
Message-ID: <e14f8636-810f-4d22-ae36-f656f499cb56@13g2000hsb.googlegroups.com>

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

I'm not sure I can use the 'WITH' clause with the IN and NOT IN clause. Also, there is the LIST_TYPE I am trying to use to identify which query the email address came from.......

And your comments about the relationship: We just need to eliminate certain email addresses that meet a different criteria, using different tables......

WITH ameritrade AS (

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) BETWEEN TO_DATE('03172008','MMDDYYYY') AND
TO_DATE('03192008','MMDDYYYY')
     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 (+))

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, 'HOUSE' list_type FROM customer c, customer_account ca, visitor v, subscr_email_product s, customer_address cd

WHERE c.date_registered BETWEEN p_start_date AND p_end_date

   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 ameritrade; Received on Thu Mar 20 2008 - 07:31:30 CDT

This message: [ Message body ]
Next message: Shakespeare: "Re: Oracle 10.2.0.3.0 install on a desktop"
Previous message: mtek_at_mtekusa.com: "Re: CASE? DECODE?"
In reply to: Shakespeare: "Re: CASE? DECODE?"
Next in thread: Shakespeare: "Re: CASE? DECODE?"
Reply: Shakespeare: "Re: CASE? DECODE?"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message