March 11, 2015 at 3:45 am
Hi guys,
I am quite new to the SQL, so if somebody is interested to help, I will be really glad. I need prepare some daily statistics from the database about running jobs. It is just some filtering / limitation conditions (I hope)
all records I need to filter are just from one table named ho.async_task
there are many columns in it, however I need just following to be displayed:
name I need / name of the column in db from ho.async_task
Job ID = ID (example: 1050711323)
Job Name = SCHEDULER_TEMPLATE_CODE (example: JOB_NAMEDA)
Start Time = START_DATE (example: 19.09.2014 3:00:00)
End Time = END_DATE (example: 19.09.2014 3:00:12)
Run time = should be difference between START_DATE and END_DATE in seconds. (example: 12)
Status = STATUS (example: F)
Error message = ERROR_MESSAGE (example: Error description)
-- Run Time field is not in db, should be difference between start / end date in seconds
-- I need track only these SCHEDULER_TEMPLATE_CODE
JOB_NAMEDA,JOB_NAMEDANOTHER, JOB_NAMELESS, JOB_ANOTHER_EXAMPLE
-- because some jobs (Scheduler_template_code) could have more records daily, in such an example I need display just last record
-- whole SQL select should be somehow easy filterable by particular day (or possibility to display records X day back when changing some parm inside the script)
--whole script could be ordered by start_date descendet
so the output should be like this for given day
Job ID--Job Name--Start Time--End Time--RUN time--Status--Error description
111--JOB_NAMEDA---3:00:00--3:00:12------12--------F-------blank
213--JOB_NAMEDANOTHER--3:01:00--3:02:00--60-----E-----something
898--JOB_NAMELESS-- etc...
875--JOB_ANOTHER_EXAMPLE--etc...
thanks for any help
March 11, 2015 at 3:52 am
Firstly, go buy yourself a copy of T-SQL Fundamentals and read it. Excellent book for someone starting in databases.
Second, to help you we need some more information:
Table definitions (as a CREATE TABLE statement)
Sample data (as INSERT statements)
Expected results.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 11, 2015 at 4:37 am
GilaMonster (3/11/2015)
Firstly, go buy yourself a copy of T-SQL Fundamentals and read it. Excellent book for someone starting in databases.Second, to help you we need some more information:
Table definitions (as a CREATE TABLE statement)
Sample data (as INSERT statements)
Expected results.
here is insert export
insert into ho.async_task (JOB_ID, JOB_NAME, STATUS, ERROR_MESSAGE, START_TIME, END_TIME)
values (1141054671, 'JOB_NAMEDA', 'F', null, to_date('10-03-2015 20:00:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('10-03-2015 20:00:35', 'dd-mm-yyyy hh24:mi:ss'));
I know I am noob, but cannot expect i will master it within few hours 🙂
this is where I have got so far, not sure if it helps you or if you can give me some advices for what I need
SELECT ID AS Job_ID,SCHEDULER_TEMPLATE_CODE AS Job_NAME,STATUS AS Status,ERROR_MESSAGE AS Error_message,START_DATE AS START_TIME,END_DATE AS END_TIME
FROM ho.async_task
WHERE SCHEDULER_TEMPLATE_CODE = ''JOB_NAMEDA' OR SCHEDULER_TEMPLATE_CODE = 'JOB_NAMEDANOTHER ' OR SCHEDULER_TEMPLATE_CODE = 'JOB_NAMELESS '
order by start_date DESC
--missing last column running time which need to be difference between start date / end date in seconds
--Start date and end end date is in format 10.03.2015 20:00:00 (date/ time - 'dd.mm.yyyy HH24:MI:SS'
--because there are daily more records for JOB_NAMEDA, i need to display only last one
--it should be filtrable by some day you insert (based on start date for example)
March 11, 2015 at 5:29 am
GilaMonster (3/11/2015)
Table definitions (as a CREATE TABLE statement)
Also, we need more than one row of sample data please. Enough to test any query written.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 11, 2015 at 5:50 am
GilaMonster (3/11/2015)
GilaMonster (3/11/2015)
Table definitions (as a CREATE TABLE statement)Also, we need more than one row of sample data please. Enough to test any query written.
how can I get it or export it ? Or you mean just right-click on the table and press view --- columns values ? (like NUMBER(18) etc... ?)
March 11, 2015 at 5:53 am
Right click table -> script as > Create
Basically, we need you to post setup code so that I can just copy it, paste into Management Studio, hit run and have something I can test a query against.
Edit: Hang on.... Just noticed the to_date in your insert.
What database engine are you using? Because this is a SQL Server forum. If you're not using SQL Server, we probably can't help you much.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 11, 2015 at 6:07 am
GilaMonster (3/11/2015)
Right click table -> script as > CreateBasically, we need you to post setup code so that I can just copy it, paste into Management Studio, hit run and have something I can test a query against.
Edit: Hang on.... Just noticed the to_date in your insert.
What database engine are you using? Because this is a SQL Server forum. If you're not using SQL Server, we probably can't help you much.
I am using PL/SQL development . is it that much different from SQL server ? I think scripting / syntaxes are the same or not ?
I do not have that create option there unfortunately :/
March 11, 2015 at 6:33 am
You're using Oracle. Very different. Basically any query I write for you will not run on Oracle.
And of course the instructions telling you how to get the script out of SQL Server Management Studio won't work on an Oracle client
I suggest you find an Oracle forum for Oracle questions, because posting on a site for MS SQL Server is probably not going to get you usable Oracle PL-SQL code.
It's like posting on a Java forum and asking for C# code. Or posting on a Unix forum asking for Windows batch commands.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 11, 2015 at 7:02 am
GilaMonster (3/11/2015)
You're using Oracle. Very different. Basically any query I write for you will not run on Oracle.And of course the instructions telling you how to get the script out of SQL Server Management Studio won't work on an Oracle client
I suggest you find an Oracle forum for Oracle questions, because posting on a site for MS SQL Server is probably not going to get you usable Oracle PL-SQL code.
It's like posting on a Java forum and asking for C# code. Or posting on a Unix forum asking for Windows batch commands.
thanks for explanation and for your time
cheers
P.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply