Self-referencing left join

dynasoft

Hi

I'm trying to convert the following sql into valid server DT code but I'm struggling a bit translating the aliases and embedded select statement in the left join.

• Here's the sql:

SELECT V.ID, ISNULL(V.CustomerIndex, C.CustomerIndex) as CustomerIndex

FROM GlobalPriceLists as V

LEFT JOIN (SELECT CustomerIndex, ListType, ListName FROM GlobalPriceLists WHERE CustomerIndex = 0) as C

ON C.ListType = V.ListType AND C.ListName = V.ListName AND V.CustomerIndex = 1

• Server code so far:

editor = new Editor(db, "GlobalPriceLists", "GlobalPriceLists.id")
.Model<CustomerSNsDBModel.GlobalPriceLists>("GlobalPriceLists");
editor.Field(new Field("GlobalPriceLists.id")
.Set(false)
);
editor.MJoin(new MJoin("GlobalPriceLists")
.Model<PriceListsDBModel>()
.Name("GlobalPriceLists.CustomerIndex")
.Link("GlobalPriceLists.ListType", "GlobalPriceLists.ListType")
.Link("GlobalPriceLists.ListName", "GlobalPriceLists.ListName")
.Where(q =>
q.Where("GlobalPriceLists.CustomerIndex", lngCustIdx, "=")
)
.Order("GlobalPriceLists.id ASC")
.Field(new Field("id")
.Options(new Options()
.Table("GlobalPriceLists")
.Value("id")
.Label("CustomerIndex")
)
.Set(false)
)
.Set(false)
);
editor.Field(new Field("GlobalPriceLists.ListType")
.GetFormatter((val, data) => CommonUtilities.IsNullOrEmpty(val) == true ? 0 : val)
);
editor.Field(new Field("GlobalPriceLists.ListName")
.GetFormatter((val, data) => CommonUtilities.IsNullOrEmpty(val) == true ? string.Empty : val)
);
editor.LeftJoin("GlobalPriceLists AS C", "GlobalPriceLists.id", "=", "GlobalPriceLists.SubPackID");
editor.Where("GlobalPriceLists.CustomerIndex", 0);
editor.Debug(true);
editor.Process(formData);

Thanks a lot.

dynasoft

Btw, there is no editor for this DT so there is only a DT.

dynasoft

Am I getting near w/ the code below? The code runs but the CustomerIndex shown in DT is for only CustomerIndex = 0. The idea is that if there is a record where CustomerIndex > 0 (if this is the case there is also a record for CustomerIndex = 0) then the CustomerIndex shown back to the user is this CustomerIndex greater than 0 and should not be 0. In my porgramme, records for CustomerIndex = 0 are general records. Records where CustomerIndex > 0 are specific to a customer. Records are defined by a type (ListType) and a name (ListName) which are identical whether CustomerIndex = 0 or CustomerIndex > 0.

                editor.Field(new Field("myGlobalPriceLists1.CustomerIndex")
                    .Options(new Options()
                        .Table("GlobalPriceLists")
                        .Value("id")
                        .Label("CustomerIndex")
                    )
                );
                editor.LeftJoin("GlobalPriceLists AS myGlobalPriceLists1", "GlobalPriceLists.CustomerIndex", "=", "myGlobalPriceLists1.CustomerIndex").Where("myGlobalPriceLists1.CustomerIndex", 0);
                editor.LeftJoin("GlobalPriceLists AS myGlobalPriceLists2", "GlobalPriceLists.ListType", "=", "myGlobalPriceLists2.ListType").Where("myGlobalPriceLists2.CustomerIndex", 0);
                editor.LeftJoin("GlobalPriceLists AS myGlobalPriceLists3", "GlobalPriceLists.ListName", "=", "myGlobalPriceLists3.ListName").Where("myGlobalPriceLists3.CustomerIndex", 0);

allan

The sub-select you are using for the join in the original SQL means that the Editor LeftJoin method isn't going to be suitable here I'm afraid.

What I would suggest in this case is that you create a VIEW that can be used for reading the data (and that way you can use your original SQL). There is an example of that available here - the .NET download has a matching example.

Allan

dynasoft

Many thanks

dynasoft

Hi
I think I need to use a SP as a variable needs to be passed but I read your product does not support SPs.

What is the best way forward and do you have examples? Thanks

allan

You are correct - Editor doesn't support stored procedures (or at least, the .NET libraries we provide for it don't).

Instead a VIEW would be the best way to do it as I suggested above, since you already know the SQL you want to use. Create a VIEW based on that and then Editor can read that exactly the same way it would with a regular TABLE.

Allan

dynasoft

How would I pass a variable to the view?

dynasoft

Hi
Stll interested in getting a reply here but I went down the route of using an data array and pure sql in the end.

colin

You wouldn't be able to pass a variable to a VIEW - VIEWs are just virtual tables so you would query them the same as a standard table.

Colin

dynasoft

I pointed out I need to pass a variable but Allan came back saying a view would work.

allan

Sorry - I missed that point. Is the variable for the WHERE condition? If so, you can just apply a WHERE to the SQL as you would do with any table.

Allan

dynasoft

Hi. Thanks.
I'm not clear still: You advised that I can't use your standard server code because I have embedded select statements; I can't use a view because I need to pass a variable (yes in the where clause) and DT does not support SP's. What is the best way forward? Is there a way to pass pure sql with a parameter? if not, when will this and using SPs be supported?

allan

We don't currently have any plans to support stored procedures with our server-side libraries I'm afraid. If that is something you require you'd need to implement the server-side code for that in your project yourself.

Regarding the view and a where condition - a view is read just like a table, so in SQL you would do:

SELECT ...
FROM view_name
WHERE view_column = :boundVariable

In the C# Editor code you would use .Where('view_column', myVariable).

The assumption is that the VIEW includes a column that you can use as your condition.

Allan