You can do it in a single call from php to mysql if you use a stored procedure: Example calls mysql> call category_hier(1); +——–+—————+—————+———————-+——-+ | cat_id | category_name | parent_cat_id | parent_category_name | depth | +——–+—————+—————+———————-+——-+ | 1 | Location | NULL | NULL | 0 | | 3 | USA | 1 | Location … Read more
This is the syntax to insert into a table from a CTE: — CREATE TABLE tmp ( tmp_id NUMBER(10) ); INSERT INTO tmp( tmp_id ) WITH cte AS ( SELECT 1 AS tmp_id FROM dual ) SELECT tmp_id FROM cte;
AFAIK, this isn’t supported by the object model. However, LINQ supports a method to execute a query (strangly enough called DataContext.ExecuteQuery). Looks like you can use that to call a arbitrary piece of SQL and map it back to LINQ. You won’t be SQL free because of the embedded SQL, but you won’t have to … Read more
The mystery is solved: the behavior I was observing is a known bug. I sent the same original post to a PostgreSQL-specific list and got this answer: This is a bug :-(. The parse analysis code seems to think that WITH can only be attached to the top level or a leaf-level SELECT within a … Read more
EDIT: previously advised solution works only for MSSQL. Therefore I am adding an Oracle solution. I am keeping the original answer below. I thought of one more solution (though the one provided by Justin Cave still seems a bit better) – using temporary tables. Here is how it may look like CREATE GLOBAL TEMPORARY TABLE … Read more
Views can be indexed but CTE can’t. So this is one important point. CTE work excellent on tree hierarchyi.e. recursive Also, consider views when dealing with complex queries. Views being a physical object on database (but does not store data physically) and can be used on multiple queries, thus provide flexibility and centralized approach. CTE, … Read more
Assuming you are using MSSQL 2012, you can use Offset and Fetch which cleans up server-side paging greatly. We’ve found performance is fine, and in most cases better. As far as getting the total column count, just use the window function below inline…it will not include the limits imposed by ‘offset’ and ‘fetch’. For Row_Number, … Read more
If you do not require them in 2 different queries, you can try ;with query as ( Select Row_Number() over (Order By UserID ASC) as TableRowNum, FirstName, LastName From Users ), totalCount AS ( SELECT COUNT(1) Total FROM query ) Select query.*, Total from query, totalCount where TableRowNum between 1 and 25 Order By TableRowNum … Read more
Sure: CREATE TABLE t AS WITH some_data AS ( SELECT 1 as some_value FROM dual UNION ALL SELECT 2 FROM dual ) SELECT * FROM some_data
Use a comma to separate CTEs ;WITH SomeClause1 AS ( SELECT …. ) , SomeClause2 AS ( SELECT …. )