SQL query with union fails in where clause

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

SQL Server 2005 Forums

Transact-SQL (2005)

SQL query with union fails in where clause

Author

Topic

Cyber_sql
Starting Member

5 Posts

Posted - 2009-07-08 : 20:47:22

Hi Team,

I have been working on an sql query to create a report and when I run the report to filter by HOLEID of type varchar, the query fails and I get the following error message:-

Error converting data type varchar to float.

The error happens at the end of the query on the WHERE clause which has the in statement

I hope you guys can help on this.

The SQL query is as below:-

SELECT DRILLINGDATE, HOLEID, PROJECTCODE, CONTRACTCODE, COSTCODE, NAME, DESCRIPTION, AMOUNT, UNITS, COST, TotalCost, COSTFROM, COSTTO, MFROM, MTO
FROM
(
select DRILLINGDATE, HOLEID, PROJECTCODE,CONTRACTCODE, COSTCODE, NAME, DESCRIPTION,
AMOUNT, UNITS, COST,
TotalCost = CASE WHEN COST IS NOT NULL AND AMOUNT IS NOT NULL THEN CAST(COST AS FLOAT)*CAST(AMOUNT AS FLOAT) ELSE 0 END, NULL AS COSTFROM, NULL AS COSTTO, NULL as MFROM, NULL as MTO
from
(
select DD.DRILLINGDATE, DD.HOLEID, DD.PROJECTCODE,DD.CONTRACTCODE, DD.COSTCODE, DD.NAME,DA.DESCRIPTION,
sum(cast(DD.VALUE as float)) AS AMOUNT, CD.VALUE AS COST, DA.UNITS
from DRILLINGDETAIL DD
INNER join CONTRACTDETAILS CD
ON DD.NAME = CD.NAME
AND DD.CONTRACTCODE = CD.CONTRACTCODE
inner join DRILLINGACTIVITY DA on DD.NAME = DA.NAME
group by DD.DRILLINGDATE, DD.HOLEID, DD.PROJECTCODE,DD.CONTRACTCODE, DD.COSTCODE, DD.NAME,DA.DESCRIPTION,CD.VALUE, DA.UNITS

)a
union
select DRILLINGDATE, HOLEID, PROJECTCODE, CONTRACTCODE, COSTCODE, DIAMETERTYPE AS NAME, NULL AS DESCRIPTION,
MetresDrilled as AMOUNT, 'm' as units, COSTMETRE as COST, TotalCost, COSTFROM, COSTTO,

MFROM = CASE
WHEN STARTDEPTH > COSTFROM THEN STARTDEPTH
WHEN STARTDEPTH > COSTFROM AND ENDDEPTH < COSTTO THEN COSTTO
WHEN COSTFROM > 0 THEN COSTFROM - 1
ELSE COSTFROM
END,

MTO = CASE
WHEN ENDDEPTH > COSTTO THEN COSTTO
WHEN ENDDEPTH < COSTTO THEN ENDDEPTH
ELSE COSTFROM
END
from (
select a.*,
DHD.STARTDEPTH, DHD.ENDDEPTH,
DHD.ENDDEPTH - DHD.STARTDEPTH as MetresDrilled,
DHD.DIAMETERTYPE,ATDC.COSTFROM,ATDC.COSTTO, ATDC.COSTMETRE,
TotalCost = CASE
WHEN DHD.ENDDEPTH > ATDC.COSTTO AND DHD.STARTDEPTH > ATDC.COSTFROM THEN SUM((ATDC.COSTTO - DHD.STARTDEPTH)*ATDC.COSTMETRE)
WHEN DHD.ENDDEPTH < ATDC.COSTTO AND DHD.STARTDEPTH < ATDC.COSTFROM THEN SUM((DHD.ENDDEPTH -(ATDC.COSTFROM-1))*ATDC.COSTMETRE)
WHEN DHD.ENDDEPTH < ATDC.COSTTO AND DHD.STARTDEPTH > ATDC.COSTFROM THEN SUM(ATDC.COSTMETRE*(DHD.ENDDEPTH-DHD.STARTDEPTH))
WHEN DHD.ENDDEPTH > COSTTO THEN SUM(ATDC.COSTMETRE*(ATDC.COSTTO - DHD.STARTDEPTH))
ELSE sum(ATDC.COSTMETRE*(DHD.ENDDEPTH-DHD.STARTDEPTH))
END
from
(
select DD.DRILLINGDATE, DD.SHIFT, DD.HOLEID
, DD.PROJECTCODE, DD.CONTRACTCODE, DD.COSTCODE,
MIN(CASE WHEN DD.NAME = 'DrillFrom' then DD.VALUE else NULL end) AS DrillFrom
,MIN(CASE WHEN DD.NAME = 'DrillTo' then DD.VALUE else NULL end) as DrillTo
FROM DRILLINGDETAIL DD

group by DD.DRILLINGDATE, DD.SHIFT, DD.HOLEID, DD.PROJECTCODE, DD.CONTRACTCODE, DD.COSTCODE
)a
inner join
DRILLHOLEDIAMETER DHD
on a.DrillFrom <= DHD.STARTDEPTH
and a.DrillTo >= DHD.ENDDEPTH
inner join
AT_DRILLCOST ATDC
on DHD.DIAMETERTYPE = ATDC.DIAMETERTYPE
and a.CONTRACTCODE = ATDC.CONTRACTCODE

group by DRILLINGDATE,SHIFT, a.CONTRACTCODE, a.DrillFrom, a.DrillTo,a.HOLEID, a.PROJECTCODE,COSTCODE,DHD.ENDDEPTH - DHD.STARTDEPTH,
DHD.DIAMETERTYPE, ATDC.COSTMETRE,DHD.STARTDEPTH, DHD.ENDDEPTH,ATDC.COSTFROM,ATDC.COSTTO
) b
WHERE METRESDRILLED IS NOT NULL AND METRESDRILLED >= 0 AND METRESDRILLED = (CAST(DRILLTO AS FLOAT)-CAST(DRILLFROM AS FLOAT))
)C
where CONTRACTCODE IN('DTQ_2009')
AND CAST(HOLEID AS VARCHAR) IN('SCR0008', 'SCR0009')
--Error here

AND TOTALCOST > 0
order by CONTRACTCODE,DRILLINGDATE ASC, HOLEID, PROJECTCODE, MFROM ASC, DESCRIPTION DESC

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-08 : 20:52:05

try casting or converting the totalcost in 2nd union query to float

KH
[spoiler]Time is always against us[/spoiler]

Cyber_sql
Starting Member

5 Posts

Posted - 2009-07-08 : 21:06:00

Changed the Totalcost to :-

AND CAST(TOTALCOST as FLOAT) > 0

in the where clause, but still no luck.

Its weird, because when I change it to
AND HOLEID LIKE ('SCR000%')

I get all the results