Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Remove decimals from a nondeterministic field Expand / Collapse
Author
Message
Posted Wednesday, April 15, 2009 9:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 6, 2011 12:48 PM
Points: 11, Visits: 41
here is what I have:

select 'D', '001', '1714827671', b.VCHRNMBR, b.DOCAMNT,
b.DOCDATE, b.DOCNUMBR, d.VNDCHKNM, b.VOIDED
from CM00100 as a
inner join PM30200 as b
on a.CHEKBKID = b.CHEKBKID
inner join PM00200 as d
on b.VENDORID = d.VENDORID
and a.CHEKBKID = 'OP FTB'
and b.DOCDATE = convert(varchar(8), getdate(), 112)

This is a 2 part question -

1st - the field b.DOCAMNT returns with decimals (164.23 example - and usually there are 50 - 100 results returned for different check amounts) and the bank requires that there be no decimal in the file.

2nd - the b.DOCDATE field needs to reflect as YYYYMMDD, and the conversion is not working .... it still returns 2009-04-14 00:00:00:000 Please help !! THANKS IN ADVANCE !
Post #697627
Posted Wednesday, April 15, 2009 10:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 13,230, Visits: 12,709

This is a 2 part question -

1st - the field b.DOCAMNT returns with decimals (164.23 example - and usually there are 50 - 100 results returned for different check amounts) and the bank requires that there be no decimal in the file.

2nd - the b.DOCDATE field needs to reflect as YYYYMMDD, and the conversion is not working .... it still returns 2009-04-14 00:00:00:000


1 - hhmmm how could i use math to remove the decimal place?
2 - The answer is in your where clause.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #697705
Posted Wednesday, April 15, 2009 10:19 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:22 AM
Points: 375, Visits: 765
On the first item do you need the decimal data still there? For example if the field contains data like 123.45 do you need 123 or 12345? If you need 12345 how will they know its a two decimal position value or is that always assumed? A little more information about how you need to format it and rules around it might help someone give an exact answer. If you need just 123 you could use a function like FLOOR() or convert it to an int type data type. IF you need the full amount then you may need to convert to string and replace the "." with an empty string. You can also multiply it out into a whol number if its a given 2 decimal always.

On the second item, you need to do the formating of the date in the SELECT portion of you statement and not the WHERE clause. The where clause only determines criteria of what result set is returned, and not the formatting of the returned data. Do a convert with a date style flag in the select portion of your statement on that field.
Post #697706
Posted Wednesday, April 15, 2009 10:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 6, 2011 12:48 PM
Points: 11, Visits: 41
Thank you for your fast reply.

If the field has 123.45, when the decimal is removed, it needs to be 12345. I am trying to convert this data all to a text file to upload to the bank.
Post #697718
Posted Wednesday, April 15, 2009 10:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 6, 2011 12:48 PM
Points: 11, Visits: 41
PS - Thought this might help as well - extracting from data tables and inserting data in to a temp table, so that the field size matches the bank requirements.

CREATE TABLE dbo.AP_POSPAY
(
ID char(1) NOT NULL,
[Bank Number] char(3) NOT NULL,
Account char(10) NOT NULL,
[Ck Number] char(10) NOT NULL,
Amount char (13) NOT NULL,
Issued char (8) NOT NULL,
[Additional Data] char(30) NOT NULL,
Payee char(80) NOT NULL,
Void char(1) NOT NULL
) ON [PRIMARY]
Post #697726
Posted Wednesday, April 15, 2009 10:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 13,230, Visits: 12,709
if it is ALWAYS 2 decimals just multiply by 100

otherwise cast it to a varchar and then replace '.' with ''


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #697732
Posted Wednesday, April 15, 2009 11:36 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 3, 2014 6:50 AM
Points: 75, Visits: 79
Hello I took your code and made the following changes to it

--Create Temp table to load converted data

Create Table #Temp
(
col1 char(1),
Trans char(3),
Actnumb char(10),
Vchnmbr char(20),
Docamnt int,
Docdate char(8),
Docnumb char(20),
Vndchknm char(64),
Voided int
)


INSERT #Temp

select 'D', '001', '1714827671', b.VCHRNMBR, (b.DOCAMNT * 100), --Remove the Decimal Points
--Convert the date to YYYYDDMM format
CONVERT(VARCHAR(8), CONVERT(VARCHAR(4), YEAR(b.DOCDATE))+ RIGHT( CONVERT(VARCHAR(2), DATEPART(dd, b.DOCDATE)),3) + RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(mm, b.DOCDATE)), 2)) as Docdate,
b.DOCNUMBR, d.VNDCHKNM, b.VOIDED, b.DOCDATE
from CM00100 as a
inner join PM30200 as b
on a.CHEKBKID = b.CHEKBKID
inner join PM00200 as d
on b.VENDORID = d.VENDORID
and a.CHEKBKID = 'USD_RBC_PAY'

Select * from #Temp


Drop Table #Temp

Post #697772
Posted Wednesday, April 15, 2009 2:28 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
don.craig (4/15/2009)
Hello I took your code and made the following changes to it

--Create Temp table to load converted data

Create Table #Temp
(
col1 char(1),
Trans char(3),
Actnumb char(10),
Vchnmbr char(20),
Docamnt int,
Docdate char(8),
Docnumb char(20),
Vndchknm char(64),
Voided int
)


INSERT #Temp

select 'D', '001', '1714827671', b.VCHRNMBR, (b.DOCAMNT * 100), --Remove the Decimal Points
--Convert the date to YYYYDDMM format
CONVERT(VARCHAR(8), CONVERT(VARCHAR(4), YEAR(b.DOCDATE))+ RIGHT( CONVERT(VARCHAR(2), DATEPART(dd, b.DOCDATE)),3) + RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(mm, b.DOCDATE)), 2)) as Docdate,
b.DOCNUMBR, d.VNDCHKNM, b.VOIDED, b.DOCDATE
from CM00100 as a
inner join PM30200 as b
on a.CHEKBKID = b.CHEKBKID
inner join PM00200 as d
on b.VENDORID = d.VENDORID
and a.CHEKBKID = 'USD_RBC_PAY'

Select * from #Temp


Drop Table #Temp



Hi Don

Nice solution! Just one little suggestion, since the date format YYYYMMDD is the standard ISO date format I would use CONVERT:
SELECT CONVERT(CHAR(8), GETDATE(), 112)

Greets
Flo



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #697916
Posted Wednesday, April 15, 2009 2:31 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
slange (4/15/2009)
if it is ALWAYS 2 decimals just multiply by 100

otherwise cast it to a varchar and then replace '.' with ''


... and if you have to remove other decimals after the first both try FLOOR:
SELECT FLOOR(234.740423 * 100)

Greets
Flo



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #697917
Posted Thursday, April 16, 2009 5:36 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 3, 2014 6:50 AM
Points: 75, Visits: 79
Hi Flo,

Thank you for your suggestions.

Don
Post #698307
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse