SQL BUILDER - How can I include a context variable in the WHERE clause
Hi: I want to use in an sql query a value prompted in the context. (I'm using an INFORMIX database). I've created the variable called "myvariable" . When I run the job, it prompts for a value, just as I want. When I want to include it in my query, I type Ctrl+SpaceBar and I can select my variable from a list (it was called "context.myvariable". The query appears so: "SELECT * FROM mytable WHERE column1 = context.myvariable" The system rejects my code saying "COLUMN context IS NOT FOUND IN ANY TABLE IN THE QUERY". Please help me to mend my syntax. Thank you very much in advance. Juan Carlos.
Hi, I have read this thread (and some others), but unfortunately are still not able to use a global variable within the SQL Builder. I use Perl and PostgreSQL, and have the following query I would like to execute: SELECT * FROM adm.job_instance WHERE job_id=.$_context{domain}
domain is a contextvariabel with the same type as job_id (being integer). I have tried all the possible options as mentioned above, but simply can't get it to work. Help would be appreciated. Kind regards, Michel
Hi Timson,
It works, thanks!
It looks like I was under the assumtion I did something wrong since I am not able to enter the SQL Builder when a context variable is part of my SQL statement. WHen there is a context variable in my SQL statement, and I try to enter the SQL builder I get the following message: Query Statement Error!. The details of the error are:
This query can not contain context parameters (like $_context) ,
or this query has not been enclosed by '.
or has some expressions. Please correct.
This made me think something was wrong.
Anyhow, many thanks.
Kind regards,
Michel
Hello:
If I write this sentence in the window "SQL Builder", I find an error.
I have defined Factura like context.
SELECT "public"."c_bpartner"."c_bpartner_id" FROM "public"."c_bpartner"
where c_bpartner_id in (+context.Factura)
group by "c_bpartner_id"
"ERROR: Falta una tabla para la entrada para la tabla context en la clausula FROM"
It seems that the program tray to reead context like a table. How to include in the sentence the content of the variable Factura?
Thanks
databases typically treat anything not in single quotes to be object names, such as column names, table names, etc. depending on where in the query you are. double quotes are also treated as object names. a recurring issue (potentially) in all of the examples above are that quotes are not being used in the proper place. when referring to column names being generated from a context variable, the result should always be placed in quotes, unless it is a numeric value of course. not using a quote in your example makes the database think it is expecting the name of a table in the brackets, if u place this in single quotes instead, the database will then expect a value instead. i think this makes sense?
Hello
Thanks for the answer. Problem was in the quote, certainly.
Another question. How can assign directly a group of values to a variable of context?
For example, I need sendto context some values of a camp from DB, in order to iterate after with this.
Thanks
Ok I have read this full thread several time but same as the others there are so many variation that I am completely lost. Here is my query (which does not work) "select * WHERE full_path like '" + ((String)globalMap.get("tFileList_2_CURRENT_FILEPATH")) + "'" Which does not work because of the " in the get function. So I thought to escape the " like this "select * WHERE full_path like '" + ((String)globalMap.get(\"tFileList_2_CURRENT_FILEPATH\")) + "'" also a fail so how about this "select * WHERE full_path like \"" + ((String)globalMap.get(\"tFileList_2_CURRENT_FILEPATH\")) + "\"" fail "select * WHERE full_path like \"" + ((String)globalMap.get("tFileList_2_CURRENT_FILEPATH")) + "\"" fail From the thread is appears clear that the issue is with the quote but which one..... This is driving me mad, any suggestions
