Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Report Server (t-sql) Parse Error

Posted on 2010-11-23
11
Medium Priority
?
440 Views
Last Modified: 2012-05-10
Hi Experts! How can I optimize/fix this query and why am I getting this error (attached)
SELECT     *
FROM         (SELECT     transDate, transYear, RecordID, QTY, extendedstandardCost, currencyCode, BusinessEntity, product, site, lineValue AS 'OrderAmount', 
                                              Cast(0 AS NUMERIC(30, 2)) AS 'InvoiceAmount', subproduct, CASE WHEN TransYear = Year(getdate()) 
                                              - 1 THEN LineValue ELSE Cast(0 AS Numeric(30, 2)) END AS 'OrderAmount_LastYear', Cast(0 AS Numeric(30, 2)) 'InvoiceAmount_LastYear', 
                                              CASE WHEN TransYear = Year(getdate()) THEN LineValue ELSE Cast(0 AS Numeric(30, 2)) END AS 'OrderAmount_CurrentYear', 
                                              Cast(0 AS Numeric(30, 2)) 'InvoiceAmount_CurrentYear'
                       FROM          vReportData
                       WHERE      (recType = 'order') AND (businessEntity = 'Energy')
                       UNION
                       SELECT     transDate, transYear, RecordID, QTY, extendedstandardCost, currencyCode, BusinessEntity, product, site, 0 AS 'OrderAmount', 
                                             lineValue AS 'InvoiceAmount', subproduct, Cast(0 AS Numeric(30, 2)) 'OrderAmount_LastYear', CASE WHEN TransYear = Year(getdate()) 
                                             - 1 THEN LineValue ELSE Cast(0 AS Numeric(30, 2)) END AS 'InvoiceAmount_LastYear', Cast(0 AS Numeric(30, 2)) 
                                             'OrderAmount_CurrentYear', CASE WHEN TransYear = Year(getdate()) THEN LineValue ELSE Cast(0 AS Numeric(30, 2)) 
                                             END AS 'InvoiceAmount_CurrentYear'
                       FROM         vReportData AS ReportData1
                       WHERE     (recType = 'invoice') AND (businessEntity = 'Energy')) A,
                          (SELECT     20 + COUNT(*) WorkDayCount
                            FROM          (SELECT     DATEADD(MONTH, DATEDIFF(MONTH, 0, getDate()), 28) AS theDate
                                                    UNION
                                                    SELECT     DATEADD(MONTH, DATEDIFF(MONTH, 0, getDate()), 29)
                                                    UNION
                                                    SELECT     DATEADD(MONTH, DATEDIFF(MONTH, 0, getDate()), 30)) AS d
                            WHERE      DATEPART(DAY, theDate) > 28 AND DATEDIFF(DAY, 0, theDate) % 7 < 5) B

Open in new window

Error.doc
0
Comment
Question by:SPLady
[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
  • 6
  • 4
11 Comments
 
LVL 10

Expert Comment

by:Humpdy
ID: 34196925
at the end of your first select statement, put in AS

 Cast(0 AS Numeric(30, 2)) AS 'InvoiceAmount_CurrentYear'
0
 
LVL 1

Author Comment

by:SPLady
ID: 34197023
Thanks @Humpdy I added the 'as" and am still getting an parse error
0
 
LVL 1

Author Comment

by:SPLady
ID: 34197074
How can I rewrite this without the union?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 3

Accepted Solution

by:
expert_dharam earned 2000 total points
ID: 34197282
let me know wether this part of your query is successfully running..
i have modified it..
if it is.. i will assist u further..
SELECT transDate, 
				transYear, 
				RecordID, 
				QTY, 
				extendedstandardCost, 
				currencyCode, 
				BusinessEntity, 
				product, 
				site, 
				lineValue AS 'OrderAmount', 
                Cast(0 AS NUMERIC(30, 2)) AS 'InvoiceAmount', 
                subproduct, 
                CASE WHEN TransYear = Year(getdate()) - 1 THEN LineValue ELSE Cast(0 AS Numeric(30, 2)) END AS 'OrderAmount_LastYear', 
                Cast(0 AS Numeric(30, 2)) 'InvoiceAmount_LastYear', 
                CASE WHEN TransYear = Year(getdate()) THEN LineValue ELSE Cast(0 AS Numeric(30, 2)) END AS 'OrderAmount_CurrentYear', 
                Cast(0 AS Numeric(30, 2)) 'InvoiceAmount_CurrentYear'
			FROM vReportData
			WHERE (recType = 'order') AND (businessEntity = 'Energy')
			
				UNION
				
			SELECT	transDate, 
					transYear, 
					RecordID, 
					QTY, 
					extendedstandardCost, 
					currencyCode, 
					BusinessEntity, 
					product, 
					site, 
					0 AS 'OrderAmount', 
					lineValue AS 'InvoiceAmount', 
					subproduct, 
					Cast(0 AS Numeric(30, 2)) AS 'OrderAmount_LastYear', 
					CASE WHEN TransYear = Year(getdate()) - 1 THEN LineValue ELSE Cast(0 AS Numeric(30, 2)) END AS 'InvoiceAmount_LastYear', 
					Cast(0 AS Numeric(30, 2)) AS 'OrderAmount_CurrentYear', 
					CASE WHEN TransYear = Year(getdate()) THEN LineValue ELSE Cast(0 AS Numeric(30, 2)) END AS 'InvoiceAmount_CurrentYear'
			FROM         vReportData AS ReportData1
			WHERE     (recType = 'invoice') AND (businessEntity = 'Energy')

Open in new window

0
 
LVL 1

Author Comment

by:SPLady
ID: 34197334
@expert_dharam yes the above part is working!
0
 
LVL 3

Expert Comment

by:expert_dharam
ID: 34197412
let me know what u want to acheive next..
0
 
LVL 1

Author Comment

by:SPLady
ID: 34197482

Thank you @ expert_dharam
I am using it as part of the attached query is there a way to optimize the whole thing? Sometimes the above join indicates I need to declare the start and end and sometimes it doesnt.
SELECT     vReportdataCurrentPrior.transDate, vReportdataCurrentPrior.extendedstandardCost, vReportdataCurrentPrior.BusinessEntity, 
                      vReportdataCurrentPrior.product, vReportdataCurrentPrior.site, vReportdataCurrentPrior.subproduct, vReportdataCurrentPrior.transYear, 
                      vReportdataCurrentPrior.OrderAmount_LastYear, vReportdataCurrentPrior.InvoiceAmount_LastYear, 
                      vReportdataCurrentPrior.OrderAmount_CurrentYear, vReportdataCurrentPrior.InvoiceAmount_CurrentYear, ExchangeRates.ExchangeRate, 
                      (DATEDIFF(DD, CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, GETDATE()) + 1, GETDATE()) AS DOUBLE PRECISION)) AS DATETIME), 
                      CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, DATEADD(MONTH, 1, GETDATE())), DATEADD(MONTH, 1, GETDATE())) AS DOUBLE PRECISION)) 
                      AS DATETIME)) + 1) - (DATEDIFF(DD, DATEADD(DD, 6 - (DATEPART(DW, CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, GETDATE()) + 1, GETDATE()) 
                      AS DOUBLE PRECISION)) AS DATETIME)) + @@DATEFIRST - 2) % 7, CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, GETDATE()) + 1, GETDATE()) 
                      AS DOUBLE PRECISION)) AS DATETIME)), DATEADD(DD, - ((DATEPART(DW, CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, DATEADD(MONTH, 1, 
                      GETDATE())), DATEADD(MONTH, 1, GETDATE())) AS DOUBLE PRECISION)) AS DATETIME)) + @@DATEFIRST - 2) % 7), CAST(FLOOR(CAST(DATEADD(DD, 
                      - DATEPART(DD, DATEADD(MONTH, 1, GETDATE())), DATEADD(MONTH, 1, GETDATE())) AS DOUBLE PRECISION)) AS DATETIME))) - 1) 
                      / 7 * 2 - CASE (DATEPART(DW, CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, GETDATE()) + 1, GETDATE()) AS DOUBLE PRECISION)) AS DATETIME)) 
                      + @@DATEFIRST - 2) % 7 + 1 WHEN 6 THEN 2 WHEN 7 THEN 1 ELSE 2 END - CASE (DATEPART(DW, CAST(FLOOR(CAST(DATEADD(DD, 
                      - DATEPART(DD, DATEADD(MONTH, 1, GETDATE())), DATEADD(MONTH, 1, GETDATE())) AS DOUBLE PRECISION)) AS DATETIME)) + @@DATEFIRST - 2) 
                      % 7 + 1 WHEN 6 THEN 1 WHEN 7 THEN 2 ELSE 0 END AS MonthWorkingDays, (DATEDIFF(DD, @start, @end) + 1) - (DATEDIFF(DD, DATEADD(DD, 
                      6 - (DATEPART(DW, @start) + @@DATEFIRST - 2) % 7, @start), DATEADD(DD, - ((DATEPART(DW, @end) + @@DATEFIRST - 2) % 7), @end)) - 1) 
                      / 7 * 2 - CASE (DATEPART(DW, @start) + @@DATEFIRST - 2) % 7 + 1 WHEN 6 THEN 2 WHEN 7 THEN 1 ELSE 2 END - CASE (DATEPART(DW, @end) 
                      + @@DATEFIRST - 2) % 7 + 1 WHEN 6 THEN 1 WHEN 7 THEN 2 ELSE 0 END AS WorkingDays, EnergySites.EnergyOrg, 
                      vReportdataCurrentPrior.QTY
FROM         vReportdataCurrentPrior INNER JOIN
                      ExchangeRates ON MONTH(vReportdataCurrentPrior.transDate) = ExchangeRates.XRateMonth AND 
                      vReportdataCurrentPrior.transYear = ExchangeRates.XRateYear AND vReportdataCurrentPrior.currencyCode = ExchangeRates.Currency INNER JOIN
                      EnergySites ON vReportdataCurrentPrior.site = EnergySites.SiteNumber
WHERE     (vReportdataCurrentPrior.transDate BETWEEN @start AND @end OR
                      vReportdataCurrentPrior.transDate BETWEEN DATEADD(yy, - 1, @start) AND DATEADD(yy, - 1, @end)) AND (EnergySites.EnergyOrg = @energyorg) AND
                       (vReportdataCurrentPrior.BusinessEntity = 'energy')
ORDER BY vReportdataCurrentPrior.transDate DESC

Open in new window

0
 
LVL 3

Expert Comment

by:expert_dharam
ID: 34198193
sorry.. i am not able to understand the relationship between the two query sets..
0
 
LVL 1

Author Comment

by:SPLady
ID: 34198303
The part of the query attached  below is from the first query set that you rewrote without the  b (date) part of the query which is used in the final query set above... I want to optimize/rewrite the final query set above with the working days and month working days--  
SELECT     vReportdataCurrentPrior.transDate, vReportdataCurrentPrior.extendedstandardCost, vReportdataCurrentPrior.BusinessEntity, 
                      vReportdataCurrentPrior.product, vReportdataCurrentPrior.site, vReportdataCurrentPrior.subproduct, vReportdataCurrentPrior.transYear, 
                      vReportdataCurrentPrior.OrderAmount_LastYear, vReportdataCurrentPrior.InvoiceAmount_LastYear, 
                      vReportdataCurrentPrior.OrderAmount_CurrentYear, vReportdataCurrentPrior.InvoiceAmount_CurrentYear, ExchangeRates.ExchangeRate

Open in new window

0
 
LVL 1

Author Closing Comment

by:SPLady
ID: 34198334
Thank you, removing the date caluculation did correct the parse error--
0
 
LVL 3

Expert Comment

by:expert_dharam
ID: 34198495
Thanks for accepting my answer..
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

593 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