April 22, 2010 at 9:43 am
Obviously you have to put the parameter name in there.
OK, let's try from scatch: copy and paste the query in a new query editor window.
Replace @Wayside with the actual value and run the query.
Does it work?
-- Gianluca Sartori
April 22, 2010 at 9:53 am
If I run the query this way, it works.
SELECT ( SELECT name + ',' + state
FROM [nms_cfg].[dbo].[ATCS_Group]
WHERE Group_Address = [WEA]
) 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_CFG].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = ( SELECT TOP 1
Base_Equip_Address
FROM [NMS_CFG].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([base1])
)
) + ' ( ' + [base1] + ')' AS 'Primary',
[SSI1],
( SELECT [Name] + ',' + [State]
FROM [NMS_CFG].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = ( SELECT TOP 1
Base_Equip_Address
FROM [NMS_CFG].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([base2])
)
) + ' ( ' + [base2] + ')' AS 'Secondary',
[SSI2],
( SELECT [Name] + ',' + [State]
FROM [NMS_CFG].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = ( SELECT TOP 1
Base_Equip_Address
FROM [NMS_CFG].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([base3])
)
) + ' ( ' + [base3] + ')' AS 'Tertiary',
[SSI3],
CAST([Date_Time] AS DATE) AS Date
FROM [NMS_RT].[dbo].[RT_Group_Coverage]
ORDER BY WEA, Date_Time
Running it like this results in the converting varchar to float error
SELECT ( SELECT name + ',' + state
FROM [nms_cfg].[dbo].[ATCS_Group]
WHERE Group_Address = [WEA]
AND name = WEA
) 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_CFG].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = ( SELECT TOP 1
Base_Equip_Address
FROM [NMS_CFG].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([base1])
)
) + ' ( ' + [base1] + ')' AS 'Primary',
[SSI1],
( SELECT [Name] + ',' + [State]
FROM [NMS_CFG].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = ( SELECT TOP 1
Base_Equip_Address
FROM [NMS_CFG].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([base2])
)
) + ' ( ' + [base2] + ')' AS 'Secondary',
[SSI2],
( SELECT [Name] + ',' + [State]
FROM [NMS_CFG].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = ( SELECT TOP 1
Base_Equip_Address
FROM [NMS_CFG].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([base3])
)
) + ' ( ' + [base3] + ')' AS 'Tertiary',
[SSI3],
CAST([Date_Time] AS DATE) AS Date
FROM [NMS_RT].[dbo].[RT_Group_Coverage]
WHERE WEA = Wayside
ORDER BY WEA, Date_Time
I also tried to use name in the AND statement.
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
April 22, 2010 at 9:56 am
Or did you mean to put the actual name of a location where the @Wayside is?
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
April 22, 2010 at 9:57 am
That's what I meant.
-- Gianluca Sartori
April 22, 2010 at 10:03 am
Ok, With the query looking like this;
SELECT ( SELECT name + ',' + state
FROM [nms_cfg].[dbo].[ATCS_Group]
WHERE Group_Address = [WEA]
AND name = 'Redondo'
) 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_CFG].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = ( SELECT TOP 1
Base_Equip_Address
FROM [NMS_CFG].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([base1])
)
) + ' ( ' + [base1] + ')' AS 'Primary',
[SSI1],
( SELECT [Name] + ',' + [State]
FROM [NMS_CFG].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = ( SELECT TOP 1
Base_Equip_Address
FROM [NMS_CFG].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([base2])
)
) + ' ( ' + [base2] + ')' AS 'Secondary',
[SSI2],
( SELECT [Name] + ',' + [State]
FROM [NMS_CFG].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = ( SELECT TOP 1
Base_Equip_Address
FROM [NMS_CFG].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([base3])
)
) + ' ( ' + [base3] + ')' AS 'Tertiary',
[SSI3],
CAST([Date_Time] AS DATE) AS Date
FROM [NMS_RT].[dbo].[RT_Group_Coverage]
ORDER BY WEA, Date_Time
The query completes however the Wayside column results with NULL.
It does give the right data for that location.
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
April 22, 2010 at 10:18 am
Ok, so let's take next step:
DECLARE @WaySide varchar(50)
SET @WaySide = 'Redondo'
SELECT ( SELECT name + ',' + state
FROM [nms_cfg].[dbo].[ATCS_Group]
WHERE Group_Address = [WEA]
AND name = @WaySide
) 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_CFG].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = ( SELECT TOP 1
Base_Equip_Address
FROM [NMS_CFG].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([base1])
)
) + ' ( ' + [base1] + ')' AS 'Primary',
[SSI1],
( SELECT [Name] + ',' + [State]
FROM [NMS_CFG].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = ( SELECT TOP 1
Base_Equip_Address
FROM [NMS_CFG].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([base2])
)
) + ' ( ' + [base2] + ')' AS 'Secondary',
[SSI2],
( SELECT [Name] + ',' + [State]
FROM [NMS_CFG].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = ( SELECT TOP 1
Base_Equip_Address
FROM [NMS_CFG].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([base3])
)
) + ' ( ' + [base3] + ')' AS 'Tertiary',
[SSI3],
CAST([Date_Time] AS DATE) AS Date
FROM [NMS_RT].[dbo].[RT_Group_Coverage]
ORDER BY WEA, Date_Time
I suppose it works now, doesn't it?
-- Gianluca Sartori
April 22, 2010 at 10:36 am
It works with the same results. Wayside column results with NULL.
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
April 22, 2010 at 10:45 am
If I delete the AND name = @WaySide it completes with the location name in the Wayside column.
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
April 22, 2010 at 10:48 am
OK, let's take it one step at a time.
CREATE PROCEDURE yourProcedureNameGoesHere
@WaySide VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON
SELECT ( SELECT name + ',' + state
FROM [nms_cfg].[dbo].[ATCS_Group]
WHERE Group_Address = [WEA]
AND name = @WaySide
) 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_CFG].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = ( SELECT TOP 1
Base_Equip_Address
FROM [NMS_CFG].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([base1])
)
) + ' ( ' + [base1] + ')' AS 'Primary',
[SSI1],
( SELECT [Name] + ',' + [State]
FROM [NMS_CFG].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = ( SELECT TOP 1
Base_Equip_Address
FROM [NMS_CFG].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([base2])
)
) + ' ( ' + [base2] + ')' AS 'Secondary',
[SSI2],
( SELECT [Name] + ',' + [State]
FROM [NMS_CFG].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = ( SELECT TOP 1
Base_Equip_Address
FROM [NMS_CFG].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([base3])
)
) + ' ( ' + [base3] + ')' AS 'Tertiary',
[SSI3],
CAST([Date_Time] AS DATE) AS Date
FROM [NMS_RT].[dbo].[RT_Group_Coverage]
ORDER BY WEA, Date_Time
END
EXEC yourProcedureNameGoesHere 'Redondo'
This should avoid conversion errors at least.
Now, let's talk about NULL Wayside returned.
What does this query return?
SELECT name + ',' + state
FROM [nms_cfg].[dbo].[ATCS_Group]
WHERE name = 'Redondo'
-- Gianluca Sartori
April 22, 2010 at 11:33 am
EXEC yourProcedureNameGoesHere 'Redondo East'
Ok, the StoredProcedure part now runs without an error while in SQL server, however it is not returning just the location specified. It returns all of the records in the table, and displays the Name of the location specified.
This query returns NULL for the name, however if I specify 'Redondo East' as the name it returns the correct results.
SELECT name + ',' + state
FROM [nms_cfg].[dbo].[ATCS_Group]
WHERE name = 'Redondo'
[/quote]
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
April 22, 2010 at 12:19 pm
If I run the StoredProcedure from the ASP.NET page, it fails to complete because it runs out of memory.
I modified the Select statement to read the TOP 100 and the call for the stored procedure returns the correct data for the 100 lines with Redondo East in the Wayside column. This at least verifies my ASP.NET function call works correctly and it will display the data I want that way.
The query does not seem to be filtering only for the specified location.
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
April 23, 2010 at 1:35 am
OK, looks like you need to filter the whole data set this way:
CREATE PROCEDURE yourProcedureNameGoesHere
@WaySide VARCHAR(50)
AS
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_CFG].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = ( SELECT TOP 1
Base_Equip_Address
FROM [NMS_CFG].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([base1])
)
) + ' ( ' + [base1] + ')' AS 'Primary',
[SSI1],
( SELECT [Name] + ',' + [State]
FROM [NMS_CFG].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = ( SELECT TOP 1
Base_Equip_Address
FROM [NMS_CFG].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([base2])
)
) + ' ( ' + [base2] + ')' AS 'Secondary',
[SSI2],
( SELECT [Name] + ',' + [State]
FROM [NMS_CFG].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = ( SELECT TOP 1
Base_Equip_Address
FROM [NMS_CFG].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([base3])
)
) + ' ( ' + [base3] + ')' AS 'Tertiary',
[SSI3],
CAST([Date_Time] AS DATE) AS Date
FROM [NMS_RT].[dbo].[RT_Group_Coverage] AS Cov
INNER JOIN [nms_cfg].[dbo].[ATCS_Group] AS Grp
ON Grp.Group_Address = Cov.[WEA]
WHERE Grp.name = @WaySide
ORDER BY WEA, Date_Time
END
EXEC yourProcedureNameGoesHere 'Redondo East'
-- Gianluca Sartori
April 23, 2010 at 6:14 am
YES!!! You are the Man!
Worked like a charm. I thank you for teaching me quite a bit in one little stored procedure.
Thanks for all of you time, and knowledge.
Brian
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
April 23, 2010 at 6:25 am
At last!
I'm glad you got it working.
-- Gianluca Sartori
April 23, 2010 at 6:27 am
Me too. I greatly appreciate your assistance. And it is always good to learn something new at the same time.
Thanks,
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply