This is a problem I've been fighting through for the last month. I've been tasked with converting access 2007 queries to SQL Server 2005. I have been able to convert a number of queries associated with one data base now I'm running into problems with IIf. I've looked at the books online and tried to debug the query I'm working on now. This query will be a model for other queries moving forward. Has anyone had success working with IIf and taking it from Access 2007 to SQL Server 2005.
Here are a few lines of the code and the syntax I'm getting.
SELECT
[tblShipments].[Load Id],
[tblShipments].[Shipment Id],
[tblShipments].[End Date],
[tblShipments].[SH Arv D Loc Date],
[tblShipments].[End TS],
[tblShipments].[SH Arv D Loc TS],
[SH Arv D Loc TS]-[End TS] AS Variance,
[tblShipments].[Conf Mode],
[tblShipments].[Transport Means Grp],
[tblShipments].[CUDC Flag] AS [CUDC],
[tblShipments].[XPD],
[tblShipments].[FP Flag] AS [LCFP],
[tblShipments].[CFAL],
[tblShipments].[CRTR],
[tblShipments].[MCAC],
[tblShipments].[MNMC],
CASE WHEN XPD = 1 OR CRTR = 1 THEN 1 ELSE 0 END as XPDFlag
CASE WHEN CUDC =1 THEN IS NULL END AS CUDC,
CASE WHEN LCFP =1 THEN IS NULL END AS LCFP,
CASE WHEN CFAL = 1 THEN Late ELSE Late END AS CFAL,
CASE WHEN MCAC = 1 THEN Late END AS MCAC,
CASE WHEN Transport Means Grp = TM2 END AS Transport Means Grp,
CASE WHEN SH Arv D Loc Date > [End Date] THEN Late ELSE IS NULL END AS [LTL OT],
CASE WHEN XPDFlag =1,
CASE WHEN MNMC =1 THEN IS NULL END AS MNMC,
CASE WHEN Variance > 0.010416667 THEN Late ELSE IS NULL END AS [XPD OT],
CASE WHEN Variance > 0.166666667 THEN Late ELSE IS NULL END AS [TL OT],
CASE WHEN OT = Late OR CFAL = 1 END AS OT,
CASE WHEN OT = Late OR MCAC =1 ESLE 0 END AS MC
INTO [temp_Ontime_tes t]
FROM [tblShipments];
Msg 156, Level 15, State 1, Line 19
Incorrect syntax near the keyword 'CASE'.
Any help is greatly appreicated.
10 3803 Delerna 1,134
Recognized Expert Top Contributor -
IIF(condition,true action,false action)
-
converts to
-
CASE WHEN condition THEN true action ELSE false action END
-
Here is one of your syntax errors
CASE WHEN CUDC =1 THEN IS NULL END AS CUDC
not sure what you are tring to do here but perhaps
CASE WHEN CUDC =1 THEN NULL END AS CUDC
Incidentally, that line as it is will always return CUCD=null because there is no ELSE
so
null as CUCD
would do the same thing. ANOTHER
CASE WHEN CFAL = 1 THEN Late ELSE Late END AS CFAL
CFAL always equals Late so why not
Late as CFAL
? ANOTHER
CASE WHEN OT = Late OR CFAL = 1 END AS OT
Huh....its not doing anything ONE MORE
CASE WHEN Transport Means Grp = TM2 END AS Transport Means Grp
field names with spaces must be enclosed in [] CASE WHEN [Transport Means Grp] = TM2 END AS [Transport Means Grp]
and its not doing anything
you are saying IIF(condition) as FieldName
Keep at it you will get it right in the end :)
thanks for the helpful tips I'm using the corrections now and i'll let you know how it went.
I modified the query and I'm getting the following errors at the end. Is there another way to represent a blank value in T SQL ?
SELECT
[Load Id],
[Shipment Id],
[End Date],
[SH Arv D Loc Date],
[End TS],
[SH Arv D Loc TS],
[SH Arv D Loc TS]-[End TS] AS [Variance],
[Conf Mode],
[Transport Means Grp],
[CUDC Flag] AS [CUDC],
[XPD],
[FP Flag] AS [LCFP],
[CFAL],
[CRTR],
[MCAC],
[MNMC],
CASE WHEN [XPD] = 1 OR [CRTR] = 1 THEN 1 ELSE 0 END as XPDFlag,
CASE WHEN [CUDC Flag] = 1 THEN 0 END AS [CUDC],
CASE WHEN [FP Flag] = 1 THEN 0 END AS [LCFP],
CASE WHEN [CFAL] = 1 THEN 'Late' END AS [CFAL],
CASE WHEN [MCAC]=1 THEN 'Late' END AS [MCAC],
CASE WHEN [SH Arv D Loc Date]>[End Date]THEN 'Late' ELSE NULL END AS [LTL OT],
CASE WHEN [MNMC]= 1 THEN NULL END AS [MNMC],
CASE WHEN [Variance] > 0.010416667 THEN 'Late' END AS [XPD OT],
CASE WHEN [Variance] > 0.166666667 THEN 'Late' END AS [TL OT]
--CASE WHEN [XPD]= 1 Or [CRTR] = 1 THEN [XPD OT] END
INTO [temp_Ontime_tes t]
FROM [tblShipments]
WHERE [OT] = 'Late'
AND [Transport Means Grp] = 'TM2'
Msg 8133, Level 16, State 1, Line 1
None of the result expressions in a CASE specification can be NULL.
Msg 207, Level 16, State 1, Line 26
Invalid column name 'Variance'.
Msg 207, Level 16, State 1, Line 27
Invalid column name 'Variance'.
is there any tool that quickly converts access 07 to sql server 05?
Delerna 1,134
Recognized Expert Top Contributor
A Tool...probably , google for it.
I have never bothered because conversion is not that difficult once you learn the rules.
In access you can reference a field that is the result of a calculation
In your query Variance is a calculated field.
In SQL server you cannot do that. There is no field in tblShipments called Variance and so it complains.
Instead of this
CASE WHEN [Variance] > 0.010416667 THEN 'Late' END AS [XPD OT],
you need to do this
CASE WHEN [SH Arv D Loc TS]-[End TS] > 0.010416667 THEN 'Late' END AS [XPD OT],
Delerna 1,134
Recognized Expert Top Contributor
None of the result expressions in a CASE specification can be NULL.
Yes I get that same error if I do similar to this
CASE WHEN [MNMC]= 1 THEN NULL END AS [MNMC],
or this
CASE WHEN [MNMC]= 1 THEN NULL ELSE NULL END AS [MNMC],
but this is OK
CASE WHEN [MNMC]= 1 THEN NULL ELSE 0 END AS [MNMC],
Anyway, I don't really understand what you are trying to do here.
What is the point of returning NULL if MNMC=1 ?
and what should be returned if MNMC<>1?
I prefer a third party applpications for migrating my data, i use data loader when i was migrating access to SQL it work great, and it can migrate almost any database.
Download Free : http://www.dbload.com
Thanks I'll check it out, will this work with the queries as well, that's my major area of concern.
"Anthiny97"
ya it will b helpful for you...
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Anand |
last post by:
Hi i am having trouble adding a recordset into the access database,
the code seems to be working fine it passs and parses through all
variables just fine without showing any errors and also when i access
the recordset it displays the results, what the real issue is that the
entry is not made into the database even though i use the Update
command and i have also tried the BeginTrans and CommitTrans nothign
seems to work and i am unable to...
|
by: wiredog |
last post by:
I am struggling rewriting my query from MS Access' IIF, Then to SQL
Servers TSQL language. I am hoping some one can give me some
guidance. I believe I have the first portion of the query correct but
do believe this requires a "NESTED" argument. This is where I am
lost.
My Original MS ACCESS Query reads--
SELECT DISTINCTROW REGION_TRAFIC.*,
IIf(Mid(,5,2)=,
|
by: dschl |
last post by:
Hi,
I'm converting an Access 2000 database to Sql Server and must be
missing something obvious.
Using the Import utility in Sql Server, the Access queries seem to get
executed and the resultant data imported as tables. Oops!
Using the Upsize lizard in Access 2003, the queries aren't even in the
selection list of "tables" to upsize. It looks like the Upsize wizard
|
by: jj |
last post by:
Is it possible to call a remote php script from within Access? I'm thinking
something like:
DoCMD... http://www.domain.com/scripts/dataquery.php
DoCmd.OpenQuery "update_data", acNormal, acEdit
.....
So the PHP script does something on the server database, then when a linked
table is viewed within access, the data changes have been made?
|
by: Dana Shields |
last post by:
I am attempting to upsize from access to SQL Server. I'm trying to convert
my queries to SQL Server views; however, I'm having a lot of difficulty with
the syntax differences.
For instance, a query with select mid(tblTable.FieldName,1,10)should look
something like select substring(tblTable.FieldName,1,10).
This is really the simplest of examples. I've tried using the SQL Server
Import tool, but it takes the queries and changes them...
| |
by: Stefan V. |
last post by:
Hello!
I am trying to convert a query written for SQL Server 2000 database
tables, to a MS Access query.
Here is what I have in SQL Server:
SELECT t2.*,
CASE WHEN t2.QType = '3' THEN t1.Note ELSE CASE WHEN t2.QType = '2'
THEN
CASE WHEN CONVERT(varchar(100), t1.ANumber) = '1' THEN 'Yes' ELSE 'No'
END ELSE CASE WHEN CONVERT(varchar(5), t2.Qnumber)+'.' +
|
by: Mark Flippin |
last post by:
I'm converting the backend of an Access 2000 database to SQL Server
2000.
The existing database has user and group security through a specific
workgroup file.
Under the "user and group accounts" there is a button to print the
users and group relationships. As there are only about 50 authorized
users and 6 groups, this has worked.
|
by: Rico |
last post by:
Hello,
I have an application that I'm converting to Access 2003 and SQL Server 2005
Express. The application uses extensive use of DAO and the SEEK method on
indexes. I'm having an issue when the recordset opens a table. When I
write
Set rst = db.OpenRecordset("MyTable",dbOpenTable, dbReadOnly)
I get an error. I believe it's invalid operation or invalid parameter, I'm
|
by: wintonsl |
last post by:
Would anyone know how to convert this Access Query to SQL Server Query?
Format(((+++)/(IIf(>0,1,0)+IIf(>0,1,0)+IIf(>0,1,0)+IIf(>0,1,0))),"Standard")
What this query is doing in Access is Averaging numbers that are not equal to 0. If the number is equal to 0, it should not be averaged in. The average is set to 2 decimal places.
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |