Dynamic clause where

Dear Sirs,

I'm using NetExpress 3.11 by going to Firebird.

I would like to know if there is a possibility of using a variable in the where clause.

Regards,
Rogerio Barbosa

You can use a host variable in the WHERE clause as a value such as:

EXEC SQL

       SELECT last_name

          INTO :lname

          FROM staff

          WHERE staff_id=:staff-id

END-EXEC

If you wish to substitute the entire WHERE clause with a variable including the column name then you will have to use dynamic SQL when you prepare a statement and then execute it.

Please see docs here:

Dear Sirs,

I'm using NetExpress 3.11 by going to Firebird.

I would like to know if there is a possibility of using a variable in the where clause.

Regards,
Rogerio Barbosa

Prezado Chris,

Tenho o comando seguinte em meu programa:

exec sql
Select produto_nome,
produto_numero,
id_secao,
id_grupo,
id_subgrupo
into :wsProdutoNome,
:wsProdutoNumero,
:wsIdSecao,
:wsIdGrupo,
:wsIdSubgrupo
from produtos "
where produto_numero = :ProdutoNumero
end-exec

Fiz assim para criar um dynamic sql:

string "select produto_nome, "
"produto_numero, "
"id_secao, "
"id_grupo, "
"id_subgrupo "
"into :wsProdutoNome, "
":wsProdutoNumero, "
":wsIdSecao, "
":wsIdGrupo, "
":wsIdSubgrupo "
"from produtos "
"where produto_numero = :ProdutoNumero"
delimited by size
into dynamic_sql
end-string

EXEC SQL
EXECUTE IMMEDIATE :dynamic_sql
END-EXEC

Da o seguinte erro:
Dynamic SQL error. Error Code = -104
Token unknown - Line 1 , column

Dear Sirs,

I'm using NetExpress 3.11 by going to Firebird.

I would like to know if there is a possibility of using a variable in the where clause.

Regards,
Rogerio Barbosa

You cannot use host variables directly within dynamic SQL statements. You would use the "?" character as a parameter marker and then supply the actual host variable for substitution using the EXECUTE USING statement.

Take a look at the Net Express Demo program in the project Dynamic.app located:
C:\\Program Files (x86)\\Micro Focus\\Net Express 5.1\\Examples\\Net Express IDE\\ODBCESQL

Thanks.

Dear Sirs,

I'm using NetExpress 3.11 by going to Firebird.

I would like to know if there is a possibility of using a variable in the where clause.

Regards,
Rogerio Barbosa

Alterei conforme exemplo:

string "select produto_nome, "
"produto_numero, "
"id_secao, "
"id_grupo, "
"id_subgrupo "
"into (?, ?, ?, ?, ?) "
"from produtos "
"where produto_numero = ?"
delimited size
into sql-stat
end-string

exec sql

prepare dynamic_sql from :sql-stat

end-exec

Quando executo o programa recebo um erro = -104

Dear Sirs,

I'm using NetExpress 3.11 by going to Firebird.

I would like to know if there is a possibility of using a variable in the where clause.

Regards,
Rogerio Barbosa

If you are returning data using a SELECT then the dynamic statement needs to be executed as a cursor instead of a singleton select.

This is documented here:

 Example:

     exec sql
        DECLARE C1 CURSOR FOR dynamic_sql
     end-exec
	   
     string "SELECT "
        "A.CustomerID, A.CompanyName, A.ContactName "
        "FROM dbo.Customers A WHERE (A.CustomerID = ?)"
	   into sql-text
     exec sql prepare dynamic_sql from :sql-text end-exec
     move "BLAUS" to Customers-CustomerID
     exec sql
        OPEN C1 USING :Customers-CustomerID
     end-exec
     exec sql fetch C1 into
         :Customers-CustomerID
        ,:Customers-CompanyName
        ,:Customers-ContactName:Customers-ContactName-NULL
     end-exec

Dear Sirs,

I'm using NetExpress 3.11 by going to Firebird.

I would like to know if there is a possibility of using a variable in the where clause.

Regards,
Rogerio Barbosa

thank you chris,

I'll try