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,094 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 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:Kdo
Kdo 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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now