Hi

I'm completely new to LLBGEN and quite a bit confused. I understand the basics such as setting Predicates, sorts, Relationships but when it comes to putting it altogether, have been having troubles.

This is my Sql statement. SELECT ((Field1 + Field2) AS Balance, Field3 FROM Table1 a, Table2 b WHERE b.PIN_Number = myPin AND a.PIN_Number = b.PIN_Number AND a.Date = '07/01/2009' AND NOT EXISTS

(SELECT 1 FROM Table2 c WHERE b.PIN_Number = c.PIN_Number AND c.Field4 IS NOT NULL AND (c.DateJoined IS NULL or c.DateJoined <= '08/12/2009') AND (c.DateLeft IS NULL or c.DateLeft >= '08/12/2009'))

Here is what I have so far. fields.DefineField(Table1Fields.Field1, 1) fields.DefineField(Table1Fields.Field2, 2) fields.DefineField(Table2Fields.Field3, 3)

filter.Add(Table2Fields.PinNumber = myPin) filter.add(Table1Fields.Date = '07/01/2009')

relationsToUse.Add(Table2Entity.Relations.Table1EntityUsingPinNumber)

I'm really stuck on the filters in the Not Exists and how to apply this bit SELECT 1 FROM Table2 c WHERE b.PIN_Number = c.PIN_Number AND c.Field4 IS NOT NULL AND (c.DateJoined IS NULL or c.DateJoined <= '08/12/2009') AND (c.DateLeft IS NULL or c.DateLeft >= '08/12/2009'))

reader = dao.GetAsDataReader(Nothing, fields, filter, relationsToUse, CommandBehavior.CloseConnection, 0, True)

I tried to break the query into 2 steps leaving out the NOT EXISTS part but could not get that working.

Any help greatly appreciated. I would prefer vb.net but will gladly accept C# also.

Thanks

Please check the documentation for FieldCompareSetPredicate

Hi

I noticed the Set Predicate. However even after reading doco on that I'm still having troubles. I also have a "Not exists", noticed an Exists but not a "Not exists"

Here is my current code for part 2 having the multiple filters. And Not Exists ' Subfilter 1 for <= or Null Dim subFilter As PredicateExpression = New PredicateExpression() subFilter.Add(New FieldCompareValuePredicate(Table2Fields.DateJoined, ComparisonOperator.LessEqual, "08/12/2009")) subFilter.AddWithOr(New FieldCompareNullPredicate(Table2Fields.DateJoined))

    ' Subfilter 2 for >= Or Not null
    Dim subFilter2 As PredicateExpression = New PredicateExpression()
    subFilter2.Add(New FieldCompareValuePredicate(Table2Fields.DateLeft, ComparisonOperator.GreaterEqual, "08/12/2009"))
    subFilter2.AddWithOr(Table2Fields.DateLeft <> System.DBNull.Value)

    'Add to main filter
    FiltersNotExists.Add(New FieldCompareValuePredicate(Table2Fields.PinNumber, ComparisonOperator.Equal, Filters(0)))
    filter.AddWithAnd(Table2Fields.Field4 <> System.DBNull.Value)
    filter.AddWithAnd(subFilter)
    filter.AddWithAnd(subFilter2)

I really just need some pointers / sample code to assist in putting it all together.

Part1 - I'm ok on but how do I go about adding this to Part2 SELECT ((Field1 + Field2) AS Balance, Field3 FROM Table1 a, Table2 b WHERE b.PIN_Number = myPin AND a.PIN_Number = b.PIN_Number AND a.Date = '07/01/2009'

Part2 - AND NOT EXISTS

(SELECT 1 FROM Table2 c WHERE b.PIN_Number = c.PIN_Number AND c.Field4 IS NOT NULL AND (c.DateJoined IS NULL or c.DateJoined <= '08/12/2009') AND (c.DateLeft IS NULL or c.DateLeft >= '08/12/2009'))

Hope that makes sense.

Thanks

I noticed the Set Predicate. However even after reading doco on that I'm still having troubles. I also have a "Not exists", noticed an Exists but not a "Not exists"

FieldCompareSetPredicate have overloads which accepts a boolean negate parameter for the NOT part.

Please check the following threads: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=14973 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=13770 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=14123 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=12543 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=11894

Hi

Thanks for the threads, however I'm still a bit lost.

Is there a way of seeing the generated sql so I can see if I'm even close with my query. At least then, I could try different things looking at examples given.

Thanks

Hi

Sorry, the examples are far too complex for my understanding.

Could you please fill in the blanks for the following example?

Dim FieldsNotExists As New ResultsetFields(1) Dim FiltersNotExists As New PredicateExpression() Dim dao As New TypedListDAO() Dim reader As IDataReader Dim filter As New PredicateExpression() Dim PinNumber As Integer = -1

FieldsNotExists.DefineField(Table1Fields.PinNumber, 0)

' 1st query part. Dim fields As New ResultsetFields(4) Dim relationsToUse As New RelationCollection()

' Define relationships relationsToUse.Add(Table1Entity.Relations.Table2EntityUsingPinNumber)

' Define fields to use fields.DefineField(New EntityField("Balance", (((Table2Fields.Entitlement + Table2Fields.AllocationTransfer - Table2Fields.InterDistrictVolume) _ * Table2Fields.SeasonAllocationPercent / 100)) - Table2Fields.OffAllocationToDate + _ ((Table2Fields.InterDistrictVolume * Table2Fields.InterDistrictPercent / 100))), 0)

fields.DefineField(Table1Fields.BFO, 1) fields.DefineField(Table1Fields.PinNumber, 2) fields.DefineField(Table2Fields.EffectiveDate, 3)

' Define filters Select Case UCase(FilterType) Case "PIN" filter.Add(Table1Fields.PinNumber = Filters(0)) Case "PINANDCUSTOMERID" filter.Add(Table1Fields.PinNumber = Filters(0)) ' filter.Add(Table2Fields.CustomerId = Filters(1)) End Select

' Dates are dd/mm/yyyy filter.AddWithAnd(Table2Fields.EffectiveDate = "01/07/2009")

filter.AddWithAnd(Not New FieldCompareSetPredicate(Table1Fields.PinNumber, _ Table2Fields.PinNumber, _ SetOperator.Exist, _ Table1Fields.PinNumber = Filters(0)))

**' 2nd query - Not Exists ' Subfilter 1 for <= or Null ** Dim subFilter As PredicateExpression = New PredicateExpression() subFilter.Add(New FieldCompareValuePredicate(Table1Fields.DateJoinedGroup, ComparisonOperator.LessEqual, "08/12/2009")) subFilter.AddWithOr(New FieldCompareNullPredicate(Table1Fields.DateJoinedGroup))

'** Subfilter 2 for >= Or Not null** Dim subFilter2 As PredicateExpression = New PredicateExpression() subFilter2.Add(New FieldCompareValuePredicate(Table1Fields.DateLeftGroup, ComparisonOperator.GreaterEqual, "08/12/2009")) subFilter2.AddWithOr(Table1Fields.DateLeftGroup <> System.DBNull.Value)

'Add to main filter FiltersNotExists.Add(New FieldCompareValuePredicate(Table1Fields.PinNumber, ComparisonOperator.Equal, Filters(0))) FiltersNotExists.AddWithAnd(Table1Fields.GroupPinNumber <> System.DBNull.Value) FiltersNotExists.AddWithAnd(subFilter) FiltersNotExists.AddWithAnd(subFilter2)

reader = dao.GetAsDataReader(Nothing, Fields, filter, Nothing, CommandBehavior.CloseConnection, 0, True)

Thanks

Is there a way of seeing the generated sql so I can see if I'm even close with my query.

Sure, please check Troubleshooting and debugging

(Edit) As described in the documentation:

You are missing this:

filter.Add(new FieldCompareSetPredicate(
    null, Table2Fields.PinNumber.SetObjectAlias("c"),
    SetOperator.Exists, FiltersNotExists), true);

Then you should use the same Alias in your FiltersNotExists predicates whenever you are going to use a field from the table aliased "c"

e.g:

FiltersNotExists.Add(Table2Fields.PinNumber.SetObjectAlias("c") == Table2Fields.PinNumber)

By the way, I couldn't fill in the blanks of your submitted code, because it's not matching the submitted query, a lot of mismatches occurs.:

SELECT ((Field1 + Field2) AS Balance, Field3 FROM Table1 a, Table2 b WHERE b.PIN_Number = myPin AND a.PIN_Number = b.PIN_Number AND a.Date = '07/01/2009' AND NOT EXISTS

(SELECT 1 FROM Table2 c WHERE b.PIN_Number = c.PIN_Number AND c.Field4 IS NOT NULL AND (c.DateJoined IS NULL or c.DateJoined <= '08/12/2009') AND (c.DateLeft IS NULL or c.DateLeft >= '08/12/2009'))

If you take a good look at your subquery, the b.PIN_Number = c.PIN_Number filter are refering to table 2 for both sides of the predicate (b& c) while you are using table1 in your code. Also c.Field4 in the SQL code, while you are using Table1Fields.GroupPinNumber in your code. So I got confused which is the right ones to use and which are the typos.

Thanks a lot for that.

I'll try it out on Monday including the debugging. I think I should be able to work it out now.

I'll let you know then.

Thanks for your help.

After setting up the trace switches, was able to see sql which was great.

However, I still could not get it working how I wanted even though the sql looked sound. An extra exists was added into the query which was strange.

For now I am going to leave the sql as two statements. Look at if it exists, If so then exit, If not then do statement

I have included the Trace statement below if you wish to add anything. Otherwise, I'm happy to close this.

Thanks

Trace statement

SELECT ((((([MyDB].[dbo].[Table1].[Entitlement] + [MyDB].[dbo].[Table1].[AllocationTransfer]) - [MyDB].[dbo].[Table1].[InterDistrictVolume]) * [MyDB].[dbo].[Table1].[SeasonAllocationPercent]) / @LLBLEP1) - [MyDB].[dbo].[Table1].[OffAllocationToDate]) + (([MyDB].[dbo].[Table1].[InterDistrictVolume] * [MyDB].[dbo].[Table1].[InterDistrictPercent]) / @LLBLEP2) AS [Balance], [MyDB].[dbo].[Table2].[BarredFromOSI] AS [BarredFromOsi], [MyDB].[dbo].[Table2].[PIN_Number] AS [PinNumber], [MyDB].[dbo].[Table1].[EffectiveDate] FROM ( [MyDB].[dbo].[Table2] INNER JOIN [MyDB].[dbo].[Table1] ON [MyDB].[dbo].[Table2].[PIN_Number]=[MyDB].[dbo].[Table1].[PIN_Number]) WHERE ( ( [MyDB].[dbo].[Table2].[PIN_Number] = @PinNumber3 AND [MyDB].[dbo].[Table1].[EffectiveDate] = @EffectiveDate4 AND NOT EXISTS (SELECT [MyDB].[dbo].[Table1].[PIN_Number] AS [PinNumber] FROM [MyDB].[dbo].[Table1] WHERE [MyDB].[dbo].[Table2].[PIN_Number] = @PinNumber5) AND EXISTS (SELECT [be2].[PIN_Number] AS [PinNumber] FROM [MyDB].[dbo].[Table2] [be2] WHERE ( [be2].[PIN_Number] = @PinNumber6 AND [be2].[GroupPIN_Number] IS NOT NULL AND ( [be2].[DateJoinedGroup] <= @DateJoinedGroup7 OR [be2].[DateJoinedGroup] IS NULL) AND ( [be2].[DateLeftGroup] >= @DateLeftGroup8 OR [be2].[DateLeftGroup] IS NOT NULL)))))

Parameter: @LLBLEP1 : Int32. Length: 0. PreMyDBion: 0. Scale: 0. Direction: Input. Value: 100.
Parameter: @LLBLEP2 : Int32. Length: 0. PreMyDBion: 0. Scale: 0. Direction: Input. Value: 100.
Parameter: @PinNumber3 : Int16. Length: 0. PreMyDBion: 5. Scale: 0. Direction: Input. Value: 1034.
Parameter: @EffectiveDate4 : DateTime. Length: 10. PreMyDBion: 0. Scale: 0. Direction: Input. Value: 07/01/2009.
Parameter: @PinNumber5 : Int16. Length: 0. PreMyDBion: 5. Scale: 0. Direction: Input. Value: 1034.
Parameter: @PinNumber6 : Int16. Length: 0. PreMyDBion: 5. Scale: 0. Direction: Input. Value: 1034.
Parameter: @DateJoinedGroup7 : DateTime. Length: 10. PreMyDBion: 0. Scale: 0. Direction: Input. Value: 12/08/2009.
Parameter: @DateLeftGroup8 : DateTime. Length: 10. PreMyDBion: 0. Scale: 0. Direction: Input. Value: 12/08/2009.