September 3, 2013 at 9:18 am
I have a query that I run as a stored procedure. It was written by someone else, and I am not that well versed in this type of query.
USE [NMS_RT]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
BEGIN
SET NOCOUNT ON;
SELECT ( Grp.name + ',' + Grp.state ) AS Wayside,
( SELECT 'G'
+ SUBSTRING(CONVERT(VARCHAR(100), CAST(WEA AS DECIMAL(38, 2))), 3, 3) + '/'
+ SUBSTRING(CONVERT(VARCHAR(100), CAST(WEA AS DECIMAL(38, 2))), 6, 3)
) AS 'Group ID',
( SELECT [Name] + ',' + [State]
FROM [NMS_CFG2].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = ( SELECT TOP 1
Base_Equip_Address
FROM [NMS_CFG2].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([base1])
)
) + ' ( ' + [base1] + ')' AS 'Primary',
( SELECT [Name] + ',' + [State]
FROM [NMS_CFG2].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = ( SELECT TOP 1
Base_Equip_Address
FROM [NMS_CFG2].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([base2])
)
) + ' ( ' + [base2] + ')' AS 'Secondary',
CAST([Date_Time] AS DATE) AS Date
FROM [NMS_RT].[dbo].[RT_Group_Status] AS Cov
INNER JOIN [NMS_CFG2].[dbo].[ATCS_Group] AS Grp
ON Grp.Group_Address = Cov.[WEA]
WHERE Lock1=1 or Lock2=1
ORDER BY Date DESC, Wayside
END
This code outputs the data like this;
20th Street MP 542.1,CO,G192/007,Denver IP GTC,CO ( 259.1.01 ),Globeville IP GTC,CO ( 278.1.01 ),2013-09-03
There are two additional fields that I need to add in this query that are part of the RT_Group_Status table. They are SSI1, SSI2. These fields are INT data type.
If possible I would like to add them inside of the ( ) field shown above e.g. (259.1.01/222).
Appreciate any help.
Thanks,
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
September 3, 2013 at 9:46 am
Maybe you need to change the primary column to something like this?
( SELECT [Name] + ',' + [State]
FROM [NMS_CFG2].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = ( SELECT TOP 1
Base_Equip_Address
FROM [NMS_CFG2].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([base2])
)
) + ' ( ' + [base2] + CAST(Cov.SSI1 AS VARCHAR) + CAST(Cov.SSI2 AS VARCHAR) + ')' AS 'Secondary',
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
September 3, 2013 at 10:05 am
That was exactly what I needed!
Thank you for the help!!!
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
September 3, 2013 at 10:10 am
Anytime 😀
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
September 3, 2013 at 11:31 am
I very much appreciate the help. This is what my final query ended as;
USE [NMS_RT]
GO
/****** Object: StoredProcedure [dbo].[GrpStatus] Script Date: 09/03/2013 12:48:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
BEGIN
SET NOCOUNT ON;
SELECT ( Grp.name + ',' + Grp.state ) AS Wayside,
( SELECT 'G'
+ SUBSTRING(CONVERT(VARCHAR(100), CAST(WEA AS DECIMAL(38, 2))), 3, 3) + '/'
+ SUBSTRING(CONVERT(VARCHAR(100), CAST(WEA AS DECIMAL(38, 2))), 6, 3)
) AS 'Group ID',
( SELECT [Name] + ',' + [State]
FROM [NMS_CFG2].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = ( SELECT TOP 1
Base_Equip_Address
FROM [NMS_CFG2].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([base1])
)
) + ' ( ' + [base1] + ')' AS 'Primary',
CAST(Cov.SSI1 as VARCHAR) as 'Primary SSI',
( SELECT [Name] + ',' + [State]
FROM [NMS_CFG2].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = ( SELECT TOP 1
Base_Equip_Address
FROM [NMS_CFG2].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([base2])
)
) + ' ( ' + [base2] + ')' AS 'Secondary',
CAST(Cov.SSI2 as VARCHAR) as 'Secondary SSI',
( SELECT [Name] + ',' + [State]
FROM [NMS_CFG2].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = ( SELECT TOP 1
Base_Equip_Address
FROM [NMS_CFG2].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([base3])
)
) + ' ( ' + [base3] + ')' AS 'Tertiary',
CAST(Cov.SSI3 as VARCHAR) as 'Tertiary SSI',
CAST([Date_Time] AS DATE) AS Date
FROM [NMS_RT].[dbo].[RT_Group_Status] AS Cov
INNER JOIN [NMS_CFG2].[dbo].[ATCS_Group] AS Grp
ON Grp.Group_Address = Cov.[WEA]
--WHERE Lock1=1 or Lock2=1
ORDER BY [Group ID], Wayside
END
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
September 3, 2013 at 11:48 am
Sweet.
One thing I should have mentioned is that its best to specify a length when casting/converting to VARCHAR. But I suppose it doesn't matter in your case as you're casting an INT which will always be 10 digits or less.
VARCHAR(n) where n is the max number if characters. I think SQL Server defaults to 30 if n is omitted like I've done.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
September 3, 2013 at 11:50 am
Thank you for the suggestion. Max is only three digits for it. If I start running into issues, I will add the value you suggest.
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
September 3, 2013 at 11:57 am
brian.cook (9/3/2013)
Thank you for the suggestion. Max is only three digits for it. If I start running into issues, I will add the value you suggest.
Why not just add the length? It takes no time to do it and makes your code that much better.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 3, 2013 at 12:07 pm
Sounds like a good idea. Hitting the reference up now.
Thanks again.
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply