Why this SQL can't get data?I run it in oracle,it returns more than 800 datas!

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • fans hou
    #1

    Why this SQL can't get data?I run it in oracle,it returns more than 800 datas!

    Hi,I need your help!
    My SQL runs in oracle and it returns more than 800 rows,but when I try
    to bind it into a DataGrid with OracleDataReade r, It returns no row!
    Here is the SQL,Why?
    Is that means C# have any claim with SQL statement?
    ------------------
    SELECT
    X.YM,X.CUST_NAM E,X.ODR_NO,X.ST YLE_NAME,X.ODR_ QTY,X.TOT_QTY,Z .TOT,'odr_no
    ='||X.ODR_NO||' &'||'style_no=' ||X.STYLE_NO AS ARG FROM (select
    substr(A.pro_da te,0,6) as
    ym,C.CUST_NAME, A.ODR_NO,D.STYL E_NO,D.STYLE_NA ME,NVL(SUM(A.od r_qty),0)
    odr_qty,nvl(sum (tot_pro_qty),0 ) tot_qty from odr_prom A,ODRM B,CUSTOM
    C,STYLEM D where A.ODR_NO=B.ODR_ NO AND B.STYLE_NO=D.ST YLE_NO AND
    B.CUST_NO=C.CUS T_NO AND A.FAC_NO='R0' AND A.DEPT_NO='R2' AND
    substr(pro_date ,0,6)='200309' GROUP BY
    substr(A.pro_da te,0,6),A.odr_n o,C.CUST_NAME,D .STYLE_NO,D.STY LE_NAME) X,
    (SELECT ODR_NO,NVL(SUM( TOT_PRO_QTY),0) AS TOT FROM ODR_PROM WHERE
    FAC_NO='R0' AND DEPT_NO='R2' GROUP BY ODR_NO) Z WHERE X.ODR_NO=Z.ODR_ NO
    ----------------------
    Thanks very much!
    fans


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Tags: None
    • Ignacio Machin \( .NET/ C# MVP \)
      #2
      Re: Why this SQL can't get data?I run it in oracle,it returns more than 800 datas!

      Hi,

      Why don't you use DataAdapter.Fil l() to fill a dataset and later bind this
      dataset to the grid.
      this will allow you to see if the data retrival from the DB is working or if
      it's the binding you are using the one that is wicked.

      Cheers,

      --
      Ignacio Machin,
      ignacio.machin AT dot.state.fl.us
      Florida Department Of Transportation


      "fans hou" <fans.h@pyzs.co m.cn> wrote in message
      news:uNDCcCKlDH A.1728@TK2MSFTN GP11.phx.gbl...[color=blue]
      > Hi,I need your help!
      > My SQL runs in oracle and it returns more than 800 rows,but when I try
      > to bind it into a DataGrid with OracleDataReade r, It returns no row!
      > Here is the SQL,Why?
      > Is that means C# have any claim with SQL statement?
      > ------------------
      > SELECT
      > X.YM,X.CUST_NAM E,X.ODR_NO,X.ST YLE_NAME,X.ODR_ QTY,X.TOT_QTY,Z .TOT,'odr_no
      > ='||X.ODR_NO||' &'||'style_no=' ||X.STYLE_NO AS ARG FROM (select
      > substr(A.pro_da te,0,6) as
      > ym,C.CUST_NAME, A.ODR_NO,D.STYL E_NO,D.STYLE_NA ME,NVL(SUM(A.od r_qty),0)
      > odr_qty,nvl(sum (tot_pro_qty),0 ) tot_qty from odr_prom A,ODRM B,CUSTOM
      > C,STYLEM D where A.ODR_NO=B.ODR_ NO AND B.STYLE_NO=D.ST YLE_NO AND
      > B.CUST_NO=C.CUS T_NO AND A.FAC_NO='R0' AND A.DEPT_NO='R2' AND
      > substr(pro_date ,0,6)='200309' GROUP BY
      > substr(A.pro_da te,0,6),A.odr_n o,C.CUST_NAME,D .STYLE_NO,D.STY LE_NAME) X,
      > (SELECT ODR_NO,NVL(SUM( TOT_PRO_QTY),0) AS TOT FROM ODR_PROM WHERE
      > FAC_NO='R0' AND DEPT_NO='R2' GROUP BY ODR_NO) Z WHERE X.ODR_NO=Z.ODR_ NO
      > ----------------------
      > Thanks very much!
      > fans
      >
      >
      > *** Sent via Developersdex http://www.developersdex.com ***
      > Don't just participate in USENET...get rewarded for it![/color]


        Comment

        • Mark Coffman
          #3
          Re: Why this SQL can't get data?I run it in oracle,it returns more than 800 datas!

          I ran into a similar problem today. A try {} catch {} around my connect call
          revealed that I had failed authentication to the server. It appeared to
          return an empty set.
          [color=blue]
          > My SQL runs in oracle and it returns more than 800 rows,but when I try
          > to bind it into a DataGrid with OracleDataReade r, It returns no row!
          > Here is the SQL,Why?
          > Is that means C# have any claim with SQL statement?[/color]


            Comment

            • fans hou
              #4
              Re: Why this SQL can't get data?I run it in oracle,it returns more than 800 datas!

              Hi,Ignacio Machin \( .NET/ C# MVP \)
              I do think of that yesterday too!
              and I tried to bind it with the DataSet!
              but it still returns a empty DataSet!
              and no errors!

              To Mark Coffman:
              I think My problem is diffrent to yours!
              I tried to simplify my SQL,I cut some Computer-field.
              I found,when I remove the "SUM(field) " & "Group By" sub-statement,It
              works!
              like this:
              -----------------
              select a.ODR_NO,d.STYL E_NO from ODR_PROM a,ODRM b,CUSTOM c,STYLEM d
              where a.ODR_NO=b.ODR_ NO AND b.STYLE_NO=d.ST YLE_NO AND
              b.CUST_NO=c.CUS T_NO AND a.FAC_NO='R0' AND a.DEPT_NO='R2'
              ------------
              --this returns more than 800 rows!
              But if I keep the "SUM(field) " & "Group By" like this:
              -------------
              select a.ODR_NO,d.STYL E_NO,SUM(a.ODR_ QTY) as ODR_QTY,sum(a.T OT_PRO_QTY)
              as TOT_PRO_QTY from ODR_PROM a,ODRM b,CUSTOM c,STYLEM d where
              a.ODR_NO=b.ODR_ NO AND b.STYLE_NO=d.ST YLE_NO AND b.CUST_NO=c.CUS T_NO AND
              a.FAC_NO='R0' AND a.DEPT_NO='R2' GROUP BY a.ODR_NO,d.STYL E_NO
              -----------------
              then it returns empty DataSet!

              why?
              how strange!
              I had try a SQL with "Group By" in other c# produce,and they works
              fine,why this not?

              tks!

              *** Sent via Developersdex http://www.developersdex.com ***
              Don't just participate in USENET...get rewarded for it!

                Comment

                Previous template Next
                Working...