 Topic:
comparing two fields to each other in table |
Posted On: 9/12/2008 6:07 PM
Posted By: Mike Hamilton
|
another question that is probably simple but I am missing it!
I took a look at the samples quickly to see if this was covered and didnt find it there so I will ask here. I am probably missing it and its right in front of my face, and just first time doing this.
We are trying to get back records where two columns in same table match each other. both columns are Unique Identifiers, just in case that is useful info. we are also using the latest 3.x version and have not had time to install 4.0 yet
I figure this should be fairly straight forward but cant figure out what to set on the column in the object. We tried adding a custom field and got really close but are running into an issue with the fact that the value in the where clause is passed as a parameter so it is like adding quotes around the 2nd field's name so obviously that comparison wont match up a GUID to a string like '[tablename].[fieldname]'
anyways heres the code we have that is in a specialized "shared" method in a partial class for the object.
| Public Shared Function FindParentItemsOnly() |
| Me.ObjectMode = ObjectModes.Search |
| |
| Me.UseAllFieldsForDisplay(False) |
| Me.ID.Visible = True |
| Me.Account_Name.Visible = True |
| |
| Dim newField As StringField = New Akal.QuickObjects.ObjectBase.StringField(Me) |
| Me.Fields.Add(newField) |
| |
| newField.IsCustom = True |
| newField.UseInSearch = True |
| newField.Visible = False |
| newField.Name.Value = "cast([" & Utilities.GetTableName(Me.ID) & "]." & Utilities.GetObjectName(Me.ID.Name, False, True, Me.ID.BusinessObject.DBType) & " as varchar(50))" |
| newField.ResultSetName = "CompareCols" |
| newField.Value = "cast([" & Utilities.GetTableName(Me.ID) & "]." & Utilities.GetObjectName(Me.Parent_Account.Name, False, True, Me.Parent_Account.BusinessObject.DBType) & "as varchar(50))" |
| newField.Operator = Operators.EqualTo |
| newField.SearchMode = SearchModes.Value |
| newField.PartialTextMatch = False |
| |
| Me.Entity_ID = New Guid(strEntityID) |
| Me.Status.Value = 1 |
| |
| Dim params As New ArrayList() |
| Dim strSQL As String = Me.GetSelectSQL(params) |
| |
| Return Me.Find() |
| end function |
so now you can tell me I am doing it wrong and show me how to do it in 3-5 lines instead of all the newField stuff I am trying.
Thanks,
Mike
Mike,
The following code shows how you can do this. There is a little trick I played here and you will see that in the code. This is not needed in 4+ as there is a separate field comparison object available to you, also LINQ syntax works too. In any case I think you'll be able to translate the code below for your requirements.
| Using menus As Menu = New Menu() | | | | | | ' Lets get the table alias nicely formatted based on our settings and it will include the trailing . so we won't have to add that either | | Dim tableAlias As String = Utilities.GetObjectName(menus.GetResultSetName(), True, menus.AutoEncloseObjectNames, menus.DBType) | | ' At this point the following isn't necessary if the compare is between the same table but you would get the right table's alias the same as above | | ' if the column being compare was in another table. | | Dim rightTable As String = tableAlias | | | | ' Lets get the name of the field nicely formatted, and this time we will not include the trailing dot. | | Dim leftField As String = Utilities.GetObjectName(menus.ID.Name, False, menus.AutoEncloseObjectNames, menus.DBType) | | | | ' Lets get the name of the second field being compared as well | | Dim rightField As String = Utilities.GetObjectName(menus.ParentMENU_ID.Name, False, menus.AutoEncloseObjectNames, menus.DBType) | | | | Dim s As Int32Field = New Int32Field(menus) | | s.IsCustom = True | | ' This line is where I play a little trick. At the end I added " AND 1 " and in the Value I will assign a 1 so the extra condition will always translate to true and will have no effect. | | ' This limitation is not there in 4.0 as there is a separate FieldComparison object available and even linq can be used to specify that expression :) | | s.Name.Value = tableAlias & leftField & " = " & rightTable & rightField & " AND 1 " | | ' pass the same value as I did at the end of the expression above and I get 1 = @parameter1 {which is equal to 1} in the final sql. | | s.Value = 1 | | s.ResultSetName = "FieldCompare1" | | s.Operator = Operators.EqualTo | | s.Visible = False | | s.UseInSearch = True | | Dim ar = New ArrayList() | | Dim sql = menus.GetSelectSQL(ar) | | | | End Using | | |
I assumed you were comparing two fields of the same type, if so you don't need to do the cast unless there is something specific you are trying to do that I am unaware of.
If this doesn't solve the problem or you have any further questions let us know. Also, QO 4.2 is almost ready so I would say wait another day or two for 4.2 to try the latest. Also, there is support for compact framework now (no samples yet though) and SQL CE (I think you asked for it sometime back).
Thanks,
Ish
| 9/12/2008 7:50:16 PM
Ish Singh
|
One more thing I noticed about your code, if you pass the business object to the contructor of the field, then you are not required to call the Fields.Add as it is done automatically for you.
Anyways, one of the reasons to update to QO 4.x is the ability to set column position, and as far as I remember you asked for that feature as well :)
Till this point we haven't had any issues reported with upgrades, rather I think it should be pretty much painless as there aren't any breaking changes in the API. Just one very small breaking change is in the Quick Objects Designer command line parameters as now you are able to specify your parameter for each template, and you can configure n number of templates in your project. This breaking change only affects you if you were using the command line to generate code. Basically instead of -gc -vb it gets replaced with one parameter and you decide the parameter.
Hope that information helps :)
Thanks,
Ish
| 9/12/2008 8:12:48 PM
Ish Singh
|
although we did ask for the setting of column positions, we have not upgraded yet due to using custom template, and the changes with the designer and getting that setup. We are in push for a release so time is not there in case something did come up. We didnt want to open up a pandoras box just as we are about to put out a release, so once that's out we plan on upgrading then.
Thanks for the code. I will let you know if we have any more questions.
Mike
| 9/12/2008 8:46:07 PM
Mike Hamilton
|
Of course there is no rush to upgrade :) Release target is more important so good luck!
Ish
| 9/12/2008 9:20:22 PM
Ish Singh
|
I just thought I'll share the code for v4.x as well in case anyone wants to do this the easier way if they are using v4.x.
| Dim fc As FieldComparison = New FieldComparison(menus, "fc1") | | fc.FieldsToCompare.LeftField = menus.ID | | fc.FieldsToCompare.RightField = menus.ParentMENU_ID |
Thats all it take.
Ish
| 9/12/2008 9:36:20 PM
Ish Singh
|