Solved

What is wrong with this query

Posted on 2013-02-07
4
421 Views
Last Modified: 2013-02-07
Hi!

Have this query:

Declare
@SQL varchar(2000),
@version_id varchar(50),
@MyCursor CURSOR,
@perioden varchar(4),
@moneden varchar(2),
@aaret1 varchar(4),
@aaret2 int,
@maaned1 varchar(2),
@maaned2 int,
@periodeteller int,
@FORECASTYEARWEEK int,
@DATAAREAID varchar(4)


SET @MyCursor = CURSOR FAST_FORWARD
FOR
Select version_id,FORECASTYEARWEEK,DATAAREAID From dbo.Perioder_tmp_perioder

OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @version_id,@FORECASTYEARWEEK,@DATAAREAID
                                           
WHILE @@FETCH_STATUS = 0              
BEGIN
    
    SET @aaret1 = SUBSTRING (@FORECASTYEARWEEK,1,4)
    SET @aaret2 = CAST(@aaret1 AS INT)

    SET @maaned1 = SUBSTRING (@FORECASTYEARWEEK,5,2)
    SET @maaned2 = CAST(@maaned1 AS INT)    

    INSERT Into TMP_Forcast_Hit_Ratio_Art_version
    (    
    [DATAAREAID], 
    [ITEMID],
    [version_id],
    [FORECASTYEARWEEK],
    [ITEMIDDAT]  
    )
    SELECT 
      @DATAAREAID,
      ART.ITEMID,
      @version_id,
      @FORECASTYEARWEEK,
      ART.ITEMID+@DATAAREAID
    FROM dbo.DIM_Artikkel as ART
    WHERE ((year(ART.CREATEDDATETIME))=@aaret2) and (cast(DATEPART(week,ART.CREATEDDATETIME)as int)<=@maaned2)
      
    FETCH NEXT FROM @MyCursor
    INTO @version_id,@FORECASTYEARWEEK,@DATAAREAID 
END
  

Open in new window


Gives me this erro message:

 [Error Code: 8116, SQL State: S1000]  Argument data type int is invalid for argument 1 of substring function. 2) [Error Code: 8116, SQL State: S1000]  Argument data type int is invalid for argument 1 of substring function.

What is wrong ?
0
Comment
Question by:team2005
  • 2
4 Comments
 
LVL 32

Accepted Solution

by:
ewangoya earned 400 total points
ID: 38863330
You declared as an integer
@FORECASTYEARWEEK int

SUBSTRING function works on strings

The declaration should be of varchar
@FORECASTYEARWEEK varchar(32)   --use your field size
0
 
LVL 2

Author Comment

by:team2005
ID: 38863364
Hi!

@FORECASTYEARWEEK is INT in the database
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 100 total points
ID: 38863367
SET @aaret1 = SUBSTRING ( CAST(@FORECASTYEARWEEK as varchar(10)) ,1,4)
0
 
LVL 2

Author Closing Comment

by:team2005
ID: 38863454
thanks
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question