Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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,435 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
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, …

610 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