572310_en-US

liliconcom

Eval insert SQL

Hi All,

i am trying to produce an SQL statment that will bring only rows with matching id names.

i have a userlist that i want to Eval insert into the SQL string. problem is that it parses it with double quotes instead of single quotes.

 

 

 

Names default to here (1);<BR />// got the userlist from a data table using dt:users << get as matrix<BR />userlist  = {"01E1B5C0651EDA80", "F62B70921920FCBE", "909D35028E2039CE", "B339888591CF1346"};
<BR />// define an SQL string to host the list of user ids
sqlstr_ =
"SELECT  \!"t1\!".uid 
FROM \!"Tables\!".\!"Table\!"  \!"t1\!"
WHERE  ( \!"t1\!".uid IN ( ^userlist^) )
;";

Eval Insert( sqlstr_logs );
// i get the following non working SQL string 
"SELECT  \!"t1\!".uid
FROM \!"Tables\!".\!"Table\!"  \!"t1\!"
WHERE  ( \!"t1\!".uid IN ( {\!"01E1B5C0651EDA80\!", \!"F62B70921920FCBE\!", \!"909D35028E2039CE\!", }) )
;"
// i would like to get the following working SQL statement
"SELECT \!"t1\!".uid 
FROM \!"Tables\!".\!"Table\!"  \!"t1\!" 
WHERE  ( \!"t1\!".uid IN  ( '01E1B5C0651EDA80' ,  'F62B70921920FCBE' ,  'F62B70921920FCBE' )  ) ;"

thank you very much in advance.

 

Data Blending and CleanupRe: Eval insert SQL

Similar example

sqlstr_uid =
"\[
SELECT DISTINCT "t1".uid 
FROM "Tables"."Table"  "t1" 
WHERE    "t1".uid REGEXP '^[A-Za-z0-9]+$' 
]\";

write(Eval Insert( "the test ☺sqlstr_uid☺ works", "☺" ));

the test
SELECT DISTINCT "t1".uid
FROM "Tables"."Table" "t1"
WHERE "t1".uid REGEXP '^[A-Za-z0-9]+$'
works

There does not appear to be a \! escaping mechanism in Eval Insert; you need to pick an unused character.

 

Re: Eval insert SQL

thank you very much @jthi this is perfect!

Re: Eval insert SQL

You can define which character to use with Eval Insert and I would suggest using some other character than default ^ if you are evaluating sql strings (especially ones with regex), I usually use "¤"

Names Default To Here(1);

sqlstr_uid =
"\[
SELECT DISTINCT "t1".uid 
FROM "Tables"."Table"  "t1" 
WHERE    "t1".uid REGEXP '^[A-Za-z0-9]+$' 
]\";

Write(Eval Insert(sqlstr_uid, "¤"));

Re: Eval insert SQL

@Craige_Hales 
the following regex expression works when submitting the sql statement as a string:
regexp_substr("t1".uid, '^[A-Za-z0-9]+$')="t1".uid )

 

yet, when submitting this expression using eval insert (sql_str) i get an error.
the error has to do with the [ so i assume that evel insert is having a hard time with the ^ just before it.
is there a way of escaping the ^  ?
the following shows the exact error.

sqlstr_uid =
"\[
SELECT DISTINCT "t1".uid 
FROM "Tables"."Table"  "t1" 
WHERE    "t1".uid REGEXP '^[A-Za-z0-9]+$' 
]\";

write(Eval Insert( sqlstr_uid ));

many thanks!

ron

 

 

Re: Eval insert SQL

Thank you @ErraticAttack ,

it didn't work. i did manage to use this expression:

 

regexp_substr("t1".uid, '^[A-Za-z0-9]+$')="t1".uid )

 

 

 

Re: Eval insert SQL

@ron_horne, try REGEXP_LIKE

Re: Eval insert SQL

Looking at some random sites on the web, it looks right. I'm not an SQL expert though.

Re: Eval insert SQL

thank you @Craige_Hales , this is very helpful.

the eval insert now works correctly. with respect to the other escape mechanism, it is much easier. pity this is not the way the software writes it.
I have another issue with SQL. i would like to get rows only where the value is made out of 16 alphanumeric characters.

the length statement by itself works fine. the regex doesn't

 

sqlstr_uid =
"\[
SELECT DISTINCT "t1".uid 
FROM "Tables"."Table"  "t1" 
WHERE ( (LENGTH ( "t1".uid) = 16) AND (  "t1".uid REGEXP '^[A-Za-z0-9]+$' ) )

]\";

am i missing a = or brackets?

thanks again!

 

Re: Eval insert SQL

userlist= {"01E1B5C0651EDA80", "F62B70921920FCBE", "909D35028E2039CE"};
sqlstr_ =
"SELECT  \!"t1\!".uid 
FROM \!"Tables\!".\!"Table\!"  \!"t1\!"
WHERE  ( \!"t1\!".uid IN ( '^
concatitems(userlist,\!"','\!")
^') )
;";

write(Eval Insert( sqlstr_ ));

The leading and trailing apostrophes are handled differently from the interior apostrophes; see just before and after the ^, vs the separator string. If there is any possibility userlist could have 0 items, you should check for that before running the query because it will probably not make any sense.

 

You can make it a little more readable using the other escape mechanism (html does not know about it so the syntax coloring is wrong on the web):

userlist= {"01E1B5C0651EDA80", "F62B70921920FCBE", "909D35028E2039CE"};
sqlstr_ =
"\[
SELECT  "t1".uid 
FROM "Tables"."Table"  "t1"
WHERE  ( "t1".uid IN ( '^
concatitems(userlist,"','")
^') );
]\";

write(Eval Insert( sqlstr_ ));

 

Using write() to display the result helps too:

SELECT  "t1".uid 
FROM "Tables"."Table"  "t1"
WHERE  ( "t1".uid IN ( '01E1B5C0651EDA80','F62B70921920FCBE','909D35028E2039CE') );