Select with out from

[Alteryx_new]

I have written a sql query in the Alteryx Input data tool as below

select date_format(date_add('month', 0, current_date),'%Y%m%d')  as  var1,
date_add('month', -1, current_date ) as var2 ;

and when I tested the query it gives an error.

What exactly the problem with the query ?

How can I make it run  create the variables ?

[patrick_mcauliffe]

Hey @Alteryx_new 

Alteryx is just passing sql to and from the database via ODBC, so the database is giving you the error.

To troubleshoot this:
1. What database technology is this connected to?

2. What is the exact text of the error?

3. Is there anything else to your SQL statement?

[lepome]

For example, if it's Oracle, you must have a From, even if it's just From DUAL

[Alteryx_new]

Hi,

1)The actual query run was below  and it was on presto the image is attached

select cast(date_format(date_add('month', 0, date_add('day', -day(current_date)+1, current_date)),'%Y%m%d') as varchar) as param1,
date_add('month', -1, date_add('day', -day(current_date)+1, current_date)) as param2 ;

2)The  error is attached

3) Nothing else to my sql statement , I just want to create the parameters  and give as input to the dynamic input tool

Thanks

[patrick_mcauliffe]

@Alteryx_new  did you try @lepome 's suggestion of using "FROM DUAL"?

[patrick_mcauliffe]

Hey @Alteryx_new another thing I thought of:

The error message "line 1.33" usually means line 1 character 33 in your statement is where it detected a problem parsing the query.

The "Expecting '('" usually means that you have a missing open paren or extra close paren, or you have an extra comma between a set of parens making it expect a different number of parameters in a statement.

I've looked over a few times and I can't seem to find where it would be (nor do I have a Presto DB handy); but I would start with Lisa's suggestion and then try to work through those statements again.

[lepome]

@Alteryx_new 
I have never used Presto, but this page from their documentation might help you.  It makes me wonder whether you need to use current_timestamp where you have current_date in the date_add() function.

[ArnaldoSandoval · Accepted solution]

Hi @Alteryx_new ; @patrick_mcauliffe and @lepome 

I also read the available documentation for the Presto Database, (as I never had used it); and discover that in their latest versions they deprecated DUAL, evidence below:

I don't have Presto DB installed in my laptop, and I have no idea how to write an SQL SELECT query without a FROM, my suggestion to @Alteryx_new are:

1. Try a more standard query SELECT - FROM table.
2. Can you try the query on Presto DB client, where it allows you to run queries against the engine or any database.
3. As suggested by @patrick_mcauliffe What did you find at line 1.33; it seems the message is coming from Presto DB itself, telling us that the query is not properly buidl.

hth

Arnaldo