Home » SQL & PL/SQL » SQL & PL/SQL » sql query not working (oracle 10g,winxp)
icon2.gif  sql query not working [message #326262] Tue, 10 June 2008 22:29 Go to next message
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 #326264 is a reply to message #326262] Tue, 10 June 2008 23:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand what you said but try to use ANSI syntax instead of Oracle one.

Regards
Michel
Re: sql query not working [message #326269 is a reply to message #326262] Tue, 10 June 2008 23:20 Go to previous messageGo to next message
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 #326271 is a reply to message #326264] Tue, 10 June 2008 23:24 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
thanks bt can you please let me know where to use it here,i haven't used this syntax before
Re: sql query not working [message #326282 is a reply to message #326262] Tue, 10 June 2008 23:53 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Shocked
Re: sql query not working [message #326295 is a reply to message #326291] Wed, 11 June 2008 00:16 Go to previous message
apps_user
Messages: 35
Registered: May 2008
Member
You have to use ANSI sql as Michel suggested earlier.
Here is the link and an example how to resolve this error.

http://awads.net/wp/2006/03/22/back-to-basics-outer-joins/
Previous Topic: partition type selection
Next Topic: Error- invalid number in query
Goto Forum:
  


Current Time: Sat Dec 10 14:43:00 CST 2016

Total time taken to generate the page: 0.12309 seconds