August 18, 2015 at 10:00 am
I have the following script below that I need help with on the following. After the temp table #OpenQuotes is created, I need to update the note column where it replaces the CharReturn,LineFeed with a non space, space (CHAR(13),''), CHAR(10),' '). However what I have below is not working. Does anyone know a way around to update this temp table.
-- Report Run
select QuoteStat, isnull (SalesOperator,'Not Entered') as SalesOperator, Quote_Num,
case orgname when 'Thomas Scientific'
then lastname
else
orgname end as orgname,
qm.AddDate, qm.ExpireDate, Summary, GrandTotal, isnull (Note,' ') as note
into #OpenQuotes
from dbo.QuoteMain qm
join org as org on org.org_id = qm.org_id
where datediff (day,qm.AddDate,getdate()) < 61 and (quotestat <> 'order') and (quotestat <> 'Expired') and (quotestat <> 'Lost')
and qm.adduser not in ('CLOUSERC','SANTIAGOL','LISAM','NEGRETS','CANDICEM','SUSANA','MARTINB','LOGANJ','RICARDOM','mooneyc','istemp','martinezc','admin')
order by qm.adduser
UPDATE #OpenQuotes SET note=REPLACE(REPLACE(note, CHAR(13),''), CHAR(10),' ');
August 18, 2015 at 11:26 am
jonathanm 4432 (8/18/2015)
I have the following script below that I need help with on the following. After the temp table #OpenQuotes is created, I need to update the note column where it replaces the CharReturn,LineFeed with a non space, space (CHAR(13),''), CHAR(10),' '). However what I have below is not working. Does anyone know a way around to update this temp table.-- Report Run
select QuoteStat, isnull (SalesOperator,'Not Entered') as SalesOperator, Quote_Num,
case orgname when 'Thomas Scientific'
then lastname
else
orgname end as orgname,
qm.AddDate, qm.ExpireDate, Summary, GrandTotal, isnull (Note,' ') as note
into #OpenQuotes
from dbo.QuoteMain qm
join org as org on org.org_id = qm.org_id
where datediff (day,qm.AddDate,getdate()) < 61 and (quotestat <> 'order') and (quotestat <> 'Expired') and (quotestat <> 'Lost')
and qm.adduser not in ('CLOUSERC','SANTIAGOL','LISAM','NEGRETS','CANDICEM','SUSANA','MARTINB','LOGANJ','RICARDOM','mooneyc','istemp','martinezc','admin')
order by qm.adduser
UPDATE #OpenQuotes SET note=REPLACE(REPLACE(note, CHAR(13),''), CHAR(10),' ');
Okay, but what, exactly, IS happening? Do you get an error? If no error, then how do you know it's not working? What process did you use to make the determination? Please be very specific...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 18, 2015 at 11:28 am
I get the following error:
Argument data type ntext is invalid for argument 1 of replace function.
August 18, 2015 at 11:44 am
jonathanm 4432 (8/18/2015)
I get the following error:Argument data type ntext is invalid for argument 1 of replace function.
Okay. Now we know what the problem is. The text and ntext data types can't be dealt with using REPLACE. Try this instead, and you won't even need to do an UPDATE:
DECLARE @COMPARE_DATE AS date = DATEADD(day, -60, GETDATE());
SELECT QuoteStat, ISNULL(SalesOperator, 'Not Entered') AS SalesOperator, Quote_Num,
CASE orgname
WHEN 'Thomas Scientific' THEN lastname
ELSE orgname
END AS orgname,
qm.AddDate, qm.[ExpireDate], Summary, GrandTotal,
ISNULL(REPLACE(REPLACE(CAST(Note AS nvarchar(max)), CHAR(13),''), CHAR(10),' '), ' ') AS note
INTO #OpenQuotes
FROM dbo.QuoteMain AS qm
INNER JOIN org AS org
ON org.org_id = qm.org_id
WHERE qm.AddDate >= @COMPARE_DATE
AND quotestat NOT IN ('Expired', 'Lost', 'order')
AND qm.adduser NOT IN ('CLOUSERC','SANTIAGOL','LISAM','NEGRETS','CANDICEM','SUSANA','MARTINB','LOGANJ','RICARDOM','mooneyc','istemp','martinezc','admin')
ORDER BY qm.adduser;
There are a couple of things I changed, which include taking the function out of the WHERE clause and using a variable to go back 60 days from today, and then just compare the date in the table to the fixed date value. That way, you don't have to subject every record to the overhead of the function. I also took out the excess not equals comparisons and just put in a single NOT IN for the 3 values involved. Finally, I just prettied up the code a bit with CAPITAL letters for functions and SQL elements. Let me know if this works for you.
You should also plan to get away from the use of text, ntext, and image data types, as they are deprecated.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 18, 2015 at 12:04 pm
Thank you very muck. Worked like a charm.:-D
August 18, 2015 at 1:29 pm
jonathanm 4432 (8/18/2015)
Thank you very muck. Worked like a charm.:-D
You're welcome very muck 😀 ... Glad I could help.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply