?
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,352 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 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

762 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