Not sure how to resolve SQL Error ?-VBForums
Results 1 to 11 of 11

Thread: Not sure how to resolve SQL Error ?

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Jan 2004
    Location
    Southern California
    Posts
    4,555

    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
    Attached Files Attached Files
    Last edited by blakemckenna; Feb 16th, 2017 at 11:21 AM.
    Blake

  2. #2
    #28 for the Yanks coming GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,266

    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:
    1. CREATE FUNCTION usp_fnsplit (
    2.     @inputVals VARCHAR(MAX),
    3.     @delimiter CHAR(1)
    4. )
    5. RETURNS @output TABLE (splitval VARCHAR(MAX))
    6. BEGIN
    7.     DECLARE @start INT, @end INT
    8.     SELECT @start = 1, @end = CHARINDEX(@delimiter, @inputVals)
    9.     WHILE @start < LEN(@inputVals) + 1
    10.         BEGIN
    11.             IF @end = 0  
    12.                 SET @end = LEN(@inputVals) + 1
    13.        
    14.             INSERT INTO @output (splitval)  
    15.             VALUES(SUBSTRING(@inputVals, @start, @end - @start))
    16.             SET @start = @end + 1
    17.             SET @end = CHARINDEX(@delimiter, @inputVals, @start)
    18.        
    19.         END
    20.     RETURN
    21. END
    Last edited by GaryMazzone; Feb 16th, 2017 at 11:45 AM.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  3. #3

    Thread Starter
    PowerPoster
    Join Date
    Jan 2004
    Location
    Southern California
    Posts
    4,555

    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
    Blake

  4. #4
    #28 for the Yanks coming GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,266

    Re: Not sure how to resolve SQL Error ?

    Here is an example using my split function:

    sql Code:
    1. DROP TABLE test;
    2. CREATE TABLE test (
    3.     ID BIGINT IDENTITY,
    4.     someNum BIGINT
    5. )
    6.  
    7. DECLARE @count INT =0,
    8.         @run INT = 0
    9.  
    10. BEGIN
    11.     WHILE @run < 300000
    12.         BEGIN
    13.             SET @run = @run + 1
    14.             INSERT INTO dbo.test
    15.                     ( someNum )
    16.             VALUES
    17.                     ( @run)
    18.         END
    19. END
    20. --TRUNCATE TABLE dbo.test
    21.  
    22. SELECT COUNT(*) FROM dbo.test WITH (NOLOCK)
    23.  
    24. DECLARE  @MainID VARCHAR(1000)='127623,127625,127626,127627,127628,127629,127630,127631,127632,127633,127634'
    25.  
    26. SELECT a.* FROM dbo.test a
    27. INNER JOIN dbo.usp_fnsplit(@MainID,',') b
    28.     ON a.someNum = b.splitval
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  5. #5

    Thread Starter
    PowerPoster
    Join Date
    Jan 2004
    Location
    Southern California
    Posts
    4,555

    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!
    Blake

  6. #6
    #28 for the Yanks coming GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,266

    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

  7. #7

    Thread Starter
    PowerPoster
    Join Date
    Jan 2004
    Location
    Southern California
    Posts
    4,555

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

  8. #8
    #28 for the Yanks coming GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,266

    Re: Not sure how to resolve SQL Error ?

    Post #2
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  9. #9

    Thread Starter
    PowerPoster
    Join Date
    Jan 2004
    Location
    Southern California
    Posts
    4,555

    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.
    Attached Images Attached Images  
    Blake

  10. #10
    #28 for the Yanks coming GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,266

    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

  11. #11

    Thread Starter
    PowerPoster
    Join Date
    Jan 2004
    Location
    Southern California
    Posts
    4,555

    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!
    Blake

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.