| Re: Summing using decode [message #375272] |
Fri, 17 August 2001 14:54 |
Jon
Messages: 483
Registered: May 2001
|
Senior Member |
|
|
What you're asking to do is to "create" data and SQL is made to report data. I do have a solution but I think it would need some work to make it efficient. First, you would want to write a stored function to get the number of transactions for a given month. Example:
CREATE OR REPLACE FUNCTION sf_number_of_transactions (i_yearmonth in VARCHAR2) -- YYYYMM
RETURN NUMBER
IS
l_count NUMBER;
BEGIN
SELECT COUNT('X')
INTO l_count
FROM master_transaction_log
WHERE To_Char(prcss_dt, 'YYYYMM') = i_yearmonth;
RETURN(l_count);
END;
/
Then you could execute the following query:
SELECT To_Char(ROWNUM, '09') Month,
sf_number_of_transactions(TRIM(To_Char(SYSDATE,'YYYY'))||TRIM(To_Char(ROWNUM, '09'))) Count
FROM master_transaction_log -- or any table of at least 12 rows
WHERE ROWNUM <= To_Number(To_Char(SYSDATE,'MM'))
There's always a solution, but they aren't always elegant!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
