• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 37
  • Last Modified:

Unable to use REPLACE in a SQL statement

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
ToolTimeGang
Asked:
ToolTimeGang
  • 6
  • 4
  • 2
3 Solutions
 
ste5anSenior DeveloperCommented:
What SQL Server version do use?

Post the entire statement. Otherwise: Use a CTE to use REPLACE() before your statement.
0
 
ToolTimeGangAuthor Commented:
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
 
ste5anSenior DeveloperCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ToolTimeGangAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
ToolTimeGangAuthor Commented:
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
 
ste5anSenior DeveloperCommented:
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
 
Scott PletcherSenior DBACommented:
>> 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
 
ToolTimeGangAuthor Commented:
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
 
ToolTimeGangAuthor Commented:
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
 
ste5anSenior DeveloperCommented:
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
 
ToolTimeGangAuthor Commented:
Points well taken.  I will fix those.  Thank you so much for pointing these out to me!
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now