Double SQL Join Problem through ODBC

I have experienced problems issuing SQL containing multiple joins through an ODBC connection. I read a thread here, which suggested an alternative to using multiple joins, this I tried and it appears to work. The code is as follows:

SELECT tblCust.AccountNo, tblCust.SalesMan, tblCust.AccManager, MAX(CASE WHEN SalesRep.Rowid = tblCust.SalesMan THEN SalesRep.Name END) AS SalesManNa, MAX(CASE WHEN SalesRep.Rowid = tblCust.AccManager THEN SalesRep.Name END) AS AccManNa
FROM Customers tblCust INNER JOIN SalesRep ON tblCust.SalesMan = SalesRep.Rowid OR tblCust.AccManager = SalesRep.Rowid
WHERE (RowID = '1126318')
GROUP BY tblCust.AccountNo, tblCust.SalesMan, tblCust.AccManager

However, I would like to know how efficient such code is compared to my original (and problematic) joining.

The following is a summary of my original problem

I have one view (Customers) and a table (SalesRep). Customers contains a list of customer companies, while SalesRep lists internal staff. Each company record has fields to identify a Sales contact and an Account Manager, both of which would be listed within the SalesRep table and could be the same person.

It seems fairly straightforward, we are using two INNER JOIN's between the tables, as follows simplified query shows:

SELECT tblCust.AccountNo, tblCust.SalesMan, tblCust.AccManager, salesRepTbl1.Name AS SalesManNa, salesRepTbl2.Name AS AccManNa
FROM Customers tblCust
INNER JOIN SalesRep salesRepTbl1 ON tblCust.SalesMan = salesRepTbl1.Rowid
INNER JOIN SalesRep salesRepTbl2 ON tblCust.AccManager = salesRepTbl2.Rowid
WHERE (tblCust.Rowid = '1126318')

This works fine when executed on SQL Server, as following result:

AccountNo--SalesMan--AccManager--SalesManNa--AccManNa
1234567--100--106--John Smith--Carol Flood

However, when issued using ASP through an ODBC connection to an Intersystems Cache database - SalesManNa is showing the result expected for AccManNa, as...

AccountNo--SalesMan--AccManager--SalesManNa--AccManNa
1234567--100--106--Carol Flood--Carol Flood

Interestingly, the above query does not return any results when executed through Vis Web Dev 2005 Express.

We are using a DSN for the connection, which uses the InterSystems Cache ODBC driver (5.2.0.329.0). I have tried many variations on the join and different syntax but no success and I'm not privy to any specific ODBC restrictions.

Any pointers as to what may be causing the problem through ODBC would be greatly appreciated as would any opinion of the efficiency of the working solution.

Many thanks,
CliffSELECT tblCust.AccountNo, tblCust.SalesMan, tblCust.AccManager, salesRepTbl1.Name AS SalesManNa, salesRepTbl2.Name AS AccManNa
FROM Customers tblCust
INNER JOIN SalesRep salesRepTbl1 ON tblCust.SalesMan = salesRepTbl1.Rowid
INNER JOIN SalesRep salesRepTbl2 ON tblCust.AccManager = salesRepTbl2.Rowid
WHERE (tblCust.Rowid = '1126318')
In terms of performance, I would suggest to try the following instead:SELECT tblCust.AccountNo, tblCust.SalesMan, tblCust.AccManager,
s1.Name AS SalesManNa, s2.Name AS AccManNa
FROM (SELECT AccManager, SalesMan
FROM Customers
WHERE Rowid = '1126318') AS tblCust
INNER JOIN SalesRep s1 ON tblCust.SalesMan = s1.Rowid
INNER JOIN SalesRep s2 ON tblCust.AccManager = s2.Rowid(Sorry, I've no hands-on experience with SqlServer, so I might be wrong about the performance improvement.)|||Many thanks for your reply Peter.

I have tried your suggestion and similar to my own 2x INNER JOIN code, it also returns incorrect data when ran through ASP to Cache ODBC data source...it gets the correct salesman and account manager ids from the Customer table but the join to the SalesRep table returns the account manager name for both!|||I believe that your original code executed on SQL Server would be slightly more efficient, however there are often problems with ODBC drivers which are reluctant to join to the same table more than once.... I think it is an issue with labelling tables within joins... and so you're original SQL code would not return the correct values through the ODBC connection.

I think you're amended code would be the most efficient way to get this resultant recordset using the current ODBC connection.

:)|||The most efficient way, as always, is to create this as a view or stored procedure. I'm betting that would solve your mysterious problem as well.|||I used to date a girl that was double jointed

DOS ODBC Driver

Does anyone know of an ODBC driver that can be used from
DOS? ie not from a DOS window, but from a system booted
into DOS 6.22?
Wanting to read a SQL or Access Database after making a
network connection from DOS.
Thanks,
Rob
There is not an ODBC driver for DOS.
Rand
This posting is provided "as is" with no warranties and confers no rights.

DOS ODBC Driver

Does anyone know of an ODBC driver that can be used from
DOS? ie not from a DOS window, but from a system booted
into DOS 6.22?
Wanting to read a SQL or Access Database after making a
network connection from DOS.
Thanks,
RobThere is not an ODBC driver for DOS.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Domain Accounts

I would like to set up an odbc connection to a MS SQL
2000 database using a domain account.
Is there a way to define a dsn odbc connection using a
windows domain account without actually login onto the
server/pc as that domain account?
Thanks,
Sherry
Security context is not saved with the DSN. A trusted connection is
established under the security credentials of the executing application. A
SQL authenticated connection uses the userid and password supplied in the
connection string.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sherry" <anonymous@.discussions.microsoft.com> wrote in message
news:31a401c4aee1$834a3650$a401280a@.phx.gbl...
>I would like to set up an odbc connection to a MS SQL
> 2000 database using a domain account.
> Is there a way to define a dsn odbc connection using a
> windows domain account without actually login onto the
> server/pc as that domain account?
> Thanks,
> Sherry
|||Hi
You may want to look into DSN less connections.
John
"Sherry" <anonymous@.discussions.microsoft.com> wrote in message
news:31a401c4aee1$834a3650$a401280a@.phx.gbl...
> I would like to set up an odbc connection to a MS SQL
> 2000 database using a domain account.
> Is there a way to define a dsn odbc connection using a
> windows domain account without actually login onto the
> server/pc as that domain account?
> Thanks,
> Sherry

Domain Accounts

I would like to set up an odbc connection to a MS SQL
2000 database using a domain account.
Is there a way to define a dsn odbc connection using a
windows domain account without actually login onto the
server/pc as that domain account?
Thanks,
SherrySecurity context is not saved with the DSN. A trusted connection is
established under the security credentials of the executing application. A
SQL authenticated connection uses the userid and password supplied in the
connection string.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Sherry" <anonymous@.discussions.microsoft.com> wrote in message
news:31a401c4aee1$834a3650$a401280a@.phx.gbl...
>I would like to set up an odbc connection to a MS SQL
> 2000 database using a domain account.
> Is there a way to define a dsn odbc connection using a
> windows domain account without actually login onto the
> server/pc as that domain account?
> Thanks,
> Sherry|||Hi
You may want to look into DSN less connections.
John
"Sherry" <anonymous@.discussions.microsoft.com> wrote in message
news:31a401c4aee1$834a3650$a401280a@.phx.gbl...
> I would like to set up an odbc connection to a MS SQL
> 2000 database using a domain account.
> Is there a way to define a dsn odbc connection using a
> windows domain account without actually login onto the
> server/pc as that domain account?
> Thanks,
> Sherry

Domain Accounts

I would like to set up an odbc connection to a MS SQL
2000 database using a domain account.
Is there a way to define a dsn odbc connection using a
windows domain account without actually login onto the
server/pc as that domain account?
Thanks,
SherrySecurity context is not saved with the DSN. A trusted connection is
established under the security credentials of the executing application. A
SQL authenticated connection uses the userid and password supplied in the
connection string.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sherry" <anonymous@.discussions.microsoft.com> wrote in message
news:31a401c4aee1$834a3650$a401280a@.phx.gbl...
>I would like to set up an odbc connection to a MS SQL
> 2000 database using a domain account.
> Is there a way to define a dsn odbc connection using a
> windows domain account without actually login onto the
> server/pc as that domain account?
> Thanks,
> Sherry|||Hi
You may want to look into DSN less connections.
John
"Sherry" <anonymous@.discussions.microsoft.com> wrote in message
news:31a401c4aee1$834a3650$a401280a@.phx.gbl...
> I would like to set up an odbc connection to a MS SQL
> 2000 database using a domain account.
> Is there a way to define a dsn odbc connection using a
> windows domain account without actually login onto the
> server/pc as that domain account?
> Thanks,
> Sherry

Doesnt see DB Table

I'm having a tough time understanding my current problem and why I'm having it.
I'm using an ODBC db connection to connect to my sql server and tryingto insert data into a table I've created, but I keep getting an errorsaying that the table doesn't exist. This is the first time I'veever used ODBC and DSN to connect to a db so I don't konw if there areqerks associated with this, but I've tried everything I can thinkof. I've changed the name of the table, placed it in brackets,placed "dbo" infront of the name, and still I get the same error aboutthe table not existing. My "Intial Catalog" in my connectionstring is the correct db, I just can't seem to get it going.
Any suggestions?
This is usually due to a table ownership discrepancy. If youqualify your table name with the owner do you have better success? E.g.instead of myTable use huffmaster.myTable (substitute the owner name forhuffmaster).
|||No, the owner is "dbo" and that doesn't seem to help.
|||OK, can you post the DSN (obfuscate the password), the connection string, and the exact error message?
|||Here's my Error:
ERROR [42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.Application'.
And here's my Connection String:
<addkey="LiquorLiabilityConnection_Local" value="Server=macdaddy;InitialCatalog=LiqourLiability;User ID=***;Password=***;" />

|||

It doesn't look like your connection string is incorrect. What about your code? Can you share some?

|||Here's my code that creates the connection and tries to push the insert:
******Segment From Web Page Code Behind************
Dim sql As String
sql = "INSERT INTO [dbo].[Application] " & insert & "VALUES " & values
'insert & values are string variables I create based on what was filled out.
Try
ll.InsertApplication(sql)
Catch
pnlIntro.Visible = False
pnlError.Visible = True
divError.Controls.Add(New LiteralControl(Err.Description.ToString &"<br><br>"))
End Try
******Segment From Class File***************
Public Sub InsertApplication(ByVal sql As String)
Dim connection As New OdbcConnection()
connection = New OdbcConnection()
connection.ConnectionString= ConfigurationSettings.AppSettings.Get("LiquorLiabilityConnection")
Dim command As New OdbcCommand(sql, connection)
connection.Open()
command.ExecuteNonQuery()
connection.Close()
End Sub
I think if the connection was bad itwould throw an error on Opening the connection, but it waits until thequery executes. And it's not until it looks for my 'Application'table. I'm just confused.

|||
It seems you may have misspelt the name of the database specified inthe Initial Catalog, but I agree that your problem does not lookconnection-related. Also, I would stick with the attributesoutlined for SQL Server - ODBC athttp://www.connectionstrings.com.
Can you supply us the value contained in command.CommandText immediately before your ExecuteNonQuery?
|||My CommandText looks like this:
INSERT INTO [dbo].[Application] ('4_state', '5_state', '9_state','10_state', '11_state', '14_years', '14_months', '14_man_years','14_man_months', '15_years', '15_months', '15_stories', '21_sqft','21_apl_sqft', '21_apt_sqft', '21_units', '21_lro_sqft','22_mon_thurs_o', '22_mon_thurs_c', '22_fri_o', '22_fri_c', '22_sat_o','22_sat_c', '22_sun_o', '22_sun_c', '23a_open', '23b_open', '23c_open','23d_open', '24', '25', '26', '27', '28', '29', '30', '31', '32', '33','34', '35', '36', '37', '38', '39', '40', '41', '42a', '42b', '42e','42f', '42g', '44', '45_food_p', '45_food_n', '45_alch_p', '45_alch_n','45_alch_tr_p', '45_alch_tr_n', '45_cc_p', '45_cc_n', '45_opc_p','45_opc_n', '45_other_p', '45_other_n', '48_gen', '48_fire', '48_prod','48_med', '48_eac', '48_pers') VALUES ('Alabama', 'Georgia', 'Alabama','Alabama', 'Alabama', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0','0', '0', 'Closed', 'Closed', 'Closed', 'Closed', 'Closed', 'Closed','Closed', 'Closed', 'Open', 'Open', 'Open', 'Open', 'No', 'No', 'No','No', 'No', 'No', 'No', 'No', 'No', 'No', 'No', 'No', 'No', 'No', 'No','No', 'No', 'Average age 21-25', 'No', 'Yes', 'No', 'No', 'No', 'Yes','0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '654','654', '654', '654', '654', '654')
Please ignore the funny values and field names, this is about as short as I could go with it without deleting any of it.
|||FWIW I made a simple page based on your code and it is working OK for me:
I had to enclose the field names beginning with a numeric character insquare brackets, and I used one of the connection string formats fromhttp://www.connectionstrings.com.

<%@. Page Language="VB"%>

<%@. Import Namespace="System.Data.ODBC"%>

<script runat="server">

    Sub Page_Load(Sender As Object, E As EventArgs)

        Dim sql As String

        sql = "INSERT INTO [dbo].[Application] ([4_state], [5_state]) VALUES ('Alabama','Pennsylvania')"

        Try

            InsertApplication(sql)

        Catch

            divError.Controls.Add(New LiteralControl(Err.Description.ToString & "<br><br>"))

        End Try

    End Sub

    Public Sub InsertApplication(ByVal sql As String)

        Dim connection As New OdbcConnection()

        connection = New OdbcConnection()

        connection.ConnectionString = "Driver={SQL Server};Server=(local);Database=test;Trusted_Connection=yes;"

        Dim command As New OdbcCommand(sql, connection)

        connection.Open()

        command.ExecuteNonQuery()

        connection.Close()

    End Sub

</script>

<html>

<head>

</head>

<body>

    <form runat="server">

        <div id="divError" runat="server">

        Any errors will appear here

        </div>

    </form>

</body>

</html>

|||I put all my column names in "[]" & that seems to have got thingssolved for me now. I'm no longer getting an error concerning thetable, just data conversion errors (ex. varchar to money). Thanksfor all the help.
|||I still don't understand why you were receiving that "invalid objectname dbo.Application" error message. The error I received aboutthe column names was:
ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '_state
Had that been the message to begin with we would have had this figuredout long ago. In any case, I am glad you got this problem sortedand are moving on to the next. :-)
|||Now that I've moved to my next problem, let me ask a question. Isthere a way to INSERT Null values into a table. For instance ifI'm preparing parameters for a stored procedure:
Dim param as New OdbcParameter("@.name", OdbcType.Varchar)
How would I make the above parameter of value NULL? Do you justnot specify a value (ie, para.Value = ?)? Or it there some otherway of doing this?

|||Dinakar has a nice blog post on how to do this:
Entering NULL values into SQL Server 2000 using Parameterized Queries (including date columns)

|||Now that I've got the DBNull.Value in place, I run my stored proocedure and get a new error:
ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure 'InsertApplication' expects parameter '@.1', which was not supplied.
This is the first parameter that has DBNull.Value assigned to it. soI'm not sure exactly what is going on. It doesn't have anythingto do with expecting nulls in the stored procedure does it?