Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 439
  • Last Modified:

issues with Oracle Function

Hi Experts! I am running into very Starnge issue!

i have the following query, when i run it in my Oracle Browser, it works finebut when the same query query is running into my QoQ it throws an error!

Here is the code, it does not work in QoQ

i get this error

Encountered "CAST ( to_char (. 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%'
 CAST(to_char(sdate,'mm/dd/yyyy') AS VARCHAR(30)) = CAST( ('10/21/2011')  AS VARCHAR(30))
 ORDER BY 
  sdate 
  desc

Open in new window

0
Gurpreet Singh Randhawa
Asked:
Gurpreet Singh Randhawa
  • 16
  • 16
  • 10
  • +1
2 Solutions
 
slightwv (䄆 Netminder) Commented:
That should not run.

you are missing and AND/OR:

...
OR Lower(name) like '%10/21/2011%'  AND
 CAST
...
0
 
slightwv (䄆 Netminder) Commented:
You should also not need to cast a TO_CHAR and a string literal as a  VARCHAR2
0
 
gdemariaCommented:
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...
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
sdstuberCommented:
does this make sense?

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

is your "name" column really a date-formatted string?
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
sdstuberCommented:
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
0
 
slightwv (䄆 Netminder) Commented:
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'))

0
 
sdstuberCommented:
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
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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
0
 
slightwv (䄆 Netminder) Commented:
>>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.
0
 
slightwv (䄆 Netminder) Commented:
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
0
 
sdstuberCommented:
>>>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)
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
ok let me try
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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

0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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

0
 
gdemariaCommented:

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.

0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
please read "submitDate" as sdate, a typing mistake by me
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
gdemariaCommented:
> 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
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
gdemariaCommented:

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.


0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
alright gd

i worked with your approach and just removed the cast, now does not show me any data
0
 
gdemariaCommented:
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?



0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
in database oracle it is stored as:

10/25/2011 2:58:00 AM
0
 
slightwv (䄆 Netminder) Commented:
>>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.
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
yes, but how, i am confused
0
 
gdemariaCommented:
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

0
 
slightwv (䄆 Netminder) Commented:
Can you post the cfquery that your QoQ is using?

I bleieve this is where the to_date and Oracle specific calls should be.
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
but that is just called as:

select * from table
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
CAST is not a CF Function then why it is handling that
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
in QoQ i am using CAST a Oracle Function and mostly all database support it then why it issues on to_char
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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.

0
 
slightwv (䄆 Netminder) Commented:
>>yes that's my questions is

Which do you want to pursue:

Why QoQ doens't like TO_CHAR or getting your app running?
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
>>getting your app running?

Also i will keep open this question for next 2 days, so catch back later, Thanks
0
 
gdemariaCommented:
myselfrandhawa, please look at my previous posts that talk about how QofQ works.  It doesn't support all functions... to_char() is Oracle specific.   CAST() exists in several database languages.   Some functions are supported, others are not.   QofQ is not fetching from your database, it's filtering a data structure that exists on your CF server.

To get this working, all you really need to do is change it from a query of query to a regular query against the database.   But if you want to use QofQ then you to do some tweaks.

Here's something you can try...  if you want to examine it more closely..

Show the output of your first query, so the value of the sDate column so we can see what the data looks like when fetched directly from the database as is...  


Or to keep moving... do as slightwv is suggesting.
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
well i am currently away from the code but it shows the sdate as:

10/21/2011 02:10:55 Am
0
 
slightwv (䄆 Netminder) Commented:
>>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.
0
 
gdemariaCommented:
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
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
gdemariaCommented:

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'



0
 
slightwv (䄆 Netminder) Commented:
Minor correction.  Add the date mask on the to_char.

change:
to_char(t.sDate) as ssDate

to:
to_char(t.sDate,'mm/dd/yyyy') as ssDate
0
 
gdemariaCommented:
oh right, thanks :)
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 16
  • 16
  • 10
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now