?
Solved

How is this SQL suppose to work? I am getting a SQL0216N error and it does not make sense

Posted on 2008-06-12
3
Medium Priority
?
2,492 Views
Last Modified: 2009-04-21
Hi all,
I must be missing something here because this SQL is giving me SQL0216N error - The number of elements on each side of a predicate operator does not match.  Predicate operator is "=".  SQLSTATE=428C4  and I don't see why. Could someone here explain and correct please?
Here it is:
SELECT A.DBNAME, A.SCHEMA, A.TABLE_NAME, A.COLCOUNT, A.CRTIME
FROM DB2ADMIN.FSNDBTRK A
WHERE A.DBNAME = 'DBAUDPYL'
  AND A.TABLE_NAME NOT IN
      (SELECT *
        FROM DB2ADMIN.FSNDBTRK B
         WHERE B.DBNAME = 'DBAUDMST')
;
I want to select only tables that are not found in another schema...differences between tables in one schema and another...simple, eh?

Thanks
0
Comment
Question by:Enuda
  • 2
3 Comments
 
LVL 10

Accepted Solution

by:
OnALearningCurve earned 260 total points
ID: 21768415
Hi Enuda,

I'm still getting to grips with SQL so sorry if this is completely off the mark but could the fix be:

SELECT A.DBNAME, A.SCHEMA, A.TABLE_NAME, A.COLCOUNT, A.CRTIME
FROM DB2ADMIN.FSNDBTRK A
WHERE A.DBNAME = 'DBAUDPYL'
  AND A.TABLE_NAME NOT IN
      (SELECT B.TABLE_NAME
        FROM DB2ADMIN.FSNDBTRK B
         WHERE B.DBNAME = 'DBAUDMST')

The change being in the select statement for your IN criteria.

Hope this helps,

Mark.
0
 
LVL 46

Assisted Solution

by:Kent Olsen
Kent Olsen earned 140 total points
ID: 21768662
Hi Enuda,

Mark's right.  The SELECT * is causing the problem.

  WHERE table_name NOT IN (list);

'list' is just that -- it is a list that you provide or that SQL generates.  It is essentially one or more rows from a single column.  SELECT * returns multiple columns so that the result is a table, not a list.

Thinking of it with real-world data:

  SELECT * FROM mytable WHERE user_ssn IN (SELECT phone, ssn FROM sometable);

The result of "SELECT name, ssn FROM sometable" is another table, therefore illegal.  From a data viewpoint, you certainly wouldn't want DB2 to match user_ssn with phone.


Good Luck,
Kent
0
 
LVL 10

Expert Comment

by:OnALearningCurve
ID: 21784413
Glad I could help.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

862 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