Hi all,
I'm trying to run the following union query, and I'm getting the error "Error: Dynamic SQL Error. SQL error code = -104. Invalid command. Data type unknown. (State:HY000, Native Code: FFFFFF98)".
The queries work individually, but can't see what the problem is with the union. Would appreciate your help!
The query is..
SELECT I.INCIDENTID as IncID,D.ID as DelID,
P.PAS_NO as PASNo, P.SURNAME as Surname, P.FIRNAME as Forename,
D.BIRTH_DATE as DOB,
F.ANSWERTEXT as ANSWERa
FROM MAT_FREEANSWERS F
INNER JOIN MAT_QUESTIONS QS ON QS.ID = F.QUESTIONID
INNER JOIN MAT_INCIDENTS I ON I.INCIDENTID = F.INCIDENTID
INNER JOIN MAT_QUESTIONNAIREINCIDENTS QI ON
QI.INCIDENTID=I.INCIDENTID
INNER JOIN MAT_QUESTIONNAIRE Q ON
Q.ID=QI.QUESTIONNAIREID
INNER JOIN MAT_DELIVERY D ON D.INCIDENTID = I.INCIDENTID
INNER JOIN PASMAIN P ON P.PAS_NO = D.MOTHER_PAS_NO
LEFT OUTER JOIN MAT_ANSWERS A ON A.QUESTIONID=QS.ID
LEFT OUTER JOIN MAT_INCIDENTANSWERS IA ON IA.ANSWERID=A.ID
WHERE D.BIRTH_DATE >= '2007-01-01 00:00:00'
AND D.BIRTH_DATE < '2007-12-31 23:59:59'
and F.QUESTIONID=72350
and F.ANSWERTEXT LIKE '%38%'
UNION
select I.INCIDENTID AS IncID,D.ID as DelID,
P.PAS_NO as PASNo, P.SURNAME as Surname, P.FIRNAME as Forename,
D.BIRTH_DATE as DOB,
A.ANSWER as ANSWERa
from MAT_INCIDENTS I
INNER JOIN MAT_DELIVERY D ON INCIDENTID=I.INCIDENTID
INNER JOIN MAT_QUESTIONNAIREINCIDENTS QI ON INCIDENTID=I.INCIDENTID
INNER JOIN MAT_INCIDENTANSWERS IA ON INCIDENTID=D.INCIDENTID
INNER JOIN PASMAIN P ON PAS_NO=D.MOTHER_PAS_NO
INNER JOIN MAT_QUESTIONNAIRE Q ON ID=QI.QUESTIONNAIREID
INNER JOIN MAT_QUESTIONS QS ON QUESTIONNAIREID=QI.QUESTIONNAIREID
INNER JOIN MAT_ANSWERS A ON ID=IA.ANSWERID
AND D.BIRTH_DATE >= '2007-01-01 00:00:00'
AND D.BIRTH_DATE < '2007-12-31 23:59:59'
AND Q.ID IN (221)
AND QS.ID IN (72467)
AND A.ID IN (1600174)
Many thanks,
Fiona
I'm trying to run the following union query, and I'm getting the error "Error: Dynamic SQL Error. SQL error code = -104. Invalid command. Data type unknown. (State:HY000, Native Code: FFFFFF98)".
The queries work individually, but can't see what the problem is with the union. Would appreciate your help!
The query is..
SELECT I.INCIDENTID as IncID,D.ID as DelID,
P.PAS_NO as PASNo, P.SURNAME as Surname, P.FIRNAME as Forename,
D.BIRTH_DATE as DOB,
F.ANSWERTEXT as ANSWERa
FROM MAT_FREEANSWERS F
INNER JOIN MAT_QUESTIONS QS ON QS.ID = F.QUESTIONID
INNER JOIN MAT_INCIDENTS I ON I.INCIDENTID = F.INCIDENTID
INNER JOIN MAT_QUESTIONNAIREINCIDENTS QI ON
QI.INCIDENTID=I.INCIDENTID
INNER JOIN MAT_QUESTIONNAIRE Q ON
Q.ID=QI.QUESTIONNAIREID
INNER JOIN MAT_DELIVERY D ON D.INCIDENTID = I.INCIDENTID
INNER JOIN PASMAIN P ON P.PAS_NO = D.MOTHER_PAS_NO
LEFT OUTER JOIN MAT_ANSWERS A ON A.QUESTIONID=QS.ID
LEFT OUTER JOIN MAT_INCIDENTANSWERS IA ON IA.ANSWERID=A.ID
WHERE D.BIRTH_DATE >= '2007-01-01 00:00:00'
AND D.BIRTH_DATE < '2007-12-31 23:59:59'
and F.QUESTIONID=72350
and F.ANSWERTEXT LIKE '%38%'
UNION
select I.INCIDENTID AS IncID,D.ID as DelID,
P.PAS_NO as PASNo, P.SURNAME as Surname, P.FIRNAME as Forename,
D.BIRTH_DATE as DOB,
A.ANSWER as ANSWERa
from MAT_INCIDENTS I
INNER JOIN MAT_DELIVERY D ON INCIDENTID=I.INCIDENTID
INNER JOIN MAT_QUESTIONNAIREINCIDENTS QI ON INCIDENTID=I.INCIDENTID
INNER JOIN MAT_INCIDENTANSWERS IA ON INCIDENTID=D.INCIDENTID
INNER JOIN PASMAIN P ON PAS_NO=D.MOTHER_PAS_NO
INNER JOIN MAT_QUESTIONNAIRE Q ON ID=QI.QUESTIONNAIREID
INNER JOIN MAT_QUESTIONS QS ON QUESTIONNAIREID=QI.QUESTIONNAIREID
INNER JOIN MAT_ANSWERS A ON ID=IA.ANSWERID
AND D.BIRTH_DATE >= '2007-01-01 00:00:00'
AND D.BIRTH_DATE < '2007-12-31 23:59:59'
AND Q.ID IN (221)
AND QS.ID IN (72467)
AND A.ID IN (1600174)
Many thanks,
Fiona