CASE? DECODE?

#1 (**permalink**) 03-20-2008, 01:46 PM

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

#2 (**permalink**) 03-20-2008, 01:46 PM

<mtek@mtekusa.com> schreef in bericht
news:89edba08-889c-4537-93cd-612068fbf54c@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

#3 (**permalink**) 03-20-2008, 01:46 PM

On Mar 19, 11:50 am, "Shakespeare" <what...@xs4all.nl> wrote:
> <m...@mtekusa.com> schreef in berichtnews:89edba08-889c-4537-93cd-612068fbf54c@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);

#4 (**permalink**) 03-20-2008, 01:46 PM

<mtek@mtekusa.com> schreef in bericht
news:439a3392-bd48-4a12-b9cd-134e5612074f@m44g2000hsc.googlegroups.com...
> On Mar 19, 11:50 am, "Shakespeare" <what...@xs4all.nl> wrote:
>> <m...@mtekusa.com> schreef in
>> berichtnews:89edba08-889c-4537-93cd-612068fbf54c@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@acme.com ) you will end up mixing up the customers....

Shakespeare

#5 (**permalink**) 03-20-2008, 01:46 PM

On Mar 19, 2:12 pm, "Shakespeare" <what...@xs4all.nl> wrote:
> <m...@mtekusa.com> schreef in berichtnews:439a3392-bd48-4a12-b9cd-134e5612074f@m44g2000hsc.googlegroups.com...
>
>
>
> > On Mar 19, 11:50 am, "Shakespeare" <what...@xs4all.nl> wrote:
> >> <m...@mtekusa.com> schreef in
> >> berichtnews:89edba08-889c-4537-93cd-612068fbf54c@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...@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!

#6 (**permalink**) 03-20-2008, 01:46 PM

On Mar 19, 2:12 pm, "Shakespeare" <what...@xs4all.nl> wrote:
> <m...@mtekusa.com> schreef in berichtnews:439a3392-bd48-4a12-b9cd-134e5612074f@m44g2000hsc.googlegroups.com...
>
>
>
> > On Mar 19, 11:50 am, "Shakespeare" <what...@xs4all.nl> wrote:
> >> <m...@mtekusa.com> schreef in
> >> berichtnews:89edba08-889c-4537-93cd-612068fbf54c@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...@acme.com ) you will end up mixing up the customers....
>
> Shakespeare

Can I use the 'WITH' clause with an IN or NOT IN clause?

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;

#7 (**permalink**) 03-20-2008, 01:46 PM

On Mar 19, 2:12 pm, "Shakespeare" <what...@xs4all.nl> wrote:
> <m...@mtekusa.com> schreef in berichtnews:439a3392-bd48-4a12-b9cd-134e5612074f@m44g2000hsc.googlegroups.com...
>
>
>
> > On Mar 19, 11:50 am, "Shakespeare" <what...@xs4all.nl> wrote:
> >> <m...@mtekusa.com> schreef in
> >> berichtnews:89edba08-889c-4537-93cd-612068fbf54c@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...@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.......

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;

#8 (**permalink**) 03-20-2008, 01:46 PM

On Mar 19, 2:12 pm, "Shakespeare" <what...@xs4all.nl> wrote:
> <m...@mtekusa.com> schreef in berichtnews:439a3392-bd48-4a12-b9cd-134e5612074f@m44g2000hsc.googlegroups.com...
>
>
>
> > On Mar 19, 11:50 am, "Shakespeare" <what...@xs4all.nl> wrote:
> >> <m...@mtekusa.com> schreef in
> >> berichtnews:89edba08-889c-4537-93cd-612068fbf54c@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...@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;

#9 (**permalink**) 03-20-2008, 01:46 PM

<mtek@mtekusa.com> schreef in bericht
news:e14f8636-810f-4d22-ae36-f656f499cb56@13g2000hsb.googlegroups.com...
> On Mar 19, 2:12 pm, "Shakespeare" <what...@xs4all.nl> wrote:
>> <m...@mtekusa.com> schreef in
>> berichtnews:439a3392-bd48-4a12-b9cd-134e5612074f@m44g2000hsc.googlegroups.com...
>>
>>

zzzzzzzzzzaaaaaaaaaapppppppppppppp

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

Ok, then it makes sense to me!

I'm not sure about the with..as but why don't you give it a try?

Shakespeare

#10 (**permalink**) 03-20-2008, 01:46 PM

On Mar 20, 8:36 am, "Shakespeare" <what...@xs4all.nl> wrote:
> <m...@mtekusa.com> schreef in berichtnews:e14f8636-810f-4d22-ae36-f656f499cb56@13g2000hsb.googlegroups.com...
>
> > On Mar 19, 2:12 pm, "Shakespeare" <what...@xs4all.nl> wrote:
> >> <m...@mtekusa.com> schreef in
> >> berichtnews:439a3392-bd48-4a12-b9cd-134e5612074f@m44g2000hsc.googlegroups.com...
>
> zzzzzzzzzzaaaaaaaaaapppppppppppppp
>
> >And your comments about the relationship: We just need to eliminate
> >certain email addresses that meet a different criteria, using
> >different tables......
>
> Ok, then it makes sense to me!
>
> I'm not sure about the with..as but why don't you give it a try?
>
> Shakespeare

Well, I did, and it does not work. But, I thought you may know if I
was using the wrong syntax.

At any rate, I'll keep trying to think of more ways to compact the
code. Specifically the second part of the query.....

Thanks!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode