Formatted spufi report incase of slqcd=0

shashikant · New User Joined: 07 Sep 2007 Posts: 6 Location: chennai

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.

mkarthikeyan · New User Joined: 07 Aug 2008 Posts: 34 Location: Bangalore

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

Suresh Ponnusamy · Posted: Tue Nov 04, 2008 12:02 am

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

shashikant · New User Joined: 07 Sep 2007 Posts: 6 Location: chennai

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(

mkarthikeyan · New User Joined: 07 Aug 2008 Posts: 34 Location: Bangalore

sorry Guys. I have misunderstood the requirement

enrico-sorichetti · Posted: Tue Nov 04, 2008 1:35 pm

shashikant · New User Joined: 07 Sep 2007 Posts: 6 Location: chennai

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.

dick scherrer · Posted: Tue Nov 04, 2008 11:24 pm

Hello,