Solved

Unable to use REPLACE in a SQL statement

Posted on 2014-03-27
13
29 Views
Last Modified: 2016-06-04
This sql is a snippet from a much larger sql statement.  
The sql runs fine with what you see below.
however, when I update the 2 occurrances of feeder_id to "REPLACE(feeder_id,' ','')"...it gives me errors.  
I tried using mycust.feeder_id and t1.feeder_id.  Neither worked.
What am I doing wrong?  I should note that there is another table that is used in the same sql that has a field name of feeder_id also.  but it was given an alias.
<more sql here>
(SELECT SUM(NumCust) as feeder_cust, feeder_id 
    FROM mycust
	WHERE date IN 
	   (SELECT coalesce
		(max(date),
		    (SELECT min(date) FROM mycust)
		) 
	    FROM mycust WHERE Date <= @end_date_time)
GROUP BY feeder_id) t1 ON 
<more sql here>

Open in new window

0
Comment
Question by:ToolTimeGang
  • 6
  • 4
  • 2
13 Comments
 
LVL 33

Expert Comment

by:ste5an
ID: 39960081
What SQL Server version do use?

Post the entire statement. Otherwise: Use a CTE to use REPLACE() before your statement.
0
 

Author Comment

by:ToolTimeGang
ID: 39960246
Thank you for getting back to me so quickly.  
I would like to try first using the CTE.  But I'm not sure how I would do it?  (I haven't used CTEs before)
0
 
LVL 33

Assisted Solution

by:ste5an
ste5an earned 334 total points
ID: 39960260
It would look like

WITH Replaced AS (
  SELECT *, REPLACE(feeder_id,' ','') AS replaced_feeder_id 
  FROM mycust
  )
<your sql>

Open in new window


and use Replaced instead of mycust in your entire SQL statement.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:ToolTimeGang
ID: 39960307
Wow.  I can't believe I haven't used CTEs before!  

Is this right?

WITH Replaced AS (
  SELECT SUM(NumCust) as feeder_cust, REPLACE(feeder_id,' ','') AS replaced_feeder_id
  FROM mycust
  )

(SELECT Replaced.feeder_cust, Replaced.replaced_feeder_id
    FROM Replaced
      WHERE date IN
         (SELECT coalesce
            (max(date),
                (SELECT min(date) FROM mycust)
            )
          FROM mycust WHERE Date <= @end_date_time)
GROUP BY Replaced.replaced_feeder_id) t1
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 166 total points
ID: 39960378
I strongly prefer CROSS APPLY just to assign an alias to a calc'd column, like so:


<more sql here>
(SELECT SUM(NumCust) as feeder_cust, feeder_id2
    FROM mycust
    CROSS APPLY (
        SELECT REPLACE(feeder_id,' ','') AS feeder_id2
    ) AS ca1
      WHERE date IN
         (SELECT coalesce
            (max(date),
                (SELECT min(date) FROM mycust)
            )
          FROM mycust WHERE Date <= @end_date_time)
GROUP BY feeder_id2) t1 ON
<more sql here>
0
 

Author Comment

by:ToolTimeGang
ID: 39961513
My brain seems to understand the CROSS APPLY more easily than the CTE for some reason.
I ran it as Scott suggested, but it is barking at me "invalid column name 'feeder_id'.  (the one we are using the replace on)
0
 
LVL 33

Accepted Solution

by:
ste5an earned 334 total points
ID: 39961564
Take a look at this simple sample:

USE AdventureWorks2012;

-- Original SQL Statement
SELECT  DISTINCT
        JobTitle
FROM    HumanResources.Employee;

-- Using CTE
WITH    Replaced
          AS ( SELECT   REPLACE(JobTitle, 'Account', 'ACCOUNT') AS JobTitle
               FROM     HumanResources.Employee
             )
    SELECT  DISTINCT
            JobTitle
    FROM    Replaced;

-- Using CROSS APPLY
SELECT  DISTINCT
        Replaced.JobTitle
FROM    HumanResources.Employee
        CROSS APPLY ( SELECT    REPLACE(JobTitle, 'Account', 'ACCOUNT')
                    ) Replaced ( JobTitle );

Open in new window


Using CROSS APPLY means that you need to change your SQL's structure. Using the CTE only means chaning the referenced table name.

When you need to reference the replaced value in more than one (sub-)query, then the CTE is more compact, cause you need to repeat the CROSS APPLY.

Also compare the actual query plan. In this sample they are the same, but this must not be true for your case.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39961646
>> but it is barking at me "invalid column name 'feeder_id' <<

So feeder_id is not in the mycust table?

Then the SQL needs re-done more extensively than just this tiny piece of it.
0
 

Author Comment

by:ToolTimeGang
ID: 39961941
steStan, your simple example helped me.  I am getting closer now.  I am down to 2 syntax errors.  It was barking about a semi colon, so I added one based on some examples I saw online.  But it still doesn't like it.  syntax error near ';' and another syntax error near t1.

;WITH Replaced AS (
   SELECT REPLACE(feeder_id,' ','') AS replaced_feeder_id
   FROM mycust
                   )
   (SELECT Sum(NumCust) as feeder_cust, Replaced.replaced_feeder_id
       FROM Replaced WHERE date IN 
          (SELECT coalesce(max(date),(SELECT min(date) FROM Replaced))
           FROM Replaced WHERE Date <= @end_date_time)
      GROUP BY Replaced.replaced.feeder_id) t1 ON

Open in new window

0
 

Author Comment

by:ToolTimeGang
ID: 39962094
BINGO!
I scooted the CTE to the very top.  Made a few more tweeks and here it is!
I like CTE!  Thank you everyone!
;WITH Replaced AS (
   SELECT NumCust,Date,REPLACE(feeder_id,' ','') AS replaced_feeder_id
   FROM myCust
   		  )

<more sql here>
(SELECT Sum(NumCust) as feeder_cust, replaced_feeder_id
     FROM Replaced WHERE date IN
      (SELECT coalesce(max(date),(select min(date) FROM Replaced)) 
       FROM Replaced WHERE Date <= @end_date_time)
   GROUP BY replaced_feeder_id) t1 ON 
		
    REPLACE(outage_duration.feeder,' ','') = t1.replaced_feeder_id

<more sql here>

Open in new window

0
 
LVL 33

Expert Comment

by:ste5an
ID: 39962114
You need also to include the original columns:

WITH Replaced AS 
	(
	   SELECT	*, 
				REPLACE(feeder_id, ' ', '') AS replaced_feeder_id
	   FROM		mycust
	)
	SELECT	Sum(NumCust) as feeder_cust, 
			replaced_feeder_id
    FROM	Replaced 
	WHERE [date] IN (	SELECT	COALESCE(MAX([date]), ( SELECT MIN([date]) FROM Replaced ))
						FROM	Replaced 
						WHERE	[date] <= @end_date_time
					)
    GROUP BY Replaced.replaced.feeder_id
	
--	) t1 ON

Open in new window

Now we see the problem: The CTE definition must placed before your original SQL statement.

btw,
SELECT	COALESCE(MAX([date]), ( SELECT MIN([date]) FROM Replaced ))
FROM	Replaced 
WHERE	[date] <= @end_date_time

Open in new window

makes not that much sense. First of all, there is always a [date], so the COALESCE() is not needed. Also you can use an = instead of the IN comparison for working with that value.

And for your semantics: [date] is a reserved word and absolute meaningless as column name. Also comparing a date value with a date/time value ([date] <= @end_date_time) makes not much sense.
0
 

Author Comment

by:ToolTimeGang
ID: 39962152
Points well taken.  I will fix those.  Thank you so much for pointing these out to me!
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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

830 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