- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
How to use evalinsert on SQL Query?
Hi all,
I am trying to query using a list and a variable. However, I cannot figure out how to query the dateNow variable. I keep on getting this error:
Error occurred while trying to execute a query: [SQLState 42601] ERROR: syntax error at or near "00"
LINE 4: and test_date_time between 03/12/2024 00:00:00 AM and 03/12/...
Here is my sample script:
namecol= {"James", "John", "Anna", "Rose")
dateNow = "03/12/2024"
eval(parse(evalinsert("\[
dt_qst = Try ( Open Database( "DSN=dbs;UID=uname;PWD=pw;APP=JMP;DATABASE=db;",
"select name, age, address, test_date_time
from db
where name in (^namecol^)
and test_date_time between ^dateNow^ 00:00:00 AM and ^dateNow^ 11:59:59 PM'",
"rawdata") )
]\")));;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: How to use evalinsert on SQL Query?
There are many small typos in your script which prevent it from working. Outside of that you need to use Concat Items to go from list to a string. Also to make it a bit easier to debug, build your query string outside of Open Database and then use that new string inside it.
Names Default To Here(1);
namecol= {"James", "John", "Anna", "Rose"};
dateNow = "03/12/2024";
sql_template = "\[
select
name,
age,
address,
test_date_time
from db
where
name in ('^namestr^')
and test_date_time between ^dateNow^ 00:00:00 AM and ^dateNow^ 11:59:59 PM'
]\";
namestr = Concat Items(namecol, "', '");
sql_str = Eval Insert(sql_template);
I also think this has been basically answered already twice
How to skip item on JSL list in SQL query if it does not exist? and How to query in SQL using list from JSL
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: How to use evalinsert on SQL Query?
Yes, but I don't understand why I kept on getting this error
Error occurred while trying to execute a query: [SQLState 42601] ERROR: syntax error at or near "00"
LINE 10: and test_date_time between 03/12/2024 00:00:00 AM and 03/12...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: How to use evalinsert on SQL Query?
Maybe your dates aren't correctly formatted? Most likely you are missing ' around the dates