Solved

sql cast syntax

Posted on 2010-11-23
4
1,089 Views
Last Modified: 2012-05-10
I am getting an error I have not been able to figure out.
Server: Msg 1035, Level 15, State 10, Line 54
Incorrect syntax near 'CAST', expected 'AS'.

DECLARE @Today DATETIME

SET @Today = GETDATE()

DECLARE @fiscalYr  NUMERIC

SET @fiscalYr = 

  case

    when MONTH(@Today) >= 11 then Year(@Today) + 1 

    else  Year(@Today)

  end

DECLARE @period  NUMERIC

SET @period = 

  case

    when MONTH(@Today) >= 11 then MONTH(@Today) - 10

    else  Month(@Today)+ 2

end  

DECLARE @FYR varchar

SET @FYR = cast(@fiscalYr as varchar(4))



DECLARE @PER varchar

SET @PER = cast(@period as varchar(2))



insert into dbo.EDCOGMATUS

select

DOCSRC,  

REFNO,   

DOCDESC, 

DOCGRP,  

FSCYR,   

DOCDATE, 

DOCPER,  

AUTOREV, 

SUMM,    

ACCTTYP, 

ACCTNO,  

DETDESC,

SUM(TRAN_AMOUNT) AS AMOUNT,

DBCR,     

SUMMACCT, 

JCGRTYP,  

PANO,     

LFCHAR   

FROM

(

 SELECT 

 'je_import ' as DOCSRC,

 Substring(PA30101.PAPROJNUMBER + space(9), 1, 9)  as REFNO,

 substring('Cogsdale Labor Usage' + 

           space(40), 1,40) as DOCDESC,

 'COGLABUSED' as DOCGRP,

  /* --------fiscal year case begin---------------- */

 case

 when month(PA30100.PAPD) >= '10'

 

 then 

   CAST(YEAR(PA30100.PAPD) + 1)  AS varCHAR(4)

 

  else  

   CAST(YEAR(PA30100.PAPD))  AS varCHAR(4)

 end  as FSCYR,

/* ------------------case end---------------- */

 

 Right('0' + CAST(MONTH(@Today) AS varCHAR(2)),2) + 

       Right('0' + CAST(DAY(@Today) AS varCHAR(2)),2) + 

                CAST(YEAR(@Today) AS varCHAR(4)) as DOCDATE,

 /* --------fiscal period case begin---------------- */

 case

 when month(PA30100.PAPD) >= '10'

 

 then 

   CASTmonth(PA30100.PAPD) - 9  AS varCHAR(2)

 

  else  

   CASTmonth(PA30100.PAPD) + 3  AS varCHAR(2)

 end  as DOCPER,

/* ------------------case end---------------- */               

 'N' as AUTOREV,

 'Y' as SUMM,

 'B' as ACCTTYP,

 '457-381-33200' as ACCTNO,

 substring(GL00100.ACTALIAS + space(30), 1, 30) as DETDESC,

 PA30103.DEBITAMT - PA30103.CRDTAMT AS TRAN_AMOUNT,

 'C' as DBCR,

 ' ' as SUMMACCT,

 ' ' as JCGRTYP,

 space(32) as PANO,

 'Ž' as LFCHAR

 

 

 FROM  

  PA30101 PA30101

  LEFT OUTER JOIN PA30100 PA30100 

  ON PA30101.PATSNO=PA30100.PATSNO 

  AND PA30101.EMPLOYID=PA30100.EMPLOYID 

  LEFT JOIN PA01201 PA01201 

  ON PA30101.PAPROJNUMBER=PA01201.PAPROJNUMBER 

  LEFT OUTER JOIN PA30103 PA30103 

  ON PA30101.PATSNO=PA30103.PATSNO 

  INNER JOIN GL00100 GL00100 

  ON PA30103.DSTINDX=GL00100.ACTINDX



 

 WHERE  GL00100.ACTALIAS LIKE '33%' 

  AND FISCYR=@FYR

  AND DOCPER = @PER

) x

GROUP BY

DOCSRC,  

REFNO,   

DOCDESC, 

DOCGRP,  

FSCYR,   

DOCDATE, 

DOCPER,  

AUTOREV, 

SUMM,    

ACCTTYP, 

ACCTNO,  

DETDESC, 

DBCR,     

SUMMACCT, 

JCGRTYP,  

PANO,     

LFCHAR

Open in new window

0
Comment
Question by:qbjgqbjg
4 Comments
 
LVL 74

Accepted Solution

by:
käµfm³d   👽 earned 250 total points
ID: 34199997
Change line 54 to:
CAST(YEAR(PA30100.PAPD) + 1  AS varCHAR(4))

Open in new window

0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34199999
the syntax for CAST is

CAST( {expr} AS {type} )

not

CAST( {expr} )  AS {type}
0
 
LVL 32

Assisted Solution

by:Erick37
Erick37 earned 250 total points
ID: 34200002
Looks like the ( ) are missing in this

 /* --------fiscal period case begin---------------- */
 case
 when month(PA30100.PAPD) >= '10'
 
 then
   CASTmonth(PA30100.PAPD) - 9  AS varCHAR(2)
 
  else  
   CASTmonth(PA30100.PAPD) + 3  AS varCHAR(2)
 end  as DOCPER,
/* ------------------case end---------------- */              



It should be:
then
CAST(month(PA30100.PAPD) - 9  AS varCHAR(2))
else  
CAST(month(PA30100.PAPD) + 3  AS varCHAR(2))




0
 

Author Closing Comment

by:qbjgqbjg
ID: 34200106
Thanks. I appreciate it.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

747 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

13 Experts available now in Live!

Get 1:1 Help Now