0

here is query which is similar to the one which I want to execute:

with x as(
    select ds.*, row_number() over (partition by FOO order by BAR) rn
    from datasource ds
    where datasupplierid = 3)
select column1, column2, rn from x where rn <= 2

And I'm executing it like this (from c#):

using (var con = new OracleConnection(this.connectionString)) {
    con.Open();

    OracleCommand cmd = con.CreateCommand();
    cmd.CommandText = this.command;//my query
    cmd.CommandType = CommandType.Text;

    var reader = cmd.ExecuteReader();
}

What I'm taking from this is ORA-00933: SQL command not properly ended error.

I've tried to rewrite query to this form:

select column1, column2, rn from (
    select ds.*, row_number() over (partition by FOO order by BAR) rn
    from datasource ds
    where datasupplierid = 3)
where rn <= 2

But this hadn't fixed my issue. Is there a way to execute such query?

Mateusz Rogulski
  • 7,357
  • 7
  • 44
  • 62
  • 1
    Have you checked what Oracle sees using sql profiler? There might be a simple syntax error when you construct query in c#, for example newline or blank missing. – Nikola Markovinović Apr 12 '17 at 11:33
  • @NikolaMarkovinović any hint of how can I do such check? I've tried with queries, some monitors integrated with oracle sql developer, but without a good outcome. – Mateusz Rogulski Apr 13 '17 at 12:19
  • 1
    Sorry, I'm almost exclusively with Sql Server for past two decades. I tried to find some useful information on tracing/profiling in Oracle but failed. See [this question](http://stackoverflow.com/questions/148648/oracle-is-there-a-tool-to-trace-queries-like-profiler-for-sql-server) for some info on tools and system tables. – Nikola Markovinović Apr 13 '17 at 13:13

1 Answers1

0

Change your first query to be

;with x as(
    select ds.*, row_number() over (partition by FOO order by BAR) rn
    from datasource ds
    where datasupplierid = 3)
select column1, column2, rn from x where rn <= 2

For the re-write of query you need to alias the inline view like

select column1, column2, rn from (
    select ds.*, row_number() over (partition by FOO order by BAR) rn
    from datasource ds
    where datasupplierid = 3) xxx
where rn <= 2
Rahul
  • 76,197
  • 13
  • 71
  • 125
  • Thanks for your answer Rahul, however first version (when I've added ';') before query caused 'ORA-00900: invalid SQL statement', and adding alias in the second case hasn't changed anything - same error. (of course both versions of query work fine in Oracle sql developer). Any other ideas? – Mateusz Rogulski Apr 12 '17 at 11:10