CODEWITHSUNDEEP
c#asp.netsqloracle-database
Ben Hill
Ben Hill

Reputation: 55

ASP.net OleDbCommand Oracle SQL not returning any results with valid query

I am trying to read the results from an SQL query using OleDbCommand in ASP.net with C# codebehind. I am using the following Oracle SQL statement which is valid and works properly within Oracle SQL Developer:

WITH "bis" AS
    (SELECT v.name AS "vendor", 
    SUM(CASE WHEN i.bi_rating=5 THEN 1 ELSE 0 END) AS "bi5", 
    SUM(CASE WHEN i.bi_rating=6 THEN 1 ELSE 0 END) AS "bi6", 
    SUM(CASE WHEN i.bi_rating=7 THEN 1 ELSE 0 END) AS "bi7" 
    FROM incident_tbl i, vendor_tbl v 
    WHERE i.vendor_code=v.vendor_code 
    GROUP BY v.name 
    ORDER BY v.name) 
SELECT DISTINCT 
    "bis"."vendor", 
    "bis"."bi5", 
    "bis"."bi6", 
    "bis"."bi7", 
    ("bis"."bi5"+"bis"."bi6"+"bis"."bi7") AS "total" 
FROM "bis" 
WHERE "vendor"!='[No Vendor]' 
ORDER BY "total" DESC

This produces a correct result of 92 rows. However when I run it on my ASP.net page, it returns zero rows. I even isolated the query into a test method to try and sort it out: (con is an already-existing working connection string)

protected void test()
    {
        OleDbCommand sql = new OleDbCommand("WITH \"bis\" AS (SELECT v.name AS \"vendor\", SUM(CASE WHEN i.bi_rating=5 THEN 1 ELSE 0 END) AS \"bi5\", SUM(CASE WHEN i.bi_rating=6 THEN 1 ELSE 0 END) AS \"bi6\", SUM(CASE WHEN i.bi_rating=7 THEN 1 ELSE 0 END) AS \"bi7\" FROM incident_tbl i, vendor_tbl v WHERE i.vendor_code=v.vendor_code GROUP BY v.name ORDER BY v.name) SELECT DISTINCT \"bis\".\"vendor\", \"bis\".\"bi5\", \"bis\".\"bi6\", \"bis\".\"bi7\", (\"bis\".\"bi5\"+\"bis\".\"bi6\"+\"bis\".\"bi7\") AS \"total\" FROM incident_tbl i, \"bis\" WHERE \"vendor\"!='[No Vendor]' ORDER BY \"total\" DESC", con);
        try
        {
            if (con.State == ConnectionState.Closed)
                con.Open();

            OleDbDataReader reader = sql.ExecuteReader();

            while (reader.Read())
            {
                // Show on a temporary label for testing
                lblDebug.Text += "<br />New row... vendor: " + reader["vendor"].ToString();
            }
        }
        catch (Exception ex)
        {
            lblDebug.Text += "<br />ERROR WITH TEST METHOD: " + ex;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
                con.Close();
        }
    }

The thing is, no exceptions are thrown, there are just zero results. Any ideas?

Upvotes: 1

Views: 1033

Answers (1)

MiMo
MiMo

Reputation: 11953

Try with

SELECT DISTINCT  
  vendor,  
  bi5,  
  bi6,  
  bi7,  
  (bi5+bi6+bi7) total  
FROM  
  (SELECT v.name vendor,  
   SUM(CASE WHEN i.bi_rating=5 THEN 1 ELSE 0 END) bi5,  
   SUM(CASE WHEN i.bi_rating=6 THEN 1 ELSE 0 END) bi6,  
   SUM(CASE WHEN i.bi_rating=7 THEN 1 ELSE 0 END) bi7  
   FROM incident_tbl i, vendor_tbl v  
   WHERE i.vendor_code=v.vendor_code  
   GROUP BY v.name  
   ORDER BY v.name) bis
WHERE vendor!='[No Vendor]'  
ORDER BY total DESC

Upvotes: 2

Related Questions