technovice
Programmer
With the SQL button on Microsoft Query I am writing the following:
SELECT JOB_EVENT.RUN_ID, JOB_EVENT.CONFIG_ITEM, JOB_EVENT.JOB_ID, JOB_EVENT.JOB_EVENT, JOB_EVENT.JOB_EVENT_TIME
FROM SRD_DBA.JOB_EVENT JOB_EVENT
WHERE (JOB_EVENT.RUN_ID=? AND
JOB_EVENT.JOB_EVENT In ('Started','Warning','Completed OK')) AND
(JOB_EVENT.JOB_ID In ('CRGDMTRWRK','CRGDWTHDRWL','CASDTPINPUT','CASDMOVES','CRGDTPSTRIP','CRGD1CASHUP','CRGD2CASHUP','CRGD3CASHUP','CRGD4CASHUP','CRGDREADS','CRGDACCMRGE','CRGD1CHECK','CRGD2CHECK','CRGD3CHECK','CRGD4CHECK') OR
JOB_EVENT.JOB_ID In (’CRGD1MAINUP’,’CRGD2MAINUP’,’CRGD3MAINUP’,’CRGD4MAINUP’,’CRGD1MBILLS’,’CRGD2MBILLS’,’CRGD3MBILLS’,’CRGD4MBILLS’,’CRGDMBLPRT’) OR
JOB_EVENT.JOB_ID In
(’CRGD1GENDRY’,’CRGD2GENDRY’,’CRGD3GENDRY’,’CRGD4GENDRY’,’CRGDGRPACCS’,’CRGDOPSLT1’,’GELDA4LTTR’,’CRGDDDBACS’,’CRGDAVPBACS’,’CRGDFINOTS’))
ORDER BY JOB_EVENT.CONFIG_ITEM, JOB_EVENT.JOB_EVENT_TIME
Please note that I have had to break the JOB_EVENT.JOB_ID down into 3 sets using the 'OR' statement as the query reported an error when I had all of the job ids' listed in one bracket.
When I have press 'OK' on the SQL button the SELECT statement is reformatted as follows:
SELECT JOB_EVENT.RUN_ID, JOB_EVENT.CONFIG_ITEM, JOB_EVENT.JOB_ID, JOB_EVENT.JOB_EVENT, JOB_EVENT.JOB_EVENT_TIME
FROM SRD_DBA.JOB_EVENT JOB_EVENT
WHERE (JOB_EVENT.JOB_ID In ('CRGDMTRWRK','CRGDWTHDRWL','CASDTPINPUT','CASDMOVES','CRGDTPSTRIP','CRGD1CASHUP','CRGD2CASHUP','CRGD3CASHUP','CRGD4CASHUP','CRGDREADS','CRGDACCMRGE','CRGD1CHECK','CRGD2CHECK','CRGD3CHECK','CRGD4CHECK')) AND (JOB_EVENT.RUN_ID=?) AND (JOB_EVENT.JOB_EVENT In ('Started','Warning','Completed OK')) OR (JOB_EVENT.JOB_ID In ('CRGD1MAINUP','CRGD2MAINUP','CRGD3MAINUP','CRGD4MAINUP','CRGD1MBILLS','CRGD2MBILLS','CRGD3MBILLS','CRGD4MBILLS','CRGDMBLPRT')) AND (JOB_EVENT.RUN_ID=?) AND (JOB_EVENT.JOB_EVENT In ('Started','Warning','Completed OK')) OR (JOB_EVENT.JOB_ID In ('CRGD1GENDRY','CRGD2GENDRY','CRGD3GENDRY','CRGD4GENDRY','CRGDGRPACCS','CRGDOPSLT1','GELDA4LTTR','CRGDDDBACS','CRGDAVPBACS','CRGDFINOTS')) AND (JOB_EVENT.RUN_ID=?) AND (JOB_EVENT.JOB_EVENT In ('Started','Warning','Completed OK'))
ORDER BY JOB_EVENT.CONFIG_ITEM, JOB_EVENT.JOB_EVENT_TIME
This gives me the problem of JOB_EVENT_RUN_ID (which is to be displayed to the user for data entry) being displayed 3 times. The user then has to enter the same data 3 times. Please help.
SELECT JOB_EVENT.RUN_ID, JOB_EVENT.CONFIG_ITEM, JOB_EVENT.JOB_ID, JOB_EVENT.JOB_EVENT, JOB_EVENT.JOB_EVENT_TIME
FROM SRD_DBA.JOB_EVENT JOB_EVENT
WHERE (JOB_EVENT.RUN_ID=? AND
JOB_EVENT.JOB_EVENT In ('Started','Warning','Completed OK')) AND
(JOB_EVENT.JOB_ID In ('CRGDMTRWRK','CRGDWTHDRWL','CASDTPINPUT','CASDMOVES','CRGDTPSTRIP','CRGD1CASHUP','CRGD2CASHUP','CRGD3CASHUP','CRGD4CASHUP','CRGDREADS','CRGDACCMRGE','CRGD1CHECK','CRGD2CHECK','CRGD3CHECK','CRGD4CHECK') OR
JOB_EVENT.JOB_ID In (’CRGD1MAINUP’,’CRGD2MAINUP’,’CRGD3MAINUP’,’CRGD4MAINUP’,’CRGD1MBILLS’,’CRGD2MBILLS’,’CRGD3MBILLS’,’CRGD4MBILLS’,’CRGDMBLPRT’) OR
JOB_EVENT.JOB_ID In
(’CRGD1GENDRY’,’CRGD2GENDRY’,’CRGD3GENDRY’,’CRGD4GENDRY’,’CRGDGRPACCS’,’CRGDOPSLT1’,’GELDA4LTTR’,’CRGDDDBACS’,’CRGDAVPBACS’,’CRGDFINOTS’))
ORDER BY JOB_EVENT.CONFIG_ITEM, JOB_EVENT.JOB_EVENT_TIME
Please note that I have had to break the JOB_EVENT.JOB_ID down into 3 sets using the 'OR' statement as the query reported an error when I had all of the job ids' listed in one bracket.
When I have press 'OK' on the SQL button the SELECT statement is reformatted as follows:
SELECT JOB_EVENT.RUN_ID, JOB_EVENT.CONFIG_ITEM, JOB_EVENT.JOB_ID, JOB_EVENT.JOB_EVENT, JOB_EVENT.JOB_EVENT_TIME
FROM SRD_DBA.JOB_EVENT JOB_EVENT
WHERE (JOB_EVENT.JOB_ID In ('CRGDMTRWRK','CRGDWTHDRWL','CASDTPINPUT','CASDMOVES','CRGDTPSTRIP','CRGD1CASHUP','CRGD2CASHUP','CRGD3CASHUP','CRGD4CASHUP','CRGDREADS','CRGDACCMRGE','CRGD1CHECK','CRGD2CHECK','CRGD3CHECK','CRGD4CHECK')) AND (JOB_EVENT.RUN_ID=?) AND (JOB_EVENT.JOB_EVENT In ('Started','Warning','Completed OK')) OR (JOB_EVENT.JOB_ID In ('CRGD1MAINUP','CRGD2MAINUP','CRGD3MAINUP','CRGD4MAINUP','CRGD1MBILLS','CRGD2MBILLS','CRGD3MBILLS','CRGD4MBILLS','CRGDMBLPRT')) AND (JOB_EVENT.RUN_ID=?) AND (JOB_EVENT.JOB_EVENT In ('Started','Warning','Completed OK')) OR (JOB_EVENT.JOB_ID In ('CRGD1GENDRY','CRGD2GENDRY','CRGD3GENDRY','CRGD4GENDRY','CRGDGRPACCS','CRGDOPSLT1','GELDA4LTTR','CRGDDDBACS','CRGDAVPBACS','CRGDFINOTS')) AND (JOB_EVENT.RUN_ID=?) AND (JOB_EVENT.JOB_EVENT In ('Started','Warning','Completed OK'))
ORDER BY JOB_EVENT.CONFIG_ITEM, JOB_EVENT.JOB_EVENT_TIME
This gives me the problem of JOB_EVENT_RUN_ID (which is to be displayed to the user for data entry) being displayed 3 times. The user then has to enter the same data 3 times. Please help.