Home » SQL & PL/SQL » SQL & PL/SQL » sql query not working (oracle 10g,winxp)
sql query not working [message #326262] |
Tue, 10 June 2008 22:29  |
swas_fly
Messages: 220 Registered: March 2008 Location: Bhubaneswar
|
Senior Member |
|
|
I have just given onl value in the 'IN' condition but i have 5000 of then
SQL> select a.ac_contact_key,a.AC_CUSTOMER_GROUP_KEY,b.ACG_LE_CODE,a.AC_TITLE,a.AC_FORENAME,a.AC_SUR
NAME,a.AC_FULL_NAME,
2 c.ACTC_TELEPHONE_NUMBER,c.ACTC_TPS_CONSENT_VALUE,c.ACTC_CTPS_CONSENT_VALUE,c.ACTC_DNC_CONSENT_V
ALUE,c.actc_telephone_number_type,
3 d.ACAC_DNM_CONSENT_VALUE,d.ACAC_MPS_CONSENT_VALUE,d.ACAC_CONSENT_OVERRIDE
4 from sven_lenin_prod.ace2_contact a,
5 sven_lenin_prod.ace2_customer_group b,
6 sven_lenin_prod.ace2_contact_telephone_consent c,
7 sven_lenin_prod.ace2_contact_address_consent d
8 where b.acg_customer_group_key=a.AC_customer_group_key
9 and a.ac_contact_key in ('2010092610')
10 and c.actc_contact_key(+)=a.ac_contact_key
11 and d.acac_contact_key(+)=a.ac_contact_key
12 and NVL(d.acac_deleted_flg, 'N') = 'N'
13 AND NVL(d.acac_primary_flg, 'N') = 'Y'
14 and NVL(c.actc_deleted_flg, 'N') = 'N'
15 AND NVL(c.actc_primary_flg, 'N') = 'Y';
or d.acac_contact_key(+)=a.ac_contact_key
*
ERROR at line 11:
ORA-01719: outer join operator (+) not allowed in operand of OR or IN
I get a proper result if the record is present the value i specify in the IN condition is present in all the tables?But not in this case
How can i avoid this?
'2010092610' this particular value is present in
ace2_contact
ace2_contact_telephone_consent
but not in ace2_contact_address_consent
but i can only give a list of values in a.ac_contact_key in ('2010092610')??
as per my understanding (+) cannot be used in 'IN' operator so how to modify?
[Updated on: Tue, 10 June 2008 22:32] Report message to a moderator
|
|
|
|
Re: sql query not working [message #326269 is a reply to message #326262] |
Tue, 10 June 2008 23:20   |
apps_user
Messages: 35 Registered: May 2008
|
Member |
|
|
Try this way.
Take another table alias
sven_lenin_prod.ace2_contact b
and join sven_lenin_prod.ace2_contact a with sven_lenin_prod.ace2_contact b by ac_contact_key and
use the following:
b.ac_contact_key in ('2010092610')
|
|
|
|
Re: sql query not working [message #326282 is a reply to message #326262] |
Tue, 10 June 2008 23:53   |
apps_user
Messages: 35 Registered: May 2008
|
Member |
|
|
This is based on the assumption that
sven_lenin_prod.ace2_contact table has ac_contact_key as primary key.
select a.ac_contact_key,a.AC_CUSTOMER_GROUP_KEY,b.ACG_LE_CODE,a.AC_TITLE,a.AC_FORENAME,a.AC_SUR
NAME,a.AC_FULL_NAME,c.ACTC_TELEPHONE_NUMBER,c.ACTC_TPS_CONSENT_VALUE,c.ACTC_CTPS_CONSENT_VALUE,c.ACTC_DNC_CONSENT_V
ALUE,c.actc_telephone_number_type, d.ACAC_DNM_CONSENT_VALUE,d.ACAC_MPS_CONSENT_VALUE,d.ACAC_CONSENT_OVERRIDE
from sven_lenin_prod.ace2_contact a,
sven_lenin_prod.ace2_contact x,
sven_lenin_prod.ace2_customer_group b,
sven_lenin_prod.ace2_contact_telephone_consent c,
sven_lenin_prod.ace2_contact_address_consent d
where a.ac_contact_key=x.ac_contact_key
and b.acg_customer_group_key=a.AC_customer_group_key
and x.ac_contact_key in ('2010092610')
and c.actc_contact_key(+)=a.ac_contact_key
and d.acac_contact_key(+)=a.ac_contact_key
and NVL(d.acac_deleted_flg, 'N') = 'N'
and NVL(d.acac_primary_flg, 'N') = 'Y'
and NVL(c.actc_deleted_flg, 'N') = 'N'
and NVL(c.actc_primary_flg, 'N') = 'Y';
or d.acac_contact_key(+)=a.ac_contact_key
|
|
|
Re: sql query not working [message #326291 is a reply to message #326282] |
Wed, 11 June 2008 00:05   |
swas_fly
Messages: 220 Registered: March 2008 Location: Bhubaneswar
|
Senior Member |
|
|
SQL> select a.ac_contact_key,a.AC_CUSTOMER_GROUP_KEY,b.ACG_LE_CODE,a.AC_TITLE,a.AC_FORENAME,a.AC_SUR
NAME,
2 a.AC_FULL_NAME,c.ACTC_TELEPHONE_NUMBER,c.ACTC_TPS_CONSENT_VALUE,c.ACTC_CTPS_CONSENT_VALUE,c.ACT
C_DNC_CONSENT_VALUE,
3 c.actc_telephone_number_type, d.ACAC_DNM_CONSENT_VALUE,d.ACAC_MPS_CONSENT_VALUE,d.ACAC_CONSENT_
OVERRIDE
4 from sven_lenin_prod.ace2_contact a,
5 sven_lenin_prod.ace2_contact x,
6 sven_lenin_prod.ace2_customer_group b,
7 sven_lenin_prod.ace2_contact_telephone_consent c,
8 sven_lenin_prod.ace2_contact_address_consent d
9 where a.ac_contact_key=x.ac_contact_key
10 and b.acg_customer_group_key=a.AC_customer_group_key
11 and x.ac_contact_key in ('2010092610')
12 and c.actc_contact_key(+)=a.ac_contact_key
13 and d.acac_contact_key(+)=a.ac_contact_key
14 and NVL(d.acac_deleted_flg, 'N') = 'N'
15 and NVL(d.acac_primary_flg, 'N') = 'Y'
16 and NVL(c.actc_deleted_flg, 'N') = 'N'
17 and NVL(c.actc_primary_flg, 'N') = 'Y'
18 or d.acac_contact_key(+)=a.ac_contact_key
19 /
and c.actc_contact_key(+)=a.ac_contact_key
*
ERROR at line 12:
ORA-01719: outer join operator (+) not allowed in operand of OR or IN
still get the same error
|
|
|
|
Goto Forum:
Current Time: Sun Feb 09 22:27:51 CST 2025
|