Loading ...
Sorry, an error occurred while loading the content.

Access to SQL Server queries - Solved

Expand Messages
  • MBDEV - DB
    Okay, Ended up with this to try to get rid of the error: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common
    Message 1 of 1 , Mar 13, 2013
    • 0 Attachment
      Okay,

      Ended up with this to try to get rid of the error:
      The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

      At first I tried to create a view in SQL Server management Studio by clicking on Views and New View but on saving you get this totally BS error:
      Object reference not set to an instance of an object. (SQLEditors)
      Explained here: http://social.msdn.microsoft.com/forums/en-US/sqltools/thread/e64ff44f-3377-452c-b0a4-e7367b1f916a/

      SO, I decided to do it the old fashioned way.

      USE [TCOMSdata]

      go

      Create View TITLES_IN_OFFICES_qry

      As

      SELECT TITLES.CustLName, TITLES.CustFName, MAX(TITLOC.SentDT) AS MaxOfSentDT, TITLES.TitleState, TITLES.VehYR, TITLES.VehMake, TITLES.VehModel, TITLOC.TitleID,

      MAX(TITLOC.TitleLocID) AS MaxOfTitleLocID, TITLES.RecDT

      FROM TITLES INNER JOIN TITLOC ON TITLES.TitleID = TITLOC.TitleID

      GROUP BY TITLES.CustLName, TITLES.CustFName, TITLES.TitleState, TITLES.VehYR, TITLES.VehMake, TITLES.VehModel, TITLOC.TitleID, TITLES.RecDT

      /*ORDER BY TITLES.CustLName, TITLES.CustFName*/

      And it works.

      John, thanks for being up at 4am to point me in the right direction.

      From: AspNetAnyQuestionIsOk@yahoogroups.com <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> [mailto:AspNetAnyQuestionIsOk@yahoogroups.com <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> ] On Behalf Of Mike Belcher - MBDEV
      Sent: Wednesday, March 13, 2013 4:38 AM
      To: AspNetAnyQuestionIsOk@yahoogroups.com <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com>
      Subject: RE: [AspNetAnyQuestionIsOk] Access to SQL Server queries - update2

      Hi,

      I came up with:

      Create View TITLES_IN_OFFICES_qry

      As

      SELECT TITLES.CustLName, TITLES.CustFName, MAX(TITLOC.SentDT) AS MaxOfSentDT, TITLES.TitleState, TITLES.VehYR, TITLES.VehMake, TITLES.VehModel, TITLOC.TitleID,

      MAX(TITLOC.TitleLocID) AS MaxOfTitleLocID, TITLES.RecDT

      FROM TITLES INNER JOIN TITLOC ON TITLES.TitleID = TITLOC.TitleID

      GROUP BY TITLES.CustLName, TITLES.CustFName, TITLES.TitleState, TITLES.VehYR, TITLES.VehMake, TITLES.VehModel, TITLOC.TitleID, TITLES.RecDT

      ORDER BY TITLES.CustLName, TITLES.CustFName

      But of course I get an error:
      The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

      Researching it now; why can’t it be simple?

      Thanks,

      Mike

      From: AspNetAnyQuestionIsOk@yahoogroups.com <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> [mailto:AspNetAnyQuestionIsOk@yahoogroups.com <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> ] On Behalf Of Mike Belcher - MBDEV
      Sent: Wednesday, March 13, 2013 4:19 AM
      To: AspNetAnyQuestionIsOk@yahoogroups.com <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com>
      Subject: RE: [AspNetAnyQuestionIsOk] Access to SQL Server queries - update

      Wow I forgot about views. Thanks John.

      P.S
      So I am not the only one up this time of the night huh?

      its barely 4am here.

      From: AspNetAnyQuestionIsOk@yahoogroups.com <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> [mailto:AspNetAnyQuestionIsOk@yahoogroups.com <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> ] On Behalf Of John Warner
      Sent: Wednesday, March 13, 2013 4:08 AM
      To: AspNetAnyQuestionIsOk@yahoogroups.com <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com>
      Subject: RE: [AspNetAnyQuestionIsOk] Access to SQL Server queries - update

      In Access think of nested Queries like querying a VIEW in SQL Server, that might help get used to the idea. If what you are doing is trying to convert from Access take the inner query and consider making a VIEW with it and then code the ‘outer’ query like normal except instead of JOIN to table X JOIN to vw_accessInnerQuery

      John Warner

      From: AspNetAnyQuestionIsOk@yahoogroups.com <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> [mailto:AspNetAnyQuestionIsOk@yahoogroups.com <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> ] On Behalf Of Mike Belcher - MBDEV
      Sent: Wednesday, March 13, 2013 2:42 AM
      To: AspNetAnyQuestionIsOk@yahoogroups.com <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com>
      Subject: RE: [AspNetAnyQuestionIsOk] Access to SQL Server queries - update

      Hi Rob,

      I did more working with it and it is a Query that uses an external Query as a table.

      It does use another query; I found it and listed it below. I am trying to figure out how to get the same results with one SQL query. I have never worked on a query that uses another query before. Not even sure why they did it this way other than access automating this in their query builder.

      TITLES_IN_MAINOFFICE_qry - Main Office – this is one of 4 offices

      SELECT TITLOC.Location, TITLES_IN_OFFICES_qry.CustLName, TITLES_IN_OFFICES_qry.CustFName, TITLES_IN_OFFICES_qry.MaxOfTitleLocID, TITLES_IN_OFFICES_qry.TitleState, TITLES_IN_OFFICES_qry.VehYR, TITLES_IN_OFFICES_qry.VehMake, TITLES_IN_OFFICES_qry.VehModel, TITLES_IN_OFFICES_qry.TitleID, TITLOC.SentDT, TITLOC.InRoute, TITLOC.Recvd

      FROM TITLES_IN_OFFICES_qry INNER JOIN TITLOC ON TITLES_IN_OFFICES_qry.MaxOfTitleLocID = TITLOC.TitleLocID

      WHERE (((TITLOC.Location)="MAIN OFFICE"))

      ORDER BY TITLES_IN_OFFICES_qry.CustLName, TITLES_IN_OFFICES_qry.CustFName;

      TITLES_IN_OFFICES_qry - All the offices use this query in their own office queries.

      SELECT TITLES.CustLName, TITLES.CustFName, Max(TITLOC.SentDT) AS MaxOfSentDT, TITLES.TitleState, TITLES.VehYR, TITLES.VehMake, TITLES.VehModel, TITLOC.TitleID, Max(TITLOC.TitleLocID) AS MaxOfTitleLocID, TITLES.RecDT

      FROM TITLES INNER JOIN TITLOC ON TITLES.TitleID = TITLOC.TitleID

      GROUP BY TITLES.CustLName, TITLES.CustFName, TITLES.TitleState, TITLES.VehYR, TITLES.VehMake, TITLES.VehModel, TITLOC.TitleID, TITLES.RecDT

      ORDER BY TITLES.CustLName, TITLES.CustFName;

      Anyone got any ideas how this can be ONE SQL Query? I am working on it but if anyone has any ideas it is appreciated.

      In query designer on access the left table is TITLES_IN_OFFICES_qry and the table on the right is TITLOC.

      I have never used a query as a table before in a select. I am trying to make the SQL equivalent of this.

      It is all a self-contained access application so there is no pass through or anything.

      Thanks,

      Mike

      From: AspNetAnyQuestionIsOk@yahoogroups.com <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> [mailto:AspNetAnyQuestionIsOk@yahoogroups.com <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> ] On Behalf Of Rob K
      Sent: Tuesday, March 12, 2013 6:24 AM
      To: AspNetAnyQuestionIsOk@yahoogroups.com <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com>
      Subject: Re: [AspNetAnyQuestionIsOk] Access to SQL Server queires

      like I said, anything that's connectable (i.e. ODBC -> MySQL, Oracle, DB2, etc.) will service native call w/o any issues (or using the Access JET engine).

      how to:

      microsoft access pass through query:

      http://support.microsoft.com/kb/303968

      Rob K

      ________________________________
      From: Mike Belcher - MBDEV <aspnetanyquestionisok@... <mailto:aspnetanyquestionisok%40live.com> <mailto:aspnetanyquestionisok%40live.com> <mailto:aspnetanyquestionisok%40live.com> <mailto:aspnetanyquestionisok%40live.com> <mailto:aspnetanyquestionisok%40live.com> <mailto:aspnetanyquestionisok%40live.com> >
      To: AspNetAnyQuestionIsOk@yahoogroups.com <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com>
      Sent: Tuesday, March 12, 2013 2:35 AM
      Subject: RE: [AspNetAnyQuestionIsOk] Access to SQL Server queires

      I joined that group and just posted it. The strange thing is that this query
      seems to use itself as a table. Opening it in design view in access shoes
      the left table as the query and the right table as TITLOC.

      -----Original Message-----
      From: AspNetAnyQuestionIsOk@yahoogroups.com <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com>
      [mailto:AspNetAnyQuestionIsOk@yahoogroups.com <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> ] On Behalf Of Charles Carroll
      Sent: Friday, March 08, 2013 9:13 AM
      To: AspNetAnyQuestionIsOk@yahoogroups.com <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com> <mailto:AspNetAnyQuestionIsOk%40yahoogroups.com>
      Subject: Re: [AspNetAnyQuestionIsOk] Access to SQL Server queires

      You may want to cross post in
      SQLQueriesNoCode i.e.
      http://groups.yahoo.com/group/sqlqueriesnocode
      lots more SQL gurus there

      On Fri, Mar 8, 2013 at 12:50 AM, Mike Belcher - MBDEV <
      aspnetanyquestionisok@... <mailto:aspnetanyquestionisok%40live.com> <mailto:aspnetanyquestionisok%40live.com> <mailto:aspnetanyquestionisok%40live.com> <mailto:aspnetanyquestionisok%40live.com> <mailto:aspnetanyquestionisok%40live.com> <mailto:aspnetanyquestionisok%40live.com> > wrote:

      > **
      >
      >
      > Hello,
      >
      > I am trying to get the same results in SQL server with a query as it
      > works in access
      >
      > There are two tables:
      > TITLES
      > Fields in query: TITLES.TitleID, TITLES.CustLName, TITLES.CustFName,
      > TITLES.TitleState, TITLES.VehYR, TITLES.VehMake, TITLES.VehModel
      >
      > Fields in Table:
      > [TitleID]
      >
      > ,[TitleNum]
      >
      > ,[TitleState]
      >
      > ,[VehYR]
      >
      > ,[VehMake]
      >
      > ,[VehModel]
      >
      > ,[NewORUsed]
      >
      > ,[OOSTitleIssueDT]
      >
      > ,[EntryDT]
      >
      > ,[UserID]
      >
      > ,[RecDT]
      >
      > ,[RecMethod]
      >
      > ,[CurLoc]
      >
      > ,[CustLName]
      >
      > ,[CustFName]
      >
      > ,[CustAdd1]
      >
      > ,[CustAdd2]
      >
      > ,[CustCity]
      >
      > ,[CustState]
      >
      > ,[CustZIP]
      >
      > ,[CustPhone]
      >
      > ,[CustLName_2]
      >
      > ,[CustFName_2]
      >
      > ,[CustLName_3]
      >
      > ,[CustFName_3]
      >
      > ,[SentToDMVDT]
      >
      > ,[SentBackDT]
      >
      > ,[Check1]
      >
      > ,[TitleImg]
      >
      > ,[From]
      >
      > ,[LHDLRID]
      >
      > ,[Locked]
      >
      > _____
      >
      > TITLOC
      > Fields: TTLLOC.Location, TTLLOC.SentDT, TTLLOC.InRoute, TTLLOC.Recvd
      >
      > Fields in Table:
      > [TitleLocID]
      >
      > ,[TitleID]
      >
      > ,[SentDT]
      >
      > ,[UserID]
      >
      > ,[Location]
      >
      > ,[Method]
      >
      > ,[Notes]
      >
      > ,[Recvd]
      >
      > ,[RecvdBy]
      >
      > ,[RecvdDT]
      >
      > ,[InRoute]
      >
      > ,[InRouteBy]
      >
      > ,[InRouteDT]
      >
      > _____
      >
      > MaxOfTitleLocID DOES NOT EXIST in either table.
      >
      > ACCESS QUERY:
      > NAME OF THE QUERY: TITLES_IN_OFFICES_qry
      >
      > ELECT TITLOC.Location, TITLES_IN_OFFICES_qry.CustLName,
      > TITLES_IN_OFFICES_qry.CustFName,
      > TITLES_IN_OFFICES_qry.MaxOfTitleLocID,
      >
      > TITLES_IN_OFFICES_qry.TitleState,
      > TITLES_IN_OFFICES_qry.VehYR, TITLES_IN_OFFICES_qry.VehMake,
      > TITLES_IN_OFFICES_qry.VehModel,
      >
      > TITLES_IN_OFFICES_qry.TitleID, TITLOC.SentDT, TITLOC.InRoute,
      > TITLOC.Recvd
      >
      > FROM TITLES_IN_OFFICES_qry INNER JOIN
      >
      > TITLOC ON TITLES_IN_OFFICES_qry.MaxOfTitleLocID = TITLOC.TitleLocID
      >
      > WHERE (TITLOC.Location = [MAIN OFFICE])
      >
      > ORDER BY TITLES_IN_OFFICES_qry.CustLName,
      > TITLES_IN_OFFICES_qry.CustFName
      >
      > It is using itself in the SQL string above. There is no
      > MaxOfTitleLocID field in either table. I was thinking some kind of
      > alias but not finding it in this query.
      >
      > MY latest SQL version that is returning messed up results.
      > ELECT TITLES.TitleID, TITLES.CustLName, TITLES.CustFName,
      > TITLES.TitleState, TITLES.VehYR, TITLES.VehMake, TITLES.VehModel,
      > TTLLOC.Location, TTLLOC.SentDT,
      >
      > TTLLOC.InRoute, TTLLOC.Recvd
      >
      > FROM TITLES INNER JOIN
      >
      > TTLLOC ON TITLES.TitleID = TTLLOC.TitleID
      >
      > WHERE (TTLLOC.Location = 'MAIN OFFICE')
      >
      > I am not sure I understand what the access query is doing; it seems to
      > be using itself in the query. Any idea what is going on here?
      >
      > Thanks,
      >
      > Mike
      >
      > [Non-text portions of this message have been removed]
      >
      >
      >

      [Non-text portions of this message have been removed]

      ------------------------------------

      Yahoo! Groups Links

      [Non-text portions of this message have been removed]

      [Non-text portions of this message have been removed]

      [Non-text portions of this message have been removed]

      [Non-text portions of this message have been removed]

      [Non-text portions of this message have been removed]

      [Non-text portions of this message have been removed]





      [Non-text portions of this message have been removed]
    Your message has been successfully submitted and would be delivered to recipients shortly.