IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

DB2 SQL using the EXCEPT operator


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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: 2481
Location: Netherlands, Amstelveen

PostPosted: Sat Jan 29, 2011 4:30 pm
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
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
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
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: 1020
Location: India

PostPosted: Sun Jan 30, 2011 9:54 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Relational operator compare date rang... CLIST & REXX 14
No new posts ICETOOL "SUBSET" operator q... DFSORT/ICETOOL 2
No new posts tws opc 5.4 how to change operator te... IBM Tools 5
No new posts using Parse on some other operator fo... DFSORT/ICETOOL 6
No new posts Rexx not equals operator CLIST & REXX 4
Search our Forums:

Back to Top