Solved

sql cast syntax

Posted on 2010-11-23
4
1,098 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 75

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

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

Suggested Solutions

Title # Comments Views Activity
Job Dependencies in SQL Server Agent: 1 31
Determine log file requirements 7 34
SQL profiler equivalent in MS-Access 3 41
SQL Server - SQL field is defined as text 3 27
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

914 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

23 Experts available now in Live!

Get 1:1 Help Now