sql server new users: I'm finally getting somewhere, but getting an error message and not sure how
to get around it. I'm guessing it's a text/numeric issue, but not sure what
to do to fix it. Anyone know what I can do?

Here's the message:

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'YTD_OSO' to a column of data type
int.

And here's the code:

SELECT aipdn.Data017_all.DealerID, aipdn.Data017_all.MTD_StkOrdAmt,
aipdn.Data017_all.MTD_TotOrdAmt, aipdn.Data017_all.YTD_StkOrdAmt,
aipdn.Data017_all.YTD_TotOrdAmt, aipdn.Data017_all.PeriodYear,
aipdn.Data017_all.PeriodMonth,
aipdn.Data017_all.MTD_StkOrdAmt / aipdn.Data017_all.MTD_TotOrdAmt * 100 AS
'MTD_OSO',
aipdn.Data017_all.YTD_StkOrdAmt / aipdn.Data017_all.YTD_TotOrdAmt * 100 AS
'YTD_OSO',
dbo.Dealers.StandardTxtCode, dbo.Dealers.DealerCode,
CASE
WHEN 60 < 'YTD_OSO' AND 'YTD_OSO' < 90 THEN (('YTD_OSO' * .01) * 100) - 60
WHEN 'YTD_OSO' > 90 THEN 30
ELSE 0
END AS 'BASEPOINTS',
CASE
WHEN 'YTD_OSO' > 80 THEN 'BASEPOINTS' - 20
ELSE 0
END AS 'BONUSPOINTS',
'BASEPOINTS' + 'BONUSPOINTS' as 'TOTALPOINTS'
FROM aipdn.Data017_all RIGHT OUTER JOIN
dbo.Dealers ON aipdn.Data017_all.DealerID =
dbo.Dealers.DealerCode
WHERE (aipdn.Data017_all.PeriodYear = 2005)
ORDER BY aipdn.Data017_all.PeriodMonth

You're not casting with that expression, nor are you referencing any column.
You're referencing a character literal. Any time you put quotes around
something it's a literal

As well the logic is sort of.... inconsistant including how you though you
went from a calced value to a varchar. Regardless. There are some other
issues as well.

Without doing any testing what it should look like is this:

select d_t3.DealerID DealerID
, d_t3.MTD_StkOrdAmt mtd_StkOrdAmt
, d_t3.MTD_TotOrdAmt mtd_TotOrdAmt
, d_t3.YTD_StkOrdAmt ytd_StkOrdAmt
, d_t3.YTD_TotOrdAmt ytd_TotOrdAmt
, d_t3.PeriodYear PeriodYear
, d_t3.PeriodMonth PerioidMonth
, d_t3.MTD_OSO mtd_oso
, d_t3.YTD_OSO ytd_oso
, d_t3.StandardTxtCode StandardTxtCode
, d_t3.DealerCode DealerCode
, d_t3.basepoints basepoints
, d_t3.bonuspoints bonuspoints
, d_t3.basepoint + d_t3.bonuspoints TOTALPOINTS
(
select d_t2.DealerID DealerID
, d_t2.MTD_StkOrdAmt mtd_StkOrdAmt
, d_t2.MTD_TotOrdAmt mtd_TotOrdAmt
, d_t2.YTD_StkOrdAmt ytd_StkOrdAmt
, d_t2.YTD_TotOrdAmt ytd_TotOrdAmt
, d_t2.PeriodYear PeriodYear
, d_t2.PeriodMonth PerioidMonth
, d_t2.MTD_OSO mtd_oso
, d_t2.YTD_OSO ytd_oso
, d_t2.StandardTxtCode StandardTxtCode
, d_t2.DealerCode DealerCode
, d_t2.basepoints basepoints
, case
when cast(d_t2.ytd_oso as int) > 80 THEN d_t2.BASEPOINTS - 20
else 0
end as BONUSPOINTS
(
select d_t.DealerID DealerID
, d_t.MTD_StkOrdAmt mtd_StkOrdAmt
, d_t.MTD_TotOrdAmt mtd_TotOrdAmt
, d_t.YTD_StkOrdAmt ytd_StkOrdAmt
, d_t.YTD_TotOrdAmt ytd_TotOrdAmt
, d_t.PeriodYear PeriodYear
, d_t.PeriodMonth PerioidMonth
, d_t.MTD_OSO mtd_oso
, d_t.YTD_OSO ytd_oso
, d_t.StandardTxtCode StandardTxtCode
, d_t.DealerCode DealerCode
, case
when d_t.ytd_oso < 90
/* you did have <, < if you meant >, < then this would be when d_t.ytd_oso
between 61 and 89
then YTD_OSO * .01) * 100) - 60
when YTD_OSO > 90 THEN 30
else 0
end as basepoints
(
select aipdn.Data017_all.DealerID DealerID
, aipdn.Data017_all.MTD_StkOrdAmt mtd_StkOrdAmt
, aipdn.Data017_all.MTD_TotOrdAmt mtd_TotOrdAmt
, aipdn.Data017_all.YTD_StkOrdAmt ytd_StkOrdAmt
, aipdn.Data017_all.YTD_TotOrdAmt ytd_TotOrdAmt
, aipdn.Data017_all.PeriodYear PeriodYear
, aipdn.Data017_all.PeriodMonth PerioidMonth
/* beware over/under flows */
, cast(aipdn.Data017_all.MTD_StkOrdAmt /
aipdn.Data017_all.MTD_TotOrdAmt * 100 as int) as MTD_OSO
, cast(aipdn.Data017_all.YTD_StkOrdAmt /
aipdn.Data017_all.YTD_TotOrdAmt * 100 as int) as YTD_OSO
, dbo.Dealers.StandardTxtCode
, dbo.Dealers.DealerCode
FROM aipdn.Data017_all RIGHT OUTER JOIN
dbo.Dealers ON aipdn.Data017_all.DealerID =
dbo.Dealers.DealerCode
where (aipdn.Data017_all.PeriodYear = 2005)
) as d_t
) as d_t2
) as d_t3
order by d_t3.PeriodMonth

Of course there is a way to do it a bit 'flatter', but it becomes more of a
maintenance hassle.

--
Walter Clayton
Any technology distinguishable from magic is insufficiently advanced.


[quoted text, click to view]