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

  • Thread starter Thread starter fans hou
  • Start date Start date
F

fans hou

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 OracleDataReader, It returns no row!
Here is the SQL,Why?
Is that means C# have any claim with SQL statement?
------------------
SELECT
X.YM,X.CUST_NAME,X.ODR_NO,X.STYLE_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_date,0,6) as
ym,C.CUST_NAME,A.ODR_NO,D.STYLE_NO,D.STYLE_NAME,NVL(SUM(A.odr_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.STYLE_NO AND
B.CUST_NO=C.CUST_NO AND A.FAC_NO='R0' AND A.DEPT_NO='R2' AND
substr(pro_date,0,6)='200309' GROUP BY
substr(A.pro_date,0,6),A.odr_no,C.CUST_NAME,D.STYLE_NO,D.STYLE_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
 
Hi,

Why don't you use DataAdapter.Fill() 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,
 
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.
 
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.STYLE_NO from ODR_PROM a,ODRM b,CUSTOM c,STYLEM d
where a.ODR_NO=b.ODR_NO AND b.STYLE_NO=d.STYLE_NO AND
b.CUST_NO=c.CUST_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.STYLE_NO,SUM(a.ODR_QTY) as ODR_QTY,sum(a.TOT_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.STYLE_NO AND b.CUST_NO=c.CUST_NO AND
a.FAC_NO='R0' AND a.DEPT_NO='R2' GROUP BY a.ODR_NO,d.STYLE_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!
 
Back
Top