Solved

SQL Report Server (t-sql) Parse Error

Posted on 2010-11-23
11
434 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
  • 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
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.

 
LVL 3

Accepted Solution

by:
expert_dharam earned 500 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

815 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

12 Experts available now in Live!

Get 1:1 Help Now