how to write a where clause for columns created using case statements in SQl

Question

1.00/5 (1 vote)

See more:

SQL-Server-2005

Hi,this is Pavan I have an sql query which cantains columns created using case statements bound by joining diff tables like this......

SQL

select 'S.No' as 'S.no',TRANS_ADP_VIOLATION_WITHDRAWAL_ADP_VIOLATION_WITHDRAWAL.int_ADP_VIOLATION_WITHDRAWAL_ID as [ID] ,
ISNULL(convert(varchar, TRANS_ADP_VIOLATION_WITHDRAWAL_ADP_VIOLATION_WITHDRAWAL.dt_4262_OF,106),'') as [Submitted On] ,
p.str_GeneratePassID[Permit Number],

case when av.str_type='C' then (
select STR_2116_OF  from  TRANS_Contractor_Contractor_Details where int_Contractor_ID in
(select str_ObjectID from STANDARD_EMPLOYEES where int_STANDARD_ID = av.str_empno))
When av.str_type= 'E' then 'BIAL';
end  as [Company],

(select str_text from CONFIG_TYPE_OF_ADP  where smallint_id in (select smallint_3936_ME from
trans_inspections_sections_33 where int_INSPECTION_ID in
(select str_checklistObjectID from MATRIX_INSPECTION_CHECKLIST_RELATION where
smallint_CategoryID = 33 and str_objectNo = av.str_objectNo))) [ADP Type] ,

ISNULL(cast( TRANS_ADP_VIOLATION_WITHDRAWAL_ADP_VIOLATION_WITHDRAWAL.str_4046_OF as varchar),'') as [ADP Withdrawal No],
ISNULL(convert(varchar, TRANS_ADP_VIOLATION_WITHDRAWAL_ADP_VIOLATION_WITHDRAWAL.dt_4039_OF,106),'') as [Date Of WithDrawal]
 from TRANS_ADP_VIOLATION_WITHDRAWAL_ADP_VIOLATION_WITHDRAWAL left outer join MATRIX_VIOLATION_WITHDRAWAL_RELATIONSHIP[w]
 on w.int_withdrawalid=TRANS_ADP_Violation_Withdrawal_ADP_Violation_Withdrawal.int_ADP_Violation_Withdrawal_ID
left outer join MATRIX_ADP_AVP_AEP_PASS_ISSUE_NUMBERS[p] on p.int_MatrixID=w.str_Objectid
left outer join MATRIX_AVIATION_SAFETY_APPLICANT_DETAILS[av] on av.str_ObjectNo=p.str_RequestID
 where  w.int_PluginID=58 

order by TRANS_ADP_VIOLATION_WITHDRAWAL_ADP_VIOLATION_WITHDRAWAL.dt_modifiedOn desc

can we write a where clause for the columns Company and AdpType if so plz Explain thanks in advance..........

Posted 19-Aug-13 20:14pm

B.S.S Pavan Kumar

Updated 19-Aug-13 21:24pm

v6

Add a Solution

Comments

Maciej Los 20-Aug-13 2:39am

Yes, you can.

B.S.S Pavan Kumar 20-Aug-13 3:11am

can u Explain me how????

Maciej Los 20-Aug-13 3:22am

See my answer.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

kishore sharma · Answer 1 · 2013-08-19T20:42:00

Solution 1

Hello pavan ,
Bellow i am giving one example You can make same changes

SQL

select * from(
select case departmentid when 9 then'A' when 6 then 'B' when 12 then 'c' end as [dpt], * from bugsmaster) bug where [dpt]='A'

Where you need to put your query inside select as bellow

Select * from (------)
Replace your query with ------ and use the same alias column for your where clause.

Cheers

Posted 19-Aug-13 20:42pm

kishore sharma

Comments

B.S.S Pavan Kumar 20-Aug-13 3:24am

Thank u for ur reply Kishore but i have multiple tables Joined it returns multiples rows in a subquery

Maciej Los · Answer 2 · 2013-08-19T21:21:00

Solution 2

Yoour query is a bit complicated, so i would suggest you to start here: WHERE clause (T-SQL) [^].

Example:

SQL

SELECT ...<fields collection>
FROM ...<table collection>
WHERE w.int_PluginID=58 AND TableAlias.Company = '' AND TableAlias.AdpType = 123

Posted 19-Aug-13 21:21pm

Maciej Los

Updated 19-Aug-13 21:26pm

v2

Comments

kishore sharma 20-Aug-13 3:28am

what are these tags in DB Query "</table>"?

Maciej Los 20-Aug-13 3:30am

</table> tags have been removed.