Solved

Unable to use REPLACE in a SQL statement

Posted on 2014-03-27
13
31 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
[X]
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
  • 6
  • 4
  • 2
13 Comments
 
LVL 34

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 34

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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 34

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 34

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Adding SQL Server Browser in after install is complete 8 56
SQL Server Express or Standard? 5 61
What Is an Error? 2 59
When are cursors useful? 8 60
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

732 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