Use query2query with sysdate - condition > 0

dev support
1

Hi, is there a way to us e the script below in a query to query ? Unfortunately, query to query don’t accept this “sysdate - kepttt > 0” is there any workaround?

<cfquery name="test" datasource="test">
		SELECT COUNT(kepttt)
		FROM  db 
                **where sysdate - kepttt > 0** 
</cfquery>

Goal:

**Main query:**
<cfquery name="test" datasource="test">
		SELECT *
		FROM  db
</cfquery>


**Query2query**
<cfquery name="dbtest" dbtype="query">
		SELECT COUNT(kepttt)
		FROM  test
               **where sysdate - kepttt > 0** 
</cfquery>
2 
You could do something like this. You would want to use proper CFSQL tags, but its a starting point.
**Main query:**
<cfset FooBar = "kepttt">

<cfquery name="test" datasource="test">
		SELECT COUNT(#fooBar#)
		FROM  db
</cfquery>
<cfset Foobar2 = #test.foobar# >


**Query2query**
<cfquery name="dbtest" dbtype="query">
		SELECT COUNT(#fooBar2#)
		FROM  test
               **where sysdate - #fooBar# > 0** 
</cfquery>
3

Thank you for the reply and support, but i get a Was expecting one of: “ALL” … “DISTINCT” … … “*” … .

any other suggestion ? thx.

4

Hi @Retooo, This is an Oracle DB right? Don’t know Oracle… But still, I’m not able to get what you are trying to do with your code. Sysdate is a datetime object that returns system date time, just like now(), isn’t it? What values are kepttt? Are they datetime values also? If you use where sysdate - kepttt > 0 and assuming both objects are the same type, wouldn’t that be the same as where kepttt <= sysdate? The problem is that I just can’t get what your main goal is, what the data and structure of the db/tables are and that makes it difficult to understand what your main goal is.

5

Hello Andreas, yes, I use oracle db. I’m sorry, I should be more specific… I apologize for the inconvenience.
Kepttt is a column with date like 24.01.2020 15:30.
What I’m trying to do is count the dates.

This query works. 
<cfquery name="test" datasource="test">
		SELECT COUNT(kepttt)
		FROM db 
               where sysdate - kepttt > 0 
</ cfquery>

Example Result: kepttt 1230 records.

sysdate - kepttt > 0

explanation: sysdate (current date/time status {now}) - Date already stored in the DB greater than 0

The result will be that I get all date/time in the past

In order not to execute the whole query again and use it for other queries, I wanted to use the query2query solution.

Use the query:
<cfquery name="test" data source="test">
		SELECTION *
		FROM db 
</cfquery>

Then use query2query, but the problem I have is that query2query does not accept sysdate - kepttt > 0.

<cfquery name="dbtest" dbtype="query">
		SELECT COUNT(kepttt)
		FROM test
              where sysdate - kepttt > 0
</ cfquery>

Is there another solution that accepts query2query sysdate - kepttt > 0 ?

6

Your first query should read something like this

SELECT
COUNT( ALL val )
FROM
db

7

after checking this is the solution thx.

1 Like