Union Query Error

[fionama]

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

 

[bborissov]

Did both queries works?
I mean Separate them and try them:

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%'



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)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[fionama]

Hi,
Yes, the queries do work separately, but together I get the error mentioned above. Thanks for your reply - I really don't know where to go with this!
Fiona

 

[EKOnerhime]

The error doesn't seem to be a SQL error - are you using Microsoft SQL Server? If so, which version?

 

[Qik3Coder]

You get data type mis-matches when the data type in column 1 doesn't match the datatype in column 2. The oddity to the is that SQL will try to cast to more restrictive column type if it thinks it can get away with it.

if you have column 1 with all numbers in query 1, and letters in query 2, you will normally get conversion error:

select 1
union all
select 'a'

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]