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 orgMike (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/techin.../2000/books.asp|||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
>|||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
>>
>>
>>
>>
