kdeutsch
asked on
add a named Column to where statement
See attached text file. I am trying to add in a named colum to my where clause to make it work correctly. All of this select goes into a datagrid for presentation.
Help.txt
Help.txt
The other way I could read your question is that if auth_doc_posn_title is like '%(%)%' then you want to check the substring and include >0, otherwise if auth_doc_posn_title isn't like that, don't bother checking the substring and include it anyway. If this is what you're looking for, try the below instead.
SELECT at.UPC as UIC,
ut.Addr_City as City,
at.Auth_Para_Dsg as PARA,
at.Auth_Line_Dsg as LINE,
at.GRADE,
substring(at.POSC, 1,4) as DMOS,
case at.AUTH_PERS_IDENT when 'E' then 'M' when 'W' then 'M' when 'O' then 'M' else 'I' end GENDER,
at.AUTH_STR,
CASE WHEN ISNUMERIC( case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end ) = 0 THEN 0
ELSE
case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end END AS OS,
at.ASGN_STR,
at.AUTH_STR + CASE WHEN ISNUMERIC( case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end ) = 0 THEN 0
ELSE
case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end - at.ASGN_STR END AS VAC,
case substring(at.DOC_NBR, 1, 2) when 'NG' then 'TDA' else 'MTOE' End as UNIT_Type,
case st.strStat when 'L' then 'Locked' when 'C' then 'Critical' Else '' end as UNIT_Stat
FROM PERS_UNIT_TBL as ut Inner join
PERS_AUTH_STR_TBL as at on at.UPC = ut.UPC Left Outer Join
DotNet.dbo.tblStatUic as st on st.strUIc = at.UPC
where Substring(at.grade, 1,1) = 'E' and at.UPC = '77726'
and ((at.AUTH_DOC_POSN_TITLE like '%(%)%' and substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end - at.ASGN_STR END > 0)
or (at.AUTH_DOC_POSN_TITLE not like '%(%)%'))
Order by at.UPC, at.Auth_Para_Dsg, at.Auth_Line_Dsg
ASKER
I keep getting an error of incorrect syntax near else on line 22.
I tired something like taht before but received 0 records.
I tired something like taht before but received 0 records.
The CASE structure is allowed in WHEN clause, so you should look at its values first and then you may decide if it works or not.
ASKER
Hi I provided some data, it might provide more insight.
Help2.txt
Help2.txt
You have duplicate END in your case, try this WHERE:
where Substring(at.grade, 1,1) = 'E' and at.UPC = '77726'
and case when at.AUTH_DOC_POSN_TITLE like '%(%)%'
then substring(at.AUTH_DOC_POSN _TITLE,
1 + charindex('(', at.AUTH_DOC_POSN_TITLE),
charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1))
else '0' end - at.ASGN_STR > 0
where Substring(at.grade, 1,1) = 'E' and at.UPC = '77726'
and case when at.AUTH_DOC_POSN_TITLE like '%(%)%'
then substring(at.AUTH_DOC_POSN
1 + charindex('(', at.AUTH_DOC_POSN_TITLE),
charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1))
else '0' end - at.ASGN_STR > 0
You can put that in a CTE expression. Try this:
;with FilterVAC as (
SELECT at.UPC as UIC,
ut.Addr_City as City,
at.Auth_Para_Dsg as PARA,
at.Auth_Line_Dsg as LINE,
at.GRADE,
substring(at.POSC, 1,4) as DMOS,
case at.AUTH_PERS_IDENT
when 'E' then 'M'
when 'W' then 'M'
when 'O' then 'M'
else 'I' end GENDER,
at.AUTH_STR,
CASE WHEN ISNUMERIC(case when at.AUTH_DOC_POSN_TITLE like '%(%)%'
then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1))
else '0' end ) = 0 THEN 0
ELSE
case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end END AS OS,
at.ASGN_STR,
at.AUTH_STR + CASE WHEN ISNUMERIC( case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end ) = 0 THEN 0
ELSE
case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end - at.ASGN_STR END AS VAC,
case substring(at.DOC_NBR, 1, 2) when 'NG' then 'TDA' else 'MTOE' End as UNIT_Type,
case st.strStat when 'L' then 'Locked' when 'C' then 'Critical' Else '' end as UNIT_Stat
FROM PERS_UNIT_TBL as ut Inner join
PERS_AUTH_STR_TBL as at on at.UPC = ut.UPC Left Outer Join
DotNet.dbo.tblStatUic as st on st.strUIc = at.UPC
where Substring(at.grade, 1,1) = 'E' and at.UPC = '77726'
Order by at.UPC, at.Auth_Para_Dsg, at.Auth_Line_Dsg
)
select * from FilterVac
where VAC > 0
You can use your select as subquery:
SELECT * FROM (
SELECT at.UPC as UIC,
ut.Addr_City as City,
at.Auth_Para_Dsg as PARA,
at.Auth_Line_Dsg as LINE,
at.GRADE,
substring(at.POSC, 1,4) as DMOS,
case at.AUTH_PERS_IDENT when 'E' then 'M' when 'W' then 'M' when 'O' then 'M' else 'I' end GENDER,
at.AUTH_STR,
CASE WHEN ISNUMERIC( case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end ) = 0 THEN 0
ELSE
case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end END AS OS,
at.ASGN_STR,
at.AUTH_STR + CASE WHEN ISNUMERIC( case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end ) = 0 THEN 0
ELSE
case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end - at.ASGN_STR END AS VAC,
case substring(at.DOC_NBR, 1, 2) when 'NG' then 'TDA' else 'MTOE' End as UNIT_Type,
case st.strStat when 'L' then 'Locked' when 'C' then 'Critical' Else '' end as UNIT_Stat
FROM PERS_UNIT_TBL as ut Inner join
PERS_AUTH_STR_TBL as at on at.UPC = ut.UPC Left Outer Join
DotNet.dbo.tblStatUic as st on st.strUIc = at.UPC
where Substring(at.grade, 1,1) = 'E' and at.UPC = '77726') SUBQ
WHERE SUBQ.VAC > 0
ASKER
pcelba
Tried taht and it always comes up with zero records, from the sampling I showed it should come up with a few.
ralmada, these are the errors on this script
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ';'.
Msg 156, Level 15, State 1, Line 29
Incorrect syntax near the keyword 'Order'.
Tried taht and it always comes up with zero records, from the sampling I showed it should come up with a few.
ralmada, these are the errors on this script
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ';'.
Msg 156, Level 15, State 1, Line 29
Incorrect syntax near the keyword 'Order'.
;with FilterVAC as (
SELECT at.UPC as UIC,
ut.Addr_City as City,
at.Auth_Para_Dsg as PARA,
at.Auth_Line_Dsg as LINE,
at.GRADE,
substring(at.POSC, 1,4) as DMOS,
case at.AUTH_PERS_IDENT when 'E' then 'M' when 'W' then 'M' when 'O' then 'M' else 'I' end GENDER,
at.AUTH_STR,
CASE WHEN ISNUMERIC( case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end ) = 0 THEN 0
ELSE
case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end END AS OS,
at.ASGN_STR,
at.AUTH_STR + CASE WHEN ISNUMERIC( case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end ) = 0 THEN 0
ELSE
case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end - at.ASGN_STR END AS VAC,
case substring(at.DOC_NBR, 1, 2) when 'NG' then 'TDA' else 'MTOE' End as UNIT_Type,
case st.strStat when 'L' then 'Locked' when 'C' then 'Critical' Else '' end as UNIT_Stat
FROM PERS_UNIT_TBL as ut Inner join
PERS_AUTH_STR_TBL as at on at.UPC = ut.UPC Left Outer Join
DotNet.dbo.tblStatUic as st on st.strUIc = at.UPC
where Substring(at.grade, 1,1) = 'E' and at.UPC = '77726'
)
Select * from FilterVac
where VAC > '0'
Order by at.UPC, at.Auth_Para_Dsg, at.Auth_Line_Dsg
sorry
;with FilterVAC as (
SELECT at.UPC as UIC,
ut.Addr_City as City,
at.Auth_Para_Dsg as PARA,
at.Auth_Line_Dsg as LINE,
at.GRADE,
substring(at.POSC, 1,4) as DMOS,
case at.AUTH_PERS_IDENT when 'E' then 'M' when 'W' then 'M' when 'O' then 'M' else 'I' end GENDER,
at.AUTH_STR,
CASE WHEN ISNUMERIC( case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end ) = 0 THEN 0
ELSE
case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end END AS OS,
at.ASGN_STR,
at.AUTH_STR + CASE WHEN ISNUMERIC( case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end ) = 0 THEN 0
ELSE
case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end - at.ASGN_STR END AS VAC,
case substring(at.DOC_NBR, 1, 2) when 'NG' then 'TDA' else 'MTOE' End as UNIT_Type,
case st.strStat when 'L' then 'Locked' when 'C' then 'Critical' Else '' end as UNIT_Stat
FROM PERS_UNIT_TBL as ut Inner join
PERS_AUTH_STR_TBL as at on at.UPC = ut.UPC Left Outer Join
DotNet.dbo.tblStatUic as st on st.strUIc = at.UPC
where Substring(at.grade, 1,1) = 'E' and at.UPC = '77726'
)
Select * from FilterVac
where VAC > '0'
Order by UPC, Auth_Para_Dsg, Auth_Line_Dsg
ASKER
ralmada: Still getting this erro, tried to remove but brings more error, i tired single quote on select asl well with no result.
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ';'.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
argh,
My development is in sql 2000 but prodcution is in sql 2005.
My development is in sql 2000 but prodcution is in sql 2005.
Well, you can use my last approach. It will work in both SQL 2000 and 2005.
Open in new window