SELECT WHERE Problem

[Raftsman]

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

[gateleys]

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?

[Raftsman]

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 =

[chulett]

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.

[Raftsman]

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.

[gateleys]

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.

[kcbland]

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

[Raftsman]

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

[rverharen]

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

[kcbland]

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).

[Raftsman]

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

[DSguru2B]

You are not using the alias in your where predicate.

[kcbland]

Your single quote is not properly closed. I see that

Code: Select all

RTRIM(LTRIM(A.COGIPF_JOBPATH,'/content/package[@name=''LANDSO Metrics'']/folder[@name=''Metric Maintenance'']/jobDefinition[@name='),''']')

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

Code: Select all

''']'

[gateleys]

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.

[kcbland]

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.