[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Need help on oracle sql query

Posted on 2014-08-07
10
Medium Priority
?
328 Views
Last Modified: 2014-08-18
SELECT  A.NO, G.ID
    FROM ABC A, DEF D, GHI G
   WHERE A.SYSTEM_CODE = 'AAA'
     AND A.SYSTEM_ID =  TO_CHAR(D.BID)
     AND D.CATID = G.CATID
     AND NOT EXISTS (SELECT 1
                       FROM XYZ
                      WHERE NO = A. NO
                        AND ID = G.ID);
--
From below query data is not coming.  
But when we pass the hard code value in BID then data is coming propery.  
Even if we remove the NOT then also give tha data properly. But when we checking NOT EXISTS then no rows selected.                  

 SELECT  A.NO, G.ID
    FROM ABC A, DEF D, GHI G
   WHERE A.SYSTEM_CODE = 'AAA'
     AND A.SYSTEM_ID =  '111'  --TO_CHAR(D.BID)
     AND D.CATID = G.CATID
     AND NOT EXISTS (SELECT 1
                       FROM XYZ
                      WHERE NO = A. NO
                        AND ID = G.ID);

I dont want to give hard code value.
Please give me  the solution .
0
Comment
Question by:digs developer
  • 5
  • 4
10 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 40246144
what values does D.BID  have?
0
 

Author Comment

by:digs developer
ID: 40246173
its number such as 5252,5406 etc.
0
 

Author Comment

by:digs developer
ID: 40246206
Please help
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:digs developer
ID: 40246266
Please help !
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40246281
do any of those numbers exist in A?

where I'm going is your query works with 111, but if it doesn't work with D.BID then it's because the values in D.BID aren't valid for that join condition.  

in other words - The query works, the data doesn't.
0
 

Author Comment

by:digs developer
ID: 40246334
Its is the valid for join...D.BID  contains the value 111
0
 

Author Comment

by:digs developer
ID: 40246417
Now it is working fine ... its data issue.
Thank you !
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40246420
I set up a test case based on your masked tables and it works for me:

create table abc (no char(1), system_code char(3), system_id varchar2(10));
create table def (catid char(1), bid varchar2(10));
create table ghi (id char(1), catid char(1));

create table xyz (no char(1), id char(1));

insert into abc values('a','AAA','111');
insert into def values('a','111');
insert into ghi values('a','a');
commit;


SELECT  A.NO, G.ID
     FROM ABC A, DEF D, GHI G
    WHERE A.SYSTEM_CODE = 'AAA'
      AND A.SYSTEM_ID =  '111'  --TO_CHAR(D.BID)
      AND D.CATID = G.CATID
      AND NOT EXISTS (SELECT 1
                        FROM XYZ
                       WHERE NO = A. NO
                         AND ID = G.ID);


SELECT  A.NO, G.ID
     FROM ABC A, DEF D, GHI G
    WHERE A.SYSTEM_CODE = 'AAA'
      AND A.SYSTEM_ID = TO_CHAR(D.BID)
      AND D.CATID = G.CATID
      AND NOT EXISTS (SELECT 1
                        FROM XYZ
                       WHERE NO = A. NO
                         AND ID = G.ID);

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40246430
glad I could help
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 1600 total points
ID: 40246500
digsdeveloper - I see you're new here.  Welcome aboard!

please remember to close your questions when you get your answer.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question