need help with sql query against oracle database

• Patrick.I

  Hall of Fame

  Points: 3105

  More actions

  February 8, 2011 at 4:44 am

  #388801

  Hi All,

  I have a .NET app and the app uses a Oracle Db.So i'm using Oracle .Net Provider for .NET

  I have a problem with the sql below as it won't parse in my query builder in Visual Studio.

  It seems i'm not doing things correctly and i'm sure i'm in the right place to ask about ORACLE with .NET

  Any help will be appreciated

  Thanks

  The problem is with this 2 lines here but when i add the 2 parameters :pStatus and :psysuser in the select it parses but then no rows are returned:

  AND (upper(SYSUSER) LIKE CONCAT(upper(:psysuser), '%'))

  AND (nvl(STATUS, 'NEW') LIKE CONCAT(decode(:pStatus, 'All', '', :pStatus), '%'))

  SELECT DT, CUST_ACCOUNT_NO, TOTALSPENT, ACCOUNTNAME,

  SYSDT, SYSUSER, HASPAY, :pStatus AS EXPR1,

  :psysuser AS EXPR2

  FROM myTable

  WHERE (CUST_ACCOUNT_NO LIKE CONCAT(:pCustAccountNo, '%'))

  AND (upper(ACCOUNTNAME) LIKE CONCAT(upper(:pAccountName), '%'))

  AND (TOTALSPENT >= nvl(:pTotalSpent, 0))

  AND (DT = :pDT)

  AND (HASPAY LIKE CONCAT(decode(:phasPay, 'All', '', :phasPay), '%'))

  AND (upper(SYSUSER) LIKE CONCAT(upper(:psysuser), '%'))

  AND (nvl(STATUS, 'NEW') LIKE CONCAT(decode(:pStatus, 'All', '', :pStatus), '%'))

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  February 8, 2011 at 7:13 am

  #1283655

  Patrick.I (2/8/2011)

  ---

  Hi All,

  I have a .NET app and the app uses a Oracle Db.So i'm using Oracle .Net Provider for .NET

  I have a problem with the sql below as it won't parse in my query builder in Visual Studio.

  It seems i'm not doing things correctly and i'm sure i'm in the right place to ask about ORACLE with .NET

  Any help will be appreciated

  Thanks

  The problem is with this 2 lines here but when i add the 2 parameters :pStatus and :psysuser in the select it parses but then no rows are returned:

  AND (upper(SYSUSER) LIKE CONCAT(upper(:psysuser), '%'))

  AND (nvl(STATUS, 'NEW') LIKE CONCAT(decode(:pStatus, 'All', '', :pStatus), '%'))[/b]

  Query is using bind-variables.

  Are you populating :psysuser and :pstatus bind variables?

  Have you tested the query hard-coding actual values instead of using bind-variables? Once you are sure query works fine - with hard-coded values - then replace with bind variables.

  _____________________________________
  Pablo (Paul) Berzukov

  Author of Understanding Database Administration available at Amazon and other bookstores.

  Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.