July 10, 2015 at 6:13 am
Hello experts,
I am getting the following error when trying to run this script: "Incorrect syntax near the keyword 'ELSE'." My goal is to change all the GivenRates that are equal to zero in the ZIF19_ACCDOC_H_T table to the monthly average stored by currency, month, year and rate in MonthlyExRates table to a monthly average rate I have in another database. GJAHR = Year, MONAT = Month UKURS = ISO Currency Code.
Many thanks in advance!
CREATE TABLE [dbo].[MonthlyExRates](
[GJAHR] [float] NULL,
[MONAT] [float] NULL,
[Rate] [float] NULL,
[ISOCurCode] [nvarchar](255) NULL
)
GO
INSERT INTO MonthlyExRates
(GJAHR, MONAT, Rate, ISOCurCode)
SELECT '2015','1',.1709,'USD' UNION ALL
SELECT '2015','2',.1819,'USD' UNION ALL
SELECT '2015','3',.1793,'USD' UNION ALL
SELECT '2015','4',.1693,'USD' UNION ALL
SELECT '2015','1',.1609,'CAD' UNION ALL
SELECT '2015','2',.1719,'CAD' UNION ALL
SELECT '2015','3',.1693,'CAD' UNION ALL
SELECT '2015','4',.1593,'CAD'
CREATE TABLE [dbo].[ZIF19_ACCDOC_H_T](
[GJAHR] [nvarchar](4) NOT NULL,
[MONAT] [nvarchar](2) NOT NULL,
[HWAER] [nvarchar](5) NOT NULL,
[UKURS] [decimal](9, 5) NULL
)
GO
INSERT INTO dbo.ZIF19_ACCDOC_H_T
(GJAHR, MONAT, UKURS, HWAER )
SELECT '2015','1',.1459,'USD' UNION ALL
SELECT '2015','2',.1799,'USD' UNION ALL
SELECT '2015','3',0,'USD' UNION ALL
SELECT '2015','4',.1693,'USD' UNION ALL
SELECT '2015','1',0,'CAD' UNION ALL
SELECT '2015','2',.1679,'CAD' UNION ALL
SELECT '2015','3',0,'CAD' UNION ALL
SELECT '2015','4',.1453,'CAD'
GO
SELECT
[GJAHR]
,[MONAT]
,[HWAER]
,[UKURS]
,UseRate = CASE WHEN [UKURS] = 0 THEN (SELECT (1/[[dbo].[MonthlyExRates].[Rate])
ELSE [dbo].[ZIF19_ACCDOC_H_T].[UKURS]
END
FROM [dbo].[ZIF19_ACCDOC_H_T]
JOIN [dbo].[MonthlyExRates] ON ([SAP].[ZIF19_ACCDOC_H_T].[GJAHR] = [dbo].[MonthlyExRates].[GJAHR] AND [SAP].[ZIF19_ACCDOC_H_T].[MONAT] = [dbo].[MonthlyExRates].[MONAT] AND [SAP].[ZIF19_ACCDOC_H_T].[HWAER] = [dbo].[MonthlyExRates].[ISOCurCode])
July 10, 2015 at 6:52 am
Why are you using a subquery?
SELECTZIF19_ACCDOC_H_T.GJAHR,ZIF19_ACCDOC_H_T.MONAT,HWAER,UKURS
,UseRate = CASE WHEN UKURS = 0 THEN 1 / dbo.MonthlyExRates.Rate
ELSE dbo.ZIF19_ACCDOC_H_T.UKURS END
FROMdbo.ZIF19_ACCDOC_H_T
JOIN dbo.MonthlyExRates
ON ZIF19_ACCDOC_H_T.GJAHR = dbo.MonthlyExRates.GJAHR
AND ZIF19_ACCDOC_H_T.MONAT = dbo.MonthlyExRates.MONAT
AND ZIF19_ACCDOC_H_T.HWAER = dbo.MonthlyExRates.ISOCurCode
Far away is close at hand in the images of elsewhere.
Anon.
July 10, 2015 at 7:05 am
Smooth.....I think it will work once I fix the fact that I wrote one database using the normal "Danish_Norwegian_CI_AS" while for some reason they have the 2nd in "Finnish_Swedish_CI_AS". The error seems do disappear but I cannot get the result to check until I fix "Cannot resolve the collation conflict between "Danish_Norwegian_CI_AS" and "Finnish_Swedish_CI_AS" in the equal to operation." Thanks for your help!!
July 10, 2015 at 7:31 am
Works like a charm...don't really know what I was actually thinking...
July 10, 2015 at 11:51 pm
There are so many professionals asking others to get the best enjoyable moments of their life. Now if you are a student you need spend your life in such a way it will be memorable for you without worrying about the essay writing because we are here to provide you the best content for your essays.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply