How to use multiple Values in Universal Function SQL query

communitysap — Thu, 02 Jan 2020 17:18:33 GMT

Hi there this is my first post here please excuse any incorrect formatting or missing info

We have SAP Business One 9.3 (9.30.180) based on a SQL database. We have Boyum and Taskcentre installed

I have the case for user query where I would like to display a list of GL Codes to a user, which would be selected by a SQL statement, and then I would like to pass the multiple values selected by the user to SQL query

In pure SQL terms I am looking to replace the "IN" statement of the following statement with a variable. I can achieve this in SSRS

eg SQL statement

Declare @DateFrom DateTime = '2018-01-01 00:00:00'

Declare @DateTo DateTime = '2018-12-31 00:00:00'
SELECT 
T0.[DocNum], convert (varchar , T0.[DocDate],103)  PostingDate, convert (varchar, T0.DocDueDate ,3) PaymentDueDate,Case when T0.DocStatus='C' Then 'Closed' else T0.DocStatus end Status,T0.[CardCode],T0.[CardName],T0.NumAtCard SupplierInvNo,T0.[TransId],T1.[ItemCode],I.ItemName,isnull(T1.[Freetxt],'') AddText, T1.[Quantity], T1.[Price], T1.[LineTotal], T1.[AcctCode], GL.AcctName,isnull(T1.[OcrCode],'') CostCentre,isnull(T1.[OcrCode2],'') ProjectCode


FROM OPCH T0  with (nolock) INNER JOIN PCH1 T1  with (nolock)  ON T0.[DocEntry] = T1.[DocEntry] 

INNER JOIN OFPR T2  with (nolock)  ON T0.[FinncPriod] = T2.[AbsEntry]left outer join OITM I on T1.ItemCode=I.ItemCode

inner join OACT GL with (nolock) on T1.AcctCode=GL.AcctCode

WHERE T1.[AcctCode] in ('21000','23000','53000','53010','53020','53110','53120','53250','54080','54140','54160','54200','54210','54240','54450','54460','77300','77900')

and (T0.DocDate>=@DateFrom and T0.DocDate <=@DateTo)

ORDER BY T0.DocDate

Re: How to use multiple Values in Universal Function SQL query

pierrez — Wed, 08 Dec 2021 14:43:21 GMT

Hey Ian

Did you manage to figure this out? I have a similar requirement.

Pierre

topic Re: How to use multiple Values in Universal Function SQL query in Application Development and Automation Discussions

How to use multiple Values in Universal Function SQL query

Re: How to use multiple Values in Universal Function SQL query