?
Solved

T-SQL Query Through ODBC--Not All Data is "There"

Posted on 2013-10-22
4
Medium Priority
?
421 Views
Last Modified: 2013-10-23
Hello:

Attached is an Excel spreadsheet whose data I access within a separate app and through an ODBC connection to the spreadsheet.

I need to build T-SQL syntax (i.e. select * from) to accommodate the fact that the Employee and Employee ID columns have blanks.  For instance, for Frank Balsamo, you can tell that he has seven courses.  But, how do I get the ODBC connection to "understand" that?

In other words, I guess I need to use PARTITION BY or something like that within the T-SQL syntax in order to "fill in" the Employee and Employee ID fields.  That way, the ODBC connection will know not to leave blanks in those columns.

I hope that makes sense.

Anyway, can you all please help me with the T-SQL syntax on this?

Please, also, let me know if you have any questions.

Thanks!  Much appreciated!

TBSupport
Book2.xlsx
0
Comment
Question by:TBSupport
  • 3
4 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39592325
>through an ODBC connection to the spreadsheet.
Sounds like the spreadsheet is eliminating these values when they duplicate with what's above.  
Since the connection is only a connection, and does not alter data, that's what you get.

I propose you build a view based on this table, that goes something like this..
SELECT n.Employee, n.EmployeeID, ClassCourse ID, CompletedDate, ExpirationDate, Completed
FROM YourTable yt
   JOIN (SELECT DISTINCT Employee, EmployeeID FROM YourTable) n ON yt.Employee = n.Employee

Open in new window

0
 
LVL 1

Author Comment

by:TBSupport
ID: 39592493
Thanks!

I slightly modified your query, and the app is saying that there is a syntax error in the "FROM" clause.  I don't see any issues, though, do you?  Incidentally, EmployeeTrainingCourses$ is the tab in the Excel workbook that I'm connecting to.

Below is the revised query.

TBSupport

SELECT a.Employee, a.EmployeeID, ClassCourseID, CompletedDate, ExpirationDate, Completed
FROM [EmployeeTrainingCourses$] b
   JOIN (SELECT DISTINCT Employee, EmployeeID FROM [EmployeeTrainingCourses$]) a
ON b.Employee = a.Employee
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 39592502
Try this..
JOINing on the ID's and not the names
Prefixed every column in the SELECT clause
SELECT a.Employee, a.EmployeeID, b.ClassCourseID, b.CompletedDate, b.ExpirationDate, b.Completed
FROM [EmployeeTrainingCourses$] b
   JOIN (SELECT DISTINCT Employee, EmployeeID FROM [EmployeeTrainingCourses$]) a 
ON b.EmployeeID = a.EmployeeID

Open in new window

0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39592508
Also, the spreadsheet had a lot of spaces in the column names, so you'll want to eyeball these and make sure it's correct.  T-SQL can't handle spaces in object names, so whenever you see then you'll need square brackets surrounging them.

e.g. EmployeeName is okay, Employee{space}Name is not, [Employee Name] is ok
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question