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
 
SELECT COUNT(Column1), SUM(column2) not working

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

New User


Joined: 20 Jan 2004
Posts: 42

PostPosted: Fri Aug 21, 2009 6:05 pm    Post subject: SELECT COUNT(Column1), SUM(column2) not working
Reply with quote

Hi,

I am facing issue with this batch query.

SELECT COUNT(col1), SUM(col2)
FROM table1
WHERE col3 = 'abc';


The output of the above query is written to the output file with wrong count and sum.

We submit this query through IKJEFT01.


Any idea what is wrong in this queru why I am not able to get the right valules.

Same query works fine with SPUFI.

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

Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2285
Location: @my desk

PostPosted: Fri Aug 21, 2009 6:17 pm    Post subject:
Reply with quote

Which program are you executing using IKJEFT01? DSNTEP2 or DSNTIAUL? What values you are getting in the output? What are your expected values? Please provide some more details.
Back to top
View user's profile Send private message
shakti

New User


Joined: 20 Jan 2004
Posts: 42

PostPosted: Fri Aug 21, 2009 6:24 pm    Post subject: Reply to: SELECT COUNT(Column1), SUM(column2) not working
Reply with quote

Hi We use DSNTIAUL and the expected result should be 5 and 50.55 respectively..Butr in the output dataset shows som hex value with in correct data.

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

Global Moderator


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

PostPosted: Fri Aug 21, 2009 6:36 pm    Post subject:
Reply with quote

as usual, we only see what the TS deems relevent -
which is sorta silly since the TS does not know the answer to his question -
how could he know what we need to know.

anyway,
the query as you have given will not work in spufi, because you have not qualified the table, thus the query is against the TS's userid/qualifier.

in batch, the run command will associate the proper qualifier.

shakti,

since we don't know what the query is, we can not provide an answer.

suggest that you provide the exact query (cut & paste) that you use in SPUFI
and the exact query that you run in the TMP as well as the RUN command.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10309
Location: italy

PostPosted: Fri Aug 21, 2009 6:41 pm    Post subject: Reply to: SELECT COUNT(Column1), SUM(column2) not working
Reply with quote

Quote:
Any idea what is wrong in this queru why I am not able to get the right valules.


Quote:
Butr in the output dataset shows som hex value with in correct data.


nothing wrong from the db2 side...
the only wrong is in Your understanding of the different functionality of different db2 utilities

before whining do a bit more of manual reading
for example differences in behavior and output of...
SPUFI, DSNTEP., DSNTIAUL
Back to top
View user's profile Send private message
Succor

New User


Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)

PostPosted: Mon Aug 24, 2009 2:58 pm    Post subject: Reply to: SELECT COUNT(Column1), SUM(column2) not working
Reply with quote

Quote:
Same query works fine with SPUFI.

Its about the representaion of data which makes you believe that the query works in SPUFI and not with DSNTIAUL(or else you are poiting to different DB2 region/Creator for the two runs).
Quote:
shows som hex value with in correct data.
How did you reach to this conclusion,what is the HEX value present over there ?did you try viewing the data set against a cobol copybook with DB2 equivalent Cobol data type representation?Few points to remember:
The COUNT function returns an integer number
The SUM of SMALLINT values returns an INTEGER result
The SUM of single precision floating point(REAL) values returns a double precision floating-point (DOUBLE or FLOAT)result.
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 column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts LIBDEF not working in called proc packerm CLIST & REXX 0 Fri Sep 15, 2017 7:22 pm
No new posts Working with hex values in Filemanage... Emile Straker IBM Tools 0 Tue Sep 12, 2017 1:42 am
No new posts Updating the Trailer count in variabl... satheshbabur DFSORT/ICETOOL 6 Wed Aug 30, 2017 9:49 pm
No new posts SORT Trailer Count - LRECL Output co... amorante DFSORT/ICETOOL 5 Tue Aug 29, 2017 8:57 pm

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