issues with Oracle Function

That should not run.

you are missing and AND/OR:

...
OR Lower(name) like '%10/21/2011%'  AND
 CAST
...

You should also not need to cast a TO_CHAR and a string literal as a  VARCHAR2

Also Query of Query does not allow all the functions that a database allows, particularly database-specific functions.

CAST(), TO_CHAR() are likely among the functions you cannot use in QofQ... and really probably don't need them anyway since the original query already handles converting the values to string...

does this make sense?

OR Lower(name) like '%10/21/2011%'

is your "name" column really a date-formatted string?

You would still need TO_CHAR() for the date to string conversion.

Never used QoQ but I would hope it allows that one.  It is fairly old.

I can see it not recognizing CAST.

also, rather than convert a string to a string and a date to a string and then into another string

why not just compare your date to a date...


SELECT   sdate, pdate
    FROM myvariablequery
   WHERE    1 = 1 AND LOWER(id) = 10
         OR LOWER(name) LIKE '%10/21/2011%' AND sdate = TO_DATE('10/21/2011', 'mm/dd/yyyy')
ORDER BY sdate DESC

also,  I'm just guessing you want AND the date check,  you might want to OR it,  but you can't tell from your original query what the intent was

sdate likely has a time piece:

...
AND (sdate >= TO_DATE('10/21/2011', 'mm/dd/yyyy') and sdate < TO_DATE('10/22/2011', 'mm/dd/yyyy'))

also on this....

OR Lower(name) like '%10/21/2011%'


if name really is a string containing a date format like that,  why use LOWER?

LOWER, UPPER or leave it alone,  the case of any alphabetic characters is irrelevant when compared to numbers and "/" characters

ok, in my original i skipped some things, the actual is like this

SELECT sdate,pdate FROM myvariablequery  
  WHERE 1 = 1    
 AND Lower(id) = 10  
 OR Lower(name) like '%10/21/2011%'  OR
 CAST(to_char(sdate,'mm/dd/yyyy') AS VARCHAR(30)) = CAST( ('10/21/2011')  AS VARCHAR(30))  
 ORDER BY  
  sdate  
  desc

i understand that itwill not find date in name or any field, so what i am trying is if it encounters date, it should treat it as varchar and matches the exactly date i entered

as far the date is stored in oracle as : mm/dd/yyyy + time also

so if i only use cast, it will never going to match the value in database table as it will not find the time with it..

so to render it and to to foce QoQ to only find the date and leave the time

So please guide

>>will never going to match the value in database table as it will not find the time with it

to_char(sdate,'mm/dd/yyyy') strips the time.  No need for cast.

in other words this is all you need to ignore time:

to_char(sdate,'mm/dd/yyyy') = '10/21/2011'

Now, if sdate has an index you want to use, you need the code in http:#a37026810

>>>as far the date is stored in oracle as : mm/dd/yyyy + time also


no,  dates don't have formats.

if sdate is a DATE type,  then use

AND (sdate >= TO_DATE('10/21/2011', 'mm/dd/yyyy') and sdate < TO_DATE('10/22/2011', 'mm/dd/yyyy'))

as slightwv showed above.  I left that out thinking your data was already truncated to the day.  If it's not
then you need to account for that.

you "could" use to_char  or trunc but doing the range based query is best  (unless you create special sequences to handle the functions)

ok let me try

ok i tried with the following and it gave me this error now

Encountered "to_char ( submitDate ,. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition,

SELECT sdate,pdate FROM myvariablequery  
  WHERE 1 = 1    
 AND Lower(id) = 10  
 OR Lower(name) like '%10/21/2011%'  OR
 to_char(sdate,'mm/dd/yyyy') = ('10/21/2011')  
 ORDER BY  
  sdate  
  desc

i mean like this

SELECT sdate,pdate FROM myvariablequery  
  WHERE 1 = 1    
 AND Lower(id) = 10  
 OR Lower(name) like '%10/21/2011%'  OR
 to_char(sdate,'mm/dd/yyyy') = '10/21/2011'
 ORDER BY  
  sdate  
  desc

From the perspective of Coldfusion and Query-of-Query, the original query has created a record set of records, those records now are stored in a coldfusion data structure.  The QofQ is going to search through this data structure for matching records.  The QofQ does not go back to the database, so database specific functions don't work and usually are not needed because you already have the data set collected.

Remove Lower() because you are comparing against numbers in both cases...

Remove data conversions... what is the format of sdate in the SELECT of your first query?  

In your original query select to_char(sdate,'mm/dd/yyyy') as SDayOnly, the will have your record set already contain only the date, no time.


SELECT sdate,pdate FROM myvariablequery  
 WHERE  id = 10  
 OR name like '%10/21/2011%'  
 OR SDayOnly = '10/21/2011'
 ORDER BY sdate DESC


General tip on QofQ...  if you're using it because you want to your page to run faster, don't use it.  The database is much faster than your server sorting through records of data.  Even if you have to call the query again, it will be faster than QofQ.    There are reason to use QofQ, but better performance is rarely one of them.

please read "submitDate" as sdate, a typing mistake by me

the thing is using QoQ because it is fetching records from different set of functions based on certain conditions, but that is another condition so let's not work on that

I'm not a cold fusion person but I cannot imagine it would not support TO_CHAR.

I can find a LOT of Cold Fusion references to using TO_CHAR.

Hopefully another Expert can help with the cold fusion specifics of QoQ.

> ok i tried with the following and it gave me this error now

right, you can't use that type of function.  As I suggestioned above, convert the date in your original query to the format you need and then do a simplier comparison in the where clause of your QofQ

I agree with the above post.

I just stumbled on a QoQ example.

Use to_char and Oracle functions in your cfquery.  Then reference the column aliases in the QoQ query.

slightwv, coldfusion certainly supports all oracle functions in a regular query.   This is not a regular query, a Query-of-Query works like this...

getData =  select to_char(xx) and all other regular stuff from oracle, sql, mysql, etc... everything supported

Then...

you do a "query of"  getData... you are actually doing a query of a query... not of the database.  In other words you are filtering an already existing record set fetched from the database down further... you are not going back to the database, you are filtering a record set.   This query-of-a-query does not support all database functions.

alright gd

i worked with your approach and just removed the cast, now does not show me any data

First, you have to determine which portion of the where clause is not matching...  and look at the data for those columns to see what it looks like in your first query.

If this is the query you are using in your QofQ, then you have only OR conditions, so any One of them could match to show you records...

SELECT sdate,pdate FROM myvariablequery  
 WHERE  id = 10  
 OR name like '%10/21/2011%'  
 OR SDayOnly = '10/21/2011'
 ORDER BY sdate DESC


So first look at the sDayOnly value.. are you selecting the date in  mm/dd/yyyy format?  You can try matching it as a string or as a date ....

 OR SDayOnly = '10/21/2011'

or date...

 OR   SDayOnly = #createODBCdate('10/21/2011')#


Be sure to look at the value selected by the original query... what does SDayOnly look like?

in database oracle it is stored as:

10/25/2011 2:58:00 AM

>>in database oracle it is stored as:

No it isn't.  In Oracle it is a date.  They don't have a format.  

They only have a format on display and that is driven by the application doing the displaying.

We just need to get QoQ or cfquery/??? to use to_char to  strip out the time portion.

yes, but how, i am confused

slightwv is right, the raw type is some type of float number, you need to choose your format in your original query.


> We just need to get QoQ or cfquery/??? to use to_char to  strip out the time portion.

exactly...  in CFQUERY, select the date in the right format, so that the Query of Query can recognize it and work with it.


something like this...

    SELECT  TO_CHAR(sDate,'mm/dd/yyyy') as  SDayOnly

Can you post the cfquery that your QoQ is using?

I bleieve this is where the to_date and Oracle specific calls should be.

but that is just called as:

select * from table

That is the problem. Do not do a select *.  add the individual columns then add to_char to the date columns:

select to_char(date_column,'mm/d/yyyy') as sdate,
     --- the rest of the columns here---
from table;


"select * from" is a bad coding practice to get into.  All a DBA/developer would have to do is add a column to the table and code breaks all over the place when it gets some extra data it isn't expecting.

CAST is not a CF Function then why it is handling that

in QoQ i am using CAST a Oracle Function and mostly all database support it then why it issues on to_char

Let's not worry about CAST for now.  Let's get the code working first.  Then we can come back to CAST if you want to.

yes that's my questions is, if i handle caset why not then to_char in QoQ, because if i run the same query in the oracle env, it works fine.

>>yes that's my questions is

Which do you want to pursue:

Why QoQ doens't like TO_CHAR or getting your app running?

>>getting your app running?

Also i will keep open this question for next 2 days, so catch back later, Thanks

well i am currently away from the code but it shows the sdate as:

10/21/2011 02:10:55 Am

>>from the code but it shows the sdate as

That must just be the default for whatever is displaying it.

If you don't specify the format, the app picks one for you.

Please change the cfquery to use to_char.  Then the QoQ to use the examples above.

As slightwv said, that format is the default... but to illustrate my point the format of 10/21/2011 02:10:55 Am  does not match your where clause of  '10/21/2011'  

So you need to add the TO_CHAR function to set the date format and truncate the time, then they will match

so gd you are saying, i am actually calling the query and inside the query i have sDate so i can call sDate as one and sDate as to_char(sDate) as ssDate and then match them

I believe that is what we are saying.

Your cfquery will convert the date data type into a string using to_char then alias the column to whatever name you want.  Then that result set is used by QoQ.  In QoQ you should not have to do any data type conversions since they have already been done.

Yes... as example..


mainQuery...

select t.*   ---- put in your column, including just sDate
        , to_char(t.sDate) as ssDate
from table t


QofQ query...

select *
  from MainQuery
where ssDate = '10/1/2011'

oh right, thanks :)