Querying a dataset

[Fred]

I want to use a dataset so that I can obtain data from a number of sources
and put it into one table. Using dataadaptors this seems to work well.

Now I have a table (Forecast) in the dataset with columns:
Rep, Area, Period, Qty.

I want to display this data on a datagrid like:
Rep, Period1_Qty, Period2_Qty, .. Period7_Qty

where the period1_Qty is the sum of the Qtys for that Rep and for a
particular period.
I can do this in SQL by:
SELECT Rep, SUM(Qty1), SUM(Qty2), ... SUM(Qty7) FROM
(SELECT F1.Rep, SUM(F1.Qty) AS Qty1, 0 AS Qty2, ..., 0 AS Qty7
FROM Forecast F1 WHERE F1.Period=1 GROUP BY F1.Rep
UNION ALL
SELECT F2Rep, 0 as Qty1, SUM(F2.Qty) AS Qty2, 0 AS Qty3, ...,0 AS Qty7
FROM Forecast F2 WHERE F2.Period=2 GROUP BY F2.Rep
UNION ALL
....
UNION ALL
SELECT F7.Rep, 0 AS Qty1, 0 AS Qty2, ... , SUM(F1.Qty) AS Qty7
FROM Forecast F7 WHERE F7.Period=2 GROUP BY F7.Rep)
GROUP BY Rep

The above statement would vary depending on which periods to display or
whther to display by Rep or Area.
How can I create a table/view by applying a SQL statement like this to a
Dataset?

Thanks
Fred

 

[Cor Ligthert [MVP]]

Fred,

As far as I know can you just create a table like this using your SQL
statement.

However keep this for showing the data, updating from such a dataset will be
a hell of a job.

Therefore, why do you not just try it?

Cor

 

[Fred]

Thanks Cor,
but I am missing something. How do I connect to the dataset. Can I use a
OleDBConnector? ODBCConnector? or what?

Fred

 

[Cor Ligthert [MVP]]

Fred,

Dirty typed here

\\\
dim conn as XXXConnection(Your type connectionstring see the link)
dim ds as new dataset
dim sqlstring as string = "Your SQL string"
dim da as new XXXdataadapter(sqlstring,conn)
da.fill(ds)
yourdatagrid.datasource = ds.tables(0)
////

XXX is OleDB or SQLClient or OracleClient

ConnectionStrings
http://www.connectionstrings.com/

And you are far on the route for this test

I hope this helps,

Cor

 

[Fred]

Thanks Cor but I obviously haven't explaned it very well.
I have already created a dataset the contains my forecast table with all of
the forecast records. These records have been obtained from various sources
and put into the single forecast table contained within the Dataset.
Now I want to create a view of the data contained in the forecast table but
the view is not a simple sort and/or filter of the forecast table. In affect
I want a connection to the dataset.

Fred

 

[Cor Ligthert [MVP]]

Fred,

The only reasonable solution that I see for you is creating than a new
datatable and add the data to that by using looping through the
parentelements of your most detailed childtable in the relation.

On our website are for the samples in the samples a lot of datatables
created if you want to know how.

To start you can probably use this one. (Not that you can use the merge for
your solution).

http://www.windowsformsdatagridhelp.com/default.aspx?ID=49f2cff5-56ad-44fc-a4c6-fc0d5c470f53

I hope this helps,

Cor

 

[david]

I want to use a dataset so that I can obtain data from a number of sources
and put it into one table. Using dataadaptors this seems to work well.

Now I have a table (Forecast) in the dataset with columns:
Rep, Area, Period, Qty.

I want to display this data on a datagrid like:
Rep, Period1_Qty, Period2_Qty, .. Period7_Qty

where the period1_Qty is the sum of the Qtys for that Rep and for a
particular period.
I can do this in SQL by:

Two thoughts. First, there's a product that purports to do this at:
http://www.queryadataset.com. I've never looked at it myself, so I
can't promise anything. I'd be curious to know how it worked for you
though, since that's always seemed like a useful thing to have (and
possibly a fun project to write).

Secondly, just glancing at your sql statement. If you merged your
tables together into a single datatable could you get what you
want with filter and expressions? It depends on your data, and I can't
tell by looking at the example.

Even if you couldn't, it looks like you could get each table
individually without too much effort and then merge them together
at the end, thus sort of emulating the unions with code.