Hi , any one could Help me. with this.
Want to create a loop automatice fetch the info for 25 days.
the Loading data store in the DB in the formate as 2017-03-05 21:13:20.340000
I use this for the single date
SyntaxEditor Code Snippet
sel count(1) as N_record, error_desc from xxxx.xxxx where SUBSTR(LOAD_DTTM,1,10) = '2017-03-05' group by error_desc order by N_record
I want ot have a loop for the date. let this single script run 25 times from 2017-03-05 to 2017-03-30
How can I do that any suggestion?
One more thing, I want to out put each one with a date tag to the excel formate which I can then store in a excel doc.
Why looping? Simply add the date to GROUP BY.
What's the datatype LOAD_DTTM? Can't be a timestamp because the SUBSTR would fail.
The LOAD_DTTM is time stam. and the SQL works
Yes. group by works.
but could you please teach me how to use the loop? then I can use that in the other situation
and how to make date increase ?
You can only loop using WHILE/REPEAT/LOOP in a Stored Procedure, but here you better run a single Select:
sel count(*) as N_record, error_desc,
-- SUBSTR(LOAD_DTTM,1,10) as dt -- better avoid the type cast and use
CAST(LOAD_DTTM AS DATE) as dt from xxxx.xxxx where LOAD_DTTM >= timestamp '2017-03-05 00:00:00' and LOAD_DTTM < timestamp '2017-03-31 00:00:00' group by error_desc, dt order by dt, N_record