April 21, 2016 at 7:24 am
Hi all,
I am trying to replicate some stuff i have made in Access in a SQL database.
However i have a query the creates a new field and determines the value with the below IFF..
IIf(Left([PDT_ORG_4_CDA],1)="X",[PDT_ORG_5_CDA],[PDT_ORG_4_CDA]) AS [Dept Name]
Im new to SQL and wondering how this would interperit into T-SQL?
Thanks in advance
Chris
April 21, 2016 at 7:30 am
Chris
You need to use a CASE expression.
John
April 21, 2016 at 7:31 am
IIF was added in SQL 2014, but in earlier versions you'd use CASE (IF is control flow)
CASE WHEN [PDT_ORG_4_CDA LIKE 'X%'
THEN [PDT_ORG_5_CDA]
ELSE [PDT_ORG_4_CDA]
END AS [DeptName]
And try to avoid column names with spaces in them
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 21, 2016 at 8:19 am
GilaMonster (4/21/2016)
IIF was added in SQL 2014, but in earlier versions you'd use CASE (IF is control flow)
CASE WHEN [PDT_ORG_4_CDA LIKE 'X%'THEN [PDT_ORG_5_CDA]
ELSE [PDT_ORG_4_CDA]
END AS [DeptName]
And try to avoid column names with spaces in them
Perfect thank you 🙂
However im trying to do an inner join after the code above but it cannot fine [DeptName]..is this not possible?
April 21, 2016 at 8:26 am
You can't join on a column alias. Either wrap the whole SELECT statement in a CTE or subquery, or repeat the whole CASE expression in your join predicate.
John
April 21, 2016 at 8:27 am
John Mitchell-245523 (4/21/2016)
You can't join on a column alias. Either wrap the whole SELECT statement in a CTE or subquery, or repeat the whole CASE expression in your join predicate.John
Sorry John im not sure what you mean. I understood the part where you said its not possible.
What i have come up with so far is
SELECT *, d.DepartmentName,
CASE WHEN p.[PDT_ORG_4_CDA] LIKE 'X%'
THEN p.[PDT_ORG_5_CDA]
ELSE p.[PDT_ORG_4_CDA]
END AS [DeptCode]
From stage_chris21.PSDET p
INNER JOIN hris.CW_Departments d
ON p.[DeptCode] = d.DepartmentCode
April 21, 2016 at 8:38 am
Something like this...
WITH Departments AS (
SELECT *, d.DepartmentName,
CASE WHEN p.PDT_ORG_4_CDA LIKE 'X%'
THEN p.PDT_ORG_5_CDA
ELSE p.PDT_ORG_4_CDA
END AS DeptCode
)
From stage_chris21.PSDET p
INNER JOIN Departments d
ON p.[DeptCode] = d.DepartmentCode
... or this:
SELECT *, d.DepartmentName,
CASE WHEN p.PDT_ORG_4_CDA LIKE 'X%'
THEN p.PDT_ORG_5_CDA
ELSE p.PDT_ORG_4_CDA
END AS DeptCode
From stage_chris21.PSDET p
INNER JOIN hris.CW_Departments d
ON p.DeptCode = CASE WHEN p.PDT_ORG_4_CDA LIKE 'X%'
THEN p.PDT_ORG_5_CDA
ELSE p.PDT_ORG_4_CDA
END
John
April 21, 2016 at 8:59 am
John Mitchell-245523 (4/21/2016)
Something like this...
WITH Departments AS (SELECT *, d.DepartmentName,
CASE WHEN p.PDT_ORG_4_CDA LIKE 'X%'
THEN p.PDT_ORG_5_CDA
ELSE p.PDT_ORG_4_CDA
END AS DeptCode
)
From stage_chris21.PSDET p
INNER JOIN Departments d
ON p.[DeptCode] = d.DepartmentCode
... or this:
SELECT *, d.DepartmentName,CASE WHEN p.PDT_ORG_4_CDA LIKE 'X%'
THEN p.PDT_ORG_5_CDA
ELSE p.PDT_ORG_4_CDA
END AS DeptCode
From stage_chris21.PSDET p
INNER JOIN hris.CW_Departments d
ON p.DeptCode = CASE WHEN p.PDT_ORG_4_CDA LIKE 'X%'
THEN p.PDT_ORG_5_CDA
ELSE p.PDT_ORG_4_CDA
END
John
Thanks John,
Cannot get either to work, it doesn't like 'DeptCode' is says invalid column name.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'DeptCode'.
April 21, 2016 at 9:10 am
Well, I don't have the luxury of being able to see your tables, so I couldn't test it. But I think if you swap those table aliases over, or put the CASE expression on the other side of the join predicate, you should be able to get it to work.
John
April 21, 2016 at 9:26 am
wrightyrx7 (4/21/2016)
John Mitchell-245523 (4/21/2016)
Something like this...
WITH Departments AS (SELECT *, d.DepartmentName,
CASE WHEN p.PDT_ORG_4_CDA LIKE 'X%'
THEN p.PDT_ORG_5_CDA
ELSE p.PDT_ORG_4_CDA
END AS DeptCode
)
From stage_chris21.PSDET p
INNER JOIN Departments d
ON p.[DeptCode] = d.DepartmentCode
... or this:
SELECT *, d.DepartmentName,CASE WHEN p.PDT_ORG_4_CDA LIKE 'X%'
THEN p.PDT_ORG_5_CDA
ELSE p.PDT_ORG_4_CDA
END AS DeptCode
From stage_chris21.PSDET p
INNER JOIN hris.CW_Departments d
ON p.DeptCode = CASE WHEN p.PDT_ORG_4_CDA LIKE 'X%'
THEN p.PDT_ORG_5_CDA
ELSE p.PDT_ORG_4_CDA
END
John
Thanks John,
Cannot get either to work, it doesn't like 'DeptCode' is says invalid column name.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'DeptCode'.
Just seems a couple things mixed up.
Maybe this?
WITH P AS (
SELECT *, --d.DepartmentName, the departments table is not available here
CASE WHEN p.PDT_ORG_4_CDA LIKE 'X%'
THEN p.PDT_ORG_5_CDA
ELSE p.PDT_ORG_4_CDA
END AS DeptCode
From stage_chris21.PSDET p --This was outside the CTE, no FROM in the CTE at all before
)
SELECT * --Also no SELECT from the CTE previously
FROM
P
INNER JOIN Departments d
ON p.[DeptCode] = d.DepartmentCode
OR
SELECT *,
CASE WHEN p.PDT_ORG_4_CDA LIKE 'X%'
THEN p.PDT_ORG_5_CDA
ELSE p.PDT_ORG_4_CDA
END AS DeptCode
From stage_chris21.PSDET p
INNER JOIN hris.CW_Departments d
--previously the below was comparing p.DeptCode,
--which would be the result of the CASE statement,
--but unavailable for use here, to itself.
ON d.DepartmentCode = CASE WHEN p.PDT_ORG_4_CDA LIKE 'X%'
THEN p.PDT_ORG_5_CDA
ELSE p.PDT_ORG_4_CDA
END
As John pointed out, we don't have sample data or DDL for your tables, so I can't make any guarantees, but this fixes at least the clear problems.
Another way I like to work with long expressions that are referenced multiple times is to generate them in a CROSS APPLY. Then I can use the aliased result in the query.
Cheers!
April 22, 2016 at 9:19 am
You can use CROSS APPLY to assign an alias name to value(s). You can then use that alias in subsequent code, even in another CA. That is, alias can be used to help define another alias -- how sweet is that!
SELECT *, d.DepartmentName, ca1.DeptCode
FROM stage_chris21.PSDET p
CROSS APPLY (
SELECT CASE WHEN p.[PDT_ORG_4_CDA] LIKE 'X%'
THEN p.[PDT_ORG_5_CDA]
ELSE p.[PDT_ORG_4_CDA]
END AS [DeptCode]
) AS ca1
INNER JOIN hris.CW_Departments d
ON ca1.[DeptCode] = d.DepartmentCode
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
April 25, 2016 at 1:10 am
I guess you need to refer DEPTCode without prefixing it with P. as Deptcode is not present in the stage_chris21.PSDET table. Try the below code and see if it works.
SELECT *, d.DepartmentName,
CASE WHEN p.[PDT_ORG_4_CDA] LIKE 'X%'
THEN p.[PDT_ORG_5_CDA]
ELSE p.[PDT_ORG_4_CDA]
END AS [DeptCode]
From stage_chris21.PSDET p
INNER JOIN hris.CW_Departments d
ON [DeptCode] = d.DepartmentCode
April 25, 2016 at 3:18 am
vivekshekhar-743222 (4/25/2016)
I guess you need to refer DEPTCode without prefixing it with P. as Deptcode is not present in the stage_chris21.PSDET table. Try the below code and see if it works.
It won't work, because you can't refer to an alias in any clause of the query it's defined in, other than ORDER BY. Hence the recommendation to use a CTE or a subquery.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply