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
- Creation of view with multiple subqueries
- SELECT with multiple subqueries to same table
- Insert into with multiple subqueries as values
- Instead of trigger to update view with multiple tables
- Creating a dynamic view with multiple tables
- Full-text Indexing for a view with multiple databases
- Query With Multiple Subqueries Efficiency?
- Alternative to subquery matching in a view with multiple matching results
- How to filter a data grid view in accordance with one or multiple drop down list selections
- Azure SQL - Create view with query through multiple databases
- 3 way join with subqueries and multiple where clause
- SQL Server 2014 - Multiple SUM/COUNT with subqueries
- SQL Server ownership chain cross schema with different owners for view selecting from multiple schemas
- SQL Server: How to improve performance for queries with multiple CTEs and subqueries in the WHERE clause
- Insert Trigger with check option on multiple tables view
- SQL View with count from multiple tables
- Stored Procedure with two input params and multiple matches and create view with in
- Creating a VIEW from multiple tables each with a different number of columns
- Error inserting multiple rows into View with INSTEAD OF trigger
- simplifying query that has multiple WITH and multiple subqueries
- SQL Server - Create View with rows from multiple result sets
- Updateable view in mssql with multiple tables and computed values
- Query to create view with concatenated column from multiple tables
- Using the results of a WITH query in multiple subqueries
- How to DROP multiple columns with a single ALTER TABLE statement in SQL Server?
- Multiple INSERT statements vs. single INSERT with multiple VALUES
- Dapper.NET and stored proc with multiple result sets
- How do you UNION with multiple CTEs?
- Using WITH NOLOCK Table Hint in Query Using View - Does it Propagate Within the View?
- How do I return multiple result sets with SqlCommand?
More questions with similar tag
- Update PHP class from mssql_ to sqlsrv_
- Unable to connect to SQL Server from Docker container (Linux image)
- Split URL using SQL and add to database
- SQL Server - Case Expression and number formatting
- SqlException: Invalid object name dbo.<tablename> when using LocalSystem as Identity
- SUM two CASE functions
- Getting error while using SQL Server database at the back end of django application
- C# SqlClient connection open in thread - SqlException not caught
- Converting string from MSSQL to MySQL
- Select Multiple XML nodes in SQL Server to append values
- Deriving start and end date from a date range for a particular record
- SQL Server Profiler Error while login
- create a resultset of calendar dates
- Specify how primary keys are generated during SSIS XML import
- SQL Server : create unique key BUT one of the columns could have several values
- Meaning of square brackets [] in MS-SQL table designer?
- SQL: how to get all the distinct characters in a column, across all rows
- Convert a row data to columns in SQL Server
- SQL Server: the maximum number of rows in table
- Hints for testing a web application which had a DB migration from SQL 2000 to 2005
- SQL query to calculate hours spent from the the other day and ending up on the next day
- Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?
- How to pass value to the data-table in the stored procedure while executing in SQL Server 2008
- SQL Server 2008 R2 - convert() works but cast() does not - any suggestions?
- Select distinct records with Min Date from two tables with Left Join
- How to run a query setting a dynamic spot date?
- convert rows/table records to json document in sql server
- How does SQL Server locking work in this scenario?
- Go MSSQL Connection
- Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion', when it *is* present in aspnetdb
Source:
stackoverflow.com