Solved

What is wrong with this query

Posted on 2013-02-07
4
433 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya 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 143

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

632 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