[steinhauer Has successfully completed the online course IntroductionHas successfully completed the online course Intermediate (200)]

5 years 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 Has successfully completed the online course IntroductionHas successfully completed the online course Intermediate (200)Has successfully completed the online course Advanced (300)Has successfully completed the online course Basics (100)Has successfully completed the online course Advanced (310)Has successfully completed the online course Advanced (320)Has successfully completed the Intermediate CertificationHas successfully completed the Advanced Certification]

5 years ago #18499

Up

1

Down

::

Hello Steinhauer,

 

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

[Mathieu Roll Has successfully completed the online course IntroductionHas successfully completed the online course Intermediate (200)Has successfully completed the online course Advanced (300)Has successfully completed the online course Basics (100)]

5 years 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 Has successfully completed the online course IntroductionHas successfully completed the online course Intermediate (200)]

5 years 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 Has successfully completed the online course IntroductionHas successfully completed the online course Intermediate (200)Has successfully completed the online course Advanced (300)Has successfully completed the online course Basics (100)]

5 years 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 Has successfully completed the online course IntroductionHas successfully completed the online course Intermediate (200)]

5 years 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 Has successfully completed the online course IntroductionHas successfully completed the online course Intermediate (200)Has successfully completed the online course Advanced (300)]

5 years 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 Has successfully completed the online course IntroductionHas successfully completed the online course Intermediate (200)]

5 years 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 Has successfully completed the online course IntroductionHas successfully completed the online course Intermediate (200)Has successfully completed the online course Advanced (300)]

5 years 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 Has successfully completed the online course IntroductionHas successfully completed the online course Intermediate (200)Has successfully completed the online course Advanced (300)]

5 years 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 Has successfully completed the online course IntroductionHas successfully completed the online course Intermediate (200)]

5 years 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 Has successfully completed the online course IntroductionHas successfully completed the online course Intermediate (200)Has successfully completed the online course Advanced (300)]

5 years 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 Has successfully completed the online course IntroductionHas successfully completed the online course Intermediate (200)]

5 years 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 Has successfully completed the online course IntroductionHas successfully completed the online course Intermediate (200)Has successfully completed the online course Advanced (300)]

5 years 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 Has successfully completed the online course IntroductionHas successfully completed the online course Intermediate (200)]

5 years 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.