Solved

Unable to use REPLACE in a SQL statement

Posted on 2014-03-27
13
26 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 32

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 32

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
 

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:ScottPletcher
ScottPletcher 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 32

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:ScottPletcher
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 32

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

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.

Join & Write a Comment

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

759 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now