Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

CASE satatement

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
tecnokrat
Warnings : 1

Active User


Joined: 22 May 2009
Posts: 160
Location: Bangalore

PostPosted: Mon Nov 02, 2009 3:26 pm    Post subject: CASE satatement
Reply with quote

Hi,
i'm having an existing update query which needs to be modified to reuse it. so i have to add a CASE statement in it the query i ran in SPUFI is executing but the result is not been modified.
The existing update query is as
Code:
 update abc_table
                  set abc_1 = :abc-1
                       abc_2 = :abc-2
               where
                       aaa_1 = :aaa-1
                    and abb_1 = :abb-1
                     


Now I modified the query like
Code:
 update abc_table
                   set   abc_1 = :abc-1
                          abc_2 = :abc-2
                         abc_3 = (CASE abc_3 WHEN 'XYZ'
                                          then 'BBB'
                                      else abc_3
                                      end)
              where
                       aaa_1 = :aaa-1
                    and abb_1 = :abb-1

                                       

this query in SPUFI says execution successful but the table value is not updated. please help......!!!!!
Back to top
View user's profile Send private message

Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Mon Nov 02, 2009 5:46 pm    Post subject:
Reply with quote

Check your SPUFi settings.

Code:
AUTOCOMMIT ...... ===>,NO


Change it to YES
Back to top
View user's profile Send private message
tecnokrat
Warnings : 1

Active User


Joined: 22 May 2009
Posts: 160
Location: Bangalore

PostPosted: Mon Nov 02, 2009 5:50 pm    Post subject:
Reply with quote

Hi srihari,
The SPUFI settings is

Code:
AUTOCOMMIT ...... ===> YES
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Mon Nov 02, 2009 6:00 pm    Post subject:
Reply with quote

Can you show your spufi output?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Mon Nov 02, 2009 6:05 pm    Post subject:
Reply with quote

well, stop giving us bullshit.
you could not be running spufi and have
aaa_1 = :aaa-1
and abb_1 = :abb-1

host variables.
Back to top
View user's profile Send private message
tecnokrat
Warnings : 1

Active User


Joined: 22 May 2009
Posts: 160
Location: Bangalore

PostPosted: Mon Nov 02, 2009 6:10 pm    Post subject:
Reply with quote

Oh Common...
i knew that spufi does'nt take the host variables....



Code:
 update abc_table
                   set   abc_1 = val1
                          abc_2 = val2
                         abc_3 = (CASE abc_3 WHEN 'XYZ'
                                          then 'BBB'
                                      else abc_3
                                      end)
              where
                       aaa_1 =    VVV1
                    and abb_1 = NNN2
 

well the above is the query to be run in the SPUFI
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Mon Nov 02, 2009 7:07 pm    Post subject:
Reply with quote

Quote:
well the above is the query to be run in the SPUFI


which it will not, but that seems to be common behavior for you.

instead of UPDATE, do a SELECT COUNT(*) WHERE
and see if any rows are selected by the WHERE clause for the results table.

also, spufi, when there is no negative sqlcode, will tell you how many rows were updated.

a zero sqlcode from an update only means you managed to get your sql syntactically correct
- something which you have evaded in all your posts in this thread so far.
Back to top
View user's profile Send private message
tecnokrat
Warnings : 1

Active User


Joined: 22 May 2009
Posts: 160
Location: Bangalore

PostPosted: Tue Nov 03, 2009 5:41 pm    Post subject:
Reply with quote

sorry, for troubling you all ,Actually the silly mistake i have done was 'WHERE' condition was an improper icon_sad.gif
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Tue Nov 03, 2009 6:13 pm    Post subject:
Reply with quote

tecnokrat,

unfortunately, most problems are due to 'silly mistakes', typos, etc...

that is why all the BS questions about what,where and why.

thanks for getting back to us.

my apologizes for any demeaning comments I made.
what you have posted motivates me, in the future, to use more respect in answering your questions.

dbz
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 RFE: DB2 support for mixed case names. Pedro DB2 0 Tue Jul 04, 2017 1:32 am
No new posts VALIDATE NULL VALUE IN DB2 CASE STATE... useit DB2 5 Thu Feb 09, 2017 4:34 pm
No new posts Summing on Mixed Case Literals Roy Ware SYNCSORT 3 Tue Jan 03, 2017 10:18 pm
No new posts Case insenstive in INCLUDE Condtion smileseenu DFSORT/ICETOOL 2 Mon Feb 15, 2016 8:42 pm
No new posts How steps execute in Job in case of a... richiewalia JCL & VSAM 4 Tue Jan 12, 2016 12:34 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us