|
View previous topic :: View next topic
|
| Author |
Message |
kumarcoolboy
New User
.jpg)
Joined: 28 May 2009
Posts: 10
Location: Hyderabad
|
|
|
|
Hi All,
I need to prepare a PLI program which would generate a control report in the spool region. It even icludes the DB2 qeuries. Below are the specifications:
1. My job includes selection of from a table1 where column1 = value1,value2,value3
2. Update the selected rows with column1 values as
value1 to value4
value2 to value5
value3 to value6
Report shld say as below:
| Code: |
************** START OF THE REPORT ********************
Name of the table : TABLE1
Number of rows selected (A): (No. fo rows from 1st step above)
Number of rows updated (B): (No. of rows updated from 2nd step above)
Number of rows bypassed (C): (if any)
Report Validation : A = B + C
Old Column Value New Column Value No. of Rows Updated
value1 value4 15
value2 value5 45
value3 value6 90
Rows Bypassed:
Column : column1
Value : valueX
=> No record found for this column value
****************** END OF REPORT ********************
|
Even a pesudocode or algorithm wud work for me.... |
|
| Back to top |
|
 |
kumarcoolboy
New User
Joined: 28 May 2009
Posts: 10
Location: Hyderabad
|
|
|
|
| am using cursors for the queries...... |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
So, what is the question?
If you want to record operational statistics, put whatever accumulators you want in the code that issues the queries. Some systems even have tables defined to store such information. Your code would insert rows nto these statistics tables as well as the tables that support the business function. |
|
| Back to top |
|
|
kumarcoolboy
New User
Joined: 28 May 2009
Posts: 10
Location: Hyderabad
|
|
|
|
hi Dick,
Am in search of such a query which would gimme those details........... for example, if I 'm using the below query
| Code: |
SELECT COL1
FROM TABLE1
WHERE CONDTION1, CONDITION2
ORDER BY COL1;
|
Can you help me modify in such a way that my output comes in the below fashion....
| Code: |
Output:
Col Value No. of Rows
Value1 15
Value2 20
Value3 5 |
|
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
What you ask about now is not the same as what was requested earlier. . .
Once you post a consistent requirement definition, someone may be able to offer a suggestion.
How will the output be used? |
|
| Back to top |
|
|
kumarcoolboy
New User
Joined: 28 May 2009
Posts: 10
Location: Hyderabad
|
|
|
|
Hi Dick,
I have asked the same query, all my query needs to do is give me the count of the distinct row values. I can program accordingly. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| I have asked the same query, all my query needs to do is give me the count of the distinct row values |
They don't look the same to me - of course it is just now sunup and i'm slow this morning 
| Quote: |
Can you help me modify in such a way that my output comes in the below fashion....
| Code: |
Output:
Col Value No. of Rows
Value1 15
Value2 20
Value3 5 |
|
| Quote: |
Report shld say as below:
| Code: |
************** START OF THE REPORT ********************
Name of the table : TABLE1
Number of rows selected (A): (No. fo rows from 1st step above)
Number of rows updated (B): (No. of rows updated from 2nd step above)
Number of rows bypassed (C): (if any)
Report Validation : A = B + C
Old Column Value New Column Value No. of Rows Updated
value1 value4 15
value2 value5 45
value3 value6 90
Rows Bypassed:
Column : column1
Value : valueX
=> No record found for this column value
****************** END OF REPORT ******************** |
|
I believe you can get the "count by value" result with a simple select using a COUNT and an ORDER BY. The other may take more work - and not all of it query code. |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
| Quote: |
| Am in search of such a query which would gimme those details |
I am not a PL/I expert, but can't this "counting" statistics stuff be done separately in your program with a couple of accumulators as suggested above? Why do you want to do this in "query" itself? And I dont think it's desirable to code an "all-in-one" sql evenif it is doable. Just a thought. |
|
| Back to top |
|
|
kumarcoolboy
New User
Joined: 28 May 2009
Posts: 10
Location: Hyderabad
|
|
|
|
Thank you verh much all of you for your help people.   |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
You're welcome - do you now have a process that does what you need?
It may help others if you post your working solution 
d |
|
| Back to top |
|
|
kumarcoolboy
New User
Joined: 28 May 2009
Posts: 10
Location: Hyderabad
|
|
|
|
I am using temp variables to do that...... here is what am doing.....
| Code: |
DEFINE C1 CURSOR FOR SELECT;
END SELECT;
OPEN C1;
END OPEN;
FETCH C1 INTO :VAR;
IF SQLCODE = 0 THEN
READ_CNT = READ_CNT +1;
END FETCH;
COND1: IF READ_CNT = 1 THEN
PRES_VALUE = VAR;
PREV_VALUE = PRES_VALUE;
ELSE
PRES_VALUE = VAR;
END;
IF PREV_VALUE = PRES_VALUE
CALL FETCH( );
ELSE
DISPLAY VAR, READ_CNT;
SET READ_CNT = 1;
LOOP BACK TO COND1;
END;
|
Hope this is helpful for someone....... |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
If that does what you need - cool
It does not look like it does what the "specs" called for. . . |
|
| Back to top |
|
|
|
|
