Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
DB2 SQL using the EXCEPT operator

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Barry Keogh

New User


Joined: 14 Jan 2011
Posts: 6
Location: UK

PostPosted: Sat Jan 29, 2011 3:54 pm    Post subject: DB2 SQL using the EXCEPT operator
Reply with quote

Hi All

On File-AID for DB2 I am trying to write a query which will include two inline views, compare them and return the rows which exist in one but not in the other, the exact code i have is

(select from tcez9.crac_offer where prc_actn_tp_no=1)
except
(select * from tcez9.crac_offer_new where prc_actn_tp_no=5);

the tables and columns exist and are spelt correctly, when i run the inline views separately they work fine, however when i run the above query i get the following error returned

DSNT408I SQLCODE = -84, ERROR: UNACCEPTABLE SQL STATEMENT
DSNT418I SQLSTATE = 42612 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHAPLY SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 101 0 0 -1 73 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000065' X'00000000' X'00000000' X'FFFFFFFF'
X'00000049' X'00000000' SQL DIAGNOSTIC INFORMATION

could you please advise where i am going wrong,

Thanks in advance
Back to top
View user's profile Send private message

PeterHolland

Global Moderator


Joined: 27 Oct 2009
Posts: 2442
Location: Netherlands, Amstelveen

PostPosted: Sat Jan 29, 2011 4:30 pm    Post subject:
Reply with quote

Which DB2 version?
Back to top
View user's profile Send private message
rocky_balboa

New User


Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope

PostPosted: Sat Jan 29, 2011 10:29 pm    Post subject: Reply to: DB2 SQL using the EXCEPT operator
Reply with quote

I guess you are using DB2 version prior to 9 where it is not supported. EXCEPT is a valid SQL statement in V9
Back to top
View user's profile Send private message
Barry Keogh

New User


Joined: 14 Jan 2011
Posts: 6
Location: UK

PostPosted: Sun Jan 30, 2011 4:32 pm    Post subject:
Reply with quote

How do i find the DB2 version from within File_AID for DB2?
Back to top
View user's profile Send private message
Barry Keogh

New User


Joined: 14 Jan 2011
Posts: 6
Location: UK

PostPosted: Sun Jan 30, 2011 6:45 pm    Post subject:
Reply with quote

When i open fild in TSO i get the following at the top of the screen

File-AID for DB2 - 6.0
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Sun Jan 30, 2011 9:54 pm    Post subject:
Reply with quote

Hi Barry,

Quote:
How do i find the DB2 version from within File_AID for DB2?

Use this query to know the version,
Code:
select getvariable('SYSIBM.VERSION') from SYSIBM.SYSDUMMY1
or
You have to go to "DB2I Primary Option Menu" --> 7(DB2 Commands) -->
Code:
-Display Group Detail

Under DB2 LVL you will find the DB2 Version

Thanks,
Sushanth
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts ICETOOL "SUBSET" operator q... David Sde DFSORT/ICETOOL 2 Tue Jul 28, 2015 7:53 pm
No new posts tws opc 5.4 how to change operator te... italo_pm IBM Tools 5 Wed Apr 29, 2015 11:22 am
No new posts using Parse on some other operator fo... guptae DFSORT/ICETOOL 6 Thu Dec 11, 2014 11:11 am
No new posts Rexx not equals operator harisukumaran CLIST & REXX 5 Sat Sep 20, 2014 2:57 am
No new posts SQL CODE -132. Issue with LIKE operator GaganGarg DB2 6 Wed Jan 30, 2013 3:22 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us