score:2

Accepted answer

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;

score:1

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;

More questions

More questions with similar tag