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
2,233 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 10

Accepted Solution

by:
OnALearningCurve earned 65 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 45

Assisted Solution

by:Kent Olsen
Kent Olsen earned 35 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

Industry Leaders: 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!

Question has a verified solution.

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

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

730 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