Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

sql 2005 query

Posted on 2008-06-16
3
185 Views
Last Modified: 2010-03-20
i have a query written when i select the query
i get the correct result
0044775019      638.42      VO      2008-06-06 00:00:00      CK      419116

but when i concetanate the query instead of 638.42 it is coming as 638.41 can any one tell me why is it happening like that my query is

and the result for that query is
D0010044775019        0000419116000006384120080606                        
SELECT  'D' + '001' + banknbr + SPACE(7)
      + ( CASE WHEN adjgdoctype = 'VC' THEN 'V'
                 ELSE ' '
            end ) + RIGHT(REPLICATE('0', 10)
                         + convert(VARCHAR(10), RTRIM(adjgrefnbr)), 10)
        + RIGHT(REPLICATE('0', 10)
              + CONVERT(varchar(10), CONVERT(int, ( CASE WHEN adjgdoctype = 'VC'
                                                          THEN 0
                                                         ELSE curyadjdamt * 100
                                                     end ))), 10)
        + REPLACE(CONVERT(VARCHAR, adjgdocdate, 102), '.', '') + SPACE(30)
        + NAME AS details
FROM #positivepay

Open in new window

0
Comment
Question by:romeiovasu
3 Comments
 
LVL 39

Accepted Solution

by:
appari earned 500 total points
ID: 21798620
what is the datatype of curyadjdamt? is it is float? try to change it to numeric or decimal or try the following query


SELECT  'D' + '001' + banknbr + SPACE(7)
      + ( CASE WHEN adjgdoctype = 'VC' THEN 'V'
                 ELSE ' '
            end ) + RIGHT(REPLICATE('0', 10)
                         + convert(VARCHAR(10), RTRIM(adjgrefnbr)), 10)
        + RIGHT(REPLICATE('0', 10)
              + CONVERT(varchar(10), CONVERT(int, ( CASE WHEN adjgdoctype = 'VC'
                                                          THEN 0
                                                         ELSE convert(decimal(10,2) , curyadjdamt) * 100
                                                     end ))), 10)
        + REPLACE(CONVERT(VARCHAR, adjgdocdate, 102), '.', '') + SPACE(30)
        + NAME AS details
FROM #positivepay
0
 
LVL 2

Expert Comment

by:mayno224
ID: 21798676
there's several nested command to get the value of 'curyadjdamt'.  i suggest breaking the query down to its simplest form then add to it step by step until you find where 638.41 is being returned.  for example, start with....

select curyadjdamt * 100 from positivepay

if the above returns 63842, then add the next 'step' to the query...

CASE WHEN adjgdoctype = 'VC' THEN 0 ELSE curyadjdamt * 100

select adjgdoctype, CASE WHEN adjgdoctype = 'VC' THEN 0 ELSE curyadjdamt * 100 from positivepay

keep adding 'steps' until you find where 638.41 (or '63841') is returned.  the last step that you entered will be where the problem lies.

hope this helps!
0
 

Author Comment

by:romeiovasu
ID: 21798780
thank you.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Syntax 5 39
SQL Stored Proc - Performance Enhancement 15 54
Accessing variables in MySQL query 4 27
Substring works but need to tweak it 14 12
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question