SQL Management Studio v. MSQuery

Transact-SQL
1

The following code works in MSQuery but SQL MS rejests rejects the 2nd AS on Line 10 ?

    SELECT bcs.BomReference, bcs.DateTimeCosted, bcs.TotalCost 
FROM (
SELECT BomReference, 
Max(DateTimeCosted) AS MaxDate
FROM NDM_Sage200.dbo.BomCostSession BomCostSession 
GROUP BY BomReference 
) AS ldc 
INNER JOIN 
NDM_Sage200.dbo.BomCostSession BomCostSession 
as bcs 
ON bcs.BomReference = ldc.BomReference AND 
bcs.DateTimeCosted = ldc.MaxDate
ORDER BY bcs.BomReference

I am working in SQL MS as I am trying to expand the code as follows cannot locate the MSQuery error message "Could not add the table (" which I could not debug.

SELECT bcs.BomReference, bcs.DateTimeCosted, bcs.TotalCost, StockItem.StockDescription 
FROM (
SELECT BomReference, 
Max(DateTimeCosted) AS MaxDate
FROM NDM_Sage200.dbo.BomCostSession BomCostSession 
GROUP BY BomReference 
) AS ldc 
INNER JOIN 
BomCostSession as bcs 
ON bcs.BomReference = ldc.BomReference AND 
bcs.DateTimeCosted = ldc.MaxDate
INNER JOIN
StockItem 
ON BomCostSession.BomReference = StockItem.ItemID
ORDER BY BomReference

I am confused

2

You second query parses without any errors for me. So I suspect it is that one of the tables might not be available or accessible.

In the first query, you have a syntax error. Remove the BomCostSession and rewrite the section above as follows

INNER JOIN 
NDM_Sage200.dbo.BomCostSession --BomCostSession 
as bcs
1 Like
3

in:

INNER JOIN StockItem 
	ON BomCostSession.BomReference = StockItem.ItemID

BomCostSession does not exist (all uses of that table have been given different Aliases)

1 Like
4

Kristen & JamesK
Thank you. I have corrected as suggested plus found another. I am now getting another error:
"Error converting data type varchar to bigint" on line 5. I have tried various CONVERTS but with no luck. The format used is "2015-07-22 13:36:02.000" which seems to be 121
It confuses me when I get no error messages in MSQuery, but the code fails in SQL MS
The code now reads:

SELECT bcs.BomReference, bcs.DateTimeCosted, bcs.TotalCost, StockItem.Name 
FROM (
SELECT BomReference, 
Max(DateTimeCosted) AS MaxDate
FROM NDM_Sage200.dbo.BomCostSession BomCostSession 
GROUP BY BomReference 
) AS ldc 
INNER JOIN 
NDM_Sage200.dbo.BomCostSession 
as bcs 
ON bcs.BomReference = ldc.BomReference AND 
bcs.DateTimeCosted = ldc.MaxDate
INNER JOIN
NDM_Sage200.dbo.StockItem StockItem 
ON bcs.BomReference = StockItem.ItemID
ORDER BY bcs.BomReference

More help much appreciated

5

The error message is complaining about VARCHAR to BIGINT. The only places I see where there could be (implicit) conversion is for StockItem.ItemID or bcs.BomReference on the join condition. Are they both numeric?

Also, you mentioned trying to use CONVERT. Which column are you trying to convert? DateTimeCosted? What is its datatype? Regardless of its data type, that should not cause a conversion error.

6

James
The problem is in the following line (line 4 not 5):
Max(DateTimeCosted) AS MaxDate

Thanks
Kris

7

James
Sorry Ignore the last post. I got myself confused
The message is:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.

Thanks
Kris

8

I don't think that should cause an error. You can use MAX function on VARCHAR. It wouldn't try to convert to BIGINT when trying to compute MAX. Is what you posted the entire code?

Try the following and see if that produces an error.

SELECT  MAX(DateTimeCosted) AS MaxDate
FROM    NDM_Sage200.dbo.BomCostSession

I am expecting that it would run without any error.

9

James
Please ignore that post of mine - got confused. The error is:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.

This confuses me even more

10

If the query you posted earlier is the entire query, the only place I see where a conversion error of this type can happen is in the final join condition bcs.BomReference = StockItem.ItemID

What are the data types of those two columns? You can find using the following query (or look in the SSMS object explorer by navigating down the tables and opening up the columns node.

SELECT table_name, column_name,data_type 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('BomReference','ItemID');
11

James
This is the full query which gives the error::
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.

SELECT bcs.BomReference, bcs.DateTimeCosted, bcs.TotalCost, StockItem.Name 
FROM (
SELECT BomReference, 
Max(DateTimeCosted) AS MaxDate
FROM NDM_Sage200.dbo.BomCostSession BomCostSession 
GROUP BY BomReference 
) AS ldc 
INNER JOIN 
NDM_Sage200.dbo.BomCostSession 
as bcs 
ON bcs.BomReference = ldc.BomReference AND 
bcs.DateTimeCosted = ldc.MaxDate
INNER JOIN
NDM_Sage200.dbo.StockItem StockItem 
ON bcs.BomReference = StockItem.ItemID
ORDER BY bcs.BomReference

This sub-query works perfectly:

SELECT bcs.BomReference, bcs.DateTimeCosted, bcs.TotalCost
FROM (
SELECT BomReference, 
Max(DateTimeCosted) AS MaxDate
FROM NDM_Sage200.dbo.BomCostSession BomCostSession 
GROUP BY BomReference 
) AS ldc 
INNER JOIN 
NDM_Sage200.dbo.BomCostSession 
as bcs 
ON bcs.BomReference = ldc.BomReference AND 
bcs.DateTimeCosted = ldc.MaxDate
ORDER BY bcs.BomReference

If I alter the first line of the full query by removing 'StockItem.Name' thus:

SELECT bcs.BomReference, bcs.DateTimeCosted, bcs.TotalCost /*, StockItem.Name */

I still get the same error.
Looking at the fields:
BomReference & StockItem.Name are alpha-numeric
DateTime Costed is the full field inc. fractional seconds
Total.Cost is numeric

309/0420/R4 2015-07-22 13:36:07.000 3.636540

Is the result from the working sub query.

As I said, I am totally confused.

12

What are the data types of the two columns in the final join? You can find using the following query (or look in the SSMS object explorer by navigating down the tables and opening up the columns node.

SELECT table_name, column_name,data_type 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('BomReference','ItemID');
1 Like
13

Are any of the "tables" actually "Views"?

If you do

EXEC sp_help 'MyTableOrViewName'

in the TYPE column will be listed either "table" or "view".

If you are referencing a VIEW it is quite possible that the view itself is making a conversion from, say, CHAR to INT which may include data which is invalid for that conversion.

You need to check all the things in your FROM / JOIN clauses, e.g.
NDM_Sage200.dbo.BomCostSession
NDM_Sage200.dbo.StockIte
etc

1 Like
14

James
I think I understand now.
StockItem.ItemID is BigInt, not null
BomCostSession.BomReference is Varchr(30), not null

I had presumed that the ItemID was a Varchr as this is how we have identified all our stock.

I should have looked deeper.. I now need tofind the table to link across
Inciderntally, in reply to Kristen, these are all tables

15

James & Kristen
Thank you for all your help.
It all works.