Move queries from Access to MS SQL Server

Question

Post reply

Move queries from Access to MS SQL Server

jaryszek

Default port

Points: 1425
More actions
March 27, 2018 at 6:10 am

#344517

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

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

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

jaryszek Default port Points: 1425 More actions · Answer 1

jaryszek

Default port

Points: 1425

March 27, 2018 at 10:50 pm

#1984739

Anyone?

Jacek

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 2

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?
😎