Joins

I have two tables, one called users and the other resources.
I'd like to select several fields from users and one from Resource (called ResourceID) based on certain criteria.

My sql statement looks like this:

gstrSQL = "Select UserNum, ResourceID, UserFName, UserLName, Password, SecurityLevel, email, Telephone, ext, UserID from Users U, Resource R where "

If UserNum <> "" Then
gstrSQL = gstrSQL & "UserID='" & UserNum & "' And u.userfname = r.fname and u.userlname = r.lname"
Else
gstrSQL = gstrSQL & "UserID='" & UserID & "' And u.userfname = r.fname and u.userlname = r.lname"
End If

Before adding "ResourceID" to the selection criteria, this statement was working just fine. It's probably a silly mistake but I can't figure it out.
Thanks.You didn't let us know the DB you're working on, nor the error itself ...

In Oracle you can NOT name your table "Resource" as it is reserved word.

However, if your DB allows it, try to rewrite the query in a way to put table aliases in front of table columns, i.e.

SELECT u.UserNum, r.ResourceID, u.UserFName, ...
FROM Users U, Resource R
WHERE ...

Perhaps your tables contain the same column names and, if you don't explicitly say the table you are selecting its value from, the column becomes ambigously defined.

Joining DataSets with Hashtables

I have Fact data (Failures by Resource) from DataSource A, and
Dimension data (Owner of Resources) in DataSource B, where A and B are
different Oracle servers. Linked Servers are not an option for at
least 3 months, if ever. These sources return DataSets A and B.
I need to develop a report where I total the Failures from A "Grouped
By" the Owner of a Resource from B. Very simple lookup.
If I use the Report custom Code, I can push the Resource/Owner mapping
into a (Public global variable) Hashtable, and pull it out in the
Details of a table. Data is pushed using a Table using DataSet A with
Detail rows that call a SetData(ID,Owner) function with a non-Visible
Details row. Data is retrieved in a separate table based on DataSet B
using a GetData(ID) function.
But:
* GetData() only appears to function in Expressions in the Details of
a table, even though GetData() is referencing the Hashtable.
* I cannot sort a Table on the GetData() results, data comes back in
random order
* I cannot Group by the GetData() results, the field appears to be set
to the Owner of the first ID (not for each row in the DataSet!!)
* I can add a Calculated Field to DataSet A that calls SetData(), and
it appears to work (I have SetData return the Owner, so I can see the
function is called), but if I add a Calculated Field to DataSet B that
calls GetData(), all the field contents are Nothing.
I am running out of ideas here. Why is an action so common made so
difficult in SSRS?
-- Scott-- Report Properties -> Code Pane
--
Public Shared mapping As New System.Collections.Hashtable()
Public Function StoreValue( ByVal dataSetName As String, ByVal
fieldName as String, _
ByVal idName As String, ByVal fieldValue as Object ) As Object
Dim hashName As String
hashName = dataSetName & "." & fieldName & "." & idName
If Not mapping.ContainsKey( hashName ) Then
mapping.Add( hashName, fieldValue )
End If
Return fieldValue
End Function
Public Function GetValue( ByVal dataSetName As String, ByVal fieldName
as String, _
ByVal idName As String ) As Object
Dim hashName As String
hashName = dataSetName & "." & fieldName & "." & idName
If mapping.ContainsKey( hashName ) Then
Return mapping( hashName )
Else
Return Nothing
End If
End Function
-- Report Body, Table Object --
-- Table 1, Details Section --
DataSet dsNames
=Code.StoreValue( "dsNames", "ORGNAME", Fields!RESOURCEID.Value,
Fields!ORGNAME.Value )
-- Table 2, Details Section --
DataSet dsResources
=Code.GetValue( "dsNames", "ORGNAME", Fields!RESOURCEID.Value )