Display Query results based on last 24 hours 5

[nicks60610]

I am trying to query my database to show me data that was entered within the last 24 hours; this is what I have in the query, but I know it's not right because it errors out each time...

TO_DATE(EQ_EVENTS.EVENT_END_DATE, 'MM/DD/YY') = TO_DATE(SYSDATE-24, 'MM/DD/YY':hh24:Mi:ss)

Can anyone please show me in the right direction so I can learn more about this function and complete this query? I have been looking online, but I cannot find much regarding what I am trying to accomplish.

Thanks,

 

[ddiamond]

I suspect the problem is with to_char(EVENT_END_TIME, 'HH24:MI'). What data-type is Event_end_time? Also what data-type is EVENT_END_DATE. I am assuming it is date-time stamp.

 

[nicks60610]

Event_End_Date is "Date" and the Event_End_Time is "varchar2"

Thanks ddiamond,

 

[ddiamond]

In that case, you don't need the 2nd to_char. Try:
WHERE to_date(to_char(EVENT_END_DATE, 'MM/DD/YYYY') || ' ' || EVENT_END_TIME, 'MM/DD/YYYY HH24:MI')
BETWEEN SYSDATE-1 and SYSDATE

 

[nicks60610]

I keep getting this:

ORA-00907: missing right parenthesis

But I can't see where it would the statement would be wrong, it looks good to me?

 

[ddiamond]

I tested the above code snipit on my oracle database, and it ran fine. Can you post your entire query that is giving that error?

 

[nicks60610]

cmdSelect = New OleDbCommand
("Select EQ_EVENTS.START_DATE, EQ_EVENTS.START_TIME, EQ_EVENTS.EVENT_END_TIME, EQ_EVENTS.EVENT_END_DATE, EQ_LOCATIONS.ISO9000, EQ_LOCATIONS.DESCRIPTION as LOCDESC, EQ_LOCATIONS.EQUIPMENT, SY_WO_STATUS.DESCRIPTION as SYDESC, EM_EMPLOYEE.EMPLOYEE_ID, EM_EMPLOYEE.SHIFT, WO_WORK_ORDER.ENTERED_BY, WO_WORK_ORDER.DEPARTMENT, WO_WORK_ORDER.REQUESTOR, WO_WORK_ORDER.STATUS, WO_WORK_ORDER.ENTERED_DATE, WO_WORK_ORDER.COMPLETED_DATE, WO_WORK_ORDER.WO_BASE, WO_WORK_ORDER.EQUIPMENT,WO_WORK_ORDER.DESCRIPTION as WODESC, WO_COMMENTS.LONG_DESCRIPTION, WO_WORK_ORDER.WO_TYPE

FROM EQ_EVENTS, WO_WORK_ORDER, WO_COMMENTS, EM_EMPLOYEE, SY_WO_STATUS, EQ_LOCATIONS

WHERE WO_WORK_ORDER.EQUIPMENT = EQ_LOCATIONS.EQUIPMENT AND WO_WORK_ORDER.STATUS = SY_WO_STATUS.CODE AND WO_WORK_ORDER.PLANT = SY_WO_STATUS.PLANT AND EM_EMPLOYEE.EMPLOYEE_ID = WO_WORK_ORDER.ENTERED_BY AND EM_EMPLOYEE.PLANT = WO_WORK_ORDER.PLANT AND WO_WORK_ORDER.PLANT = WO_COMMENTS.PLANT AND WO_WORK_ORDER.WO_BASE = EQ_EVENTS.WO_BASE AND WO_WORK_ORDER.WO_BASE = WO_COMMENTS.WO_BASE AND WO_WORK_ORDER.DEPARTMENT IN (''," & strDepartment & "'') AND EM_EMPLOYEE.SHIFT LIKE '%'
AND to_date(to_char(EVENT_END_DATE, 'MM/DD/YYYY') || ' ' || (EVENT_END_TIME, 'MM/DD/YYYY HH24:MI')) BETWEEN SYSDATE-1 And SYSDATE

order by EQ_LOCATIONS.ISO9000, EQ_EVENTS.EVENT_END_DATE, EQ_EVENTS.EVENT_END_TIME", conTab)

This is it, could it be because I am running the query through a .net page, but it still connects to an Oracle DB and runs the code through the DB?

 

[ddiamond]

I think I see the problem:

WO_WORK_ORDER.DEPARTMENT IN (''," & strDepartment & "[b][red]'[/red][/b]')

One of your single quotes are in the wrong spot. Try:

WO_WORK_ORDER.DEPARTMENT IN ('', '" & strDepartment & "')

 

[nicks60610]

Not sure why, but I still get the error...ORA-00907: missing right parenthesis

cmdSelect = New OleDbCommand("Select EQ_EVENTS.START_DATE, EQ_EVENTS.START_TIME, EQ_EVENTS.EVENT_END_TIME,

EQ_EVENTS.EVENT_END_DATE, EQ_LOCATIONS.ISO9000,

EQ_LOCATIONS.DESCRIPTION as LOCDESC,

EQ_LOCATIONS.EQUIPMENT, SY_WO_STATUS.DESCRIPTION as

SYDESC, EM_EMPLOYEE.EMPLOYEE_ID, EM_EMPLOYEE.SHIFT,

WO_WORK_ORDER.ENTERED_BY, WO_WORK_ORDER.DEPARTMENT,

WO_WORK_ORDER.REQUESTOR, WO_WORK_ORDER.STATUS,

WO_WORK_ORDER.ENTERED_DATE, WO_WORK_ORDER.COMPLETED_DATE,

WO_WORK_ORDER.WO_BASE,

WO_WORK_ORDER.EQUIPMENT,WO_WORK_ORDER.DESCRIPTION as

WODESC, WO_COMMENTS.LONG_DESCRIPTION,

WO_WORK_ORDER.WO_TYPE FROM EQ_EVENTS, WO_WORK_ORDER,

WO_COMMENTS, EM_EMPLOYEE, SY_WO_STATUS, EQ_LOCATIONS

WHERE WO_WORK_ORDER.EQUIPMENT = EQ_LOCATIONS.EQUIPMENT AND

WO_WORK_ORDER.STATUS = SY_WO_STATUS.CODE AND

WO_WORK_ORDER.PLANT = SY_WO_STATUS.PLANT AND

EM_EMPLOYEE.EMPLOYEE_ID = WO_WORK_ORDER.ENTERED_BY AND

EM_EMPLOYEE.PLANT = WO_WORK_ORDER.PLANT AND

WO_WORK_ORDER.PLANT = WO_COMMENTS.PLANT AND

WO_WORK_ORDER.WO_BASE = EQ_EVENTS.WO_BASE AND

WO_WORK_ORDER.WO_BASE = WO_COMMENTS.WO_BASE AND


WO_WORK_ORDER.DEPARTMENT IN ('', '" & strDepartment & "')

AND EM_EMPLOYEE.SHIFT LIKE '%'

AND to_date(to_char(EVENT_END_DATE, 'MM/DD/YYYY') || ' ' || (EVENT_END_TIME, 'MM/DD/YYYY HH24:MI')) BETWEEN SYSDATE-1 And SYSDATE


order by EQ_LOCATIONS.ISO9000, EQ_EVENTS.EVENT_END_DATE, EQ_EVENTS.EVENT_END_TIME", conTab)

Do you think because it is using ASP.net commands that something is not interpreting the query correct? I never have much success with Oracle and .NET as much as I do with SQL and .NET....

 

[ddiamond]

It is possible that it is ASP.NET, but not likely. I'll take a look at the query you just posted and see if I can spot any other syntax problems.

 

[nicks60610]

Thanks ddiamond, I can't tell you enough how much I appreciate your help!

 

[ddiamond]

AND to_date(to_char(EVENT_END_DATE, 'MM/DD/YYYY') || ' ' || (EVENT_END_TIME, 'MM/DD/YYYY HH24:MI')) BETWEEN SYSDATE-1 And

change to

AND to_date(to_char(EVENT_END_DATE, 'MM/DD/YYYY') || ' ' || EVENT_END_TIME, 'MM/DD/YYYY HH24:MI') BETWEEN SYSDATE-1 And

 

[ddiamond]

I forgot the final sysdat.

AND to_date(to_char(EVENT_END_DATE, 'MM/DD/YYYY') || ' ' || EVENT_END_TIME, 'MM/DD/YYYY HH24:MI') BETWEEN SYSDATE-1 And [blue]SYSDATE[/blue]

 

[nicks60610]

:- ( , no, the same thing, "missing right parenthesis..."

At this point I am about ready to tell them to live with the "36 hr", hehehehe.

 

[ddiamond]

Can you post what your query looks like now.

 

[nicks60610]

cmdSelect = New OleDbCommand("Select EQ_EVENTS.START_DATE, EQ_EVENTS.START_TIME, EQ_EVENTS.EVENT_END_TIME,

EQ_EVENTS.EVENT_END_DATE, EQ_LOCATIONS.ISO9000,

EQ_LOCATIONS.DESCRIPTION as LOCDESC,

EQ_LOCATIONS.EQUIPMENT, SY_WO_STATUS.DESCRIPTION as

SYDESC, EM_EMPLOYEE.EMPLOYEE_ID, EM_EMPLOYEE.SHIFT,

WO_WORK_ORDER.ENTERED_BY, WO_WORK_ORDER.DEPARTMENT,

WO_WORK_ORDER.REQUESTOR, WO_WORK_ORDER.STATUS,

WO_WORK_ORDER.ENTERED_DATE, WO_WORK_ORDER.COMPLETED_DATE,

WO_WORK_ORDER.WO_BASE,

WO_WORK_ORDER.EQUIPMENT,WO_WORK_ORDER.DESCRIPTION as

WODESC, WO_COMMENTS.LONG_DESCRIPTION,


WO_WORK_ORDER.WO_TYPE FROM EQ_EVENTS, WO_WORK_ORDER,

WO_COMMENTS, EM_EMPLOYEE, SY_WO_STATUS, EQ_LOCATIONS

WHERE WO_WORK_ORDER.EQUIPMENT = EQ_LOCATIONS.EQUIPMENT AND

WO_WORK_ORDER.STATUS = SY_WO_STATUS.CODE AND

WO_WORK_ORDER.PLANT = SY_WO_STATUS.PLANT AND

EM_EMPLOYEE.EMPLOYEE_ID = WO_WORK_ORDER.ENTERED_BY AND

EM_EMPLOYEE.PLANT = WO_WORK_ORDER.PLANT AND

WO_WORK_ORDER.PLANT = WO_COMMENTS.PLANT AND

WO_WORK_ORDER.WO_BASE = EQ_EVENTS.WO_BASE AND

WO_WORK_ORDER.WO_BASE = WO_COMMENTS.WO_BASE AND

WO_WORK_ORDER.DEPARTMENT IN ('', '" & strDepartment & "')

AND EM_EMPLOYEE.SHIFT LIKE '%'

AND to_date(to_char(EVENT_END_DATE, 'MM/DD/YYYY') || ' ' || EVENT_END_TIME, 'MM/DD/YYYY HH24:MI') BETWEEN SYSDATE-1 And SYSDATE

order by EQ_LOCATIONS.ISO9000, EQ_EVENTS.EVENT_END_DATE, EQ_EVENTS.EVENT_END_TIME", conTab)

 

[ddiamond]

You also may want to test a simpler form of your query.

cmdSelect = New OleDbCommand("Select EQ_EVENTS.START_DATE, EQ_EVENTS.START_TIME, EQ_EVENTS.EVENT_END_TIME, 
EQ_EVENTS.EVENT_END_DATE, EQ_LOCATIONS.ISO9000, 
EQ_LOCATIONS.DESCRIPTION as LOCDESC
FROM EQ_EVENTS
WHERE 
  to_date(to_char(EVENT_END_DATE, 'MM/DD/YYYY') || ' ' || EVENT_END_TIME, 'MM/DD/YYYY HH24:MI') BETWEEN SYSDATE-1 And SYSDATE 
order by EQ_LOCATIONS.ISO9000, EQ_EVENTS.EVENT_END_DATE, EQ_EVENTS.EVENT_END_TIME", conTab)

 

[nicks60610]

Cool, it is getting closer now:

ORA-01843: not a valid month

So this is the new simplified query I am running:

cmdSelect = New OleDbCommand("Select EQ_EVENTS.START_DATE, EQ_EVENTS.START_TIME, EQ_EVENTS.EVENT_END_TIME, EQ_EVENTS.EVENT_END_DATE

FROM EQ_EVENTS

WHERE to_date(to_char(EVENT_END_DATE, 'MM/DD/YYYY') || ' ' || EVENT_END_TIME, 'MM/DD/YYYY HH24:MI') BETWEEN SYSDATE-1 And SYSDATE

order by EQ_EVENTS.EVENT_END_DATE, EQ_EVENTS.EVENT_END_TIME", conTab)

 

[ddiamond]

Your perentheses look correct. I assume this is VB.NET?
I don't know if this will make a difference, but you could try the following:

sSQL = "Select EQ_EVENTS.START_DATE, "
sSQL = sSQL & "EQ_EVENTS.START_TIME, "
sSQL = sSQL & "EQ_EVENTS.EVENT_END_TIME, "
sSQL = sSQL & "EQ_EVENTS.EVENT_END_DATE, EQ_LOCATIONS.ISO9000, "
sSQL = sSQL & "EQ_LOCATIONS.DESCRIPTION as LOCDESC, "
sSQL = sSQL & "EQ_LOCATIONS.EQUIPMENT, SY_WO_STATUS.DESCRIPTION as SYDESC, "
sSQL = sSQL & "EM_EMPLOYEE.EMPLOYEE_ID, EM_EMPLOYEE.SHIFT, "
sSQL = sSQL & "WO_WORK_ORDER.ENTERED_BY, WO_WORK_ORDER.DEPARTMENT, "
sSQL = sSQL & "WO_WORK_ORDER.REQUESTOR, WO_WORK_ORDER.STATUS, "
sSQL = sSQL & "WO_WORK_ORDER.ENTERED_DATE, WO_WORK_ORDER.COMPLETED_DATE, "
sSQL = sSQL & "WO_WORK_ORDER.WO_BASE, "
sSQL = sSQL & "WO_WORK_ORDER.EQUIPMENT,WO_WORK_ORDER.DESCRIPTION as WODESC, "
sSQL = sSQL & "WO_COMMENTS.LONG_DESCRIPTION, "
sSQL = sSQL & "WO_WORK_ORDER.WO_TYPE FROM EQ_EVENTS, WO_WORK_ORDER, "
sSQL = sSQL & "WO_COMMENTS, EM_EMPLOYEE, SY_WO_STATUS, EQ_LOCATIONS "
sSQL = sSQL & "WHERE WO_WORK_ORDER.EQUIPMENT = EQ_LOCATIONS.EQUIPMENT AND "
sSQL = sSQL & "WO_WORK_ORDER.STATUS = SY_WO_STATUS.CODE AND "
sSQL = sSQL & "WO_WORK_ORDER.PLANT = SY_WO_STATUS.PLANT AND "
sSQL = sSQL & "EM_EMPLOYEE.EMPLOYEE_ID = WO_WORK_ORDER.ENTERED_BY AND "
sSQL = sSQL & "EM_EMPLOYEE.PLANT = WO_WORK_ORDER.PLANT AND "
sSQL = sSQL & "WO_WORK_ORDER.PLANT = WO_COMMENTS.PLANT AND "
sSQL = sSQL & "WO_WORK_ORDER.WO_BASE = EQ_EVENTS.WO_BASE AND "
sSQL = sSQL & "WO_WORK_ORDER.WO_BASE = WO_COMMENTS.WO_BASE AND "
sSQL = sSQL & "WO_WORK_ORDER.DEPARTMENT IN ('', '" & strDepartment & "') "
sSQL = sSQL & "AND EM_EMPLOYEE.SHIFT LIKE '%' "
sSQL = sSQL & "AND to_date(to_char(EVENT_END_DATE, 'MM/DD/YYYY') || ' ' || EVENT_END_TIME, "
sSQL = sSQL & "'MM/DD/YYYY HH24:MI') BETWEEN SYSDATE-1 And SYSDATE "
sSQL = sSQL & "order by EQ_LOCATIONS.ISO9000, "
sSQL = sSQL & "EQ_EVENTS.EVENT_END_DATE, "
sSQL = sSQL & "EQ_EVENTS.EVENT_END_TIME "
cmdSelect = New OleDbCommand( sSQL, conTab )

 

[ddiamond]

I posted my last post before I saw your response. A timing thing. From your last post, I don't think rewriting it with sSQL will help.

 

[nicks60610]

So odd that anyone would design a DB like that, separating time and date stamps, I have never seen it before in other databases, but they must have had a reason? I may just have to live with the 24-36 hr option until I can get further with this...I don't want to take up all your time ddiamond, and I truly appreciate all you have helped me with so far.