Passing list of Invoice number from Excel - To SQL Query

[srinivas_pradeep]

Hi Team,

Hope you are well. I need one help.
Currently I am trying to extraction information from the invoice . Currently there are many columns are extracted from pdf / invoices and exported into excel. I want to pass “only the invoice number column” from the excel file to the sql query .
“SELECT T1.[SYS_ID] as System_id
,T1.[ACC_DOC_NUM] as Doc_Num
,T1.[DOC_DATE] as Date
,T1.[REF_DOC_NUM] as Invoice
,T2.[PO_DOC_FX_VALUE] as TOTAL
,T2.[VENDOR_NO] as Vendor_No
,T3.VENDOR_NAME_1 as Vendor_Name
FROM [CACM_SAP].[dbo].[SAP_TFR_BKPF] T1
INNER JOIN [CACM_SAP].[dbo].[SAP_TFR_BSEG] T2 ON T1.SYS_ID=T2.SYS_ID AND T1.[ACC_DOC_NUM]=T2.[ACC_DOC_NUM] AND T1.[FISC_YEAR]=T2.[FISC_YEAR]
LEFT JOIN [CACM_SAP].[dbo].[SAP_TFR_LFA1] T3 ON T1.SYS_ID = T2.SYS_ID AND T2.VENDOR_NO = T3.VENDOR_NO
WHERE T2.VENDOR_NO =‘xxxxxxxxxx’
AND T1. Invoice = (Invoice no from the output of the invoice_extraction flow)
how can i do that please?

Thanks,
Sri

[Anil_G (Anil Gorthi)]

Hi @srinivas_pradeep

To get a single string out of whole column you can so this

String.Join(",",dt.AsEnumerable.Select(function(x) x("InvoiceColumn").ToString))

this will join the values with a comma.

dt is the datatable where data is present

InvoiceColumn is the column name where you have invoices

cheers

[srinivas_pradeep]

Hi @Anil_G .

Thank you for the reply. Is it possible for you to help me with the workflow please?

Cheers,
Sri

[Anil_G (Anil Gorthi)]

@srinivas_pradeep

For this you only need a assign and use that string in your sql query

str is the string variable where the invoice number concatenated with comma are stored

cheers

[srinivas_pradeep]

Thank you @Anil_G ,

you mean something like this below please
“Select T1.[SYS_ID] As System_id
,T1.[ACC_DOC_NUM] As Doc_Num
,T1.[DOC_DATE] As Date
,T1.[REF_DOC_NUM] As Invoice
,T2.[PO_DOC_FX_VALUE] As TOTAL
,T2.[VENDOR_NO] As Vendor_No
,T3.VENDOR_NAME_1 As Vendor_Name
From [CACM_SAP].[dbo].[SAP_TFR_BKPF] T1
INNER Join [CACM_SAP].[dbo].[SAP_TFR_BSEG] T2 On T1.SYS_ID=T2.SYS_ID And T1.[ACC_DOC_NUM]=T2.[ACC_DOC_NUM] And T1.[FISC_YEAR]=T2.[FISC_YEAR]
LEFT Join [CACM_SAP].[dbo].[SAP_TFR_LFA1] T3 On T1.SYS_ID = T2.SYS_ID And T2.VENDOR_NO = T3.VENDOR_NO
Where T2.VENDOR_NO =‘0001009311’
AND T1. [REF_DOC_NUM] =‘str’”

Thanks
Sri

[Anil_G (Anil Gorthi)]

@srinivas_pradeep

Yes …I joined with comma(,) may be you need to change that to Single quote comma single quote(‘,’)

So that each value is surrounded with single quotes

and instead of equals I believe in sql you have to use IN and also “(‘str’)” brackets needs to be included

Check the syntax in sql and then use the same here

cheers

[srinivas_pradeep]

@Anil_G

Yes …I joined with comma(,) may be you need to change that to Single quote comma single quote(‘,’)

So that each value is surrounded with single quotes

Thanks Sunil. Sorry i didnt understand this. "Yes …I joined with comma(,) may be you need to change that to Single quote comma single quote(‘,’)

So that each value is surrounded with single quotes"
Cheers

[Anil_G (Anil Gorthi)]

Hi @srinivas_pradeep

To start with I am Anil

And coming to the question

so In the query if you want to search multiple values in a column we give as T1.[Columname] IN (‘A’,‘B’,‘C’) so if you join only by comma the values in str will be like A,B,C and what you wrote was “‘str’” (any way you have to join like this “'” + Str +“'”) which turns out to be ‘A,B,C’ not the expected one

So Join by (‘,’) which makes str as A’,‘B’,‘C and when you give "(’" + str + “')”(Even I gave it wrong in previous post with out plus) like this (‘A’,‘B’,‘C’)

So your final query would be

"Select T1.[SYS_ID] As System_id
,T1.[ACC_DOC_NUM] As Doc_Num
,T1.[DOC_DATE] As Date
,T1.[REF_DOC_NUM] As Invoice
,T2.[PO_DOC_FX_VALUE] As TOTAL
,T2.[VENDOR_NO] As Vendor_No
,T3.VENDOR_NAME_1 As Vendor_Name
From [CACM_SAP].[dbo].[SAP_TFR_BKPF] T1
INNER Join [CACM_SAP].[dbo].[SAP_TFR_BSEG] T2 On T1.SYS_ID=T2.SYS_ID And T1.[ACC_DOC_NUM]=T2.[ACC_DOC_NUM] And T1.[FISC_YEAR]=T2.[FISC_YEAR]
LEFT Join [CACM_SAP].[dbo].[SAP_TFR_LFA1] T3 On T1.SYS_ID = T2.SYS_ID And T2.VENDOR_NO = T3.VENDOR_NO
Where T2.VENDOR_NO =‘0001009311’
AND T1. [REF_DOC_NUM] =('" + str + "’)"

cheers

[srinivas_pradeep]

Apologies @Anil_G . Sorry for taking the name wrong.
Thank you so much for the kind reply and the help.
I will try to amend this and if stuck will get back to you on this please.

[srinivas_pradeep]

@Anil_G

"Select T1.[SYS_ID] As System_id
,T1.[ACC_DOC_NUM] As Doc_Num
,T1.[DOC_DATE] As Date
,T1.[REF_DOC_NUM] As Invoice
,T2.[PO_DOC_FX_VALUE] As TOTAL
,T2.[VENDOR_NO] As Vendor_No
,T3.VENDOR_NAME_1 As Vendor_Name
From [CACM_SAP].[dbo].[SAP_TFR_BKPF] T1
INNER Join [CACM_SAP].[dbo].[SAP_TFR_BSEG] T2 On T1.SYS_ID=T2.SYS_ID And T1.[ACC_DOC_NUM]=T2.[ACC_DOC_NUM] And T1.[FISC_YEAR]=T2.[FISC_YEAR]
LEFT Join [CACM_SAP].[dbo].[SAP_TFR_LFA1] T3 On T1.SYS_ID = T2.SYS_ID And T2.VENDOR_NO = T3.VENDOR_NO
Where T2.VENDOR_NO =‘0001009311’
AND T1. [REF_DOC_NUM] =('" + str + "’)"

Hi @Anil_G ,

Thanks for the help. When i am trying to use this query its giving me below error . can you please help what i am missing?

[Anil_G (Anil Gorthi)]

Hi @srinivas_pradeep

Is str declared as a string variable? And is the scope appropriate?

Cheers

[srinivas_pradeep]

@Anil_G

"Select T1.[SYS_ID] As System_id
,T1.[ACC_DOC_NUM] As Doc_Num
,T1.[DOC_DATE] As Date
,T1.[REF_DOC_NUM] As Invoice
,T2.[PO_DOC_FX_VALUE] As TOTAL
,T2.[VENDOR_NO] As Vendor_No
,T3.VENDOR_NAME_1 As Vendor_Name
From [CACM_SAP].[dbo].[SAP_TFR_BKPF] T1
INNER Join [CACM_SAP].[dbo].[SAP_TFR_BSEG] T2 On T1.SYS_ID=T2.SYS_ID And T1.[ACC_DOC_NUM]=T2.[ACC_DOC_NUM] And T1.[FISC_YEAR]=T2.[FISC_YEAR]
LEFT Join [CACM_SAP].[dbo].[SAP_TFR_LFA1] T3 On T1.SYS_ID = T2.SYS_ID And T2.VENDOR_NO = T3.VENDOR_NO
Where T2.VENDOR_NO =‘0001009311’
AND T1. [REF_DOC_NUM] =('" + str + "’)"

Hi @Anil_G .

Thank you for the kind reply and feedback. Sorry in the meantime i changed to strAccountID
Please find attached and let me know if anything is missing

[Anil_G (Anil Gorthi)]

@srinivas_pradeep

Can you show what error you are seeing and what value you gave …i mean the full value

Cheers

[srinivas_pradeep]

Hi @Anil_G ,

Please find attached the pic. These are the errors i am getting .

Thanks

[Anil_G (Anil Gorthi)]

@srinivas_pradeep

I believe you forgot to attache. Anyways… Please check the string that you gave properly. There is some issue there… because geneally when you give some string between double quotes in Advanced editor values turns little red unless something is wrong

If you have copy pasted the string please replace the inverted comma again in the code

cheers

[srinivas_pradeep]

[Anil_G (Anil Gorthi)]

@srinivas_pradeep

is the error visible to you in this picture? because i cannot see the error message other than the red exclamation symbol

[srinivas_pradeep]

[Anil_G (Anil Gorthi)]

@Anil_G

"Select T1.[SYS_ID] As System_id
,T1.[ACC_DOC_NUM] As Doc_Num
,T1.[DOC_DATE] As Date
,T1.[REF_DOC_NUM] As Invoice
,T2.[PO_DOC_FX_VALUE] As TOTAL
,T2.[VENDOR_NO] As Vendor_No
,T3.VENDOR_NAME_1 As Vendor_Name
From [CACM_SAP].[dbo].[SAP_TFR_BKPF] T1
INNER Join [CACM_SAP].[dbo].[SAP_TFR_BSEG] T2 On T1.SYS_ID=T2.SYS_ID And T1.[ACC_DOC_NUM]=T2.[ACC_DOC_NUM] And T1.[FISC_YEAR]=T2.[FISC_YEAR]
LEFT Join [CACM_SAP].[dbo].[SAP_TFR_LFA1] T3 On T1.SYS_ID = T2.SYS_ID And T2.VENDOR_NO = T3.VENDOR_NO
Where T2.VENDOR_NO =‘0001009311’
AND T1. [REF_DOC_NUM] =('" + str + "’)"

@srinivas_pradeep

I gave the same and i dont see any errors. if you have then it means you did something wrong and to see the error place the cursor in red exclamation and then the error would be visible

cheers

[srinivas_pradeep]

Hi @Anil_G ,

Thank you for the clarification and help. Very useful.
Please find attached the error