Solved

Convert MS Access SQL to SQL Server SQL

Posted on 2009-04-01
10
172 Views
Last Modified: 2012-05-06
I am trying to convert an MS Access Query to SQL Server.  I understand I can't use the field below ClosedF3 (etc) because it is not a field in Qry3.  Any idea how to convert this and get it to work.  I receive an error on the following 2 lines:

CAST(dtClosed AS datetime) as ClosedF2,
CASE WHEN dtClosed Is Null THEN Null ELSE ClosedF2 END AS ClosedF3

SELECT 
dbo.Qry3.Module2,
dbo.Qry3.EOTFL, 
CASE WHEN [OTFOT] = 'ON-TIME' THEN 1 ELSE 0 END AS ToDoOT, 
dbo.Qry3.C AS TotalRecords, 
CAST(dbo.Qry3.Hrs AS NUMERIC(13, 2)) AS Hours, 
CASE WHEN [dbo.Qry3.dtclose] IS NULL 
	THEN NULL ELSE CAST(MONTH([dbo.Qry3.dtclose]) AS varchar(2)) + '/1/' + 
		CAST(YEAR([dbo.Qry3.dtclose]) AS varchar(4)) + ' 5:00 PM' END AS dtClosed,
CAST(dtClosed AS datetime) as ClosedF2,
CASE WHEN dtClosed Is Null THEN Null ELSE ClosedF2 END AS ClosedF3
 
FROM dbo.Qry3 LEFT OUTER JOIN
	dbo.field3 ON dbo.Qry3.mngr = dbo.mngr.field3

Open in new window

0
Comment
Question by:CipherIS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
10 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24045767
if you don't mind, can you please show me your table structure or paste the create script of both table?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24045779
all I guess in fly look that if your field is datetime and your are creating a string by

 CAST(MONTH([dbo.Qry3.dtclose]) AS varchar(2)) + '/1/' +
                CAST(YEAR([dbo.Qry3.dtclose]) AS varchar(4)) + ' 5:00 PM' END AS dtClosed

may creating problem. if you convert complete result into datetime, it may solve problem even I am waiting for your create script to investigate more.
0
 
LVL 1

Author Comment

by:CipherIS
ID: 24045869
you don't need to see the structure.  I can tell you the problem is here:
CAST(dtClosed AS datetime) as ClosedF2,
CASE WHEN dtClosed Is Null THEN Null ELSE ClosedF2 END AS ClosedF3

dtClosed and ClosedF2 is not part of a  table or query.  It is defined in the SQL statement.  This works in Access but not in SQL server.
0
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
LVL 51

Expert Comment

by:Mark Wills
ID: 24046117
One method is to use your main query as if it were a subquery :

But think there are other ways to get dtclosed, more on that later if needed...

In the meantime, try :

SELECT *, CAST(dtClosed AS datetime) as ClosedF2, CASE WHEN dtClosed Is Null THEN Null ELSE ClosedF2 END AS ClosedF3
FROM
(
SELECT
            dbo.Qry3.Module2,
            dbo.Qry3.EOTFL,
            CASE WHEN [OTFOT] = 'ON-TIME' THEN 1 ELSE 0 END AS ToDoOT,
            dbo.Qry3.C AS TotalRecords,
            CAST(dbo.Qry3.Hrs AS NUMERIC(13, 2)) AS Hours,
            CASE WHEN [dbo.Qry3.dtclose] IS NULL THEN NULL ELSE CAST(MONTH([dbo.Qry3.dtclose]) AS varchar(2)) + '/1/' + CAST(YEAR([dbo.Qry3.dtclose]) AS varchar(4)) + ' 5:00 PM' END AS dtClosed
FROM dbo.Qry3 LEFT OUTER JOIN
      dbo.field3 ON dbo.Qry3.mngr = dbo.mngr.field3
) SQ
0
 
LVL 1

Author Comment

by:CipherIS
ID: 24046157
wHAT IS sq?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24046189
The name of the subquery - it is an "Alias" and when you "select * from something"   then that "something" has to have a name, usually a table name. But, when it is a subquery it doesn't have a name, so we simply give it an "Alias" and then you can use that alias in place of the table name / query name etc...

select SQ.* from (select stuff from mytable) as SQ

You can use an Alias for table names as well. e.g.

select q3.module2, q3.eotfl from dbo.qry3 as q3

it is often prefaced with the word 'as' as in : select aliasname.* from mytablename as aliasname
but is not needed as per :  select aliasname.* from mytablename aliasname

Does that explain it ?
0
 
LVL 1

Author Comment

by:CipherIS
ID: 24046245
Yes but still receiving error:  Invalid ColumnNameF2.  I've attached code below.

SELECT *, 
 
CAST(ClosingDateFinal AS datetime) AS ClosingDateF2, 
CASE WHEN ClosingDateFinal Is Null THEN Null ELSE ClosingDateF2 END AS ClosingDt3
 
FROM
(
  SELECT     
  dbo.QryActivityDetail3.Module2,
  dbo.QryActivityDetail3.FacName, 
  dbo.QryActivityDetail3.EventID,
  dbo.QryActivityDetail3.EventTargetDate, 
  dbo.QryActivityDetail3.EventType,
  dbo.QryActivityDetail3.EventCat, 
  dbo.QryActivityDetail3.EOFullName, 
  dbo.QryActivityDetail3.OwnerDept, 
  dbo.QryActivityDetail3.TaskID, 
  dbo.QryActivityDetail3.TargetDt, 
  dbo.QryActivityDetail3.ToIDFULL, 
  dbo.QryActivityDetail3.ToDoCat, 
  dbo.QryActivityDetail3.PlannedHrs, 
  dbo.QryActivityDetail3.TgtDate, 
  dbo.QryActivityDetail3.TOFullName, 
  dbo.QryActivityDetail3.TDOFullName, 
  dbo.QryActivityDetail3.Supervisor, 
  dbo.Supervisor.fldValue, 
  dbo.QryActivityDetail3.dtClosing, 
  dbo.QryActivityDetail3.OTFOT, 
  dbo.QryActivityDetail3.OTFL, 
  dbo.QryActivityDetail3.Status, 
  dbo.QryActivityDetail3.OTDR2, 
  dbo.QryActivityDetail3.OTDR3, 
  dbo.QryActivityDetail3.C, 
  dbo.QryActivityDetail3.TOTFOT, 
  dbo.QryActivityDetail3.TOTFL, 
  dbo.QryActivityDetail3.TaskStatus, 
  dbo.QryActivityDetail3.EvenStatus, 
  dbo.QryActivityDetail3.EOTFOT, 
  dbo.QryActivityDetail3.EOTFL, 
  CASE WHEN [OTFOT] = 'ON-TIME' THEN 1 ELSE 0 END AS ToDoOT, 
  CASE WHEN [OTFL] = 'LATE' THEN 1 ELSE 0 END AS ToDoL, 
  CASE WHEN [Status] = 'CLOSED' THEN 1 ELSE 0 END AS ToDoStatus, 
  CASE WHEN [OTDR2] = 'O' THEN 1 ELSE 0 END AS Original, 
  CASE WHEN [OTDR3] = 'R' THEN 1 ELSE 0 END AS ReSchedule, 
  CASE WHEN [TOTFOT] = 'ON-TIME' THEN 1 ELSE 0 END AS TaskOT, 
  CASE WHEN [TOTFL] = 'LATE' THEN 1 ELSE 0 END AS TaskL, 
  CASE WHEN [TaskStatus] = 'CLOSED' THEN 1 ELSE 0 END AS TaskStaus, 
  CASE WHEN [EvenStatus] = 'CLOSED' THEN 1 ELSE 0 END AS EventStatus, 
  CASE WHEN [EOTFOT] = 'ON-TIME' THEN 1 ELSE 0 END AS EventOT, 
  CASE WHEN [EOTFL] = 'LATE' THEN 1 ELSE 0 END AS EventL, 
  dbo.QryActivityDetail3.C AS TotalRecords, 
  CAST(dbo.QryActivityDetail3.PlannedHrs AS NUMERIC(13, 2)) AS 
      PlannedHours, 
--IIf([Dtclosing] Is Null,Null,[ClosingDatemonth] & "/1/" & 
--[ClosingDateYear] & " 5:00 PM") AS ClosingDateFinal, 
  CASE WHEN [dtclosing] IS NULL 
      THEN NULL ELSE CAST(MONTH([dtclosing]) AS varchar(2)) + '/1/' + 
	CAST(YEAR([dtclosing]) AS varchar(4)) + 
         ' 5:00 PM' END AS ClosingDateFinal
FROM         
    dbo.QryActivityDetail3 LEFT OUTER JOIN
	dbo.Supervisor ON dbo.QryActivityDetail3.Supervisor = 
              dbo.Supervisor.EMail
) AS SQ

Open in new window

0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 24046262
Sorry about that, one of the derived columns is still being referred to in the outer query...

SELECT *,
 
CAST(ClosingDateFinal AS datetime) AS ClosingDateF2,
CASE WHEN ClosingDateFinal Is Null THEN Null ELSE CAST(ClosingDateFinal AS datetime)  END AS ClosingDt3
 
FROM
(
  SELECT    
  dbo.QryActivityDetail3.Module2,
  dbo.QryActivityDetail3.FacName,
  dbo.QryActivityDetail3.EventID,
  dbo.QryActivityDetail3.EventTargetDate,
  dbo.QryActivityDetail3.EventType,
  dbo.QryActivityDetail3.EventCat,
  dbo.QryActivityDetail3.EOFullName,
  dbo.QryActivityDetail3.OwnerDept,
  dbo.QryActivityDetail3.TaskID,
  dbo.QryActivityDetail3.TargetDt,
  dbo.QryActivityDetail3.ToIDFULL,
  dbo.QryActivityDetail3.ToDoCat,
  dbo.QryActivityDetail3.PlannedHrs,
  dbo.QryActivityDetail3.TgtDate,
  dbo.QryActivityDetail3.TOFullName,
  dbo.QryActivityDetail3.TDOFullName,
  dbo.QryActivityDetail3.Supervisor,
  dbo.Supervisor.fldValue,
  dbo.QryActivityDetail3.dtClosing,
  dbo.QryActivityDetail3.OTFOT,
  dbo.QryActivityDetail3.OTFL,
  dbo.QryActivityDetail3.Status,
  dbo.QryActivityDetail3.OTDR2,
  dbo.QryActivityDetail3.OTDR3,
  dbo.QryActivityDetail3.C,
  dbo.QryActivityDetail3.TOTFOT,
  dbo.QryActivityDetail3.TOTFL,
  dbo.QryActivityDetail3.TaskStatus,
  dbo.QryActivityDetail3.EvenStatus,
  dbo.QryActivityDetail3.EOTFOT,
  dbo.QryActivityDetail3.EOTFL,
  CASE WHEN [OTFOT] = 'ON-TIME' THEN 1 ELSE 0 END AS ToDoOT,
  CASE WHEN [OTFL] = 'LATE' THEN 1 ELSE 0 END AS ToDoL,
  CASE WHEN [Status] = 'CLOSED' THEN 1 ELSE 0 END AS ToDoStatus,
  CASE WHEN [OTDR2] = 'O' THEN 1 ELSE 0 END AS Original,
  CASE WHEN [OTDR3] = 'R' THEN 1 ELSE 0 END AS ReSchedule,
  CASE WHEN [TOTFOT] = 'ON-TIME' THEN 1 ELSE 0 END AS TaskOT,
  CASE WHEN [TOTFL] = 'LATE' THEN 1 ELSE 0 END AS TaskL,
  CASE WHEN [TaskStatus] = 'CLOSED' THEN 1 ELSE 0 END AS TaskStaus,
  CASE WHEN [EvenStatus] = 'CLOSED' THEN 1 ELSE 0 END AS EventStatus,
  CASE WHEN [EOTFOT] = 'ON-TIME' THEN 1 ELSE 0 END AS EventOT,
  CASE WHEN [EOTFL] = 'LATE' THEN 1 ELSE 0 END AS EventL,
  dbo.QryActivityDetail3.C AS TotalRecords,
  CAST(dbo.QryActivityDetail3.PlannedHrs AS NUMERIC(13, 2)) AS
      PlannedHours,
--IIf([Dtclosing] Is Null,Null,[ClosingDatemonth] & "/1/" & 
--[ClosingDateYear] & " 5:00 PM") AS ClosingDateFinal,
  CASE WHEN [dtclosing] IS NULL
      THEN NULL ELSE CAST(MONTH([dtclosing]) AS varchar(2)) + '/1/' +
      CAST(YEAR([dtclosing]) AS varchar(4)) +
         ' 5:00 PM' END AS ClosingDateFinal
FROM        
    dbo.QryActivityDetail3 LEFT OUTER JOIN
      dbo.Supervisor ON dbo.QryActivityDetail3.Supervisor =
              dbo.Supervisor.EMail
) AS SQ
0
 
LVL 1

Author Closing Comment

by:CipherIS
ID: 31565607
Awesome.  Thx
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24046330
instead of :

 CASE WHEN [dtclosing] IS NULL
      THEN NULL ELSE CAST(MONTH([dtclosing]) AS varchar(2)) + '/1/' +
      CAST(YEAR([dtclosing]) AS varchar(4)) +
         ' 5:00 PM' END AS ClosingDateFinal

why not :

select CASE WHEN dtclosing IS NULL  THEN dtclosing ELSE convert(datetime, convert(varchar,MONTH(dtclosing)) + '/1/' + convert(varchar,year(dtclosing)) + ' 5:00PM',101) END AS ClosingDateFinal
--or
select case when dtclosing IS NULL then dtclosing else convert(datetime, convert(varchar(6),dtclosing,112) + '01 17:00:00') end as ClosingDateFinal


and then it is a datetime already and your outer query looks a bit different - and in fact probably no longer needed at all :

SELECT *,
 
ClosingDateFinal AS ClosingDateF2,
CASE WHEN ClosingDateFinal Is Null THEN ClosingDateFinal ELSE ClosingDateFinal  END AS ClosingDt3
 
FROM
(
  SELECT    
  dbo.QryActivityDetail3.Module2,
  dbo.QryActivityDetail3.FacName,
  dbo.QryActivityDetail3.EventID,
  dbo.QryActivityDetail3.EventTargetDate,
  dbo.QryActivityDetail3.EventType,
  dbo.QryActivityDetail3.EventCat,
  dbo.QryActivityDetail3.EOFullName,
  dbo.QryActivityDetail3.OwnerDept,
  dbo.QryActivityDetail3.TaskID,
  dbo.QryActivityDetail3.TargetDt,
  dbo.QryActivityDetail3.ToIDFULL,
  dbo.QryActivityDetail3.ToDoCat,
  dbo.QryActivityDetail3.PlannedHrs,
  dbo.QryActivityDetail3.TgtDate,
  dbo.QryActivityDetail3.TOFullName,
  dbo.QryActivityDetail3.TDOFullName,
  dbo.QryActivityDetail3.Supervisor,
  dbo.Supervisor.fldValue,
  dbo.QryActivityDetail3.dtClosing,
  dbo.QryActivityDetail3.OTFOT,
  dbo.QryActivityDetail3.OTFL,
  dbo.QryActivityDetail3.Status,
  dbo.QryActivityDetail3.OTDR2,
  dbo.QryActivityDetail3.OTDR3,
  dbo.QryActivityDetail3.C,
  dbo.QryActivityDetail3.TOTFOT,
  dbo.QryActivityDetail3.TOTFL,
  dbo.QryActivityDetail3.TaskStatus,
  dbo.QryActivityDetail3.EvenStatus,
  dbo.QryActivityDetail3.EOTFOT,
  dbo.QryActivityDetail3.EOTFL,
  CASE WHEN [OTFOT] = 'ON-TIME' THEN 1 ELSE 0 END AS ToDoOT,
  CASE WHEN [OTFL] = 'LATE' THEN 1 ELSE 0 END AS ToDoL,
  CASE WHEN [Status] = 'CLOSED' THEN 1 ELSE 0 END AS ToDoStatus,
  CASE WHEN [OTDR2] = 'O' THEN 1 ELSE 0 END AS Original,
  CASE WHEN [OTDR3] = 'R' THEN 1 ELSE 0 END AS ReSchedule,
  CASE WHEN [TOTFOT] = 'ON-TIME' THEN 1 ELSE 0 END AS TaskOT,
  CASE WHEN [TOTFL] = 'LATE' THEN 1 ELSE 0 END AS TaskL,
  CASE WHEN [TaskStatus] = 'CLOSED' THEN 1 ELSE 0 END AS TaskStaus,
  CASE WHEN [EvenStatus] = 'CLOSED' THEN 1 ELSE 0 END AS EventStatus,
  CASE WHEN [EOTFOT] = 'ON-TIME' THEN 1 ELSE 0 END AS EventOT,
  CASE WHEN [EOTFL] = 'LATE' THEN 1 ELSE 0 END AS EventL,
  dbo.QryActivityDetail3.C AS TotalRecords,
  CAST(dbo.QryActivityDetail3.PlannedHrs AS NUMERIC(13, 2)) AS
      PlannedHours,
--IIf([Dtclosing] Is Null,Null,[ClosingDatemonth] & "/1/" & 
--[ClosingDateYear] & " 5:00 PM") AS ClosingDateFinal,
  CASE WHEN [dtclosing] IS NULL
      THEN NULL ELSE CAST(MONTH([dtclosing]) AS varchar(2)) + '/1/' +
      CAST(YEAR([dtclosing]) AS varchar(4)) +
         ' 5:00 PM' END AS ClosingDateFinal
FROM        
    dbo.QryActivityDetail3 LEFT OUTER JOIN
      dbo.Supervisor ON dbo.QryActivityDetail3.Supervisor =
              dbo.Supervisor.EMail
) AS SQ

0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

724 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