​​Hi - I'm creating a map where the input is EDI and the output is SQL.  I am trying to query a DB2 database table on the input side of the map in order to get the cross-reference value of the N104 data. 

This works  (Where ABCD is the N104 value):
This does not work  (Where variable = N104 value):

------------------------------
Karen Gallello
------------------------------

#SupplyChain
#B2BIntegration

Good morning Karen,

I'm not sure to understand the issue, so pardon me in advance if I seem to be asking dumb questions , or if I sound too condescending.

Could you share the syntax of the query that fails?
Cold you confirm you are using the TX variable syntax, using # signs around the variable name?
​

------------------------------
Laurent Barthelemy
Technical director
Satisco
mont saint guibert
+33 616792115
------------------------------

Original Message:
Sent: Mon February 10, 2020 04:14 PM
From: Karen Gallello
Subject: User Exit to Query External Database

​​Hi - I'm creating a map where the input is EDI and the output is SQL.  I am trying to query a DB2 database table on the input side of the map in order to get the cross-reference value of the N104 data. 

This works  (Where ABCD is the N104 value):

Select Column5 from Table where Column1 = 'EDI' and Column2 = "ABCD"

This does not work  (Where variable = N104 value):

Select Column5 from Table where Column1 = 'EDI' and Column2 = variable

Does anyone know how to accomplish what I'm trying to do?

Any assistance would be greatly appreciated!

Thank you - Karen 

------------------------------
Karen Gallello
------------------------------

#B2BIntegration
#SupplyChain

Hi Karen,

It seems that you are trying to query DB from the input side (EDI). You might not be query an External DB table from a regular layout i.e. EDI, XML and CSV, but you can refer or query Sterling internal cross-reference table i.e. Codelist. You can do this only SQL layout which is your output side.

It would been more helpful if you could have shared map.

Thanks,
Raja

------------------------------
Rajasekhar Muthamsetty
------------------------------

Original Message:
Sent: Mon February 10, 2020 04:14 PM
From: Karen Gallello
Subject: User Exit to Query External Database

​​Hi - I'm creating a map where the input is EDI and the output is SQL.  I am trying to query a DB2 database table on the input side of the map in order to get the cross-reference value of the N104 data. 

This works  (Where ABCD is the N104 value):

Select Column5 from Table where Column1 = 'EDI' and Column2 = "ABCD"

This does not work  (Where variable = N104 value):

Select Column5 from Table where Column1 = 'EDI' and Column2 = variable

Does anyone know how to accomplish what I'm trying to do?

Any assistance would be greatly appreciated!

Thank you - Karen 

------------------------------
Karen Gallello
------------------------------

#B2BIntegration
#SupplyChain

Hello Karen,

You need to build statement with "Quotes" around the variable. e.g. 

If Value of variable = "ABCDE"
then your query will look like
Select Column5 from Table where Column1 = 'EDI' and Column2 = ABCDE
However, it should look as
Select Column5 from Table where Column1 = 'EDI' and Column2 = "ABCDE"  <-Quotes

So you need to concat strings to build query
strQuery = "Select Column5 from Table where Column1 = 'EDI' and Column2 = '" + variable + "'"  
and then use strQuery.

_Dhiraj Patil

------------------------------
Dhiraj Patil
------------------------------

Original Message:
Sent: Mon February 10, 2020 04:14 PM
From: Karen Gallello
Subject: User Exit to Query External Database

​​Hi - I'm creating a map where the input is EDI and the output is SQL.  I am trying to query a DB2 database table on the input side of the map in order to get the cross-reference value of the N104 data. 

This works  (Where ABCD is the N104 value):

Select Column5 from Table where Column1 = 'EDI' and Column2 = "ABCD"

This does not work  (Where variable = N104 value):

Select Column5 from Table where Column1 = 'EDI' and Column2 = variable

Does anyone know how to accomplish what I'm trying to do?

Any assistance would be greatly appreciated!

Thank you - Karen 

------------------------------
Karen Gallello
------------------------------

#SupplyChain
#B2BIntegration

Good day Karen,

We were able to successfully implement what you are trying to do.

• We wrote a java class that has methods manages the database connection and executes sql queries.

• This class will be installed as a 3rd party jar on SI.
• In the map we declared Object variable that will invoke the different methods that are needed from database.

Let me know if you need more details.

Regards

------------------------------
Clayton Snyman
------------------------------

Original Message:
Sent: Mon February 10, 2020 04:14 PM
From: Karen Gallello
Subject: User Exit to Query External Database

​​Hi - I'm creating a map where the input is EDI and the output is SQL.  I am trying to query a DB2 database table on the input side of the map in order to get the cross-reference value of the N104 data. 

This works  (Where ABCD is the N104 value):

Select Column5 from Table where Column1 = 'EDI' and Column2 = "ABCD"

This does not work  (Where variable = N104 value):

Select Column5 from Table where Column1 = 'EDI' and Column2 = variable

Does anyone know how to accomplish what I'm trying to do?

Any assistance would be greatly appreciated!

Thank you - Karen 

------------------------------
Karen Gallello
------------------------------

#SupplyChain
#B2BIntegration

​Thank you everyone for your assistance.  I was able to resolve this issue by concatenating the string variable to the query statement.


​

------------------------------
Karen Gallello
------------------------------

Original Message:
Sent: Thu February 13, 2020 02:42 AM
From: Clayton Snyman
Subject: User Exit to Query External Database

Good day Karen,

We were able to successfully implement what you are trying to do.

• We wrote a java class that has methods manages the database connection and executes sql queries.

• This class will be installed as a 3rd party jar on SI.
• In the map we declared Object variable that will invoke the different methods that are needed from database.

Let me know if you need more details.

Regards

------------------------------
Clayton Snyman

Original Message:
Sent: Mon February 10, 2020 04:14 PM
From: Karen Gallello
Subject: User Exit to Query External Database

​​Hi - I'm creating a map where the input is EDI and the output is SQL.  I am trying to query a DB2 database table on the input side of the map in order to get the cross-reference value of the N104 data. 

This works  (Where ABCD is the N104 value):

Select Column5 from Table where Column1 = 'EDI' and Column2 = "ABCD"

This does not work  (Where variable = N104 value):

Select Column5 from Table where Column1 = 'EDI' and Column2 = variable

Does anyone know how to accomplish what I'm trying to do?

Any assistance would be greatly appreciated!

Thank you - Karen 

------------------------------
Karen Gallello
------------------------------

#SupplyChain
#B2BIntegration

Hi Clayton,

Can you please guide me the step by step process. Also how you declared them in the map.

Because in an X12 to X12 map where i want to do some query from external data base by which depending on the claim id of the file and retrieve the values associated to claim id of the file from DB and write them to Target filed. As we process huge claims I don't want to write the lookup or that db statement to Primary document and use doc to Dom to convert to xml type and read the data when i do that it is taking almost 5 hrs to process 10000 claims. But I need these claims to be processed in 10min. Can you please guide me with all the steps. I can share you also what i did and where it is going wrong as my Extended rule is giving me java null pointer error.

------------------------------
Lakshman Kumar
------------------------------

Original Message:
Sent: Thu February 13, 2020 02:42 AM
From: Clayton Snyman
Subject: User Exit to Query External Database

Good day Karen,

We were able to successfully implement what you are trying to do.

• We wrote a java class that has methods manages the database connection and executes sql queries.

• This class will be installed as a 3rd party jar on SI.
• In the map we declared Object variable that will invoke the different methods that are needed from database.

Let me know if you need more details.

Regards

------------------------------
Clayton Snyman

Original Message:
Sent: Mon February 10, 2020 04:14 PM
From: Karen Gallello
Subject: User Exit to Query External Database

​​Hi - I'm creating a map where the input is EDI and the output is SQL.  I am trying to query a DB2 database table on the input side of the map in order to get the cross-reference value of the N104 data. 

This works  (Where ABCD is the N104 value):

Select Column5 from Table where Column1 = 'EDI' and Column2 = "ABCD"

This does not work  (Where variable = N104 value):

Select Column5 from Table where Column1 = 'EDI' and Column2 = variable

Does anyone know how to accomplish what I'm trying to do?

Any assistance would be greatly appreciated!

Thank you - Karen 

------------------------------
Karen Gallello
------------------------------

#SupplyChain
#B2BIntegration