CAST Command

I am using MS-Access as a front end for my MS-SQL DB. I have a sql view that
uses the following:

SELECT TOP 100 PERCENT RECID, PATNUMBER AS [PAT #], SVCCODE AS [Service
Code], QTY, PROF_CHRGS AS [Pro Fee's'], AMOUNT,
BILLDATE AS [Bill Date], CHKAMT AS [Check Amt], PSDATE
AS [Service Date], POSTDATE AS [Post Date], TRNSCODE AS [T Code],
TRLR AS [T Code Desc], SUBSTRING(CAST(SVCCODE AS
varchar), 1, 4) AS [Dept #]
FROM dbo.PAT_Transactions
ORDER BY PATNUMBER, SVCCODE

My problem is the cast command. Will this sql view works and cast the
SVCCODE field into a varchar, I need to cast the reseult of this,
SUBSTRING(CAST(SVCCODE AS varchar), 1, 4) AS [Dept #], back in to a decimal
format. I am dropping this view onto a form and need to link to a field on
the form that is in decimal format.

The only way I could get this to work was to create another view, based on
the one above, and cast the [Dept #] field back into the decimal format. Is
there any better way to do this? Can I nest the Cast command?

Hope this makes sense.

Thanks in advance.

Mike
m charney at dunlap hospital dot org

Like this:

CAST(SUBSTRING(CAST(svccod AS VARCHAR), 1, 4) AS DECIMAL(4,0))

--
David Portas
SQL Server MVP
--

"Mike" <noway@forgetit.com> wrote in message
news:qns6f.1047$jV2.300@newssvr17.news.prodigy.com ...
>I am using MS-Access as a front end for my MS-SQL DB. I have a sql view
>that uses the following:
>
> SELECT TOP 100 PERCENT RECID, PATNUMBER AS [PAT #], SVCCODE AS
> [Service Code], QTY, PROF_CHRGS AS [Pro Fee's'], AMOUNT,
> BILLDATE AS [Bill Date], CHKAMT AS [Check Amt],
> PSDATE AS [Service Date], POSTDATE AS [Post Date], TRNSCODE AS [T Code],
> TRLR AS [T Code Desc], SUBSTRING(CAST(SVCCODE AS
> varchar), 1, 4) AS [Dept #]
> FROM dbo.PAT_Transactions
> ORDER BY PATNUMBER, SVCCODE
>
> My problem is the cast command. Will this sql view works and cast the
> SVCCODE field into a varchar, I need to cast the reseult of this,
> SUBSTRING(CAST(SVCCODE AS varchar), 1, 4) AS [Dept #], back in to a
> decimal format. I am dropping this view onto a form and need to link to a
> field on the form that is in decimal format.
>
> The only way I could get this to work was to create another view, based on
> the one above, and cast the [Dept #] field back into the decimal format.
> Is there any better way to do this? Can I nest the Cast command?
>
> Hope this makes sense.
>
> Thanks in advance.
>
> Mike
> m charney at dunlap hospital dot org
>
>
>
>

Mike (noway@forgetit.com) writes:
> My problem is the cast command. Will this sql view works and cast the
> SVCCODE field into a varchar, I need to cast the reseult of this,
> SUBSTRING(CAST(SVCCODE AS varchar), 1, 4) AS [Dept #], back in to a
> decimal format. I am dropping this view onto a form and need to link to
> a field on the form that is in decimal format.
>
> The only way I could get this to work was to create another view, based
> on the one above, and cast the [Dept #] field back into the decimal
> format. Is there any better way to do this? Can I nest the Cast
> command?

Yes, you can nest:

CAST(SUBSTRING(CAST(SVCCODE AS varchar), 1, 4) AS decimal(p, s))




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Thanks!!


"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message
news:GtydncJkzuT17MfeRVnyjQ@giganews.com...
> Like this:
>
> CAST(SUBSTRING(CAST(svccod AS VARCHAR), 1, 4) AS DECIMAL(4,0))
>
> --
> David Portas
> SQL Server MVP
> --
>
> "Mike" <noway@forgetit.com> wrote in message
> news:qns6f.1047$jV2.300@newssvr17.news.prodigy.com ...
>>I am using MS-Access as a front end for my MS-SQL DB. I have a sql view
>>that uses the following:
>>
>> SELECT TOP 100 PERCENT RECID, PATNUMBER AS [PAT #], SVCCODE AS
>> [Service Code], QTY, PROF_CHRGS AS [Pro Fee's'], AMOUNT,
>> BILLDATE AS [Bill Date], CHKAMT AS [Check Amt],
>> PSDATE AS [Service Date], POSTDATE AS [Post Date], TRNSCODE AS [T Code],
>> TRLR AS [T Code Desc], SUBSTRING(CAST(SVCCODE AS
>> varchar), 1, 4) AS [Dept #]
>> FROM dbo.PAT_Transactions
>> ORDER BY PATNUMBER, SVCCODE
>>
>> My problem is the cast command. Will this sql view works and cast the
>> SVCCODE field into a varchar, I need to cast the reseult of this,
>> SUBSTRING(CAST(SVCCODE AS varchar), 1, 4) AS [Dept #], back in to a
>> decimal format. I am dropping this view onto a form and need to link to a
>> field on the form that is in decimal format.
>>
>> The only way I could get this to work was to create another view, based
>> on the one above, and cast the [Dept #] field back into the decimal
>> format. Is there any better way to do this? Can I nest the Cast command?
>>
>> Hope this makes sense.
>>
>> Thanks in advance.
>>
>> Mike
>> m charney at dunlap hospital dot org
>>
>>
>>
>>
>
>