Left Join Returns Nulls in View

[dkillilea]

Has anyone experienced problems with left joins when working with a linked ODBC flat-file?

I am getting all columns from table a.inventory

However, info from table b.inventory_group_desc is coming back null when I do a left join. I've tested the crap out of it. It works in Access when i link the tables individually. But not in SQL Server.

Any suggestions?

 

[SQLDenis]

can you show the query?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[dkillilea]

--Create view #1
CREATE VIEW VIEW_INVENTORY_GROUP_DESC
AS
SELECT
CAST(a.GROUP_CODE AS VARCHAR(6)) AS GROUP_CODE,
CAST(a.GROUP_PFX AS VARCHAR(2)) AS GROUP_PFX,
CAST(b.GROUP_DESC AS VARCHAR(30)) AS GROUP_DESC
FROM MAJESTI..root.INRPRG AS a --LINK THROUGH ODBC TO FLAT FILE
LEFT JOIN
MAJESTI..root.SCRMNS AS b --LINK THROUGH ODBC TO FLAT FILE
ON CAST(a.GROUP_CODE AS VARCHAR(6)) = CAST(b.MNS_GROUP_CODE AS VARCHAR(6))

--Create view #2 which uses view #1
CREATE VIEW VIEW_CURRENT_DAY_INVENTORY
AS
SELECT
CAST(a.CODE AS VARCHAR(6)) AS INV_CODE,
CAST(a.TAG_NUMBER AS VARCHAR(8)) AS INV_TAG_NUM,
CAST(b.GROUP_PFX AS VARCHAR(2)) AS INV_PFX,
CAST(b.GROUP_DESC AS VARCHAR(30)) AS INV_DESC,
CONVERT(VARCHAR(10),a.INVENTORY_DATE,126) AS IMPORT_DATE
FROM INVENTORY AS a
LEFT JOIN
VIEW_INVENTORY_GROUP_DESC AS b
ON a.CODE = CAST(b.GROUP_CODE AS VARCHAR(6))
WHERE CONVERT(VARCHAR(10),A.INVENTORY_DATE,126) =
(SELECT CONVERT(VARCHAR(10),MAX(INVENTORY_DATE),126)
FROM INVENTORY)

 

[SQLDenis]

perhaps this?

ON a.CODE = CAST(b.GROUP_CODE AS VARCHAR(6)) has no cast but the select has

SELECT
CAST(a.CODE AS VARCHAR(6)) AS INV_CODE

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[dkillilea]

Thanks for the idea. Unfortunately that makes no difference in this situation. I commented out many variations of the casting (and tested them) to see if this was the issue on the left join. It is not.

I removed the where clause (to get the most recent inventory day) and that made no difference.

I took the results of the left and right joins, put them in excel and compared. Aside from the null columns, I have every inventory tag in both.