-
Feb 16th, 2017, 12:16 PM
#1
Thread Starter
PowerPoster
Not sure how to resolve SQL Error ?
I'm getting an error in my SQL script and just sure how to resolve it. You can see the attached screenshot but I have also included the complete query below.
Code:
DECLARE @MainID VARCHAR(1000)='127623,127625,127626,127627,127628,127629,127630,127631,127632,127633,127634'
DECLARE @cnt INT=0
SET NOCOUNT ON;
IF 1 = 0
SET FMTONLY OFF;
SELECT * INTO #TMP FROM [dbo].[ufnSingleColumnTable](@MainID, ',');
SELECT HL1.[MainID]
,SUBSTRING(CONVERT(VARCHAR, DL1.[LoadOutput]), 1, 6) AS LoadOutput
,MAX(DL1.[LoadValue]) AS LoadValue
,ROW_NUMBER() OVER (PARTITION BY HL1.MainID ORDER BY DL1.[LoadOutput]) AS "RowNumber"
INTO #LOADOUTPUTS
FROM [Test].[HEADER_Load] HL1 INNER JOIN [Test].[DETAILS_LoadLink] DLL1 ON HL1.[LoadID] = DLL1.[LoadID]
INNER JOIN [Test].[DETAILS_Load] DL1 ON DLL1.[LoadLinkID] = DL1.[LoadLinkID]
WHERE HL1.[MainID] IN (SELECT * FROM #TMP)
AND SUBSTRING(CONVERT(VARCHAR, LoadOutput), 1, 6) > '0.0000'
GROUP BY HL1.[MainID], LoadOutput, LoadValue;
SELECT MainID, MAX(LoadValue) AS LoadValue INTO #MAXLOAD FROM #LOADOUTPUTS GROUP BY MainID;
SELECT D.[SerialNO]
,A.[MainID]
,A.[ChannelID]
,FORMAT(A.[Capacity],'0.####') + ' ' + LU2.[Abbreviation] AS CapacityUnit
,B.[DirectionID]
,LD.[Direction]
,FORMAT(HR.[RIn], '0.###') AS RIn
,FORMAT(HR.[ROut], '0.###') AS ROut
,FORMAT(HF.[FirstZeroBalance], '0.000') AS ZeroBalance
,FORMAT(ML.[LoadValue], '0.###') AS LoadValue
,FORMAT(MAX(LoadOutput), '0.0000') AS FSOutput
,A.[ChannelID]
,'(' + LU1.[Abbreviation] + ')' AS Unit
,B.[FirstTemperature] AS Temperature
,B.[FirstHumidity] AS Humidity
,FORMAT(HE.[FirstMeasuredExcitation], '0.###') + ' Vdc' AS MeasuredExcitation
,LM.[Manufacturer]
,D.[ItemNO]
,D.[ModelNO]
,FORMAT(ML.[LoadValue], '0.####') + ' ' + LU2.[Abbreviation] AS FSLoad
FROM [Test].[HEADER_Load] A INNER JOIN [Test].[DETAILS_LoadLink] B ON A.[LoadID] = B.[LoadID]
INNER JOIN [Test].[DETAILS_Load] C ON B.[LoadLinkID] = C.[LoadLinkID]
INNER JOIN [Test].[HEADER_Item] D ON A.[MainID] = D.[MainID]
INNER JOIN [Test].[HEADER_Resistance] HR ON A.[MainID] = HR.[MainID]
INNER JOIN [Test].[HEADER_FirstFinalZero] HF ON A.[MainID] = HF.[MainID]
INNER JOIN [Test].[HEADER_Excitation] HE ON A.[MainID] = HE.[MainID]
INNER JOIN [Lookup].[Direction] AS LD ON B.[DirectionID] = LD.[DirectionID]
LEFT JOIN [Lookup].[Manufacturer] LM ON D.[ManufacturerID] = LM.[ManufacturerID]
LEFT JOIN [Lookup].[Units] AS LU1 ON A.[OutputUnitID] = LU1.[UnitID]
LEFT JOIN [Lookup].[Units] AS LU2 ON A.[LoadUnitID] = LU2.[UnitID]
LEFT JOIN [Lookup].[Channel] AS LC ON LC.[ChannelID] = A.[ChannelID]
INNER JOIN #MAXLOAD ML ON A.[MainID] = ML.[MainID]
WHERE A.[MainID] IN (@MainID)
GROUP BY B.[DirectionID], A.[MainID], D.[SerialNO], HF.[FirstZeroBalance], ML.[LoadValue], A.[ChannelID],
HR.[RIn], HR.[ROut], A.[Capacity], LU2.[Abbreviation], LD.[Direction], A.[ChannelID], LU1.[Abbreviation],
B.[FirstTemperature], B.[FirstHumidity], HE.[FirstMeasuredExcitation], LM.[Manufacturer], D.[ItemNO],
D.[ModelNO], ML.[LoadValue], LU2.[Abbreviation];
DROP TABLE #TMP;
DROP TABLE #MAXLOAD;
DROP TABLE #LOADOUTPUTS;
Attachment 145077
Last edited by blakemckenna; Feb 16th, 2017 at 12:21 PM.
Blake
-
Feb 16th, 2017, 12:37 PM
#2
Re: Not sure how to resolve SQL Error ?
What is the def of the function.... The complaint is appear to be coming from there. The value is to large for an INT data type
This is my split function:
sql Code:
CREATE FUNCTION usp_fnsplit (
@inputVals VARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @output TABLE (splitval VARCHAR(MAX))
BEGIN
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @inputVals)
WHILE @start < LEN(@inputVals) + 1
BEGIN
IF @end = 0
SET @end = LEN(@inputVals) + 1
INSERT INTO @output (splitval)
VALUES(SUBSTRING(@inputVals, @start, @end - @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @inputVals, @start)
END
RETURN
END
Last edited by GaryMazzone; Feb 16th, 2017 at 12:45 PM.
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
Feb 16th, 2017, 12:41 PM
#3
Thread Starter
PowerPoster
Re: Not sure how to resolve SQL Error ?
Gary,
Here is the complete SP.
Code:
USE [SENSITCalibrationDev]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ==================================================================================================================================
-- Author: Blake McKenna =
-- Description: This SP returns single record test data for multiple sensors. =
-- =
-- ******************************************************************************************************************************** =
-- M O D I F I C A T I O N L O G =
-- ******************************************************************************************************************************** =
-- =
-- PROGRAMMER DATE MODIFICATION =
-- -------------- ---------- -------------------------------------------------------------------------------------------- =
-- =
-- ==================================================================================================================================
ALTER PROCEDURE [Certificate].[uspGetConformanceLoadData3]
@MainID VARCHAR(1000)=NULL
AS
BEGIN
DECLARE @cnt INT;
SET @cnt = 0;
SET NOCOUNT ON;
IF 1 = 0
SET FMTONLY OFF;
SELECT * INTO #TMP FROM [dbo].[ufnSingleColumnTable](@MainID, ',');
SELECT HL1.[MainID]
,SUBSTRING(CONVERT(VARCHAR, DL1.[LoadOutput]), 1, 6) AS LoadOutput
,MAX(DL1.[LoadValue]) AS LoadValue
,ROW_NUMBER() OVER (PARTITION BY HL1.MainID ORDER BY DL1.[LoadOutput]) AS "RowNumber"
INTO #LOADOUTPUTS
FROM [Test].[HEADER_Load] HL1 INNER JOIN [Test].[DETAILS_LoadLink] DLL1 ON HL1.[LoadID] = DLL1.[LoadID]
INNER JOIN [Test].[DETAILS_Load] DL1 ON DLL1.[LoadLinkID] = DL1.[LoadLinkID]
WHERE HL1.[MainID] IN (SELECT * FROM #TMP)
AND SUBSTRING(CONVERT(VARCHAR, LoadOutput), 1, 6) > '0.0000'
GROUP BY HL1.[MainID], LoadOutput, LoadValue;
SELECT MainID, MAX(LoadValue) AS LoadValue INTO #MAXLOAD FROM #LOADOUTPUTS GROUP BY MainID;
SELECT D.[SerialNO]
,A.[MainID]
,A.[ChannelID]
,FORMAT(A.[Capacity],'0.####') + ' ' + LU2.[Abbreviation] AS CapacityUnit
,B.[DirectionID]
,LD.[Direction]
,FORMAT(HR.[RIn], '0.###') AS RIn
,FORMAT(HR.[ROut], '0.###') AS ROut
,FORMAT(HF.[FirstZeroBalance], '0.000') AS ZeroBalance
,FORMAT(ML.[LoadValue], '0.###') AS LoadValue
,FORMAT(MAX(LoadOutput), '0.0000') AS FSOutput
,A.[ChannelID]
,'(' + LU1.[Abbreviation] + ')' AS Unit
,B.[FirstTemperature] AS Temperature
,B.[FirstHumidity] AS Humidity
,FORMAT(HE.[FirstMeasuredExcitation], '0.###') + ' Vdc' AS MeasuredExcitation
,LM.[Manufacturer]
,D.[ItemNO]
,D.[ModelNO]
,FORMAT(ML.[LoadValue], '0.####') + ' ' + LU2.[Abbreviation] AS FSLoad
FROM [Test].[HEADER_Load] A INNER JOIN [Test].[DETAILS_LoadLink] B ON A.[LoadID] = B.[LoadID]
INNER JOIN [Test].[DETAILS_Load] C ON B.[LoadLinkID] = C.[LoadLinkID]
INNER JOIN [Test].[HEADER_Item] D ON A.[MainID] = D.[MainID]
INNER JOIN [Test].[HEADER_Resistance] HR ON A.[MainID] = HR.[MainID]
INNER JOIN [Test].[HEADER_FirstFinalZero] HF ON A.[MainID] = HF.[MainID]
INNER JOIN [Test].[HEADER_Excitation] HE ON A.[MainID] = HE.[MainID]
INNER JOIN [Lookup].[Direction] AS LD ON B.[DirectionID] = LD.[DirectionID]
LEFT JOIN [Lookup].[Manufacturer] LM ON D.[ManufacturerID] = LM.[ManufacturerID]
LEFT JOIN [Lookup].[Units] AS LU1 ON A.[OutputUnitID] = LU1.[UnitID]
LEFT JOIN [Lookup].[Units] AS LU2 ON A.[LoadUnitID] = LU2.[UnitID]
LEFT JOIN [Lookup].[Channel] AS LC ON LC.[ChannelID] = A.[ChannelID]
INNER JOIN #MAXLOAD ML ON A.[MainID] = ML.[MainID]
WHERE A.[MainID] IN (@MainID)
GROUP BY B.[DirectionID], A.[MainID], D.[SerialNO], HF.[FirstZeroBalance], ML.[LoadValue], A.[ChannelID],
HR.[RIn], HR.[ROut], A.[Capacity], LU2.[Abbreviation], LD.[Direction], A.[ChannelID], LU1.[Abbreviation],
B.[FirstTemperature], B.[FirstHumidity], HE.[FirstMeasuredExcitation], LM.[Manufacturer], D.[ItemNO],
D.[ModelNO], ML.[LoadValue], LU2.[Abbreviation];
DROP TABLE #TMP;
DROP TABLE #MAXLOAD;
DROP TABLE #LOADOUTPUTS;
END
-
Feb 16th, 2017, 12:58 PM
#4
Re: Not sure how to resolve SQL Error ?
Here is an example using my split function:
sql Code:
DROP TABLE test; CREATE TABLE test ( ID BIGINT IDENTITY, someNum BIGINT ) DECLARE @count INT =0, @run INT = 0 BEGIN WHILE @run < 300000 BEGIN SET @run = @run + 1 INSERT INTO dbo.test ( someNum ) VALUES ( @run) END END --TRUNCATE TABLE dbo.test SELECT COUNT(*) FROM dbo.test WITH (NOLOCK) DECLARE @MainID VARCHAR(1000)='127623,127625,127626,127627,127628,127629,127630,127631,127632,127633,127634' SELECT a.* FROM dbo.test a INNER JOIN dbo.usp_fnsplit(@MainID,',') b ON a.someNum = b.splitval
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
Feb 16th, 2017, 01:06 PM
#5
Thread Starter
PowerPoster
Re: Not sure how to resolve SQL Error ?
Sorry Gary...I'm kinda lost on what to do here. SQL coding isn't my strong suite. I'm thinking I need to apply the usp_fnsplit function on the #MAXLOAD temp table but not sure how to!
-
Feb 16th, 2017, 01:11 PM
#6
Re: Not sure how to resolve SQL Error ?
Create a testDB. Run the create function (usp_fnsplit)
then you can run the code I sent as the example. It will insert 300,000 into the table and then run a select based on the values in @main
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
Feb 16th, 2017, 01:20 PM
#7
Thread Starter
PowerPoster
Re: Not sure how to resolve SQL Error ?
I'm assuming your "Split" function is a User-Defined Function. Where is the code for it?
-
Feb 16th, 2017, 01:21 PM
#8
Re: Not sure how to resolve SQL Error ?
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
Feb 16th, 2017, 01:44 PM
#9
Thread Starter
PowerPoster
Re: Not sure how to resolve SQL Error ?
I've created the function but now my Query doesn't recognize the function!!!
What kind of function is usp_fnsplit? I've defined it under "Function" ---> "Table-valued Functions".
I've attached a screenshot.
-
Feb 20th, 2017, 08:32 AM
#10
Re: Not sure how to resolve SQL Error ?
I can't read the screen shot very well to small for these old eyes. It is a table valued function. The example in #4 showed how I used it
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
Feb 20th, 2017, 09:28 AM
#11
Thread Starter
PowerPoster
Re: Not sure how to resolve SQL Error ?
Gary,
After re-analyzing this query, I was able to remove everything but the main query. But your split function worked. Thank you for your help brother! I appreciate it!
Blessings!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|