Oracle Query Help!

[emmim44]

Hi all, do you see any error with query? It is not functioning...Thank u all.

SELECT EMP_EID as EID,

CASE WHEN AVG(AHT) IS NULL THEN 0 ELSE AVG(AHT) END As Value,

  CASE WHEN AVG(AHT) IS NULL then 1 ELSE 0 END As Invalid

  FROM    OPS$RMS.SCM_TEST

  WHERE

  Start_date = {d '2009-07-01'} AND

  End_date = {d '2009-07-31'} AND

  EID IN ( (param 1) , (param 2) , (param 3) , (param 4) , (param 5) )

  GROUP BY EID

  ORDER BY EID

[Adam Cameron.]

Just a tip: when you're asking a question along the lines of "this doesn't work, why?" it's helpful if you include the error message (or other symptom)  which makes you think it's not working.

- What error are you getting?

- is {d '2009-07-01'} a valid format to express a date in Oracle?

--

Adam

[emmim44]

That is a product of createodbcdate...

[Adam Cameron.]

That is a product of createodbcdate...

Sure.  The thing is here you're not passing the resultant string to some sort of ODBC process as a date, you're just embedding the result in the SQL string that you're sending to the DB.  I imagine you're using a JDBC driver to connect to your Oracle DB anyway (so ODBC format isn't really relevant)?  But even if you were using ODBC drivers, if you're formatting a CF date for ODBC, then you need to actually then pass that result as a date, not just part of the SQL string.  The DB drivers are not clever enough to see a sequence of characters in the middle of the SQL string, recognise it as ODBC date format, replace that string with a param, pass the string as a date, send it to the DB etc.

So you either pass your dates as dates (via <cfqueryparam>), or you simply use a string and use TO_DATE() to convert it to a date on the Oracle side of things.

That said, I'm still just guessing that's your problem cos you still ain't posted the actual error msg...

?

--

Adam

[Cyril Hanquez]

The problem is probably with the dates but please post the source code and not the debug information in that case. I suppose that your "dates variables" are string, so you can do something like:

SELECT EMP_EID as EID,

CASE WHEN AVG(AHT) IS NULL THEN 0 ELSE AVG(AHT) END As Value,

  CASE WHEN AVG(AHT) IS NULL then 1 ELSE 0 END As Invalid

  FROM    OPS$RMS.SCM_TEST

  WHERE

  TO_CHAR(Start_date,'YYYY-MM-DD') = '2009-07-01' AND

  TO_CHAR(End_date,'YYYY-MM-DD') = '2009-07-31' AND

  EID IN ( (param 1) , (param 2) , (param 3) , (param 4) , (param 5) )

  GROUP BY EID

  ORDER BY EID

It will work fine with = but if you want to compare <> then you need to go with TO_DATE on the strings.

[emmim44]

Thank you...