mercredi 11 mars 2020

SQL nested IIF function

I try to make a complicated formula in SQL with nested IIF function. There are many IFF's in this formula. But somehow Microsoft query will not take the statement: enter image description here

SELECT

IIF (system_Machine.Machine_omschrijving IN ('BE'),
IIF (PD_FleeceRecipe.FleeceRecipe_IntermediateLayer1Width is NULL,
IIF (PD_FleeceRecipe.FleeceRecipe_BottomLayerWidth is NULL, PD_FleeceRecipe.FleeceRecipe_IntermediateLayer2Width, PD_FleeceRecipe.FleeceRecipe_BottomLayerWidth)
,
IIF(PD_FleeceRecipe.FleeceRecipe_BottomLayerWidth is NULL,
IIF(PD_FleeceRecipe.FleeceRecipe_IntermediateLayer1Width is NULL, PD_FleeceRecipe.FleeceRecipe_BottomLayerWidth, PD_FleeceRecipe.FleeceRecipe_IntermediateLayer1Width)
,
PD_FleeceRecipe.FleeceRecipe_BottomLayerWidth-PD_FleeceRecipe.FleeceRecipe_IntermediateLayer2Width+PD_FleeceRecipe.FleeceRecipe_IntermediateLayer1Width-PD_FleeceRecipe.FleeceRecipe_IntermediateLayer2Width)
)))
[res] ,

PD_Packaging.Packaging_Itemnr,  
PD_Packaging.Packaging_Description,  
PD_Packaging.Packaging_Width,   
PD_Packaging.Packaging_Weight,   
PD_Packaging.Packaging_Weightgm2,   
PD_Packaging.Packaging_Overlap,   
PD_Packaging.Packaging_Verstrekking,   
PD_Packaging.Packaging_CategoryName,    
PD_Main.Main_StatusID,  
PD_Main.Main_Itemnr,
system_Machine.Machine_omschrijving,   
PD_Main.Main_Product,   
PD_Main.Main_WidthTobeInvoiced,  
PD_Main.Main_AssemblingRollDiameter,  
PD_Main.Main_AssemblingRollLength,  
PD_Main.Main_LabelArea,    
PD_FleeceRecipe.FleeceRecipe_IntermediateLayer1Width,  
PD_FleeceRecipe.FleeceRecipe_IntermediateLayer2Width,  
PD_FleeceRecipe.FleeceRecipe_BottomLayerWidth

FROM testsystemOBB.dbo.PD_Packaging PD_Packaging    
LEFT OUTER JOIN testsystemOBB.dbo.PD_Main
ON PD_Packaging.Packaging_ID = PD_Main.Main_AssemblingPackagingSingleRollID AND PD_Main.Main_StatusID = 2

LEFT OUTER JOIN testsystemOBB.dbo.PD_FleeceRecipe  
ON PD_Main.Main_ID = PD_FleeceRecipe.FleeceRecipe_MainID AND FleeceRecipe_Preferred = 1    

LEFT OUTER JOIN testsystemOBB.dbo.BOM_Results  
ON PD_Main.Main_Itemnr = BOM_Results.Item_Number

LEFT OUTER JOIN testsystemOBB.dbo.system_Machine  
ON BOM_Results.SelMachineID = system_Machine.Machine_id

WHERE (PD_Packaging.Packaging_CategoryName='STRETCH')  AND (PD_Main.Main_Itemnr = 406181)

I also tried a small example with another ISNULL function, but the outcome is "Unexpected RES behind the select columnlist. I do not understand what is my mistake.

SELECT

IIF (system_Machine.Machine_omschrijving IN ('BE'),
IIF (ISNULL(FleeceRecipe_IntermediateLayer1Width), 3,4))

[res] ,

PD_Packaging.Packaging_Itemnr,  
PD_Packaging.Packaging_Description,  
PD_Packaging.Packaging_Width,   
PD_Packaging.Packaging_Weight,   
PD_Packaging.Packaging_Weightgm2,   
PD_Packaging.Packaging_Overlap,   
PD_Packaging.Packaging_Verstrekking,   
PD_Packaging.Packaging_CategoryName,    
PD_Main.Main_StatusID,  
PD_Main.Main_Itemnr,
system_Machine.Machine_omschrijving,   
PD_Main.Main_Product,   
PD_Main.Main_WidthTobeInvoiced,  
PD_Main.Main_AssemblingRollDiameter,  
PD_Main.Main_AssemblingRollLength,  
PD_Main.Main_LabelArea,    
PD_FleeceRecipe.FleeceRecipe_IntermediateLayer1Width,  
PD_FleeceRecipe.FleeceRecipe_IntermediateLayer2Width,  
PD_FleeceRecipe.FleeceRecipe_BottomLayerWidth

FROM testsystemOBB.dbo.PD_Packaging PD_Packaging    
LEFT OUTER JOIN testsystemOBB.dbo.PD_Main
ON PD_Packaging.Packaging_ID = PD_Main.Main_AssemblingPackagingSingleRollID AND PD_Main.Main_StatusID = 2

LEFT OUTER JOIN testsystemOBB.dbo.PD_FleeceRecipe  
ON PD_Main.Main_ID = PD_FleeceRecipe.FleeceRecipe_MainID AND FleeceRecipe_Preferred = 1    

LEFT OUTER JOIN testsystemOBB.dbo.BOM_Results  
ON PD_Main.Main_Itemnr = BOM_Results.Item_Number

LEFT OUTER JOIN testsystemOBB.dbo.system_Machine  
ON BOM_Results.SelMachineID = system_Machine.Machine_id

WHERE (PD_Packaging.Packaging_CategoryName='STRETCH')  AND (PD_Main.Main_Itemnr = 406181)

enter image description here

Aucun commentaire:

Enregistrer un commentaire