Solved

What is wrong with this query

Posted on 2013-02-07
4
417 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

864 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now