Move queries from Access to MS SQL Server

  • Hi Guys,

    i have query  in Access like here:

    Now i want to build something similar in MS SQL Server.
    Except tables i am joining also queries  here.

    How can i do it in MS SQL Server?
    Create Views from tables and create constraints within another view? 
    I want to preserve all relationships here.

    C_ServerInfo_copy is query build from 2 tables. The same with C_MaxMachInfo. 

    SQL here is:
    SELECT t_MachineInfo.MachineName, t_CpuInfo.Server, t_CpuInfo.Model, t_Workload.WorkloadName, IIf([t_Workload].[WorkloadName]='BWoH' And [t_CpuInfo].[Cpu_id]=1,512*[t_DicMemory].[Size],IIf([t_Workload].[WorkloadName]='BWoH' And [t_CpuInfo].[Cpu_id]=2,512*[t_DicMemory].[Size],IIf([t_Workload].[WorkloadName]='SoH' And [t_CpuInfo].[Cpu_id]=1,1*DLookUp("Size","t_DicMemory","[Label]='TiB'"),DLookUp("Size","t_DicMemory","[Label]='TiB'")))) AS UsableMemoryBM, IIf([t_Workload].[WorkloadName]='BWoH' And [t_CpuInfo].[Cpu_id]=1,512*[t_DicMemory].[Size],IIf([t_Workload].[WorkloadName]='BWoH' And [t_CpuInfo].[Cpu_id]=2,512*[t_DicMemory].[Size],IIf([t_Workload].[WorkloadName]='SoH' And [t_CpuInfo].[Cpu_id]=1,768*[t_DicMemory].[Size],1*DLookUp("Size","t_DicMemory","[Label]='TiB'")))) AS UsableMemory55, IIf([t_Workload].[WorkloadName]='BWoH' And [t_CpuInfo].[Cpu_id]=1,512*[t_DicMemory].[Size]/(1024*1024*1024),IIf([t_Workload].[WorkloadName]='BWoH' And [t_CpuInfo].[Cpu_id]=2,512*[t_DicMemory].[Size]/(1024*1024*1024),IIf([t_Workload].[WorkloadName]='SoH' And [t_CpuInfo].[Cpu_id]=1,768*DLookUp("Size","t_DicMemory","[Label]='TiB'")/(1024*1024*1024),1*DLookUp("Size","t_DicMemory","[Label]='TiB'")/(1024*1024*1024)))) AS UsableMemory60, t_ServerInfo.Sockets, C_MaxMachInfo.MaxMachineMem, C_MaxMachInfo.HAStandbyServer, t_ServerDetailedInfo.Environment, IIf([t_MachineInfo].[MachineInfo_ID]=1,[UsableMemory55]*[t_ServerInfo].[Sockets],[UsableMemoryBM]*[t_ServerInfo].[Sockets]) AS ConsumableAdd, IIf([ConsumableAdd]<[C_MaxMachInfo].[MaxMachineMem],[ConsumableAdd],[C_MaxMachInfo].[MaxMachineMem]) AS Consumable, C_ServerInfo_Copy.MaxMemory AS Total, IIf([t_MachineInfo].[MachineInfo_ID]=1,[t_ServerSizeInfo].[MemorySizeGiB]*[t_DicMemory].[Size],[t_ServerSizeInfo].[MemorySizeGiB]*DLookUp("Size","t_DicMemory","[Label]='TiB'")) AS MemorySizeBytes
    FROM t_DicMemory, t_Workload INNER JOIN (((t_ServerInfo INNER JOIN ((C_ServerInfo_Copy INNER JOIN (t_MachineInfo INNER JOIN C_MaxMachInfo ON t_MachineInfo.MachineInfo_ID = C_MaxMachInfo.MachineInfo_ID_FK) ON C_ServerInfo_Copy.MachineInfo_ID_FK = t_MachineInfo.MachineInfo_ID) INNER JOIN (t_CpuInfo INNER JOIN t_Junction ON t_CpuInfo.Cpu_id = t_Junction.CpuInfo_ID_FK) ON t_MachineInfo.MachineInfo_ID = t_Junction.MachineInfo_ID_FK) ON t_ServerInfo.ServerInfo_ID = t_CpuInfo.ServerInfo_ID_FK) INNER JOIN (t_ServerDetailedInfo INNER JOIN t_ServerSizeInfo ON t_ServerDetailedInfo.ServerDetailedInfo_Id = t_ServerSizeInfo.ServerDetailedInfo_ID_FK) ON (t_ServerSizeInfo.ServerSizeInfo_Id = t_Junction.ServerSizeInfo_ID_FK) AND (t_ServerInfo.ServerInfo_ID = t_ServerSizeInfo.ServerInfo_ID_FK)) INNER JOIN t_ServerSizeInfoToWorkload ON t_ServerSizeInfo.ServerSizeInfo_Id = t_ServerSizeInfoToWorkload.ServerSizeInfo_ID_FK) ON t_Workload.Workload_ID = t_ServerSizeInfoToWorkload.Workload_ID_FK
    WHERE (((t_DicMemory.Label)='GiB'));

    please help,
    Jacek

  • Anyone? 

    Jacek

  • jaryszek - Tuesday, March 27, 2018 6:10 AM

    Hi Guys,

    i have query  in Access like here:

    Now i want to build something similar in MS SQL Server.
    Except tables i am joining also queries  here.

    How can i do it in MS SQL Server?
    Create Views from tables and create constraints within another view? 
    I want to preserve all relationships here.

    C_ServerInfo_copy is query build from 2 tables. The same with C_MaxMachInfo. 

    SQL here is:
    SELECT t_MachineInfo.MachineName, t_CpuInfo.Server, t_CpuInfo.Model, t_Workload.WorkloadName, IIf([t_Workload].[WorkloadName]='BWoH' And [t_CpuInfo].[Cpu_id]=1,512*[t_DicMemory].[Size],IIf([t_Workload].[WorkloadName]='BWoH' And [t_CpuInfo].[Cpu_id]=2,512*[t_DicMemory].[Size],IIf([t_Workload].[WorkloadName]='SoH' And [t_CpuInfo].[Cpu_id]=1,1*DLookUp("Size","t_DicMemory","[Label]='TiB'"),DLookUp("Size","t_DicMemory","[Label]='TiB'")))) AS UsableMemoryBM, IIf([t_Workload].[WorkloadName]='BWoH' And [t_CpuInfo].[Cpu_id]=1,512*[t_DicMemory].[Size],IIf([t_Workload].[WorkloadName]='BWoH' And [t_CpuInfo].[Cpu_id]=2,512*[t_DicMemory].[Size],IIf([t_Workload].[WorkloadName]='SoH' And [t_CpuInfo].[Cpu_id]=1,768*[t_DicMemory].[Size],1*DLookUp("Size","t_DicMemory","[Label]='TiB'")))) AS UsableMemory55, IIf([t_Workload].[WorkloadName]='BWoH' And [t_CpuInfo].[Cpu_id]=1,512*[t_DicMemory].[Size]/(1024*1024*1024),IIf([t_Workload].[WorkloadName]='BWoH' And [t_CpuInfo].[Cpu_id]=2,512*[t_DicMemory].[Size]/(1024*1024*1024),IIf([t_Workload].[WorkloadName]='SoH' And [t_CpuInfo].[Cpu_id]=1,768*DLookUp("Size","t_DicMemory","[Label]='TiB'")/(1024*1024*1024),1*DLookUp("Size","t_DicMemory","[Label]='TiB'")/(1024*1024*1024)))) AS UsableMemory60, t_ServerInfo.Sockets, C_MaxMachInfo.MaxMachineMem, C_MaxMachInfo.HAStandbyServer, t_ServerDetailedInfo.Environment, IIf([t_MachineInfo].[MachineInfo_ID]=1,[UsableMemory55]*[t_ServerInfo].[Sockets],[UsableMemoryBM]*[t_ServerInfo].[Sockets]) AS ConsumableAdd, IIf([ConsumableAdd]<[C_MaxMachInfo].[MaxMachineMem],[ConsumableAdd],[C_MaxMachInfo].[MaxMachineMem]) AS Consumable, C_ServerInfo_Copy.MaxMemory AS Total, IIf([t_MachineInfo].[MachineInfo_ID]=1,[t_ServerSizeInfo].[MemorySizeGiB]*[t_DicMemory].[Size],[t_ServerSizeInfo].[MemorySizeGiB]*DLookUp("Size","t_DicMemory","[Label]='TiB'")) AS MemorySizeBytes
    FROM t_DicMemory, t_Workload INNER JOIN (((t_ServerInfo INNER JOIN ((C_ServerInfo_Copy INNER JOIN (t_MachineInfo INNER JOIN C_MaxMachInfo ON t_MachineInfo.MachineInfo_ID = C_MaxMachInfo.MachineInfo_ID_FK) ON C_ServerInfo_Copy.MachineInfo_ID_FK = t_MachineInfo.MachineInfo_ID) INNER JOIN (t_CpuInfo INNER JOIN t_Junction ON t_CpuInfo.Cpu_id = t_Junction.CpuInfo_ID_FK) ON t_MachineInfo.MachineInfo_ID = t_Junction.MachineInfo_ID_FK) ON t_ServerInfo.ServerInfo_ID = t_CpuInfo.ServerInfo_ID_FK) INNER JOIN (t_ServerDetailedInfo INNER JOIN t_ServerSizeInfo ON t_ServerDetailedInfo.ServerDetailedInfo_Id = t_ServerSizeInfo.ServerDetailedInfo_ID_FK) ON (t_ServerSizeInfo.ServerSizeInfo_Id = t_Junction.ServerSizeInfo_ID_FK) AND (t_ServerInfo.ServerInfo_ID = t_ServerSizeInfo.ServerInfo_ID_FK)) INNER JOIN t_ServerSizeInfoToWorkload ON t_ServerSizeInfo.ServerSizeInfo_Id = t_ServerSizeInfoToWorkload.ServerSizeInfo_ID_FK) ON t_Workload.Workload_ID = t_ServerSizeInfoToWorkload.Workload_ID_FK
    WHERE (((t_DicMemory.Label)='GiB'));

    please help,
    Jacek

    This is straight forward, the main thing would be replacing the IIFs with CASE and then rewrite the join statements, removing the nesting and adding aliases. 
    Can you post the DDL (create table) scripts and sample data as insert statements please?
    😎

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply