I'm trying to do some positioning and calculation of charindex and substrings and was wondering if it be possible to store the value to a variable and then reference it from there.Here's my example:, CASE WHEN CHARINDEX('-', WO.ENGINEERED_BY, 3) > 0 THEN LEFT(WO.ENGINEERED_BY, 1) END AS A_MBU, CASE WHEN CHARINDEX('-', WO.ENGINEERED_BY, 3) > 0 THEN CHARINDEX('-', WO.ENGINEERED_BY, 2) - CHARINDEX('-', WO.ENGINEERED_BY, 1) END AS ENG_LEN, CASE WHEN CHARINDEX('-', WO.ENGINEERED_BY, 3) > 0 THEN SUBSTRING(WO.ENGINEERED_BY, - CHARINDEX('-', WO.ENGINEERED_BY, 1)+1, ENG_LEN) END AS A_ENG, CASE WHEN CHARINDEX('-', WO.ENGINEERED_BY, 3) > 0 THEN SUBSTRING(WO.ENGINEERED_BY, CHARINDEX('-', WO.ENGINEERED_BY, 2)+1, 1) END AS A_ASME, CASE WHEN CHARINDEX('-', WO.ENGINEERED_BY, 3) > 0 THEN SUBSTRING(WO.ENGINEERED_BY, CHARINDEX('-', WO.ENGINEERED_BY, 3)+1, 1) END AS A_ELECThe 3rd line is trying to reference the output of the 2nd line where ENG_LEN references output of line 2. I was thinking about declaring a variable and then set that variable to the second line, but not sure how to do it. The example below does now work and was wondering if any one can assist., @ENG_LEN = CASE WHEN CHARINDEX('-', WO.ENGINEERED_BY, 3) > 0 THEN CHARINDEX('-', WO.ENGINEERED_BY, 2) - CHARINDEX('-', WO.ENGINEERED_BY, 1) END AS ENG_LEN , CASE WHEN CHARINDEX('-', WO.ENGINEERED_BY, 3) > 0 THEN SUBSTRING(WO.ENGINEERED_BY, - CHARINDEX('-', WO.ENGINEERED_BY, 1)+1, @ENG_LEN) END AS A_ENGAny ideas?
Using variables
Posted - 2005-08-05 : 09:32:45
MadhivananFailing to plan is Planning to fail 