Need help on oracle sql query

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 .
digs developerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
what values does D.BID  have?
0
digs developerAuthor Commented:
its number such as 5252,5406 etc.
0
digs developerAuthor Commented:
Please help
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

digs developerAuthor Commented:
Please help !
0
sdstuberCommented:
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
digs developerAuthor Commented:
Its is the valid for join...D.BID  contains the value 111
0
digs developerAuthor Commented:
Now it is working fine ... its data issue.
Thank you !
0
slightwv (䄆 Netminder) Commented:
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
sdstuberCommented:
glad I could help
0
sdstuberCommented:
digsdeveloper - I see you're new here.  Welcome aboard!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.