CODEWITHSUNDEEP
sql-serversql-server-2008-r2sql-server-2012
Matt Williamson
Matt Williamson

Reputation: 7095

TSQL to extract column data in a where clause

I have the following query that extracts some column data and also extracts everything between 2 xml tags in a column. 

Use Database
DECLARE @First VARCHAR(15), @Second VARCHAR(15)
SET @First = '<InstrumentID>'
SET @Second = '</InstrumentID>'

SELECT out_interface_id, msg_id , 
SUBSTRING(xml_msg, CHARINDEX(@First, xml_msg) + LEN(@First), 
     CHARINDEX(@Second, xml_msg) - 
     CHARINDEX(@First, xml_msg) - 
     LEN(@First)) as InstrumentID,
msg_type, xml_msg, CAST(xml_msg AS XML) as [Quick_XML], 
date_received,status, last_modified, environment,
transaction_closed_date
from Table1 with (nolock)
where msg_type in ('BALMIS','ACCTV21') 
and date_received >= CAST(GETDATE() -1 as DATE)
and date_received < CAST(GETDATE() as DATE)
order by date_received desc

Now, I want to be able to add to the where clause: and InstrumentID = 'ABC123'

This should be simple but I just can't figure it out.

Upvotes: 0

Views: 142

Answers (2)

gmiley
gmiley

Reputation: 6604

You cannot reference an alias from the SELECT list within your WHERE clause. You would need to recreate the same string result in the same manor used in your SELECT list:

Use Database
DECLARE @First VARCHAR(15), @Second VARCHAR(15)
SET @First = '<InstrumentID>'
SET @Second = '</InstrumentID>'

SELECT out_interface_id, msg_id , 
SUBSTRING(xml_msg, CHARINDEX(@First, xml_msg) + LEN(@First), 
     CHARINDEX(@Second, xml_msg) - 
     CHARINDEX(@First, xml_msg) - 
     LEN(@First)) as InstrumentID,
msg_type, xml_msg, CAST(xml_msg AS XML) as [Quick_XML], 
date_received,status, last_modified, environment,
transaction_closed_date
from Table1 with (nolock)
where msg_type in ('BALMIS','ACCTV21') 
and date_received >= CAST(GETDATE() -1 as DATE)
and date_received < CAST(GETDATE() as DATE)
and SUBSTRING(xml_msg, CHARINDEX(@First, xml_msg) + LEN(@First), 
     CHARINDEX(@Second, xml_msg) - 
     CHARINDEX(@First, xml_msg) - 
     LEN(@First)) = 'ABC123'
order by date_received desc;

Also, when dealing with XML, it might be better to work with it as XML rather than as a string. I am more familiar with Oracle in this case, but here is a SO entry that touches on the subject: Parse XML in SQL Server

Upvotes: 0

Sean Lange
Sean Lange

Reputation: 33581

You can do this fairly easily. And be careful throwing that NOLOCK hint around. It brings a lot more issues to the table than most people realize. http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/

select *
from 
(
    SELECT out_interface_id, msg_id , 
    SUBSTRING(xml_msg, CHARINDEX(@First, xml_msg) + LEN(@First), 
         CHARINDEX(@Second, xml_msg) - 
         CHARINDEX(@First, xml_msg) - 
         LEN(@First)) as InstrumentID,
    msg_type, xml_msg, CAST(xml_msg AS XML) as [Quick_XML], 
    date_received,status, last_modified, environment,
    transaction_closed_date
    from Table1 with (nolock)
    where msg_type in ('BALMIS','ACCTV21') 
    and date_received >= CAST(GETDATE() -1 as DATE)
    and date_received < CAST(GETDATE() as DATE)
) x
where x.InstrumentID = 'ABC123'
order by date_received desc

Upvotes: 1

Related Questions