Joins with Datatables

[Guest]

Hi

I have stored within dataset, the data modeling of a database, with the
intention of being able to work of disconnected way (datatables, schema,
relations..)

It is possible to create a Dataview with join of several datatables
including in dataset?

Regards

Nask

 

[W.G. Ryan - MVP]

No, but you can use a DataRelation between them and work with it that way as
a 1-1 relationship

 

[Adrian Moore]

Nask,

You might be interested in the assembly I've been working on at
http://www.queryadataset.com. Besides INNER JOINS, it lets you perform
complex SQL SELECT statements including UNION, OUTER JOINS, GROUP BY,
HAVING, ORDER BY, sub-queries, functions, etc against the tables in a
dataset. The result of the query is a DataView.

Adrian Moore
http://www.queryadataset.com

 

[Jesús López]

Hi Adrian,

Your component seems very interesting. Let me ask you a question about that.

Will these dataviews be updatable?

For example. Imagine you have the following query:

SELECT C.CategoryID, C.CategoryName, P.ProductId, P.CategoryID,
P.ProductName
FROM Categories C JOIN Produtcs P ON C.CategoryID = P.CategoryID

And you get a dataview for it.

If you change P.ProductName on the dataview, will ProductName of base
datatable Products be changed as well ?

Will the changes made on the dataview produce the corresponding change on
the base databables ?

Will the changes made on the base datatables produce the corresponding
change on the queries based on these base datatables inmediatly?

I would be very cool if your queries behave as Microsoft Access queries do.
As you likely know, Access queries can be updatable in some circunstances.
And I love the way they behave.

Regards:

Jesús López
MVP

 

[Guest]

Hi Adrian, I´m trying your component but i have some problems with
complicated queries;

For example:

SELECT e.SkillCode, a.Description as Area, n.Description as Nivel,
x.Description as Experience, s.Description FROM CV_EmploySkill e,
CO_SkillArea a, CO_Skill s, CO_NivelSkill n, CO_NivelExperience x WHERE
e.SkillCode = s.SkillCode AND LEFT(s.AreaCode,4) = a.AreaCode AND
e.NivelSkillCode = n.NivelSkillCode AND e.NivelExperience Code =
x.NivelExperienceCode AND e.EmpCode ='0000001' ORDER BY a.AreaCode,
s.Description


Line 1: Incorrect syntax near 'LEFT'

OR

Your component seems very interesting, but I need to know if it is possible
to make this type of queries before deciding to me to buy it.

Regards

Nask

 

[Adrian Moore]

Jesús,

Unfortunately, the resulting DataViews are not updatable.

Ad.

 

[Adrian Moore]

Nask,

LEFT is not a builtin function. However, you can use the SUBSTRING function
to accomplish the same thing.

http://www.queryadataset.com/documentation/builtin-functions.aspx

Hope this helps
Ad.

 

[Guest]

Hi Ad,
I continue evaluating your interesting component; but I need something but
of time (the version that I have expires the 23 of January)

You can facilitate a version to me that allows me to finalize my test and to
display it to it to my team leaders?

Regards

 

[Adrian Moore]

Nask,

No problem. Please contact me at (e-mail address removed) to get a version
that doesn't expire for another month.

Thanks
Ad.