SQL Help!

[Elroacho]

Hi all,

I'm trying to write a script for interbase and I've hit a wall. firstly I have a simple script which is fine.

SELECT STAFF.STAFF_ID,
STAFF.NAME,
STAFF AGE,
SALARY.WAGES
FORM STAFF, SALARY
WHERE SALARY > 10000 AND
STAFF.STAFF_ID = SALARY_STAFF_ID

OK it's easy to see what this is doing but I need to add a boolean field called COMPANY CAR. The value will come from another table called BENEFITS. The benefits table comtains the fields STAFF_ID and BENEFIT. the BENEFIT field will contain various codes such as 'CAR', 'PEN' and BONUS' so one employee could have more than one benefit code assigned to them so they will have multiple records. The COMPANY CAR field in the output will return 'CAR' (or just 'TRUE') if they have the 'CAR' code with their STAFF_ID in the BEFEFITS table or FALSE if they don't. As you can see my problem is there is no 'NO CAR' value in the BENFITS table so no record for them will exist if they don't have a car.

Can you help?

YNWA
Elraocho

 

[swampBoogie]

SELECT STAFF.STAFF_ID,
       STAFF.NAME,
       STAFF.AGE,
       SALARY.WAGES,
       coalesce(benefits.benefit,'NO CAR')
FORM STAFF join SALARY on
STAFF.STAFF_ID = SALARY_STAFF_ID
left join benefits on
SALARY_STAFF_ID = benefits.staff_id
and benefits.benefit = 'CAR'
WHERE SALARY > 10000

 

[Elroacho]

My version of interbase (5.5) doesn;t seem to like the COALESCE function...

Dynamic SQL Error
-SQL error code = -804
-Function unknown
-COALESCE

It this a new funtion for a later version?

 

[johnherman]

(SELECT STAFF.STAFF_ID,
STAFF.NAME,
STAFF.AGE,
SALARY.WAGES,
'CAR'
FROM STAFF WHERE SALARY > 10000
AND STAFF.STAFF_ID IN
(SELECT STAFF_ID FROM BENEFITS
WHERE BENEFITS.BENEFIT = 'CAR'))

UNION

(SELECT STAFF.STAFF_ID,
STAFF.NAME,
STAFF.AGE,
SALARY.WAGES,
'NO CAR'
FROM STAFF WHERE SALARY > 10000
AND STAFF.STAFF_ID NOT IN
(SELECT STAFF_ID FROM BENEFITS
WHERE BENEFITS.BENEFIT = 'CAR'))




Sometimes the grass is greener on the other side because there is more manure there - original.

 

[dnoeth]

Elroacho wrote:

"-COALESCE

It this a new funtion for a later version?"


Don't know if new IB versions support CASE/COALESCE/NULLIF, but IIRC Firebird 1.5 does.
But you can include a UDF for the same purpose. There are several free UDF libraries available, you'll probably have to look for a IFNULL function.

Dieter