UOM parameter

1

Hi,

I would like to have my parameter 'uom' parameter connected to the query. When I choose the parameter 'LM' I would like to see the data on the report in LM if i choose m2 i would like to see data in M2. My statement is: (JUST PART OF IT)

select
T1.BUSINESS ,
T1.ITEM_TYPE ,
T1.ITEM_CATEGORY ,
2.UOM_STOCK ,
ssi_davl_func('PNCLASS',T1.ITEM_CATEGORY,'E') ,
T1.ITEM_CODE ,

DECODE(T1.QTY_DTS,0,0 ,ssi_units_conversion(T1.BUSINESS,T1.ITEM_CODE,T1.QTY_DTS,T2.UOM_STOCK,KG,6)) QTY_DTS ,

DECODE(T1.QTY_REJ,0,0 ,ssi_units_conversion(T1.BUSINESS,T1.ITEM_CODE,T1.QTY_REJ,T2.UOM_STOCK,'KG',6)) QTY_REJ,

I don;t want 'kg' to be hard coded or create single columns for different UOM. Is there any way I can add parameter to this statement. I will aprreaciate any help,

Renata

2

I presume you want to filter by uom. Filters are done with the WHERE clause.

If you want a better answer then:

  1. Provide DDL. (CREATE TABLE TestTable(s) etc)
  2. Provide consumable test data. (INSERT INTO TestTable(s)...)
  3. Show what you have tried and why it does not work with the test data.
  4. Show the result you want from the test data.

Also, scalar functions are potentialy very slow so you had better post the code for them as well.

ps This is a SQL Server site. DECODE is an Oracle function.

3

Thanks for your time and your respond. I don't want to filter by UOM but I can try to explain again. This is my sql code:
select
T1.BUSINESS ,
T1.ITEM_TYPE ,
T1.ITEM_CATEGORY ,
2.UOM_STOCK ,
ssi_davl_func('PNCLASS',T1.ITEM_CATEGORY,'E') ,
T1.ITEM_CODE ,

ssi_units_conversion(T1.BUSINESS,T1.ITEM_CODE,T1.QTY_DTS,T2.UOM_STOCK,KG,6)) QTY_DTS ,
ssi_units_conversion(T1.BUSINESS,T1.ITEM_CODE,T1.QTY_REJ,T2.UOM_STOCK,'KG',6)) QTY_REJ,

ssi_units_conversion(T1.BUSINESS,T1.ITEM_CODE,T1.QTY_DTS,T2.UOM_STOCK,'KG',6))

,ssi_units_conversion(T1.BUSINESS,T1.ITEM_CODE,T1.QTY_REJ,T2.UOM_STOCK,'KG',6)) TOTAL_PRODUCTION ,

T1.FIXED_OH ,
T1.GEN_OH1 ,
T1.GEN_OH2 ,
T1.LABOUR ,
T1.LABOUR_OH ,
T1.MATERIAL_COST ,
T1.VARIABLE_OH ,
T1.COST_SET,

T1.COST_DATE ,
T1.PRODUCTION_COST

from LIVEDB.MBC010 T2, LIVEDB.ODL_VALUED_PRODUCTION T1

where T2.ACCOUNT15_INV=T1.BUSINESS and T2.PARTNO_INV=T1.ITEM_CODE and T2.WAREHOUSE=' ' and T2.LOCSTOR_INV=' ' and T2.LOCBIN_INV=' ' and T1.BUSINESS='1 1 5'
That's how my report looks like.

image
image1500×716 32.5 KB

When I write in parameters different UOM I would like my data is changed in the table as well. I have a conversion done for kg in sql code but just wonder if that could be connected with my parameter.

i mean in this code instead of 'kg' to write something which would make a connection to my parameter.
I am sorry for my explanation, I hope there is a chance to understand what I mean.

Thanks

4

Yes, that is possible. In this video on youtube it's explained:

I have to know how you named your parameter but I guess it will be something like this:

SELECT ...
ssi_units_conversion(T1.BUSINESS,T1.ITEM_CODE,T1.QTY_DTS,T2.UOM_STOCK,@UOM,6)) QTY_DTS

5

I would suggest you change your parameter so the user can only use valid values for UOM.