query

Question

Post reply

query

Katmai Stratovolcano

SSChasing Mays

Points: 626
More actions
July 16, 2009 at 5:52 am

#132060

select distinct
emp.First_Name,emp.Last_Name,emp.Middle_Name,
comp.employee_id,comp.request_dt,comp.numofdays,
comp.reason,comp.request_by,comp.approve_dt,
tech.First_Name+' ' +tech.Last_Name+' '+isNull(tech.Middle_Name,'') as REQUESTEDBY,
comp.approve_status,comp.approve_desc,
case when (comp.approve_by) is null then '' else ( select first_name+' '+last_name from employee_tbl where user_id=comp.approve_by) end as APPROVEBY
from
employee_tbl emp,tl_compoff_tbl comp,TL_activity_tbl tla
,employee_tbl tech
where emp.employee_id = comp.employee_id
and comp.employee_id = tla.employee_id
and emp.employee_id = tla.employee_id
and comp.request_by = 'AST/016'
and emp.employee_status = 'A'
and (comp.approve_status='O' or comp.approve_status='D')
and tla.status='A'
and getdate() between tla.start_date and tla.end_date
and tech.User_Id = comp.request_by
order by comp.approve_status--emp.First_Name
Hi,
My question is can we use any other statement instead of case in this.Pls correct and reply

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29255 More actions · Answer 1

your case statement looks incorrect. can you post table structure, sample data and expected output pls.

This URL will help you in posting these.

http://qa.sqlservercentral.com/articles/Best+Practices/61537

Pradeep Singh

Ken McKelvey SSCoach Points: 18484 More actions · Answer 2

Try this:

SELECT DISTINCT

emp.First_Name

,emp.Last_Name

,emp.Middle_Name

,comp.employee_id

,comp.request_dt

,comp.numofdays

,comp.reason

,comp.request_by

,comp.approve_dt

,tech.First_Name + ' ' + tech.Last_Name + ' ' + COALESCE(tech.Middle_Name, '') AS REQUESTEDBY

,comp.approve_status

,comp.approve_desc

,COALESCE(approve.first_name + ' ' + approve.last_name, '') AS APPROVEBY

FROM employee_tbl emp

JOIN l_compoff_tbl comp

ON emp.employee_id = comp.employee_id

JOIN TL_activity_tbl tla

ON emp.employee_id = tla.employee_id

JOIN employee_tbl tech

ON comp.request_by = tech.[User_Id]

LEFT JOIN employee_tbl approve

ON comp.approve_by = approve.[user_id]

WHERE comp.request_by = 'AST/016'

AND emp.employee_status = 'A'

AND (comp.approve_status='O' OR comp.approve_status='D')

AND tla.status='A'

AND GETDATE() BETWEEN tla.start_date AND tla.end_date

ORDER BY approve_status--First_Name

Katmai Stratovolcano SSChasing Mays Points: 626 More actions · Answer 3

Katmai Stratovolcano

SSChasing Mays

Points: 626

July 16, 2009 at 7:14 am

#1025607

Thanks dude...........it really works

Eswin SSCrazy Eights Points: 8320 More actions · Answer 4

Ken McKelvey (7/16/2009)
Try this:
SELECT DISTINCT
emp.First_Name
,emp.Last_Name
,emp.Middle_Name
,comp.employee_id
,comp.request_dt
,comp.numofdays
,comp.reason
,comp.request_by
,comp.approve_dt
,tech.First_Name + ' ' + tech.Last_Name + ' ' + COALESCE(tech.Middle_Name, '') AS REQUESTEDBY
,comp.approve_status
,comp.approve_desc
,COALESCE(approve.first_name + ' ' + approve.last_name, '') AS APPROVEBY
FROM employee_tbl emp
JOIN l_compoff_tbl comp
ON emp.employee_id = comp.employee_id
JOIN TL_activity_tbl tla
ON emp.employee_id = tla.employee_id
JOIN employee_tbl tech
ON comp.request_by = tech.[User_Id]
LEFT JOIN employee_tbl approve
ON comp.approve_by = approve.[user_id]
WHERE comp.request_by = 'AST/016'
AND emp.employee_status = 'A'
AND (comp.approve_status='O' OR comp.approve_status='D')
AND tla.status='A'
AND GETDATE() BETWEEN tla.start_date AND tla.end_date
ORDER BY approve_status--First_Name

Can you please tell me how to change this toa HQL(Hibernate Query Language)

Tanx 😀

Eswin SSCrazy Eights Points: 8320 More actions · Answer 5

Jinu try this:

select distinct

emp.First_Name,emp.Last_Name,emp.Middle_Name,

comp.employee_id,comp.request_dt,comp.numofdays,

comp.reason,comp.request_by,comp.approve_dt,

tech.First_Name+' ' +tech.Last_Name+' '+isNull(tech.Middle_Name,'') as REQUESTEDBY,

comp.approve_status,comp.approve_desc,

case when (comp.approve_by) is null then '' else app.first_name+' '+app.Last_Name end as APPROVEBY

from

employee_tbl emp,tl_compoff_tbl comp,TL_activity_tbl tla

,employee_tbl tech,employee_tbl app

where emp.employee_id = comp.employee_id

and comp.employee_id = tla.employee_id

and emp.employee_id = tla.employee_id

and comp.request_by = 'AST/016'

and emp.employee_status = 'A'

and (app.user_id=comp.approve_by or (comp.approve_by) is null)

and (comp.approve_status='O' or comp.approve_status='D')

and tla.status='A'

and getdate() between tla.start_date and tla.end_date

and tech.User_Id = comp.request_by

order by comp.approve_status--emp.First_Name

Tanx 😀