Error in Union Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Error in Union Query

Author

Topic

fionama
Starting Member

6 Posts

Posted - 2008-11-13 : 12:11:34

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-13 : 12:22:13

i dont think error is in code posted. i cant see any obvious errors on code. also error msg suggest dynamic sql but i cant see any.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-11-13 : 14:08:14

Not pertaining to your error (I don't think), but you are missing about 1 second of data by comparing D.BIRTH_DATE < '2007-12-31 23:59:59'? I figured you knew, but just waned to mention it incase you didn't realize it.

fionama
Starting Member

6 Posts

Posted - 2008-11-13 : 18:19:12

yes, now that you've mentioned it, I know now that I'm missing a second of data! Thanks, still haven't gotten anywhere with my original problem though!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources