How can I do this - some sort of UNION maybe?

[nick]

We have two tables, one ('Addlist') containing addresses and the other ('Phonelis') containing personnel details. Entries in the phonelis table are keyed to the address table. Some of the addresses are schools. A query selects headmasters (for example) from the schools as follows:

SELECT {FIELDS} FROM Addlist, Phonelis
WHERE Phonelis.ADDLISTID=Addlist.KEY_NUM
AND upper(Phonelis.JOBTITLE) like 'HEAD'

So far very easy, this produces a line for each head. What I am struggling with is to return a line with a literal value, 'The Head', in the JOBTITLE field when there is no head name for that school in the phonelis table, so we can send a letter addressed to 'The Head' if we don't know his name. I would be very happy if someone could help me with this.

As an added complication, I might need to return further lines with 'The Deputy' (for example) in the same query, in the event that there is nobody with 'Deputy' as their jobtitle named for that school.

The actual sql, built by a small Delphi application with lots of check boxes, is more complicated than the above: it may have clauses to select only the most recently edited head if there is more than one; to limit the schools to ones we support; and/or to limit the schools to ones within a certain distance (using a table of postcode distances 'Pc_dist). In the following real example STRMAKESALUTE is a user-defined function and SAL is a field indicating whether we are on first-name terms:

SELECT DISTINCT STRMAKESALUTE(SAL,TITLE,FIRSTNAME,SURNAME) AS SALUTE,TRIM(COALESCE(FIRSTNAME,' ')+' '+COALESCE(SURNAME,' '))AS CONTACT,ADDLIST.CO_NAME, ADD1,ADD2,ADD3,ADD4,POSTCODE,THE_DIST AS _THE_DIST
FROM ADDLIST,PHONELIS P1,PC_DIST
WHERE ((UPPER(CO_TYPE) LIKE 'PRIMARY'))
AND ((UPPER(LEA) LIKE 'LIVERPOOL') OR (UPPER(LEA) LIKE 'BEDFORD'))
AND ((KEY_NUM IN (SELECT ADDLIST_ID FROM SUPPORTED)))
AND ((TRUE))
AND (((POSTCODE LIKE '%') AND (STRLEFTOF(POSTCODE,' ')=PC_START) AND (THE_DIST<10)))
AND ((UPPER(JOBTITLE) LIKE 'HEAD'))
AND (NOT (EXISTS(SELECT * FROM PHONELIS P2 WHERE P2.LAST_MOD > P1.LAST_MOD AND P2.JOBTITLE=P1.JOBTITLE AND P2.ADDLISTID=P1.ADDLISTID)))
AND P1.ADDLISTID=ADDLIST.KEY_NUM
AND NOT (CO_NAME LIKE '*%')
ORDER BY ADDLIST.CO_NAME

On 13/06/2012 9:51 AM, nick wrote:
> We have two tables, one ('Addlist') containing addresses and the other
> ('Phonelis') containing personnel details. Entries in the phonelis table
> are keyed to the address table. Some of the addresses are schools. A
> query selects headmasters (for example) from the schools as follows:
>
> SELECT {FIELDS} FROM Addlist, Phonelis
> WHERE Phonelis.ADDLISTID=Addlist.KEY_NUM
> AND upper(Phonelis.JOBTITLE) like 'HEAD'
>
> So far very easy, this produces a line for each head. What I am
> struggling with is to return a line with a literal value, 'The Head', in
> the JOBTITLE field when there is no head name for that school in the
> phonelis table, so we can send a letter addressed to 'The Head' if we
> don't know his name. I would be very happy if someone could help me with
> this.
>
> As an added complication, I might need to return further lines with 'The
> Deputy' (for example) in the same query, in the event that there is
> nobody with 'Deputy' as their jobtitle named for that school.
>
> The actual sql, built by a small Delphi application with lots of check
> boxes, is more complicated than the above: it may have clauses to select
> only the most recently edited head if there is more than one; to limit
> the schools to ones we support; and/or to limit the schools to ones
> within a certain distance (using a table of postcode distances
> 'Pc_dist). In the following real example STRMAKESALUTE is a user-defined
> function and SAL is a field indicating whether we are on first-name
> terms:
>
> SELECT DISTINCT STRMAKESALUTE(SAL,TITLE,FIRSTNAME,SURNAME) AS
> SALUTE,TRIM(COALESCE(FIRSTNAME,' ')+' '+COALESCE(SURNAME,' '))AS
> CONTACT,ADDLIST.CO_NAME, ADD1,ADD2,ADD3,ADD4,POSTCODE,THE_DIST AS
> _THE_DIST
> FROM ADDLIST,PHONELIS P1,PC_DIST
> WHERE ((UPPER(CO_TYPE) LIKE 'PRIMARY'))
> AND ((UPPER(LEA) LIKE 'LIVERPOOL') OR (UPPER(LEA) LIKE 'BEDFORD'))
> AND ((KEY_NUM IN (SELECT ADDLIST_ID FROM SUPPORTED)))
> AND ((TRUE))
> AND (((POSTCODE LIKE '%') AND (STRLEFTOF(POSTCODE,' ')=PC_START) AND
> (THE_DIST<10)))
> AND ((UPPER(JOBTITLE) LIKE 'HEAD'))
> AND (NOT (EXISTS(SELECT * FROM PHONELIS P2 WHERE P2.LAST_MOD >
> P1.LAST_MOD AND P2.JOBTITLE=P1.JOBTITLE AND
> P2.ADDLISTID=P1.ADDLISTID)))
> AND P1.ADDLISTID=ADDLIST.KEY_NUM
> AND NOT (CO_NAME LIKE '*%')
> ORDER BY ADDLIST.CO_NAME

I would suggest looking at the CASE statement which can be used
to pick between several values based on conditional expressions.

Basic structure is:

SELECT studentID,studentName,
CASE
WHEN gender = 'F' THEN 'Female'
WHEN gender = 'M' THEN 'Male'
ELSE 'Not available'
END AS gender_description
FROM students



--
Brian Evans [NDX]
Ottawa, ON, CANADA
GMT-5