Skip to main content
E
emmim44
Inspiring
September 15, 2009
Question

Oracle Query Help!

  • September 15, 2009
  • 3 replies
  • 1910 views

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

This topic has been closed for replies.

3 replies

E
emmim44Author
Inspiring
September 26, 2009

Thank you...

Cyril Hanquez
Cyril Hanquez
Participating Frequently
September 25, 2009

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.

A
Adam Cameron.
Inspiring
September 15, 2009

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

E
emmim44Author
Inspiring
September 15, 2009

That is a product of createodbcdate...

A
Adam Cameron.
Inspiring
September 15, 2009

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

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices