unable to access columns from a sub query

Hi

(I've just edited this to make it a bit clearer to understand my problem. Thanks)

I was wondering if anybody could help me out here. This is for an App in ASP(classic) and MS SQL


A Quick explanation... I have 3 tables called ADMIN_SUB, ADMIN & STAFF.

What I want to do is depending on a particular row value in table ADMIN, either populate a field with either the forename + surname from ADMIN_SUB or the forename + surname from STAFF. (see EXAMPLE 1)


EXAMPLE 1
"SELECT ADMIN.admin_id, CASE WHEN ADMIN.staff_id IS NULL THEN ADMIN_SUB.forename ELSE STAFF.forename END AS forename, CASE WHEN ADMIN.staff_id IS NULL THEN ADMIN_SUB.surname ELSE STAFF.surname END AS surname FROM ADMIN LEFT OUTER JOIN STAFF ON ADMIN.staff_id = STAFF.staff_id LEFT OUTER JOIN ADMIN_SUB ON ADMIN.admin_id = ADMIN_SUB.admin_id"
(The above works fine and I can just access them as forename and surname, but when i use this as a sub query i can no longer get to their values.)

BUT

I need to try and use this as a SUB QUERY and the problem is I can't get at the values of forename and surname output-ed by
EXAMPLE 2. You can see how EXAMPLE 1 is placed inside EXAMPLE 2.

The columns I need from EXAMPLE 2 are ward_id, ward_name, phone, main_contact, notes, forename, surname
I have the following SQL (accessed as an ADO recordset)


EXAMPLE 2
rs.open "SELECT ward_id, ward_name, phone, main_contact, notes FROM WARD WHERE EXISTS (SELECT ADMIN.admin_id, CASE WHEN ADMIN.staff_id IS NULL THEN ADMIN_SUB.forename ELSE STAFF.forename END AS forename, CASE WHEN ADMIN.staff_id IS NULL THEN ADMIN_SUB.surname ELSE STAFF.surname END AS surname FROM (ADMIN LEFT OUTER JOIN STAFF ON ADMIN.staff_id = STAFF.staff_id LEFT OUTER JOIN ADMIN_SUB ON ADMIN.admin_id = ADMIN_SUB.admin_id) WHERE ADMIN.admin_id = main_contact) ORDER BY ward_name"





Can anybody help?
(Thanks in advance)
Flipflops.

is the sub query (example 1 is it??) executing ok? I use a seperate syntax in CASE statements when checking for NULL values, to be honest I don't know your syntax is correct or not, if I was @ work I could test it for you...I'll check it out when I get up there today though...but take out the sub query, run it, it runs ok??

nevermind, that Case syntax is ok. Sorry.

Thanks for thinking about it.

In answer to your question, yes the sub query part (EXAMPLE 1) works fine on its own.

Yesterday, I got to thinking about a slightly different tack, and it occured to me that if I did something along the lines of using the sub query as a function that I might be able to get around it that way...

i.e. call the query with the admin_id as a parameter.

...so i'm going to look at that today.

(I'm a relative newbie when it comes to SQL like this, and I only built my first SQL User Defined function yesterday - although I'm damn glad I did - it took me a hell of a long time to get it working - but so worthwhile)

Hi

Following on from what I've been doing above I though I'd have a go at placing the CASE statement in a USER DEFINED FUNCTION.

I've been searching google, but can't find anything that helped me out.

I was hoping somebody could point me in the right directtion or recomend a tutorial.

This is the function I'm working on:

CREATE FUNCTION [someowner].[somename] (@admin_id int)
RETURNS varchar(20) AS
BEGIN

DECLARE @forename varchar(20)



SELECT
@forename = CASE
SELECT dbo.ADMIN.staff_id FROM dbo.ADMIN INNER JOIN dbo.STAFF ON dbo. ADMIN.staff_id = dbo.STAFF.staff_id WHERE dbo.ADMIN.admin_id = @admin_id
WHEN
NOT NULL
THEN
SELECT dbo.STAFF.forename FROM dbo.ADMIN INNER JOIN dbo.STAFF ON dbo. ADMIN.staff_id = dbo.STAFF.staff_id
ELSE
SELECT dbo.ADMIN_SUB.forename FROM dbo.ADMIN INNER JOIN dbo.ADMIN_SUB ON dbo. ADMIN.admin_id = dbo.ADMIN_SUB.admin_id
END





RETURN @forename

END

And this are the synatx error messages I get:

Error 156: Incorrect syntax near keyword 'SELECT'
Incorrect syntax near keyword 'WHEN'
Incorrect syntax near keyword 'ELSE'
Incorrect syntax near keyword 'RETURN'

I've been messing around with this all morning but haven't manged to get any further than this.
Thanks.

I've done it.

Here it is, and it return the results I'm after. Hope thats useful to somebody.

CREATE FUNCTION [someowner].[somename] (@admin_id int)
RETURNS varchar(20) AS
BEGIN

DECLARE @forename varchar(20)
DECLARE @forename2 varchar(20)
DECLARE @forename3 varchar(20)

SELECT @forename2 = dbo.STAFF.forename FROM dbo.ADMIN INNER JOIN dbo.STAFF ON dbo. ADMIN.staff_id = dbo.STAFF.staff_id WHERE dbo.ADMIN.admin_id = @admin_id
SELECT @forename3 = dbo.ADMIN_SUB.forename FROM dbo.ADMIN INNER JOIN dbo.ADMIN_SUB ON dbo. ADMIN.admin_id = dbo.ADMIN_SUB.admin_id WHERE dbo.ADMIN.admin_id = @admin_id

SELECT
@forename = CASE
WHEN

@forename2 IS NOT NULL
THEN
@forename2
ELSE
@forename3
END




RETURN @forename

END