Up The Wall
-
Can any tell me why I'm getting invalid column name error on this?
Invalid Column Name 'CHF'
INSERT INTO MasterDailyBalances
( [BalanceDate], [loan_id], [ScheduledBalance], [ArrearsCapital], [ArrearsInterest], [ArrearsCosts], [TotalClaim], [Currency], [LNRTotalClaimEUR], [AdditionalInterest])
SELECT getdate() AS BalanceDate, H3DESC AS loan_id, [H3PRBL]-[H3Pod]
AS ScheduledBalance, H3POD AS ArrearsCapital, H3IOD
AS ArrearsInterest, H3FOD
AS ArrearsCosts, (H3PRBL-H3POD)+H3POD+H3IOD+H3AIBL+HPTRCH.H3FOD
AS TotalClaim,
[H4CURR] AS Currency, (H3PRBL-H3POD+H3POD+H3IOD+H3AIBL+H3FOD)/1.5476
AS LNRTotalClaimEUR, dbo.HPTRCH.H3AIBL AS AdditionalInterest
FROM dbo.HPTRCH LEFT JOIN dbo.HPTRPM ON dbo.HPTRCH.H3SERL = dbo.HPTRPM.H4SERL
WHERE [H4CURR]= "CHF"
NewBe very grateful foe any help/advice
- I havent checked the entire sql but a quick glance tells me you should single quotes and not double quotes in sql.WHERE [H4CURR]= 'CHF'EDIT : I assumed CHF is a value you are trying to filter the records against. If its a column name you should be using :WHERE [H4CURR]= <tablename>.CHF
******************
Dinakar Nethi
Life is short. Enjoy it.
****************** -
Thats fixed it thanks
I copy and pasted from MS Access (too much to ask that products from the same family use the same sql I s'pose)
What does it mean then when the syntax turns red - this put me off trying the single quote??
-
red = string. as soon as you type a single quote anything typed after that becomes red until you close the single quote which marks the end of the string.
******************
Dinakar Nethi
Life is short. Enjoy it.
****************** -
Thank you once again. My sanity is preserved
-
SET QUOTED_IDENTIFIER OFF allows you to use double quotes as well as single for strings but this is not usually a database default so needs to be included in your stored procedures to be safe.
-
It may also be set by the connecting client, so to be on the safe side, it's best to get into the habit of always using single quotes when denoting strings, never use double quotes.
Single quotes will also give you the nice colormarking, whereas doublequotes won't.
/Kenneth
-
Agreed, but sometimes it's useful, eg including a literal string in a constructed OPENQUERY(), eg :
SET @strCommand = "SELECT * FROM OPENQUERY(server, SELECT * FROM Database.dbo.Client WHERE Surname = '" + @NameWanted + "')"
-
Though, you'd still be missing the nice red colour
On the other hand, it's just as 'easy' to replace the double quotes with single
SET @strCommand = 'SELECT * FROM OPENQUERY(server, SELECT * FROM Database.dbo.Client WHERE Surname = ''' + @NameWanted + ''')'
However, it tends to be messy pretty quick when you have to escape singlequotes, so another option to 'keep it neat' might be to denote strings as usual with single quotes, and when necessary to place explicit singlequotes, use the CHAR() function for it... and still keep the syntax colouring
SET @strCommand = 'SELECT * FROM OPENQUERY(server, SELECT * FROM Database.dbo.Client WHERE Surname = ' + char(39) + @NameWanted + char(39) + ')'
/Kenneth
-
Thank you - much better. I'll do it that way (CHR(39)) in future. As you say, escaping quotes is messy and difficult to read so I prefer to avoid that.
-
I'm starting to get really peed off now. In the original statement how do I change it so the it asks:-
if [H4CURR]= 'CHF', then (H3PRBL-H3POD+H3POD+H3IOD+H3AIBL+H3FOD)/1.5476,else(H3PRBL-H3POD+H3POD+H3IOD+H3AIBL+H3FOD)
AS LNRTotalClaimEUR
-
(H3PRBL-H3POD+H3POD+H3IOD+H3AIBL+H3FOD) / (case when [H4CURR]= 'CHF', then 1.5476 else 1 end)
AS LNRTotalClaimEUR
hth
* Noel -
You have a comma that should not be there :
(H3PRBL-H3POD+H3POD+H3IOD+H3AIBL+H3FOD) / (case when [H4CURR]= 'CHF' then 1.5476 else 1 end)
AS LNRTotalClaimEUR
-
Yep, you are right. I call it the cut and paste effect
* Noel -
I seeeeeeeee.
You really have to spell it out to the system dontcha
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply