March 27, 2018 at 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
March 27, 2018 at 10:50 pm
Anyone?
Jacek
March 28, 2018 at 2:33 am
jaryszek - Tuesday, March 27, 2018 6:10 AMHi 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