SQL: Unable to use 'with as' to save selected result

understanding sql error messages
sql server error codes
sql server return codes
sql errors
sql errors and solutions
sql error meaning
sql error code
error handling in sql server

I have an inner join result that I want to save it by using with as but received an error. I'm using snowflake.

My code:

with t as (select *
from
(select ID, PRICE from DB.TABLE1 
WHERE PRICE IS NOT NULL and ID = '1111') A
inner join 
(select ID, BID, ACCEPTED from DB.TABLE2
WHERE BID IS NOT NULL and ID = '1111') B
ON A.ID = B.ID);

Error: SQL compilation error: syntax error line 8 at position 25 unexpected ';'.

If I only run the inner join

select *
    from
    (select ID, PRICE from DB.TABLE1 
    WHERE PRICE IS NOT NULL and ID = '1111') A
    inner join 
    (select ID, BID, ACCEPTED from DB.TABLE2
    WHERE BID IS NOT NULL and ID = '1111') B
    ON A.ID = B.ID

I got this result

ID,  PRICE,ID,BIDS,ACCEPTED
1111,180,1111,200,FALSE
1111,180,1111,180,FALSE
1111,180,1111,180,FALSE
1111,180,1111,100,TRUE

Any idea why I got the error message?

You use with to essentially create an alias (called a common table expression) for the query that can then be used in that specific query. All you've done is create the alias without using it. You need something like:

with t as (select *
    from
    (select ID, PRICE from DB.TABLE1 
    WHERE PRICE IS NOT NULL and ID = '1111') A
    inner join 
    (select ID, BID, ACCEPTED from DB.TABLE2
    WHERE BID IS NOT NULL and ID = '1111') B
    ON A.ID = B.ID)
select * from t

Although obviously you'd usually do more complex work than that or else you'd just write the base query without using with

Database engine events and errors, Use a larger integer column. 245, 16, No, Conversion failed when converting the %ls value '%.*ls' to data type %ls. Troubleshoot connecting to the SQL Server Database Engine. 11/25/2019; 15 minutes to read +7; In this article. Applies to: SQL Server (all supported versions) This article lists troubleshooting techniques to use when you cannot connect to an instance of the SQL Server Database Engine on a single server.

This is too long for a comment.

WITH is syntax used to introduced a common table expression. This is an expression used within a single query. It is a lot like a subquery in the FROM clause, except that it can be referenced more than once.

So a correct usage would be:

with t as (
      select . . .
     )
select count(*)
from t;

In other words, you need to follow the with with something that uses the CTE. Otherwise, you want to store the results in a real table -- temporary or otherwise.

SQL error messages and exceptions, The upgraded database is now ready for use. Revalidating stored prepared statements failed. See next exception for details of failure. 01J06, ResultSet not� Step 2: In the left pane of the SQL Server Configuration Manager, click SQL Server Services. All services are in the right pane. Right-click on SQL Server service and you can then select Restart from the context menu. 3. Start SQL Server in Single-user Mode

To use CTE, join should be made after creating the tables.

with t as 
(select ID, PRICE from DB.TABLE1 
WHERE PRICE IS NOT NULL and ID = '1111') ,
t1 as 
(select ID, BID, ACCEPTED from DB.TABLE2
 WHERE BID IS NOT NULL and ID = '1111') 
select * 
from t
inner join
t1
on t.ID = t1.ID;

SQL Runtime Messages (SQL), Cause: SQLLIB was unable to allocate enough memory to execute the program. Action: Allocate SQL-02133 Unable to initialize process for use with threads. Unable to restore database "..because it is in use by this session." SQL Server For discussions on features exposed through tools, please post to the directly-related forum 54 8

Unable to use SQL query for filtering, Unable to use SQL query for filtering. Hi. I've been working on filters for quite some time now. I'm able to use contentFilters, queryFilters with� Use the MMC Certificates Snap-in on the client computer to install the exported certificate file. In the SQL Server Configuration Manager right-click SQL Server Native Client Configuration, and then click Properties. On the Flags tab, select Yes in the ForceEncryption box, then click OK.

Can't connect to SQL Server using 'sa' account, what I'm missing , Like @SpaceManSpiff said, don't forget to check if Mixed mode is enabled. Someone changed that setting for me and I had the same problem. Here is how to� In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM. Right-click the instance, and then click Properties. In the SQL Server Properties dialog box, click the FILESTREAM tab. Select the Enable FILESTREAM for Transact-SQL access check box.

Solved: Failed to connect to SQL Server, SQL Server is setup on a custom port and I am able to access from my local using SSMS but not through PowerBI Desktop or Enterprise Gateway. Any idea? Open Database Connectivity (ODBC) is a protocol that you use to connect an Access database to an external data source such as Microsoft SQL Server. Typically, you use file data sources (also called DSN files) to add a connection string, in which case, the FILEDSN keyword is used on the connection string, or stored in the registry, in which case

Comments
  • "To use CTE, join should be made after creating the tables." This statement is not true, this linked website allows to change between three SQL database systems, MySQL, PostgreSQL and SQLite all three worked like expected with a JOIN in the CTE.