 Variables [message #219440] |
Wed, 14 February 2007 07:08  |
abis123
Messages: 31
Registered: February 2007
|
Member |
|
|
I have some code shown below, what I want to do is replace all instances of the number 9 with a variable, which holds a count of results. If someone could inform me how to use declare and use variables and where in my code to put them I would be very grateful.
Procedure GET_TIMES (
o_cursor OUT T_CURSOR
)
IS
BEGIN
OPEN o_cursor FOR
SELECT TRUNC(((SELECT COUNT(DATE_OF_ALLEGATION - DATE_OF_CALL) FROM tablename.columnname
WHERE (DATE_OF_ALLEGATION - DATE_OF_CALL) = 0)/9)*100) AS NO_DAYS,
TRUNC(((SELECT COUNT(DATE_OF_ALLEGATION - DATE_OF_CALL) FROM tablename.columnname
WHERE (DATE_OF_ALLEGATION - DATE_OF_CALL) = 1)/9)*100) AS ONE_DAY,
TRUNC(((SELECT COUNT(DATE_OF_ALLEGATION - DATE_OF_CALL) FROM tablename.columnname
WHERE (DATE_OF_ALLEGATION - DATE_OF_CALL) = 2)/9)*100) AS TWO_DAYS,
TRUNC(((SELECT COUNT(DATE_OF_ALLEGATION - DATE_OF_CALL) FROM tablename.columnname
WHERE (DATE_OF_ALLEGATION - DATE_OF_CALL) = 3)/9)*100) AS THREE_DAYS,
TRUNC(((SELECT COUNT(DATE_OF_ALLEGATION - DATE_OF_CALL) FROM tablename.columnname
WHERE (DATE_OF_ALLEGATION - DATE_OF_CALL) = 4)/9)*100) AS FOUR_DAYS,
TRUNC(((SELECT COUNT(DATE_OF_ALLEGATION - DATE_OF_CALL) FROM tablename.columnname
WHERE (DATE_OF_ALLEGATION - DATE_OF_CALL) = 5)/9)*100) AS FIVE_DAYS,
TRUNC(((SELECT COUNT(DATE_OF_ALLEGATION - DATE_OF_CALL) FROM tablename.columnname
WHERE (DATE_OF_ALLEGATION - DATE_OF_CALL) > 5)/9)*100) AS MORE_THAN_FIVE_DAYS
FROM DUAL;
END GET_TIMES;
end;
|
|
|
|
| Re: Variables [message #219443 is a reply to message #219440] |
Wed, 14 February 2007 08:02  |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
Procedure GET_TIMES (var1 IN number, o_cursor OUT T_CURSOR) IS
...
Then just use var1 anywhere you'd like. Of course, you'd have the edit your procedure and change every occurrence of '9' with 'var1.'
|
|
|
|
|
|
 Re: Variables [message #219447 is a reply to message #219443] |
Wed, 14 February 2007 08:11 |
abis123
Messages: 31
Registered: February 2007
|
Member |
|
|
Thanks very much for both of your answers! After some MAJOR messing around, and probably the wrong way I have found a way as well, pretty much the exact same as one of yours. Its:
as
Procedure GET_TIMES (
o_cursor OUT T_CURSOR,
total_count OUT NUMBER <-- i created another out number
)
IS
BEGIN
SELECT COUNT(REFERENCE) INTO total_count FROM tablename.columnname; <-- then i can call total_count anywhere
[Updated on: Wed, 14 February 2007 08:12] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
