CODEWITHSUNDEEP
sqlsql-server
David Vandenbroucke
David Vandenbroucke

Reputation: 114

Creation of view with multiple subqueries

I'm experiencing a little problem concerning a view. I've learned most SQL things on my own, but this one is a bit too much for the moment, but if any has a fix, could you please add a small explanation or link to it so I can learn it without copy/pasting a solution.

Here is my query:

CREATE VIEW [HMVIEW] AS
SELECT (select testid
from Alert, Action 
where alert.ID = Action.alertId) as col1,
select (SELECT SUBSTRING(Action.action, 2, CHARINDEX(' ', action.action) - 1)
from action)) as col2;

it errors on the creat view as it says it has to be the only statement in the batch, but the second select (for col2) is the main reason. It works when only taking col1 but I need a view of the 2 columns together.

For future people who might end up reading this: I forgot to add that the columns should join together. I want the col1 (a testId) to have the right actions to it. For example: 0001|stoptestbyid

Thanks in advance & with kind regards,

David Vandenbroucke

Upvotes: 0

Views: 474

Answers (2)

Dusan
Dusan

Reputation: 831

You are defining column alias to subquery in select statement, so you only need one select keyword. You need to delete the other select keyword. So that two subquerys are representing two columns with the alias col1 and col2. See example below:

CREATE VIEW [HMVIEW] AS
SELECT (select testid
from Alert, Action 
where alert.ID = Action.alertId) as col1,
(SELECT SUBSTRING(Action.action, 2, CHARINDEX(' ', action.action) - 1)
from action) as col2;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Here is your query:

SELECT (select testid
        from Alert, Action 
        where alert.ID = Action.alertId
       ) as col1,
select (SELECT SUBSTRING(Action.action, 2, CHARINDEX(' ', action.action) - 1)
from action)) as col2;

The first select is probably going to return more than one row -- that is one error. The second select is just dangling out there. You could fix this as:

SELECT (select testid
        from Alert, Action 
        where alert.ID = Action.alertId
       ) as col1,
       (SELECT SUBSTRING(Action.action, 2, CHARINDEX(' ', action.action) - 1)
        from action) as col2;

But, in all likelihood, you want:

select testid, SUBSTRING(Action.action, 2, CHARINDEX(' ', action.action) - 1) as col2
from Alert join
     Action 
     on alert.ID = Action.alertId;

Upvotes: 2

Related Questions