Searching an SQL Server database with Substring

Question

I have a query that extracts only two columns and trims one of the columns to only the media name. This is the query for that:

   Select
   [Object],  
   CASE WHEN MsgID = '61' THEN SUBSTRING(Parms,35,6) END AS [MEDIA]
   from JnlDataSection

The results from this is:

Object
---------
061 STATEMENTS

MEDIA
---------
X01180
X01181

As you can see there are multiple medias for one object. What I want is a manual query in which I can just modify it with the object name and search one object with all of its respective medias and vice versa.

This is the query I came up with:

  Select
  [Object],  
  CASE WHEN MsgID = '61' THEN SUBSTRING(Parms,35,6) END AS [MEDIA]
  WHERE [OBJECT] = '061 STATEMENTS'
  from JnlDataSection

However I am receiving this error:

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'from'.

Please Note: I'm using SQL Server Management Studio 2008.

UPDATE

After reading comments, I tried this query:

Select
  [Object],  
  CASE WHEN MsgID = '61' THEN SUBSTRING(Parms,35,6) END AS [MEDIA]
 from JnlDataSection
 WHERE [MEDIA] = 'X05219'

It gave me this error:

Msg 207, Level 16, State 1, Line 5
Invalid column name 'MEDIA'.

How can I fix it?

The order of clauses needs to be SELECT...FROM...WHERE...GROUP BY...HAVING...ORDER BY. You have your WHERE before your FROM. — JNK
– JNK, Commented Jul 15, 2013 at 13:40
Select [Object], CASE WHEN MsgID = '61' THEN SUBSTRING(Parms,35,6) END AS [MEDIA] from JnlDataSection WHERE [MEDIA] = 'X05219' — omar K
– omar K, Commented Jul 15, 2013 at 13:43
@JNK I get this error when I try to run this. Msg 207, Level 16, State 1, Line 5 Invalid column name 'MEDIA'. — omar K
– omar K, Commented Jul 15, 2013 at 13:43
Because you can't filter on a case expression that you define in the SELECT. You need to either reproduce the case in the WHERE or redefine it in a table expression or CROSS APPLY — JNK
– JNK, Commented Jul 15, 2013 at 13:45

Community · Accepted Answer · 2017-05-23 11:56:32Z

1

To expand on @JNK's suggestion, if you want to compare against the calculated MEDIA column, here are your options:

Reproduce the CASE expression in the WHERE clause:

SELECT
  [Object],  
  CASE WHEN MsgID = '61' THEN SUBSTRING(Parms, 35, 6) END AS [MEDIA]
FROM JnlDataSection
WHERE CASE WHEN MsgID = '61' THEN SUBSTRING(Parms, 35, 6) END = 'X05219'
;

Redefine the query as a common table expression:

WITH JnlDataSectionMedia AS (
  SELECT
    [Object],  
    CASE WHEN MsgID = '61' THEN SUBSTRING(Parms, 35, 6) END AS [MEDIA]
  FROM JnlDataSection
)
SELECT *
FROM JnlDataSectionMedia
WHERE [MEDIA] = 'X05219'
;

or a (normal) subselect:

SELECT *
FROM (
  SELECT
    [Object],  
    CASE WHEN MsgID = '61' THEN SUBSTRING(Parms, 35, 6) END AS [MEDIA]
  FROM JnlDataSection
) AS s
WHERE [MEDIA] = 'X05219'
;

Use CROSS APPLY:

SELECT
  j.[Object],
  x.[MEDIA]
FROM JnlDataSection AS j
CROSS APPLY (
  SELECT CASE WHEN MsgID = '61' THEN SUBSTRING(Parms, 35, 6) END
) AS x ([MEDIA])
WHERE x.[MEDIA] = 'X05219'
;

You can also define your query as a view:

CREATE VIEW JnlDataSectionMedia
AS
SELECT
  [Object],  
  CASE WHEN MsgID = '61' THEN SUBSTRING(Parms, 35, 6) END AS [MEDIA]
FROM JnlDataSection
;

After that, you can call it at any time like this:

SELECT [Object], [MEDIA]
FROM JnlDataSectionMedia
;

You can filter on either of the columns too, when necessary:

SELECT [Object], [MEDIA]
FROM JnlDataSectionMedia
WHERE [MEDIA] = 'X05219'
;

edited May 23, 2017 at 11:56

CommunityBot

11 silver badge

answered Jul 15, 2013 at 17:52

Andriy M

78k18 gold badges100 silver badges157 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

omar K Over a year ago

Thank you for your help, I have solved this issue. I am having trouble with something else regarding this same query, if you could take a look please. stackoverflow.com/questions/17658995/…

Collectives™ on Stack Overflow

Searching an SQL Server database with Substring

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related