Convert number stored as text to number

Question

Post reply

Convert number stored as text to number

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply

eddiewillcox SSChasing Mays Points: 637 More actions · Answer 1

funbi - Friday, January 27, 2017 2:03 AM
Do you still have else '' in your case statements?

Yes I do

funbi SSCarpal Tunnel Points: 4992 More actions · Answer 2

eddiewillcox - Friday, January 27, 2017 2:05 AM
funbi - Friday, January 27, 2017 2:03 AM
Do you still have else '' in your case statements?
Yes I do

Compare the output from the two select statements, do you need to changed your else to return a number?

DECLARE @test-2 TABLE (laction int, sNote varchar(1000))

insert into @test-2 select 1, 'LI-FU-4DV-DCTN-EACH-0059-Monaco Couch Fully Upholstered Exotic Leather 4 Seater, line 1, changed from 21 595.00 to 22 000.00'

insert into @test-2 select 2, 'LI-FU-4DV-DCTN-EACH-0059-Monaco Couch Fully Upholstered Exotic Leather 4 Seater, line 1, changed from 21 595.00 to 22 000.00'

SELECT

case lAction when 2 then cast(replace(cast(replace(ltrim(rtrim((LEFT(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13),CHARINDEX(' to ',right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13)))))),' ','') as decimal(18,6)) ,' ','') as decimal(18,6)) else '' end AS OLD_PRICE,

case lAction when 2 then cast(replace(cast(replace(ltrim(rtrim((right(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13),LEN(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13))-CHARINDEX('to ',right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13))-2)))),' ','') as decimal(18,6)),' ','') as decimal(18,6)) else '' end AS NEW_PRICE

from @test-2

SELECT

case lAction when 2 then cast(replace(cast(replace(ltrim(rtrim((LEFT(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13),CHARINDEX(' to ',right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13)))))),' ','') as decimal(18,6)) ,' ','') as decimal(18,6)) else 0 end AS OLD_PRICE,

case lAction when 2 then cast(replace(cast(replace(ltrim(rtrim((right(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13),LEN(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13))-CHARINDEX('to ',right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13))-2)))),' ','') as decimal(18,6)),' ','') as decimal(18,6)) else 0 end AS NEW_PRICE

from @test-2

eddiewillcox SSChasing Mays Points: 637 More actions · Answer 3

funbi - Friday, January 27, 2017 2:23 AM
eddiewillcox - Friday, January 27, 2017 2:05 AM
funbi - Friday, January 27, 2017 2:03 AM
Do you still have else '' in your case statements?
Yes I do
Compare the output from the two select statements, do you need to changed your else to return a number?
DECLARE @test-2 TABLE (laction int, sNote varchar(1000))
insert into @test-2 select 1, 'LI-FU-4DV-DCTN-EACH-0059-Monaco Couch Fully Upholstered Exotic Leather 4 Seater, line 1, changed from 21 595.00 to 22 000.00'
insert into @test-2 select 2, 'LI-FU-4DV-DCTN-EACH-0059-Monaco Couch Fully Upholstered Exotic Leather 4 Seater, line 1, changed from 21 595.00 to 22 000.00'
SELECT
case lAction when 2 then cast(replace(cast(replace(ltrim(rtrim((LEFT(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13),CHARINDEX(' to ',right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13)))))),' ','') as decimal(18,6)) ,' ','') as decimal(18,6)) else '' end AS OLD_PRICE,
case lAction when 2 then cast(replace(cast(replace(ltrim(rtrim((right(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13),LEN(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13))-CHARINDEX('to ',right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13))-2)))),' ','') as decimal(18,6)),' ','') as decimal(18,6)) else '' end AS NEW_PRICE
from @test-2
SELECT
case lAction when 2 then cast(replace(cast(replace(ltrim(rtrim((LEFT(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13),CHARINDEX(' to ',right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13)))))),' ','') as decimal(18,6)) ,' ','') as decimal(18,6)) else 0 end AS OLD_PRICE,
case lAction when 2 then cast(replace(cast(replace(ltrim(rtrim((right(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13),LEN(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13))-CHARINDEX('to ',right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13))-2)))),' ','') as decimal(18,6)),' ','') as decimal(18,6)) else 0 end AS NEW_PRICE
from @test-2

Hi funbi. Using this exact script returns the results I need in both select statements. However, as soon as I try to apply this to my query, I get "Error converting data type varchar to numeric"

funbi SSCarpal Tunnel Points: 4992 More actions · Answer 4

eddiewillcox - Friday, January 27, 2017 2:38 AM
funbi - Friday, January 27, 2017 2:23 AM
eddiewillcox - Friday, January 27, 2017 2:05 AM
funbi - Friday, January 27, 2017 2:03 AM
Do you still have else '' in your case statements?
Yes I do
Compare the output from the two select statements, do you need to changed your else to return a number?
DECLARE @test-2 TABLE (laction int, sNote varchar(1000))
insert into @test-2 select 1, 'LI-FU-4DV-DCTN-EACH-0059-Monaco Couch Fully Upholstered Exotic Leather 4 Seater, line 1, changed from 21 595.00 to 22 000.00'
insert into @test-2 select 2, 'LI-FU-4DV-DCTN-EACH-0059-Monaco Couch Fully Upholstered Exotic Leather 4 Seater, line 1, changed from 21 595.00 to 22 000.00'
SELECT
case lAction when 2 then cast(replace(cast(replace(ltrim(rtrim((LEFT(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13),CHARINDEX(' to ',right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13)))))),' ','') as decimal(18,6)) ,' ','') as decimal(18,6)) else '' end AS OLD_PRICE,
case lAction when 2 then cast(replace(cast(replace(ltrim(rtrim((right(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13),LEN(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13))-CHARINDEX('to ',right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13))-2)))),' ','') as decimal(18,6)),' ','') as decimal(18,6)) else '' end AS NEW_PRICE
from @test-2
SELECT
case lAction when 2 then cast(replace(cast(replace(ltrim(rtrim((LEFT(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13),CHARINDEX(' to ',right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13)))))),' ','') as decimal(18,6)) ,' ','') as decimal(18,6)) else 0 end AS OLD_PRICE,
case lAction when 2 then cast(replace(cast(replace(ltrim(rtrim((right(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13),LEN(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13))-CHARINDEX('to ',right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13))-2)))),' ','') as decimal(18,6)),' ','') as decimal(18,6)) else 0 end AS NEW_PRICE
from @test-2
Hi funbi. Using this exact script returns the results I need in both select statements. However, as soon as I try to apply this to my query, I get "Error converting data type varchar to numeric"

I get that error when running the first statement 🙂 What's the rest of your query?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

funbi - Friday, January 27, 2017 2:53 AM
eddiewillcox - Friday, January 27, 2017 2:38 AM
funbi - Friday, January 27, 2017 2:23 AM
eddiewillcox - Friday, January 27, 2017 2:05 AM
funbi - Friday, January 27, 2017 2:03 AM
Do you still have else '' in your case statements?
Yes I do
Compare the output from the two select statements, do you need to changed your else to return a number?
DECLARE @test-2 TABLE (laction int, sNote varchar(1000))
insert into @test-2 select 1, 'LI-FU-4DV-DCTN-EACH-0059-Monaco Couch Fully Upholstered Exotic Leather 4 Seater, line 1, changed from 21 595.00 to 22 000.00'
insert into @test-2 select 2, 'LI-FU-4DV-DCTN-EACH-0059-Monaco Couch Fully Upholstered Exotic Leather 4 Seater, line 1, changed from 21 595.00 to 22 000.00'
SELECT
case lAction when 2 then cast(replace(cast(replace(ltrim(rtrim((LEFT(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13),CHARINDEX(' to ',right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13)))))),' ','') as decimal(18,6)) ,' ','') as decimal(18,6)) else '' end AS OLD_PRICE,
case lAction when 2 then cast(replace(cast(replace(ltrim(rtrim((right(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13),LEN(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13))-CHARINDEX('to ',right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13))-2)))),' ','') as decimal(18,6)),' ','') as decimal(18,6)) else '' end AS NEW_PRICE
from @test-2
SELECT
case lAction when 2 then cast(replace(cast(replace(ltrim(rtrim((LEFT(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13),CHARINDEX(' to ',right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13)))))),' ','') as decimal(18,6)) ,' ','') as decimal(18,6)) else 0 end AS OLD_PRICE,
case lAction when 2 then cast(replace(cast(replace(ltrim(rtrim((right(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13),LEN(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13))-CHARINDEX('to ',right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13))-2)))),' ','') as decimal(18,6)),' ','') as decimal(18,6)) else 0 end AS NEW_PRICE
from @test-2
Hi funbi. Using this exact script returns the results I need in both select statements. However, as soon as I try to apply this to my query, I get "Error converting data type varchar to numeric"
I get that error when running the first statement 🙂 What's the rest of your query?

Try the code I posted up here ^^ without the CONVERT, and examine the output. The advantage of CROSS APPLY over a single gobbledegook statement is that you can perform your substrings and look at the pieces VERY EASILY.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

eddiewillcox SSChasing Mays Points: 637 More actions · Answer 6

ChrisM@Work - Friday, January 27, 2017 3:01 AM
funbi - Friday, January 27, 2017 2:53 AM
eddiewillcox - Friday, January 27, 2017 2:38 AM
funbi - Friday, January 27, 2017 2:23 AM
eddiewillcox - Friday, January 27, 2017 2:05 AM
funbi - Friday, January 27, 2017 2:03 AM
Do you still have else '' in your case statements?
Yes I do
Compare the output from the two select statements, do you need to changed your else to return a number?
DECLARE @test-2 TABLE (laction int, sNote varchar(1000))
insert into @test-2 select 1, 'LI-FU-4DV-DCTN-EACH-0059-Monaco Couch Fully Upholstered Exotic Leather 4 Seater, line 1, changed from 21 595.00 to 22 000.00'
insert into @test-2 select 2, 'LI-FU-4DV-DCTN-EACH-0059-Monaco Couch Fully Upholstered Exotic Leather 4 Seater, line 1, changed from 21 595.00 to 22 000.00'
SELECT
case lAction when 2 then cast(replace(cast(replace(ltrim(rtrim((LEFT(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13),CHARINDEX(' to ',right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13)))))),' ','') as decimal(18,6)) ,' ','') as decimal(18,6)) else '' end AS OLD_PRICE,
case lAction when 2 then cast(replace(cast(replace(ltrim(rtrim((right(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13),LEN(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13))-CHARINDEX('to ',right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13))-2)))),' ','') as decimal(18,6)),' ','') as decimal(18,6)) else '' end AS NEW_PRICE
from @test-2
SELECT
case lAction when 2 then cast(replace(cast(replace(ltrim(rtrim((LEFT(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13),CHARINDEX(' to ',right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13)))))),' ','') as decimal(18,6)) ,' ','') as decimal(18,6)) else 0 end AS OLD_PRICE,
case lAction when 2 then cast(replace(cast(replace(ltrim(rtrim((right(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13),LEN(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13))-CHARINDEX('to ',right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13))-2)))),' ','') as decimal(18,6)),' ','') as decimal(18,6)) else 0 end AS NEW_PRICE
from @test-2
Hi funbi. Using this exact script returns the results I need in both select statements. However, as soon as I try to apply this to my query, I get "Error converting data type varchar to numeric"
I get that error when running the first statement 🙂 What's the rest of your query?
Try the code I posted up here ^^ without the CONVERT, and examine the output. The advantage of CROSS APPLY over a single gobbledegook statement is that you can perform your substrings and look at the pieces VERY EASILY.

The CROSS APPLY query does give the results I need. I just need to try and figure out how to use it in my query to see if it works there.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

eddiewillcox - Friday, January 27, 2017 4:08 AM
ChrisM@Work - Friday, January 27, 2017 3:01 AM
funbi - Friday, January 27, 2017 2:53 AM
eddiewillcox - Friday, January 27, 2017 2:38 AM
funbi - Friday, January 27, 2017 2:23 AM
eddiewillcox - Friday, January 27, 2017 2:05 AM
funbi - Friday, January 27, 2017 2:03 AM
Do you still have else '' in your case statements?
Yes I do
Compare the output from the two select statements, do you need to changed your else to return a number?
DECLARE @test-2 TABLE (laction int, sNote varchar(1000))
insert into @test-2 select 1, 'LI-FU-4DV-DCTN-EACH-0059-Monaco Couch Fully Upholstered Exotic Leather 4 Seater, line 1, changed from 21 595.00 to 22 000.00'
insert into @test-2 select 2, 'LI-FU-4DV-DCTN-EACH-0059-Monaco Couch Fully Upholstered Exotic Leather 4 Seater, line 1, changed from 21 595.00 to 22 000.00'
SELECT
case lAction when 2 then cast(replace(cast(replace(ltrim(rtrim((LEFT(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13),CHARINDEX(' to ',right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13)))))),' ','') as decimal(18,6)) ,' ','') as decimal(18,6)) else '' end AS OLD_PRICE,
case lAction when 2 then cast(replace(cast(replace(ltrim(rtrim((right(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13),LEN(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13))-CHARINDEX('to ',right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13))-2)))),' ','') as decimal(18,6)),' ','') as decimal(18,6)) else '' end AS NEW_PRICE
from @test-2
SELECT
case lAction when 2 then cast(replace(cast(replace(ltrim(rtrim((LEFT(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13),CHARINDEX(' to ',right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13)))))),' ','') as decimal(18,6)) ,' ','') as decimal(18,6)) else 0 end AS OLD_PRICE,
case lAction when 2 then cast(replace(cast(replace(ltrim(rtrim((right(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13),LEN(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13))-CHARINDEX('to ',right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13))-2)))),' ','') as decimal(18,6)),' ','') as decimal(18,6)) else 0 end AS NEW_PRICE
from @test-2
Hi funbi. Using this exact script returns the results I need in both select statements. However, as soon as I try to apply this to my query, I get "Error converting data type varchar to numeric"
I get that error when running the first statement 🙂 What's the rest of your query?
Try the code I posted up here ^^ without the CONVERT, and examine the output. The advantage of CROSS APPLY over a single gobbledegook statement is that you can perform your substrings and look at the pieces VERY EASILY.
The CROSS APPLY query does give the results I need. I just need to try and figure out how to use it in my query to see if it works there.

Post what you have.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

eddiewillcox SSChasing Mays Points: 637 More actions · Answer 8

ChrisM@Work - Friday, January 27, 2017 4:12 AM
eddiewillcox - Friday, January 27, 2017 4:08 AM
ChrisM@Work - Friday, January 27, 2017 3:01 AM
funbi - Friday, January 27, 2017 2:53 AM
eddiewillcox - Friday, January 27, 2017 2:38 AM
funbi - Friday, January 27, 2017 2:23 AM
eddiewillcox - Friday, January 27, 2017 2:05 AM
funbi - Friday, January 27, 2017 2:03 AM
Do you still have else '' in your case statements?
Yes I do
Compare the output from the two select statements, do you need to changed your else to return a number?
DECLARE @test-2 TABLE (laction int, sNote varchar(1000))
insert into @test-2 select 1, 'LI-FU-4DV-DCTN-EACH-0059-Monaco Couch Fully Upholstered Exotic Leather 4 Seater, line 1, changed from 21 595.00 to 22 000.00'
insert into @test-2 select 2, 'LI-FU-4DV-DCTN-EACH-0059-Monaco Couch Fully Upholstered Exotic Leather 4 Seater, line 1, changed from 21 595.00 to 22 000.00'
SELECT
case lAction when 2 then cast(replace(cast(replace(ltrim(rtrim((LEFT(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13),CHARINDEX(' to ',right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13)))))),' ','') as decimal(18,6)) ,' ','') as decimal(18,6)) else '' end AS OLD_PRICE,
case lAction when 2 then cast(replace(cast(replace(ltrim(rtrim((right(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13),LEN(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13))-CHARINDEX('to ',right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13))-2)))),' ','') as decimal(18,6)),' ','') as decimal(18,6)) else '' end AS NEW_PRICE
from @test-2
SELECT
case lAction when 2 then cast(replace(cast(replace(ltrim(rtrim((LEFT(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13),CHARINDEX(' to ',right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13)))))),' ','') as decimal(18,6)) ,' ','') as decimal(18,6)) else 0 end AS OLD_PRICE,
case lAction when 2 then cast(replace(cast(replace(ltrim(rtrim((right(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13),LEN(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13))-CHARINDEX('to ',right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13))-2)))),' ','') as decimal(18,6)),' ','') as decimal(18,6)) else 0 end AS NEW_PRICE
from @test-2
Hi funbi. Using this exact script returns the results I need in both select statements. However, as soon as I try to apply this to my query, I get "Error converting data type varchar to numeric"
I get that error when running the first statement 🙂 What's the rest of your query?
Try the code I posted up here ^^ without the CONVERT, and examine the output. The advantage of CROSS APPLY over a single gobbledegook statement is that you can perform your substrings and look at the pieces VERY EASILY.
The CROSS APPLY query does give the results I need. I just need to try and figure out how to use it in my query to see if it works there.
Post what you have.

This is the exact query I use without the CAST, REPLACE or CONVERT. The 2 lines in bold is where my problems are

select c.sLocation,c.sCompName, a.sTranNo, a.dtdatetime, case a.ldoctype when 3 then a.sLinkedTranNo else '' end as LinkedTran, case a.ldoctype when 3 then (select top(1)sPayMCode from tblPOSReceipts g where g.stranno = a.sTranNo) else '' end As RefundPayMeth, case a.ldoctype when 3 then (select orderDate from tblOrders e where e.stranno = case a.ldoctype when 3 then a.sLinkedTranNo else null end) else '' end AS LinkedTranDate, case a.ldoctype when 3 then f.ItemNum else '' end as "CN Item", a.sRequestUser,a.sAuthUser ,b.sDescr, a.lDOCType,a.snote,a.sreason,d.Notes, case a.lAction when 2 then (ltrim(rtrim((LEFT(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-12),CHARINDEX(' to ',right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13))))))) else null end AS OLD_PRICE, case a.lAction when 2 then (ltrim(rtrim((right(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13),LEN(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13))-CHARINDEX('to ',right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13))-2))))) else null end AS NEW_PRICE from tblPOSAuthList a left outer join tblPOSAuthActions b on a.lAction = b.lAction left outer join tblCompanyInfo c on a.lLocationNo = c.lCompID left outer join tblOrders d on a.sTranNo = d.sTranNo left join tblorderdetail f on d.lcompid = f.lcompid and d.orderid = f.orderid and d.ordertype = f.ordertype where dtDateTime between CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101) and CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))),DATEADD(mm,1,GETDATE())),101) and lDOCType in (1,2,3) and f.Quantity <> 0 and a.lAction = 2 order by c.sLocation, b.sDescr,a.dtDateTime

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Thanks Eddie. Have a play with this. Order by the two prices, ascending and descending, and do a "stare and compare" - you're looking for values which won't convert to a decimal number. If you don't find any, then try putting a CAST to decimal around the two price columns in the SELECT list.
Don't forget the questions at the end.

SELECT

c.sLocation, c.sCompName, a.sTranNo, a.dtdatetime,

case a.ldoctype when 3 then a.sLinkedTranNo else '' end as LinkedTran,

case a.ldoctype when 3 then (select top(1) sPayMCode from tblPOSReceipts g where g.stranno = a.sTranNo) else '' end As RefundPayMeth,

case a.ldoctype when 3 then (select orderDate from tblOrders e where e.stranno = case a.ldoctype when 3 then a.sLinkedTranNo else null end) else '' end AS LinkedTranDate,

case a.ldoctype when 3 then f.ItemNum else '' end as "CN Item", a.sRequestUser,a.sAuthUser ,b.sDescr, a.lDOCType,a.snote,a.sreason,d.Notes,

--case a.lAction when 2 then (ltrim(rtrim((LEFT(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-12),CHARINDEX(' to ',right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13))))))) else null end AS OLD_PRICE,

--case a.lAction when 2 then (ltrim(rtrim((right(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13),LEN(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13))-CHARINDEX('to ',right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13))-2))))) else null end AS NEW_PRICE

OLD_PRICE = REPLACE(x3.FromNumber,' ',''),

NEW_PRICE = REPLACE(x3.ToNumber,' ','')

FROM tblPOSAuthList a

left outer join tblPOSAuthActions b

on a.lAction = b.lAction

left outer join tblCompanyInfo c

on a.lLocationNo = c.lCompID

left outer join tblOrders d

on a.sTranNo = d.sTranNo

left join tblorderdetail f

on d.lcompid = f.lcompid

and d.orderid = f.orderid

and d.ordertype = f.ordertype

CROSS APPLY (SELECT BitWithNumbersIn = SUBSTRING(sNote,CHARINDEX('changed from ',sNote)+13,8000)) x1

CROSS APPLY (SELECT p2 = CHARINDEX(' to ',x1.BitWithNumbersIn)) x2

CROSS APPLY (SELECT FromNumber = LEFT(BitWithNumbersIn,x2.p2-1), ToNumber = SUBSTRING(BitWithNumbersIn,x2.p2+4,8000)) x3

where dtDateTime between CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101) and CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))),DATEADD(mm,1,GETDATE())),101)

and lDOCType in (1,2,3)

and f.Quantity <> 0

and a.lAction = 2

order by OLD_PRICE

--c.sLocation, b.sDescr ,a.dtDateTime

-- Q1 is dtDateTime really a varchar?

-- Q2 which table is dtDateTime from? and lDOCType? and sNote?

-- Q3 those two date expressions - are they to pick up the first and last date of the current month?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

eddiewillcox SSChasing Mays Points: 637 More actions · Answer 10

ChrisM@Work - Friday, January 27, 2017 5:45 AM
Thanks Eddie. Have a play with this. Order by the two prices, ascending and descending, and do a "stare and compare" - you're looking for values which won't convert to a decimal number. If you don't find any, then try putting a CAST to decimal around the two price columns in the SELECT list.
Don't forget the questions at the end.
SELECT
c.sLocation, c.sCompName, a.sTranNo, a.dtdatetime,
case a.ldoctype when 3 then a.sLinkedTranNo else '' end as LinkedTran,
case a.ldoctype when 3 then (select top(1) sPayMCode from tblPOSReceipts g where g.stranno = a.sTranNo) else '' end As RefundPayMeth,
case a.ldoctype when 3 then (select orderDate from tblOrders e where e.stranno = case a.ldoctype when 3 then a.sLinkedTranNo else null end) else '' end AS LinkedTranDate,
case a.ldoctype when 3 then f.ItemNum else '' end as "CN Item", a.sRequestUser,a.sAuthUser ,b.sDescr, a.lDOCType,a.snote,a.sreason,d.Notes,
--case a.lAction when 2 then (ltrim(rtrim((LEFT(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-12),CHARINDEX(' to ',right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13))))))) else null end AS OLD_PRICE,
--case a.lAction when 2 then (ltrim(rtrim((right(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13),LEN(right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13))-CHARINDEX('to ',right(sNote,LEN(sNote)-CHARINDEX('changed from ',sNote)-13))-2))))) else null end AS NEW_PRICE
OLD_PRICE = REPLACE(x3.FromNumber,' ',''),
NEW_PRICE = REPLACE(x3.ToNumber,' ','')
FROM tblPOSAuthList a
left outer join tblPOSAuthActions b
on a.lAction = b.lAction
left outer join tblCompanyInfo c
on a.lLocationNo = c.lCompID
left outer join tblOrders d
on a.sTranNo = d.sTranNo
left join tblorderdetail f
on d.lcompid = f.lcompid
and d.orderid = f.orderid
and d.ordertype = f.ordertype
CROSS APPLY (SELECT BitWithNumbersIn = SUBSTRING(sNote,CHARINDEX('changed from ',sNote)+13,8000)) x1
CROSS APPLY (SELECT p2 = CHARINDEX(' to ',x1.BitWithNumbersIn)) x2
CROSS APPLY (SELECT FromNumber = LEFT(BitWithNumbersIn,x2.p2-1), ToNumber = SUBSTRING(BitWithNumbersIn,x2.p2+4,8000)) x3
where dtDateTime between CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101) and CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))),DATEADD(mm,1,GETDATE())),101)
and lDOCType in (1,2,3)
and f.Quantity <> 0
and a.lAction = 2
order by OLD_PRICE
--c.sLocation, b.sDescr ,a.dtDateTime
-- Q1 is dtDateTime really a varchar?
-- Q2 which table is dtDateTime from? and lDOCType? and sNote?
-- Q3 those two date expressions - are they to pick up the first and last date of the current month?

Thanks Chris. Running the query as is, gives the data back, but any value in both old and new price columns more than 1000 has a space as the 1000 seperator. Replace does not replace the space in the middle, but rather at the beginning (tested this by doing this - OLD_PRICE = REPLACE(x3.FromNumber,' ',',')

Adding Cast then just brings my old error back (Error converting data type varchar to decimal)

Q1 - dtDateTime is a datetime field. The date is stored as yyyy-MM-dd and for that reason the convert(varchar(25)) in the where clause
Q2 - All of those fields are from tblPOSAuthList
Q3 - Yes. The 2 date expressions are to get the first and last days of the month

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

It looks like it's not a space in the middle then...it's a different character.

Put this

SELECT ASCII('')

into a query window. Copy the "space" from your results panel and paste it between the two single quotes, then run the statement. A space will return 32.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

If dtDateTime is a datetime field then it's stored internally as a number - but displays according to local settings. Are you absolutely sure it's a datetime? If it is, then lose the CONVERT to VARCHAR.

Here's some very standard date arithmetic for obtaining the first and last date of the current month:

SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)

SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())+1,-1)

-- you can figure out how it works from this (the 0 is converted into the first day of DATETIME which is 19000101)

SELECT DATEDIFF(MONTH,0,GETDATE())

SELECT DATEADD(MONTH,1404,0)

SELECT DATEADD(MONTH,1405,-1)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

eddiewillcox SSChasing Mays Points: 637 More actions · Answer 13

ChrisM@Work - Friday, January 27, 2017 6:09 AM
It looks like it's not a space in the middle then...it's a different character.
Put this
SELECT ASCII('')
into a query window. Copy the "space" from your results panel and paste it between the two single quotes, then run the statement. A space will return 32.

It returns 160

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 14

eddiewillcox - Friday, January 27, 2017 6:15 AM
ChrisM@Work - Friday, January 27, 2017 6:09 AM
It looks like it's not a space in the middle then...it's a different character.
Put this
SELECT ASCII('')
into a query window. Copy the "space" from your results panel and paste it between the two single quotes, then run the statement. A space will return 32.
It returns 160

Next, use this in your code:

OLD_PRICE = REPLACE(x3.FromNumber,CHAR(160),''),

NEW_PRICE = REPLACE(x3.ToNumber,CHAR(160),'')

If the spaces disappear, try the convert to decimal.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

eddiewillcox SSChasing Mays Points: 637 More actions · Answer 15

ChrisM@Work - Friday, January 27, 2017 6:31 AM
eddiewillcox - Friday, January 27, 2017 6:15 AM
ChrisM@Work - Friday, January 27, 2017 6:09 AM
It looks like it's not a space in the middle then...it's a different character.
Put this
SELECT ASCII('')
into a query window. Copy the "space" from your results panel and paste it between the two single quotes, then run the statement. A space will return 32.
It returns 160
Next, use this in your code:
OLD_PRICE = REPLACE(x3.FromNumber,CHAR(160),''),
NEW_PRICE = REPLACE(x3.ToNumber,CHAR(160),'')
If the spaces disappear, try the convert to decimal.

Thank you Chris. This solved the issue.