DSXchange

I created a server job in the Parallel EE version for UserStatus purposes. For some reason the following sequel gives me error. It doesn't like the TO_CHAR line. If I copy the sequel into SQLPlus, it works. Could someone please advise. Thanks

SELECT field1 , field2
FROM CG8_LOG.COGIPF_RUNJOB a
WHERE TO_CHAR(COGIPF_LOCALTIMESTAMP,'YYYY-MM-DD HH24:MI:SS') > '2008-01-22 13:00:02'
ORDER BY COGIPF_LOCALTIMESTAMP DESC

Which Database?
Which Stage contains the SQL?
What is the exact error?
Does it work with an equality and not '>'?
Does it work with just the date part, and not with the timestamp?

Oracle 10G

Oracle OCI

As soon as I put the time segment into the variable, the sequel complains.
If I remove the time,the sequel return records but not the specific time variant ones.

I can get the sequel to work up to the hours. As soon as I enter minutes, the sequel return returns bogus info. If I enter the seconds, the sequel aborts.

The sequel still doesn't work with =

What is the data type of the COGIPF_LOCALTIMESTAMP column in Oracle (not your job)? What is the exact error you are getting? Saying it "complains" doesn't really help us.

It is a timestamp. The exact error is

METRICREFRESH_STATUS..Oracle_OCI_0: ORA-00904: "CG8_LOG_COGIPF_RUNJOB"."COGIPF_STATUS": invalid identifier
METRICREFRESH_STATUS..Oracle_OCI_0.DSLink1: DSP.Open GCI $DSP.Open error -100.

This appears as soon as I add the seconds to the variable.

Raftsman wrote:It is a timestamp. The exact error is

METRICREFRESH_STATUS..Oracle_OCI_0: ORA-00904: "CG8_LOG_COGIPF_RUNJOB"."COGIPF_STATUS": invalid identifier
METRICREFRESH_STATUS..Oracle_OCI_0.DSLink1: DSP.Open GCI $DSP.Open error -100.

This appears as soon as I add the seconds to the variable.

You get that error when your tablename or/and column name is invalid.

The query is complaining about a column not in your SQL. Please post the COMPLETE SQL statement.

What is strange is that if I remove the seconds from the variable, the sequel does not complain. I agree, the message is complaining about the fields but it's not the issue.

For your question, I provided the sequel below.

SELECT TO_CHAR(A.COGIPF_LOCALTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS'),
RTRIM(LTRIM(A.COGIPF_JOBPATH,'/content/package[@name=''LANDSO Metrics'']/folder[@name=''Metric Maintenance'']/jobDefinition[@name='),''']'),
A.COGIPF_STATUS
WHERE TO_CHAR(COGIPF_LOCALTIMESTAMP,'YYYY-MM-DD HH24:MI:SS') > '2008-01-22 13:00:02'
ORDER BY COGIPF_LOCALTIMESTAMP

You're missing the From <table> clause in your statement.
Oracle then always returns the last column in your select statement as the column which cannot be found
make sure the from clause is added

I concur. Now that we can see the SQL, the error message makes sense. You're missing the FROM <tablename> a (don't forget your alias).

My mistake, it was included. I did not cut and paste properly.

SELECT TO_CHAR(A.COGIPF_LOCALTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS'),
RTRIM(LTRIM(A.COGIPF_JOBPATH,'/content/package[@name=''LANDSO Metrics'']/folder[@name=''Metric Maintenance'']/jobDefinition[@name='),''']'),
A.COGIPF_STATUS
FROM CG8_LOG.COGIPF_RUNJOB a
WHERE TO_CHAR(COGIPF_LOCALTIMESTAMP,'YYYY-MM-DD HH24:MI:SS') > '2008-01-22 13:00:02'
ORDER BY COGIPF_LOCALTIMESTAMP

You are not using the alias in your where predicate.

Your single quote is not properly closed. I see that

has an opening single quote ' and then two later on. There's something screwy with that column. This doesn't make sense to me:

Raftsman wrote:What is strange is that if I remove the seconds from the variable, the sequel does not complain.

Note that he has said that the query complains ONLY if seconds are included in the where clause.

Yeah but improper quotes can cause the query parser to do weird things. It's like pulling teeth trying to get this query working. I traced my cursor over each quote and three single quotes in a row are a big red flag.