for the record, this is my first post and i'm not sure this is the right place to post, but it makes sence to me.
i've been spending a great deal of time with our new MS SQL server and love it but i've noticed there's a fairly large diff between the SQL i'm used to seeing in MS Access and the SQL that the server uses. as you can see by my below statement i'm starting to get the hang of this but i still run into new issues...
my question... what's wrong with my IIf statements? this is what the server spits out...
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'LIKE'.
[ACCPAC_OESHDT].[TERRITORY] is an nvarchar(6) and contains data such as '1', '2', '3', etc...
i've tried many many variants and always have the same outcom. i've tried casting as an int, rtrim to rid the value of any possible white space, expanded versions.
oh, almost forgot... my environment is... I open the SQL Management Studio then logon to SQL Database Engine and then create a New Query.
i'm stuck, please advise. ;-)
SELECT
ACCPAC_OESHDT.YR,
ACCPAC_OESHDT.PERIOD,
IIf([ACCPAC_OESHDT].[TERRITORY] LIKE '[1-4]','Group A',
IIf([ACCPAC_OESHDT].[TERRITORY] LIKE '[5-7]','Group B',
IIf([ACCPAC_OESHDT].[TERRITORY] LIKE '[89]','Group C','_Unknown Territory'))) AS TERRITORY,
SUM(CAST([SAMTSALES] AS MONEY)) AS Sales
FROM ACCPAC_OESHDT
GROUP BY
ACCPAC_OESHDT.YR,
ACCPAC_OESHDT.PERIOD,
IIf([ACCPAC_OESHDT].[TERRITORY] LIKE '[1-4]','Group A',
IIf([ACCPAC_OESHDT].[TERRITORY] LIKE '[5-7]','Group B',
IIf([ACCPAC_OESHDT].[TERRITORY] LIKE '[89]','Group C','_Unknown Territory')))
HAVING ((SUM(CAST([SAMTSALES] AS MONEY)))>0);
Look up the CASE feature in the documentation for a good solution to this problem.
-Ryan
|||Case when ACCPAC_OESHDT.TERRITORY LIKE '[1-4]' then 'Group A'
when ACCPAC_OESHDT.TERRITORY LIKE '[5-7]' then 'Group B'
when ACCPAC_OESHDT.TERRITORY LIKE '[8-9]' then 'Group C'
else '_Unknown Territory'
end as TERRITORY,
I think that should get you started...
Zep--
|||
thank you VERY much for your guidance... I have been able to make great progress with my queries and am now off to learn how to nest.
Thank you again ;-)
|||Oh, you're going to be forced to learn all kinds of fun stuff coming from access :)
Zep--
|||I’m starting to see that… well i hope i'm not going to get labeled... I’ve got a decent amount of experience in other languages such as Perl on WinTel, Mac, and Linux, Postscript, VB, Apple Script and others so I’m not a canned Access dude floating into a new SQL world, hope this is a good thing for me, hehe… thank you very much again for the support… this has been an exciting journey for me and I’m now seeing this platform, MS SQL 2005, is like a whole new world, absolutely massive, wondrous, full of pit holes and wiled ability, at least in my eyes… very exciting ;-)
