select T1.*,
case
when T2.sumQty =0 then 'FALSE' else 'TRUE' end as RELEASABLE
from Mytable T1
inner join
(select ID,magasin ,sum([Qty Available]) as sumQty from Mytable group by ID,magasin) T2
on T1.ID =T2.ID and T1.magasin= T2.magasin
Regards,
Stefan Uzunov
MCTS: .NET Framework 3.5 ASP.NET Applications
pluciorx
Member
53 Points
69 Posts
DataSet Filling column regarding others.
Aug 26, 2009 06:58 PM|LINK
HI
I Have a DataSet filled like below::
And i have to mark field Releasable with TRUE if for all parts in ID exist quantity in QTY Available at least = 1 in any of magasines.
Finally Data Set contains around 9000 rows
there is no constant number of records for each ID regarding parts ( Some ID can have 2 some 3 and some 10 ... )
each part can have max 5 magasines and min 1
At the end DataSet Should look:
I'm struggling with that two days.
and any help would be appreciated
<div style="overflow: hidden; position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px;" id="_mcePaste">103131
logic dataset algorithm
Steelymar
All-Star
15283 Points
2239 Posts
Re: DataSet Filling column regarding others.
Aug 26, 2009 07:06 PM|LINK
if you get this date from SQL server... is better to do this in SQL statement
Stefan Uzunov
MCTS: .NET Framework 3.5 ASP.NET Applications
pluciorx
Member
53 Points
69 Posts
Re: DataSet Filling column regarding others.
Aug 26, 2009 07:15 PM|LINK
Yes it is from SQL / How to do that ?
Steelymar
All-Star
15283 Points
2239 Posts
Re: DataSet Filling column regarding others.
Aug 26, 2009 07:19 PM|LINK
this is the way in Dataset:
//DataSet ds = new DataSet(); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { string Where = "ID =" + ds.Tables[0].Rows[i]["ID"] + " and magasin = " + ds.Tables[0].Rows[i]["magasin"]; ds.Tables[0].Rows[i]["RELEASABLE"] = (int)ds.Tables[0].Compute("sum(QtyAvailable)", Where) > 0 ? "TRUE" : "FALSE"; }Stefan Uzunov
MCTS: .NET Framework 3.5 ASP.NET Applications
Steelymar
All-Star
15283 Points
2239 Posts
Re: DataSet Filling column regarding others.
Aug 26, 2009 07:25 PM|LINK
in SQL:
Stefan Uzunov
MCTS: .NET Framework 3.5 ASP.NET Applications
pluciorx
Member
53 Points
69 Posts
Re: DataSet Filling column regarding others.
Aug 26, 2009 07:36 PM|LINK
wow!
i will be able to test that tomorrow but i have question
It Should be placed just after Data set is filled ?
I'm filling it like :
With your Code it should be like:
cmd = "SELECT ..... dap1.SelectCommand = cmd dap1.Fill(Data, "Dane") FunCREATEreleasableCOL() // some code here to create RELEASABLE column; for (int i = 0; i < Data.Tables[0].Rows.Count; i++) { string Where = "ID =" + Data.Tables[0].Rows[i]["ID"] + " and magasin = " + Data.Tables[0].Rows[i]["magasin"]; Data.Tables[0].Rows[i]["RELEASABLE"] = (int)Data.Tables[0].Compute("sum(QtyAvailable)", Where) > 0 ? "TRUE" : "FALSE"; } gridview1.datasource = Data; gridview1.databind();?
Steelymar
All-Star
15283 Points
2239 Posts
Re: DataSet Filling column regarding others.
Aug 26, 2009 07:45 PM|LINK
yes. but you should add before that RELEASABLE column:
Data.Tables[0].Columns.Add("RELEASABLE");
for (int i = 0; i < Data.Tables[0].Rows.Count; i++)
..........
Stefan Uzunov
MCTS: .NET Framework 3.5 ASP.NET Applications
pluciorx
Member
53 Points
69 Posts
Re: DataSet Filling column regarding others.
Aug 26, 2009 07:54 PM|LINK
FunCREATEreleasableCOL() <- in that place i have some code to create That column.
pluciorx
Member
53 Points
69 Posts
Re: DataSet Filling column regarding others.
Aug 26, 2009 08:46 PM|LINK
i have made a test code :
SqlConnection con = new SqlConnection (@"Data Source=xxxxxxxxxxx"); SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Test", con); DataSet DS = new DataSet(); da.Fill(DS,"Test"); DS.Tables[0].Columns.Add("RELEASABLE"); for (int i = 0; i < DS.Tables[0].Rows.Count; i++) { string Where = "ID =" + DS.Tables[0].Rows[i]["ID"] + " and magasin = " + DS.Tables[0].Rows[i]["magasin"]; DS.Tables[0].Rows[i]["RELEASABLE"] = (int)DS.Tables[0].Compute("sum(QtyAva)", Where) > 0 ? "TRUE" : "FALSE"; } GridView1.DataSourceID = null; GridView1.DataSource = DS; GridView1.DataBind(); }and seems that there is an error somewhere
as it gives error :
Cannot find column [a].
at line:
DS.Tables[0].Rows[i]["RELEASABLE"] = (int)DS.Tables[0].Compute("sum(QtyAva)", Where) > 0 ? "TRUE" : "FALSE";
any Suggestions ?
Steelymar
All-Star
15283 Points
2239 Posts
Re: DataSet Filling column regarding others.
Aug 27, 2009 04:09 AM|LINK
magasin is string...thats way value should be in quotation marks:
string Where = "ID =" + DS.Tables[0].Rows[i]["ID"] + " and magasin = '" + DS.Tables[0].Rows[i]["magasin"]+"'" ;
have you try SQL version?
Stefan Uzunov
MCTS: .NET Framework 3.5 ASP.NET Applications