The expression in the calculated field needs to evaluate to either the type specified for the field, or null.
You can have nested expressions like
(CASE WHEN ProdGroup.Description LIKE 'TULIP%' THEN
(CASE
WHEN ProdGroup.ProdCode IN (`1234`, '1235', '1239') THEN 'TULIP - PG 12xx'
WHEN ProdGroup.ProdCode IN (`6634`, '6635', '6639') THEN 'TULIP - PG 66xx'
ELSE 'TULIP - PG ??'
END)
WHEN ProdGroup.Description LIKE 'ACME%' THEN 'ACME - PG'
ELSE 'unsupported - PG'
END)
You can’t do sequential expressions. It has to be exactly one expression.
Use LIKE when you need a wild card. And the % is the wildcard character.
Using LIKE 'WAR' will only match that exactly. Using 'WAR%' will match WAR, WARR, WARBLE,etc.
Only one of the WHEN’s in the first level CASE will happen. If the Decription is blank it would never get to test JobNum. Maybe do the tests for JobNum first.
(CASE
WHEN SubQuery15.ProdGrup_Description LIKE ‘TULIP%’ THEN
(CASE
WHEN SubQuery15.PartClass_Description LIKE ‘Berti%’ THEN ‘Berti Stock’
WHEN o.Calculated_Job LIKE ‘RD%’ THEN ‘R&D’
WHEN o.Calculated_Job LIKE ‘WAR%’ THEN ‘WARRANTY’
WHEN SubQuery15.ProdGrup_ProdCode IN (‘1900’, ‘1901’, ‘1910’, ‘1911’, ‘1920’, ‘1930’, ‘1940’, ‘1990’) THEN ‘TULIP’
WHEN SubQuery15.ProdGrup_ProdCode IN (‘1000’, ‘1100’, ‘1200’, ‘1250’, ‘1300’, ‘1400’, ‘1500’, ‘1600’, ‘1700’, ‘1750’) THEN ‘MACHINE’
WHEN SubQuery15.ProdGrup_ProdCode IN (‘2400’, ‘2500’, ‘2600’, ‘9000’, ‘9100’, ‘9200’, ‘9300’, ‘9400’, ‘9500’, ‘9600’, ‘9700’, ‘9800’ , ‘9900’) THEN ‘PARTS’
ELSE ‘UNCLASSIFIED’
END)
ELSE ‘UNCLASSIFIED’
END)
But it’s pulling back Unclassified all of the time. JobHead.ProdCode is top level query as I didn’t know how else to use that table name.
To make sure your BAQ is actually getting the right records, make the expression: 'PG:'+SubQuery15.ProdGrup_Description+’,PC.D:’+SubQuery15.PartClass_Description+‘o.Job:’+o.Calculated_Job+’,PG.Code:’+SubQuery15.ProdGrup_ProdCode`
Test it and then look at the column to see that each of the fields you’re testing tin the expression are what you think they are.
To debug this, I would:
Copy your exiting expression to notepad (just so you don’t have to retype it later)
Make the expression just SubQuery15.ProdGrup_Description, and test it. Do you see different values in the column? Do you see values that begin with TULIP? If so, continue, else figure out why you’re not getting what you expect.
make the expression
(CASE
WHEN SubQuery15.ProdGrup_Description LIKE ‘TULIP%’
THEN SubQuery15.PartClass_Description
ELSE 'UNCLASSIFIED'
END)
That should show The part class description on all the records that the test from #2 showed lines beginning with TULIP
4. make the expression
(CASE
WHEN SubQuery15.ProdGrup_Description LIKE 'TULIP%'
THEN (CASE
WHEN SubQuery15.PartClass_Description LIKE 'Berti%'
THEN 'Berti Stock'
ELSE 'Non-Berti PC'
END)
ELSE 'UNCLASSIFIED'
END)
If that works then do:
(CASE
WHEN SubQuery15.ProdGrup_Description LIKE 'TULIP%'
THEN (CASE
WHEN SubQuery15.PartClass_Description LIKE 'Berti%'
THEN 'Berti Stock'
WHEN o.Calculated_Job LIKE ‘RD%’
THEN ‘R&D’
ELSE 'TULIP-Unclassified'
END)
ELSE 'UNCLASSIFIED'
END)
And just keep building up until you get the desired result.
Would this calculated field sit on the TopLevel query or on SubQuery15? If it’s on SubQuery15 I can’t find JobNum anywhere. If I add JobHead with a left join it still pulls duplicate records.
Neverless, Calvin thank you so much for all your help.
Can anyone tell me why I’m not getting the same rows, it’s bugging me now. I have changed the join types… Looked through the criteria to make sure I’m getting all of the right information.
The SQL query returns 5933 rows
SQLQuery.sql (3.7 KB)
.
The BAQ query returns 5401 rows
