can a if be add to a select statement ? 1

[Greg25]

Here’s a statement that I have there is a field named status how do I go about adding an if statement to produce status, 2 = not ready, 3 = ready, 4 = export insted of the number thanks for looking
Greg X-)

Set nocount on

SELECT
'['+ nullif(last_cost_center_code,'') +'] '+ isnull(cc.cost_center_name,'') as 'Status ',
(SELECT max(job_Number) FROM order_entry jc WHERE jc.job_number = oe.job_number) as 'Number',
(SELECT max(job_Name) FROM order_entry jc WHERE jc.job_number = oe.job_number) as 'Name ',
status as 'status', insted of 2,3,4, I would perfer not ready,ready or exported
billed_Date as 'Billed'



FROM order_entry oe LEFT OUTER JOIN cost_centers cc ON oe.last_cost_center_code = cc.cost_center_code
LEFT OUTER JOIN invoices ON oe.job_Name = invoices.job_Name

where closed_job = 1
and posted = 0

Order by (SELECT max(job_Number) FROM order_entry jc WHERE jc.job_number = oe.job_number)



-- end X-)

 

[Cagliostro]

select proj,
(Case status when 2 then status) as status3,
...from..
union
select proj,
(Case status when 3 then status) as status3,
...from..
John Fill

ivfmd@mail.md

 

[Greg25]

Thanks I will look into the case when statment

 

[Cagliostro]

THere is a little to change this main idea:
select* from
(
select ...
(Case status when 2 then status) as status2,
0 as status3
...from..
union
select ..., 0 as status2
(Case status when 3 then status) as status3,
...from..
) t where status1 <> 0 or status2 <> 0
it is a simulating of crosstab queries. John Fill

ivfmd@mail.md

 

[tlbroadbent]

I think you want use a CASE statement to do something like this.

Case status
When 2 Then 'not ready'
When 3 Then 'ready'
When 4 Then 'exported'
Else 'Unknown'
End as 'status'
Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.