[steinhauer]

5 years, 6 months ago #17996

In the SQL connector, the where part is used to filter for an order number. The order number is determined dynamically and is stored in the global variables in a struct, string would also be possible.

select * from orders where orderId = globalVariable

How do I have to define the struct/string as GlobalVariable?

[Christian Kleinschroth]

5 years, 5 months ago #18499

Up

1

Down

::

Hello Steinhauer,

 

You can see the sql call from your testdialog in the logs & monitoring tile.

[Mathieu Roll]

5 years, 6 months ago #18004

Up

0

Down

::

Hello,

you have to Delfine a Parameter named params/myVar an than you can use it with Double points in the sql Statement.

select * from myTable where col1 = :myVar:

[steinhauer]

5 years, 6 months ago #18012

Up

0

Down

::

Hello Malfurion,

thank you for your reply.

But something is wrong here. My where-clause is longer

where …
and …
and ..
and pur.t_orno =: gs_auftrag:

When testing the connector, I get the following error message:

{
“message”: “Falsche Syntax in der Nähe von ‘:’.”,
“success”: false
}

I tried like instead of =, the result remains the same.
gs_auftrag is a global variable of type string, which is initialized with a default value.
In the application I need a structure, gs_projekt.
I test:

and pur.t_orno in( :gs_projekt:)

the result is unfortunately the same error message. With fixed Parameter

and pur.t_orno = ‘100023276’

the query works.

[Mathieu Roll]

5 years, 6 months ago #18213

Up

0

Down

::

Hello,

perhaps you have to change the mode.

I have created some screenshots with an example i’ve created.

The mode Execute is used for DELELE, UPDATE or INSERT and the mode query is used for selects

[steinhauer]

5 years, 6 months ago #18344

Up

0

Down

::

I created the input parameters and the execute mode as suggested,
now the error message is:

{
“message”: “The index 2 is out of range.”,
“success”: false
}

[digitalysator]

5 years, 6 months ago #18377

Up

0

Down

::

Hi steinhauer,

pay attention to your syntax. I see a space in your statement between the variable name and the colon. Maybe there was the error.

[steinhauer]

5 years, 6 months ago #18390

Up

0

Down

::

Hi Thomas Gärtner,

sorry, the white spaces are not the problem, I tried

and pur.t_orno=:auftrag:String:

and the message is

{
“message”: “The index 2 is out of range.”,
“success”: false
}

like before. My question is: What does the message mean? Where have I to look for the mistake? Is there any possibility for debugging?

and pur.t_orno = ‘100023276’

works fine.

[jonas.rausch]

5 years, 6 months ago #18391

Up

0

Down

::

Hi steinauer,

 

The error means that you want to access an element of an array or structure that does not exist.

Can you share the complete query string you are currently using?

[Kahn]

5 years, 6 months ago #18394

Up

0

Down

::

Could you also post a screenshot of your whole call. It might be an issue with your parameter definition.

The named parameters are translated into JDBC-Syntax internally and JDBC works with array positions and not names.

 

Cheers

[steinhauer]

5 years, 6 months ago #18395

Up

0

Down

::

First the query that works with fixed value:

select pur.t_otbp as Lieferant,
com.t_nama as Name,
pur.t_orno as Bestellung,
pur.t_pono as Position,
pur.t_sqnb as Folgenummer,
pur.t_item as Artikelnummer,
ibd.t_dsca as Artikelbeschreibung,
pur.t_qoor as Bestellmenge,
pur.t_qidl as ‘eingegangene Menge’,
pur.t_qibo as Nachlieferungsmenge,
pur.t_odat as Auftragsdatum,
pur.t_qiiv as ‘fakturierte Menge’,
pur.t_iamt as ‘Rechnungsbetrag’,
bes.t_ccur as Währung
from ttdpur401101 pur
inner join ttcibd001101 ibd on
pur.t_item = ibd.t_item
inner join ttdpur400101 bes on
bes.t_orno = pur.t_orno
inner join ttccom100101 com on
com.t_bpid = pur.t_otbp
where pur.t_item <> ‘ LE’
and pur.t_item <> ‘ HE’
and pur.t_item <> ‘ LLE’
and pur.t_orno = ‘100023276’
order by pur.t_otbp,pur.t_orno

 

and then the qoury that does not work, error see above:

select pur.t_otbp as Lieferant,
com.t_nama as Name,
pur.t_orno as Bestellung,
pur.t_pono as Position,
pur.t_sqnb as Folgenummer,
pur.t_item as Artikelnummer,
ibd.t_dsca as Artikelbeschreibung,
pur.t_qoor as Bestellmenge,
pur.t_qidl as ‘eingegangene Menge’,
pur.t_qibo as Nachlieferungsmenge,
pur.t_odat as Auftragsdatum,
pur.t_qiiv as ‘fakturierte Menge’,
pur.t_iamt as ‘Rechnungsbetrag’,
bes.t_ccur as Währung
from ttdpur401101 pur
inner join ttcibd001101 ibd on
pur.t_item = ibd.t_item
inner join ttdpur400101 bes on
bes.t_orno = pur.t_orno
inner join ttccom100101 com on
com.t_bpid = pur.t_otbp
where pur.t_item <> ‘ LE’
and pur.t_item <> ‘ HE’
and pur.t_item <> ‘ LLE’
and pur.t_orno=:auftrag:String:
order by pur.t_otbp,pur.t_orno

 

 

 

input parameter: see file input parameter.

output parameter: see file output parameter

query result: see file query result.

 

 

Attachments:

You must be logged in to view attached files.

[Kahn]

5 years, 6 months ago #18400

Up

0

Down

::

You should have posted those screenshots much more early.

 

Please provide your parameter auftrag  the following way params/auftrag  you have to provide each named parameter this way

• params/MYPARAM1
• params/MYPARAM2

If you need to be more dynamic just provide params and chose a Structure as the DataType this way you can provide your parameter arbitrary.

 

Cheers

 

Kahn

[steinhauer]

5 years, 6 months ago #18404

Up

0

Down

::

Is it possible, if I use a global variable, to simply display all parameters?
Do I really have to define all the parameters in the output individually?
I have another query with two parameters, where the output parameter “/” works correctly.

I have now entered the output parameters  “/params/Lieferant”, the error message has not changed.

[Kahn]

5 years, 6 months ago #18406

Up

0

Down

::

1. Yes by using / you can display all data in the OUTPUT-Parameters

2. You have to provide the parameters (params) in the INPUT-Parameters

[steinhauer]

5 years, 6 months ago #18407

Up

0

Down

::

The input-parameter is  now /params/auftrag

and the message is like before

{
“message”: “The index 2 is out of range.”,
“success”: false
}

Attachments:

You must be logged in to view attached files.