Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

What is wrong with this query

Posted on 2013-02-07
4
Medium Priority
?
449 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:
Ephraim Wangoya earned 1600 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 400 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

926 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