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
 
Formatted spufi report incase of slqcd=0

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

New User


Joined: 07 Sep 2007
Posts: 6
Location: chennai

PostPosted: Mon Nov 03, 2008 3:40 pm    Post subject: Formatted spufi report incase of slqcd=0
Reply with quote

Hi,
Can someone help me to print the SPUFI output in the below format:

table:exam_res
fields:Exam_Dt Roll_NUM pass_fail cOUNT TOTAL_score

Output format expected:
Exam_Dt Roll_NUM pass_fail cOUNT TOTAL_score
10/31/2008 012-07023 pass 0 00.00
10/31/2008 012-07023 fail 0 00.00
10/31/2008 012-07024 pass 0 00.00
10/31/2008 012-07024 fail 0 00.00
10/31/2008 012-07025 pass 0 00.00
10/31/2008 012-07025 fail 0 00.00

The count and Score fields should be intialized as 0 in output incase the SQLcode=100 else it should write the data fetched in row exists in table.


Please respond.
Back to top
View user's profile Send private message

mkarthikeyan

New User


Joined: 07 Aug 2008
Posts: 34
Location: Bangalore

PostPosted: Mon Nov 03, 2008 11:36 pm    Post subject: Reply to: Formatted spufi report incase of slqcd=0
Reply with quote

do you mean in case if the value for column Count & Total_score is missing you want it to be filled as 0 and 0.00. Because if SQLCODE is +100 means no row will be return.

In such case, if the Column Count & Total_score has a null then you can value clause

eg Select Exam_Dt, Roll_NUM, pass_fail, VALUE(COUNT,0),
VALUE(TOTAL_score,0.0) from exam_res;

T-H-I-N-K
IBM
Back to top
View user's profile Send private message
Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Tue Nov 04, 2008 12:02 am    Post subject:
Reply with quote

Hi Karthi,

First thing, when we get SQLCODE = 100, we will not any output from the table. So VALUE Clause does not make any difference.

Hi Shashi

Please give us some more info on your query.
1) Are you trying to select from one table or joining multiple tables
2) If it is from one table, are you expecting the remaining fields as SPACES when the SQLCODE is 100.

Kindly elaborate little more about your requirement
Back to top
View user's profile Send private message
shashikant

New User


Joined: 07 Sep 2007
Posts: 6
Location: chennai

PostPosted: Tue Nov 04, 2008 11:49 am    Post subject: Reply to: Formatted spufi report incase of slqcd=0
Reply with quote

Hi,
The query i have coded is a Join of couple of tables and i am doing a UNION ALL for two diff scenarios of PASS and FAIL (based on a explicit TYPE_CD ) and i need the report for a hardcoded set of roll numbers.

The requirement is to get a report for these roll numbers with zeroes incase the Query Join doesnt return any rows(sqlcd 100) .

Suresh as u rightly said(
Quote:
"First thing, when we get SQLCODE = 100, we will not any output from the table. So VALUE Clause does not make any difference")


there will not be any row fetched and report will be empty.But i want to display a report with Zeroes EVEN incase OF sqlcd100 and populate corresponding data from table incase a row in found(sqlcd 0)

SAMPLE QUERY:
SELECT
P.EXAM_DT ,
R.Roll_NUM,
'PASS' AS pass_fail
,COUNT(*) AS ATTEMPT_COUNT
,COALESCE(SUM(P.SCORE_AGG,0) TOTAL_SCORE
FROM ROLL_NUM_TBL R
,pASS_FAIL_TBL P

WHERE ROLL_NUM IN ('012-07023','012-07024','012-07025')
AND R.ROLL_NUM = P.ROLL_NUM
AND P.EXAM_DT = CURRENT DATE
AND (
(AT_XPL_CD = 'PASS'
AND FEE_PD_IN ='Y'
)
GROUP BY R.ROLL_NO
UNION ALL
SELECT
P.EXAM_DT ,
R.Roll_NUM,
'FAIL' AS pass_fail
,COUNT(*) AS ATTEMPT_COUNT
,COALESCE(SUM(P.SCORE_AGG,0) TOTAL_SCORE
FROM ROLL_NUM_TBL R
,pASS_FAIL_TBL P

WHERE ROLL_NUM IN ('012-07023','012-07024','012-07025')
AND R.ROLL_NUM = P.ROLL_NUM
AND P.EXAM_DT = CURRENT DATE
AND (
(AT_XPL_CD = 'FAIL'
AND FEE_PD_IN ='Y'
)
GROUP BY R.ROLL_NO


Hope this gives a clear insight now.
Back to top
View user's profile Send private message
mkarthikeyan

New User


Joined: 07 Aug 2008
Posts: 34
Location: Bangalore

PostPosted: Tue Nov 04, 2008 1:27 pm    Post subject: Reply to: Formatted spufi report incase of slqcd=0
Reply with quote

sorry Guys. I have misunderstood the requirement
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10375
Location: italy

PostPosted: Tue Nov 04, 2008 1:35 pm    Post subject: Reply to: Formatted spufi report incase of slqcd=0
Reply with quote

Quote:
sorry Guys. I have misunderstood the requirement


you have no need to be sorry, You did not misunderstand !
it' s the topic starter that should be sorry/worried for not being able
to post a question clearly to start with,
generating useless network traffice
and making people lose time to answer
wrong questions when the real issue is different
Back to top
View user's profile Send private message
shashikant

New User


Joined: 07 Sep 2007
Posts: 6
Location: chennai

PostPosted: Tue Nov 04, 2008 3:48 pm    Post subject: Reply to: Formatted spufi report incase of slqcd=0
Reply with quote

Enricol,karthik: I just gave a brief requirement in start so that i dont misguide you all with my approach,as i was already working on that option.And wanted to hear from you all on other options/approaches.


Thanks anyways Guys.
I got it!

Here's a sample query....and its working

SELECT
'PASS' AS pass_fail,
P.EXAM_DT ,
R.Roll_NUM
, (
SELECT COUNT(*)
FROM ROLL_NUM_TBL R
,pASS_FAIL_TBL P
WHERE ROLL_NUM IN ('012-07023','012-07024','012-07025')
AND R.ROLL_NUM = P.ROLL_NUM
AND P.EXAM_DT = CURRENT DATE
AND (
(AT_XPL_CD = 'P'
AND FEE_PD_IN ='Y'
)
)
) AS ATTEMPT_COUNT
,
(
SELECT
COALESCE(SUM(P.SCORE_AGG,0) TOTAL_SCORE
) AS TOTAL_SCORE
FROM ROLL_NUM_TBL R
,pASS_FAIL_TBL P
WHERE ROLL_NUM IN ('012-07023','012-07024','012-07025')
AND R.ROLL_NUM = P.ROLL_NUM
AND P.EXAM_DT = CURRENT DATE
AND (
(AT_XPL_CD = 'P'
AND FEE_PD_IN ='Y'
)
GROUP BY R.ROLL_NO
UNION ALL
SELECT
'FAIL' AS pass_fail
----
--
--
similar QUERY FOR FAIL EXECCPT AT_XPL_CD = 'F'...

Thanks again and apologies to eating up the netowrk traffic.
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: Tue Nov 04, 2008 11:24 pm    Post subject:
Reply with quote

Hello,

Quote:
Thanks again and apologies to eating up the netowrk traffic.
Not to worry - we have vigilant "traffic cops". . . icon_smile.gif

Good to hear that you have a solution - thank you for posting it.
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 Copying part of the report using SARBCH Aedulla CA Products 1 Tue Oct 24, 2017 9:24 am
No new posts Report cost in CA-dispatch Nileshkul CA Products 3 Wed Jun 07, 2017 10:32 pm
No new posts Splitting of single report to seperat... abdulrafi TSO/ISPF 7 Mon Mar 27, 2017 3:59 pm
No new posts SPUFI -- Joining 3 tables – data in... Sysaron DB2 2 Wed Mar 08, 2017 4:18 am
No new posts Easytrieve report shalem CA Products 4 Sun Feb 26, 2017 9:08 am

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