question

JonathanBrotto-3536 avatar image
0 Votes"
JonathanBrotto-3536 asked JonathanBrotto-3536 commented

Rewrite ERP paramater to accept one or more values.

My ERP uses parameters within a square bracket but in some cases I'll need to enter more than one and not to have multiple versions help.


 --Change the years to match the year
 --Will need excel formulas for blank columns see code for details
 SELECT  T0.SuppCatNum AS 'Part Number', 
 T0.[ItemCode], -- inverted the order of the first 2 columns to get the correct vlookup
 T0.[ItemName], T0.[OnHand],
 T0.[NumInBuy] AS 'Items per Purchasing Unit', 
 T0.[LastPurPrc] AS 'Purchase Price 2021', 
 '=IFNA(VLOOKUP(A2,"2022 Price List"!A:P,16,FALSE),"")' AS 'New Buying Price 2022', -- =IFNA(VLOOKUP(A2,'2022 Price List'!A:P,16,FALSE),"") second parameter in Vlookup should be name of price sheet with the column of the price
 T2.[Price] AS '01 Liste de Prix 2021', 
 '=IFERROR(ROUND(G2/E2,2),"")' AS 'New cost per UoM 2022', -- =IFERROR(ROUND(G2/E2,2),"") is New Buying Price 2022 / Items per Purchasing Unit
 '=IFERROR(I2-F2,"")' AS 'Difference per unit', -- =IFERROR(I2-F2,"") for first row to calculate difference
 '=IF(ISERROR(VLOOKUP(A2,"2022 Price List"!A:A,1,FALSE)),"Not on price list","")' AS 'If item is on price list' -- =IF(ISERROR(VLOOKUP(A2,'2022 Price List'!A:A,1,FALSE)),"Not on price list","")  second parameter in Vlookup should be name of price sheet
 FROM OITM T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode 
 INNER JOIN ITM1 T2 ON T0.ItemCode = T2.ItemCode 
 WHERE T2.[PriceList] ='1'
 and t0.cardcode ='[%0]'
 and t0.ValidFor='Y'
sql-server-generalsql-server-transact-sql
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

And your question is? Sorry, but we are entirely unfamiliar with your ERP system. Maybe you should ask in a forum for that product instead?

0 Votes 0 ·

Also I can't guess what your issue is.

0 Votes 0 ·

Hi @JonathanBrotto-3536

but in some cases I'll need to enter more than one and not to have multiple versions

Sorry I can't understand what you mean in this sentence ,couls you plesase explain the issue with more details?



0 Votes 0 ·

To run reports in the ERP we pass a value many times. In this query it is and t0.cardcode ='[%0]', but this works with only one value and I was wondering with SQL syntax if I could have multiple square brackets or something for those moments that I might have to run a report for 2 or more companies. Right now I am running the report twice or more when I need to make a consolidated report.

0 Votes 0 ·

1 Answer

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered JonathanBrotto-3536 commented

to run a report for 2 or more companies

For this you can use the IN operator, like

WHERE T2.[PriceList] ='1'
and t0.cardcode ('[%0]', '[%1]', '[%2]')

See https://docs.microsoft.com/en-us/sql/t-sql/language-elements/in-transact-sql?view=sql-server-ver15
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Would the in operator having a blank treat the value as empty string and not like a wildcard?

0 Votes 0 ·
OlafHelper-2800 avatar image OlafHelper-2800 JonathanBrotto-3536 ·

I don't understand your question?
You have an equal compare, wildcard characters are treaten as normal characters.

0 Votes 0 ·

Just want clarification how the in operator works as in my ERP I'll enter the code in the first parameter and the others if left blank will be consider something like '' which means no value.

0 Votes 0 ·

It works with or without parameters entered.

0 Votes 0 ·