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 query. Unexpected result. Need Explanation.

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

Active User


Joined: 11 Nov 2008
Posts: 143
Location: India

PostPosted: Tue Nov 24, 2009 3:52 pm    Post subject: Db2 query. Unexpected result. Need Explanation.
Reply with quote

hi all please find the below db2 query.

Code:
SELECT                                                               
      A.CLM_NUM_CD,                                                 
      A.UPDT_TS,                                                     
      M.SITUS_ST                                                     
FROM                                                                 
 F3696DB.TUDCLINF A,                                                 
 F3696DB.TUDEMPLR M                                                 
WHERE                                                               
      A.RPRT_NUM = M.RPRT_NUM                                       
        AND A.CLM_NUM_CD = '200910138950'                           
       --   AND (M.SITUS_ST IN ('NY',' ') OR  (M.SITUS_ST IS NULL)) 
                                                                     
WITH UR;   


o/p

Code:
CLM                                           
NUM                   UPDT                                    SITUS
CD                     TS                                        ST   
------------        ------------------------                -----
222222222222  2009-11-02-14.25.24.555555     



Code:
SELECT                                                               
      A.CLM_NUM_CD,                                                 
      A.UPDT_TS,                                                     
      M.SITUS_ST                                                     
FROM                                                                 
 F3696DB.TUDCLINF A,                                                 
 F3696DB.TUDEMPLR M                                                 
WHERE                                                               
      A.RPRT_NUM = M.RPRT_NUM                                       
        AND A.CLM_NUM_CD = '200910138950'                           
          AND (M.SITUS_ST IN ('NY',' ') OR  (M.SITUS_ST IS NULL)) 
                                                                     
WITH UR;


o/p

Code:
CLM                                             
NUM           UPDT                           SITUS 
CD             TS                               ST   
------------  --------------------------  -----     



Can i know why no row is fetched in the second query.


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

Mukesh Pandey

Active User


Joined: 11 Nov 2008
Posts: 143
Location: India

PostPosted: Tue Nov 24, 2009 3:59 pm    Post subject:
Reply with quote

The STS_ST is blank in first o/p . the second query gives 0 rows when sts_st is queried for spaces or blank.
Back to top
View user's profile Send private message
Mukesh Pandey

Active User


Joined: 11 Nov 2008
Posts: 143
Location: India

PostPosted: Tue Nov 24, 2009 5:08 pm    Post subject:
Reply with quote

i am getting the hex value of of SITUS code as 00
00

Can i know what is the value of 00
00
Back to top
View user's profile Send private message
Mukesh Pandey

Active User


Joined: 11 Nov 2008
Posts: 143
Location: India

PostPosted: Tue Nov 24, 2009 5:46 pm    Post subject:
Reply with quote

the field in STITUS_ST is neither NULL nor SPACES. what query will fetch these values keeping in mind i need to give suggestion for the time being. i cant change data.
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Tue Nov 24, 2009 6:06 pm    Post subject:
Reply with quote

Code:
column IS NOT NULL
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Tue Nov 24, 2009 7:54 pm    Post subject:
Reply with quote

"the field in STITUS_ST is neither NULL nor SPACES. what query will fetch these values keeping in mind i need to give suggestion for the time being. i cant change data."

The column is not nullable but it contains a x'00' which is a valid character but it is not a space (x'40'). Suggest you look at the application that loaded the data in the first place.
Back to top
View user's profile Send private message
Mukesh Pandey

Active User


Joined: 11 Nov 2008
Posts: 143
Location: India

PostPosted: Wed Nov 25, 2009 12:11 pm    Post subject: Reply to: Db2 query. Unexpected result. Need Explanation.
Reply with quote

Thanks Craq. I will do that.
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Wed Nov 25, 2009 1:29 pm    Post subject:
Reply with quote

Hello Mukesh,

Are you sure that you are getting followng output of First query?

Code:
CLM                                           
NUM                   UPDT                                    SITUS
CD                     TS                                        ST   
------------        ------------------------                -----
[b]222222222222[/b]  2009-11-02-14.25.24.555555   


As in the query you have specified A.CLM_NUM_CD = '200910138950'
in the predicate then output record should have 200910138950 as CLM_NUM_CD

Code:
SELECT                                                               
      A.CLM_NUM_CD,                                                 
      A.UPDT_TS,                                                     
      M.SITUS_ST                                                     
FROM                                                                 
 F3696DB.TUDCLINF A,                                                 
 F3696DB.TUDEMPLR M                                                 
WHERE                                                               
      A.RPRT_NUM = M.RPRT_NUM                                       
     [b]   AND A.CLM_NUM_CD = '200910138950'  [/b]                         
       --   AND (M.SITUS_ST IN ('NY',' ') OR  (M.SITUS_ST IS NULL)) 
                                                                     
WITH UR;   
Back to top
View user's profile Send private message
Mukesh Pandey

Active User


Joined: 11 Nov 2008
Posts: 143
Location: India

PostPosted: Mon Nov 30, 2009 11:07 am    Post subject:
Reply with quote

hi Craq and others please clear my below doubt..

IF PLN-SITUS-ST > SPACES
MOVE PLN-SITUS-ST TO EMPLR-SITUS-ST
ELSE
MOVE SPACES TO EMPLR-SITUS-ST
END-IF.

WILL THE ABOVE CODE CHECK FOR ALL LOW VALUES AND AVOID IT GETTING POPULATED IN THE VAR EMPLR-SITUS-ST ?

I WANT TO AVOID HEX( ) AND USE '>' OPERATOR IN MY COBOL PROGRAM FOR SIMPLICITY.

PLEASE ADVICE.


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

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Nov 30, 2009 11:29 am    Post subject:
Reply with quote

Hello,

If you are only interested in preventing low-values (x'00'), you might consider creating a working-storage item called ws-low-valu and give a VALUE of x'00'. Then your IF can be a simple = comparison.
Back to top
View user's profile Send private message
Mukesh Pandey

Active User


Joined: 11 Nov 2008
Posts: 143
Location: India

PostPosted: Mon Nov 30, 2009 11:36 am    Post subject:
Reply with quote

Hi dick, Thanks for your suggestion. i understand what you suggest.

Also please advice if my code will bypass all the low values or not.

IF PLN-SITUS-ST > SPACES
MOVE PLN-SITUS-ST TO EMPLR-SITUS-ST
ELSE
MOVE SPACES TO EMPLR-SITUS-ST
END-IF.


Thanks,
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Nov 30, 2009 11:13 pm    Post subject:
Reply with quote

Hello,

Quote:
Also please advice if my code will bypass all the low values or not.
I don't understand what this is asking? What does "all the low values" mean? There is only 1 low-value. . .

A space is an x'40' which will always compare higher than an x'00'. If there are other non-displayable values, comparing for greater than spaces would not be sufficient.
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 query to fetch record which has only ... maxsubrat DB2 12 Mon Dec 11, 2017 5:03 pm
No new posts How to query MAXGENS of an existing P... Chuchulo Hatyzak JCL & VSAM 4 Wed Nov 22, 2017 10:23 pm
No new posts Native SQL Query kishpra DB2 1 Wed Nov 22, 2017 8:38 pm
No new posts Query on IEFBR14 with GDG Ashishpanpaliya JCL & VSAM 4 Tue Nov 07, 2017 8:34 pm
No new posts interesting explanation of DB concurr... dbzTHEdinosauer General Talk & Fun Stuff 0 Thu Oct 26, 2017 3:02 pm

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