CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 2 of 2

Threaded View

  1. #1
    Join Date
    Jun 2009
    Location
    Beograd, Serbia
    Posts
    15

    Left join problem while translating from SQL to MSaccess

    I wrote query for SQL database, which works as it should, and have problem with "translation" into MSaccess, in "left join" part. The thing is that I placed "OR" inside that join:

    Code:
    left join SDBT_Device 
    		ON SDBT_Device.DEV_Unique = PDBT_FDEVAssembly.DeviceID 
    		or SDBT_Device.Dev_Unique = PDBT_FDEV.DeviceID)
    which MSAccess won't allow. Any ideas how can I fight with this?

    Here is my whole sql query if needed:
    Code:
    select 
    	MIN(SDBT_Device_ML.szDefText) AS szDefText, 
    	MIN(SDBT_Device_ML.DEV_Memo) AS DEV_Memo, 
    	MIN(SDBT_Device.DEV_CostGroup) AS CostGroup,
    	MIN(SDBT_CostGroup_ML.Translation) AS CostGroupTranslation,
    	MIN(SDBT_Device_ML.GAEB_ShortText) AS GAEB_ShortText, 
    	MIN(SDBT_Device_ML.GAEB_LongText) AS GAEB_LongText, 
    	MIN(PDBT_DWG_TRIC.ID_DemandPos) AS ID_DemandPos, 
    	'Qty' = 	
    	SUM(case 
    		when PDBT_FDEVAssembly.DeviceID is null then PDBT_FDEV.FDEV_Qty
    		else PDBT_FDEVAssembly.Quantity * PDBT_FDEV.FDEV_Qty 
    		END),
    	MIN(SDBT_DeviceUnit_ML.DevUnitName) as DEV_UnitID,
    	MIN(SDBT_DevCommerce.DEV_Price) AS DEV_Price, 
            MIN(SDBT_Currency.Cur_Description) AS CurDesc,
    	SDBT_Device.DEV_Unique
    from
    	(((((((((((((PDBT_Project
    	inner join PDBT_Title
    		on PDBT_Project.ID = PDBT_Title.ProjectID)
    	inner join PDBT_ISP
    		on PDBT_Title.ID = PDBT_ISP.TitleID)
    	inner join PDBT_GEWERK
    		on PDBT_ISP.ID = PDBT_GEWERK.IspID)
    	inner join PDBT_Anlage
    		on PDBT_GEWERK.ID = PDBT_Anlage.GewerkID)
    	inner join PDBT_DWG_TRIC
    		on PDBT_Anlage.ID = PDBT_DWG_TRIC.BL_Anlage_ID)
    	inner join PDBT_FDEV
    		on PDBT_DWG_TRIC.ID = PDBT_FDEV.ID_DWG)
    	left join PDBT_FDEVAssembly
    		on PDBT_FDEV.ID_FDEV = PDBT_FDEVAssembly.FDEV_ID)
    	left join SDBT_Device 
    		ON SDBT_Device.DEV_Unique = PDBT_FDEVAssembly.DeviceID 
    		or SDBT_Device.Dev_Unique = PDBT_FDEV.DeviceID)
     	inner join SDBT_DevCommerce 
    		ON SDBT_Device.DEV_Unique = SDBT_DevCommerce.SD_ID)
    	inner join SDBT_Device_ML 
    		ON SDBT_Device.DEV_Unique = SDBT_Device_ML.DEV_Unique)
    	inner join SDBT_DeviceUnit_ML
                    on SDBT_Device.DEV_UnitID = SDBT_DeviceUnit_ML.DevUnitID)
    	inner join  SDBT_Currency
     		on SDBT_CURRENCY.CUR_ID = SDBT_DevCommerce.DEV_Pr_Currency)
    	left join SDBT_CostGroup_ML 
    		on  SDBT_CostGroup_ML.StringID = SDBT_Device.DEV_CostGroup)
    where
    PDBT_Anlage.ID = %d
    and 
    ((SDBT_Device_ML.LA_ID Is Not Null AND PDBT_Project.P_Language=[SDBT_Device_ML].[LA_ID])or (SDBT_Device_ML.LA_ID Is Null))
    and
    (SDBT_DeviceUnit_ML.LA_ID = PDBT_Project.P_Language)
    
    GROUP BY SDBT_Device.DEV_Unique, SDBT_Device.DEV_CostGroup
    
    ORDER BY SDBT_Device.DEV_CostGroup
    ('Qty' = SUM(case ... part is changed into iif(...) for MSAccess, everything else is just same)

    SQL is SQL Server 2005, and access is MSAccess 2002.
    Last edited by cocteau; March 7th, 2011 at 10:26 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured