Is the IF function available in SemQL for SQL Server? I now the CASE statement is and that is what I've used in the past. I'm having to replicate a complicated If statement from excel into semarchy. Using an If statement would simplify the syntax over a CASE statement. The CASE statement isn't documented as a function so I was wondering if the IF statement was also available but not documented. If so, what is the syntax?
Thanks,
Matt
Best Answer
S
Subham Dixit
said
5 months ago
Hello Matthew,
IF statement is not present but you can use CASE WHEN instead of IF which helps you to achieve your use case, also CASE function is present in the documentation.
For sample-
CASE
WHEN condition_1 THEN result_1
...
WHEN condition_n THEN result_n
[ELSE default_result]
END
IF statement is not present but you can use CASE WHEN instead of IF which helps you to achieve your use case, also CASE function is present in the documentation.
For sample-
CASE
WHEN condition_1 THEN result_1
...
WHEN condition_n THEN result_n
[ELSE default_result]
END
M
Matthew Ammons
said
5 months ago
My use case is that we are replacing a Google Sheet that is used to request a material creation. One of the fields in the sheet is "Status". The formula on that cell is pretty complex and I need to replicate it in Semarchy. Below is the example formula. A CASE statement would be extremely complex. Would this be a good example to use a SQL HOOK?
here would be the SQL Server compatible formula. I don't feel it is that much complex but hopefully this works anyways.
CASE
WHEN
L2 <> '' OR M2 <> '' OR N2 <> '' OR O2 <> '' OR P2 <> '' OR Q2 <> '' OR
R2 <> '' OR S2 <> '' OR T2 <> '' OR U2 <> '' OR V2 <> '' OR W2 <> '' OR
X2 <> '' OR Y2 <> '' OR Z2 <> ''
THEN
CASE
WHEN
(
(M2 IN ('Label', 'Shrink Band', 'Other Material', 'Sticker') AND
L2 <> '' AND O2 <> '' AND P2 <> '' AND Q2 <> '' AND R2 <> '' AND S2 <> '')
OR
(M2 IN ('FG SKU', 'WIP SKU') AND
L2 <> '' AND O2 <> '' AND P2 <> '' AND Q2 <> '' AND R2 <> '' AND S2 <> '' AND W2 <> '' AND X2 <> '')
)
THEN
CASE
WHEN S2 = 'New' THEN 'Complete'
WHEN T2 <> '' THEN 'Complete'
ELSE 'Incomplete'
END
ELSE 'Incomplete'
END
ELSE ''
END
Matthew Ammons
Is the IF function available in SemQL for SQL Server? I now the CASE statement is and that is what I've used in the past. I'm having to replicate a complicated If statement from excel into semarchy. Using an If statement would simplify the syntax over a CASE statement. The CASE statement isn't documented as a function so I was wondering if the IF statement was also available but not documented. If so, what is the syntax?
Thanks,
Matt
Hello Matthew,
IF statement is not present but you can use CASE WHEN instead of IF which helps you to achieve your use case, also CASE function is present in the documentation.
For sample-
- Oldest First
- Popular
- Newest First
Sorted by Oldest FirstSubham Dixit
Hello Matthew,
IF statement is not present but you can use CASE WHEN instead of IF which helps you to achieve your use case, also CASE function is present in the documentation.
For sample-
Matthew Ammons
My use case is that we are replacing a Google Sheet that is used to request a material creation. One of the fields in the sheet is "Status". The formula on that cell is pretty complex and I need to replicate it in Semarchy. Below is the example formula. A CASE statement would be extremely complex. Would this be a good example to use a SQL HOOK?
=IF(OR(L2<>"",M2<>"",N2<>"",O2<>"",P2<>"",Q2<>"",R2<>"",S2<>"",T2<>"",U2<>"",V2<>"",W2<>"",X2<>"",Y2<>"",Z2<>""),IF(OR(AND(OR(M2="Label",M2="Shrink Band",M2="Other Material",M2="Sticker"),L2<>"",O2<>"",P2<>"",Q2<>"",R2<>"",S2<>""),AND(OR(M2="FG SKU",M2="WIP SKU"),L2<>"",O2<>"",P2<>"",Q2<>"",R2<>"",S2<>"",W2<>"",X2<>"")),IF(S2="New","Complete",IF(T2<>"","Complete","Incomplete")),"Incomplete"),"")
P
Stéphanie FOURRIER
hello Matthew,
here would be the SQL Server compatible formula. I don't feel it is that much complex but hopefully this works anyways.
Stéphanie.
-
Extend a model with new entities or attributes
-
Data types in xDM
-
Effective date on entities
-
Search using wild cards
-
Export a model from production and import on a development environment
-
"Allow Delete" vs "Allow Removal" privileges
-
LOV label in Named Query
-
Select location on a map and save coordinates
-
Is there a way to set up a master-detail relationship on browse mode?
-
Choose Either a Stepper or A Workflow Based on The User Privileges
See all 297 topics