Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Need Help in SQL Stored Procedure 0.00 to .00

Dear all,

I am trying to querying a database to export to a bar delimited file using stored procedure.

However I am getting .00 instead of 0.00 in my exported file. In SQL server showing 0.00 but it becomes .00 in the bar delimited file.

I have tried everything, playing with different datatypes in SQL server, different datatypes, formatting but nothing worked.

Below please find my stored procedure to export bar delimited file:

CREATE PROCEDURE [dbo].[GAF_Ledger]

@StartDate datetime,

@EndDate datetime

AS

BEGIN

  -- SET NOCOUNT ON added to prevent extra result sets from

  -- interfering with SELECT statements.

  /*Create temporary table*/

CREATE TABLE ##LRec (

  ID char(1) DEFAULT 'L',

RefDate date NULL,

Account nvarchar(15) NULL,

AcctName nvarchar(100) NULL,

Memo nvarchar(50) NULL,

CardName nvarchar(100) NULL,

TransID int NOT NULL,

BaseRef nvarchar(11) NULL,

TransType nvarchar(20) NULL,

Debit numeric(19,2),

Credit numeric(19,2),

Balance numeric(19,2)

  ) 

SET NOCOUNT ON;

INSERT INTO ##LRec (ID,RefDate,Account,AcctName,Memo,CardName,TransID,BaseRef,TransType,Debit,Credit,Balance)

SELECT 'L', CONVERT(nvarchar(10),T0.[RefDate],103) as DocDate, T2.FormatCode as 'Account ID', T2. AcctName,T0.Memo,T3.CardName, T0.[TransId], T0.BaseRef,

CASE

WHEN T0.TransType = 13 THEN 'AR Invoice'

WHEN T0.TransType = 14 THEN 'AR Cred Memo'

WHEN T0.TransType = 18 THEN 'AP Invoice'

WHEN T0.TransType = 19 THEN 'AP Cred Memo'

WHEN T0.TransType = 24 THEN 'Incoming Payment'

WHEN T0.TransType = 30 THEN 'Journal Entry'

WHEN T0.TransType = 46 THEN 'Outgoing Payment'

ELSE 'Other'

END AS 'Trans Type', T1.Debit, T1.Credit, T1.Debit - T1.Credit As 'Balance' FROM OJDT T0 INNER JOIN JDT1 T1 ON T1.TransId = T0.TransId LEFT JOIN OCRD T3 ON T3.CardCode = T1.ShortName INNER JOIN OACT T2 ON T2.AcctCode = T1.Account WHERE T0.RefDate >=@StartDate and  T0.[RefDate]<= @EndDate

SET NOCOUNT OFF

EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT * FROM ##LRec" queryout C:\Data\L_Record.txt -t"|" -c -T'

END

I need to show 2 decimal places for Debit, Credit & Balance. However those 0.00 records are being exported as .00 in the bar delimited file.

If you are able to assist me with this I would be greatly appreciated.


Thanks.

Best Regards,

Leng

replied

Hi Leng,

When you tried Jim's suggestion, did you also change the field type of your temp table to nvarchar(21) ?

Regards,

Johan

0 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question