locked
Common Table Expressions (CTE) with SQLDataSource SelectCommand?? RRS feed

  • Question

  • User-947668712 posted

    Trying to set a datasource's SelectCommand equal to a SQL query that starts with "WITH" CTEs.

    ie

    WITH a1 AS (SELECT colA FROM table A),

    a2 AS (SELECT colA FROM table B)

    SELECT * FROM a1, a2

    The SQL itself is fine.  I have verified that it works in TOAD.  However, is this even supported?  I don't get an error message; I just get nothing in return.  And when I changed the code & just assigned it a simple SELECT query instead, it returned results so it's definitely the SQL I'm passing.

    Any thoughts?  Thanks!

    Tuesday, October 22, 2013 6:37 PM

Answers

  • User-947668712 posted

    Switched connection string from "Provider=MSDAORA" to "Provider=OraOLEDB.Oracle" and now it works like a charm!  Sorry this ended up in the wrong forum, but hopefully it may end up helping someone else down the road!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, October 23, 2013 12:24 PM

All replies

  • User-821857111 posted

    There's no reason that I can think of why this shouldn't be supported. The SqlDataSource doesn't care what kind of SQL you give it. It doesn't validate it or anything like that. It just attempts to get it executed by SQL Server. If SQL Server generates a resultset, the SqlDataSource will stuff that into a DataTable (by default). 

    Wednesday, October 23, 2013 1:54 AM
  • User1508394307 posted

    Just run this

    <%@ Page  %>
    
    <html>
      <head>
        <title>ASP.NET Example</title>
    </head>
    <body>
        <form id="form1" runat="server">
    
          <asp:SqlDataSource
              id="SqlDataSource1"
              runat="server" 
              DataSourceMode="DataSet"
              ConnectionString="Data Source=Test;Initial Catalog=Test;Integrated Security=True;"
              SelectCommand="WITH a1 AS (SELECT colA FROM table A),
    
    a2 AS (SELECT colA FROM table B)
    
    SELECT * FROM a1, a2">
          </asp:SqlDataSource>
    
          <asp:GridView
              id="GridView1"
              runat="server" 
              DataSourceID="SqlDataSource1">
          </asp:GridView>
    
        </form>
      </body>
    </html>

    It works.

    As Mike already said the SqlDataSource doesn't care what kind of SQL you give it. 

    Wednesday, October 23, 2013 3:03 AM
  • User-947668712 posted

    Tried putting it directly in the sqldatasource like above & it still returned no results.  No error or anything, just no results.

    But when I remove the CTE's from the query, I have no problem.  Ugh.  Here is the exact query I am running (it's built dynamically).  Can you see anything that .NET would not like for some reason?

    WITH COM AS (SELECT DISTINCT multiplier AS "COM" FROM price_sheet_header_pricing WHERE header_id = 6 AND pricing_cat = 'COM' AND pricing_type = 'Default'),

    SPC AS (SELECT DISTINCT multiplier AS "SPC" FROM price_sheet_header_pricing WHERE header_id = 6 AND pricing_cat = 'SPC' AND pricing_type = 'Default')

    SELECT * FROM COM, SPC

    Wednesday, October 23, 2013 10:41 AM
  • User-947668712 posted

    Well, it looks like my OleDbCommand/DataReader doesn't like the SQL either, so it's not the SQLDataSource.  I probably need to take this topic to another area.

    Wednesday, October 23, 2013 11:00 AM
  • User1508394307 posted

    I'm not sure how you used SQLDataSource with OleDbCommand and why, but either move to SqlCommand or put your query into the stored procedure and call it from your controls.

    Wednesday, October 23, 2013 11:34 AM
  • User753101303 posted

    Hi,

    Could it be caused by " you are using inside an attribute which is itself enclosed within " ?

    What is trying to use AS [COM] and AS [SPC] instead...

    According to your last post it seems you have an error. It's always better to tell which error you have as usually it greatly narrow down the issue.

     

     

    Wednesday, October 23, 2013 11:42 AM
  • User-947668712 posted

    I didn't, but the sqldatasource is using an oledb provider connection.  I just used an OleDBCommand separately to test the query string.

    Wednesday, October 23, 2013 11:58 AM
  • User-947668712 posted

    I don't get an error; I just get 0 rows returned.

    It looks like there may be an issue with the OleDB Provider supporting CTE's I think.

    Wednesday, October 23, 2013 12:04 PM
  • User-947668712 posted

    Switched connection string from "Provider=MSDAORA" to "Provider=OraOLEDB.Oracle" and now it works like a charm!  Sorry this ended up in the wrong forum, but hopefully it may end up helping someone else down the road!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, October 23, 2013 12:24 PM