Query question

rbuser

Hi ,

I do need to create a query (over 3 views) with a difficult join.
Is something like this possiblein the end-user environment:

SELECT
DISTINCT
CL.CLIENT_AANHEFMETACHTERNAAM,
CL.CLIENT_ADRES_HUISNUMMER,
CL.CLIENT_POSTCODE,
CL.CLIENT_WOONPLAATS,
ST.DATUMVAN,
ST.DATUMTOT
FROM
VRB_CLIENT_TRAJECT_OVZ TR
LEFT JOIN VRB_CLIENT CL
ON CL.CLIENT_ID = TR.CLIENT_ID
JOIN VRB_CLIENT_TRAJECTSTATUS ST
ON ST.CLIENT_ID = TR.CLIENT_ID
AND ST.TRAJECT_ID = TR.TRAJECT_ID
AND ST.INITIELE_STARTDATUM = TR.INITIELE_STARTDATUM
AND ST.STATUS_ID = 2
AND ST.DATUMVAN < '1-31-2005'
AND ( (NOT EXISTS (
SELECT * FROM VRB_CLIENT_TRAJECTSTATUS ST1
WHERE ST1.CLIENT_ID = ST.CLIENT_ID
AND ST1.TRAJECT_ID = ST.TRAJECT_ID
AND ST1.INITIELE_STARTDATUM = ST.INITIELE_STARTDATUM
AND ST1.STATUS_ID = 5)
)
OR (EXISTS (
SELECT * FROM VRB_CLIENT_TRAJECTSTATUS ST1
WHERE ST1.CLIENT_ID = ST.CLIENT_ID
AND ST1.TRAJECT_ID = ST.TRAJECT_ID
AND ST1.INITIELE_STARTDATUM = ST.INITIELE_STARTDATUM
AND ST1.STATUS_ID = 5
AND ST1.DATUMVAN >= '1-1-2005')
)
)
ORDER BY CL.CLIENT_ACHTERNAAM

Eric

nicocizik

Hi Eric,

Unfortunately embedded queries are very limited in the data workspace
(limited just to a search expression). The SQL code below will need to be
created manually. If you need some examples of linking datasets after
editing the SQL let me know.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com