• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1022
  • Last Modified:

how to use row_num in select statement from access to oracle linked table

I have an Oracle sql query that I want to run in Access to generate a report. How do I use row_num in Access? I have links in Access to the Oracle tables, except for the f3 table, which is accessed locally to Access. This is the functioning Oracle query:
SELECT * FROM
 (SELECT rownum row_number, common_equip_id, equip_descr, manufacturer, equip_group, drawing_id, reference_file, equip_comment1, equip_comment2  FROM common_equip)
WHERE row_number IN (SELECT * FROM common_equip_f3);

This is what I tried in Access:
SELECT * FROM
(SELECT  WMMSADBA_COMMON_EQUIP.rownum row_number, WMMSADBA_COMMON_EQUIP.COMMON_EQUIP_ID, WMMSADBA_COMMON_EQUIP.EQUIP_DESCR, WMMSADBA_COMMON_EQUIP.MANUFACTURER, WMMSADBA_COMMON_EQUIP.EQUIP_COMMENT1, WMMSADBA_COMMON_EQUIP.EQUIP_COMMENT2, WMMSADBA_COMMON_EQUIP.EQUIP_GROUP, WMMSADBA_COMMON_EQUIP.DRAWING_ID, WMMSADBA_COMMON_EQUIP.REFERENCE_FILE FROM  WMMSADBA_COMMON_EQUIP) WHERE row_number IN (SELECT * FROM f3.ComEqu);

When I try to run the query, I get an error and the query has saved as this:
SELECT *
FROM [SELECT  WMMSADBA_COMMON_EQUIP.rownum row_number, WMMSADBA_COMMON_EQUIP.COMMON_EQUIP_ID, WMMSADBA_COMMON_EQUIP.EQUIP_DESCR, WMMSADBA_COMMON_EQUIP.MANUFACTURER, WMMSADBA_COMMON_EQUIP.EQUIP_COMMENT1, WMMSADBA_COMMON_EQUIP.EQUIP_COMMENT2, WMMSADBA_COMMON_EQUIP.EQUIP_GROUP, WMMSADBA_COMMON_EQUIP.DRAWING_ID, WMMSADBA_COMMON_EQUIP.REFERENCE_FILE FROM  WMMSADBA_COMMON_EQUIP]. AS [%$##@_Alias]
WHERE row_number IN (SELECT * FROM f3.ComEqu);

Please help.

0
ecuPirates
Asked:
ecuPirates
  • 6
  • 6
  • 3
  • +1
1 Solution
 
flavoCommented:
select * from [subquery]??

why use the fisrt sub-query?

SELECT  WMMSADBA_COMMON_EQUIP.rownum row_number, WMMSADBA_COMMON_EQUIP.COMMON_EQUIP_ID, WMMSADBA_COMMON_EQUIP.EQUIP_DESCR, WMMSADBA_COMMON_EQUIP.MANUFACTURER, WMMSADBA_COMMON_EQUIP.EQUIP_COMMENT1, WMMSADBA_COMMON_EQUIP.EQUIP_COMMENT2, WMMSADBA_COMMON_EQUIP.EQUIP_GROUP, WMMSADBA_COMMON_EQUIP.DRAWING_ID, WMMSADBA_COMMON_EQUIP.REFERENCE_FILE FROM  WMMSADBA_COMMON_EQUIP WHERE row_number IN (SELECT * FROM f3.ComEqu);
0
 
ecuPiratesAuthor Commented:
The error I get is: Syntax error(missing operator) in query expression 'WMMSADBA_COMMON_EQUIP.rownum rownumber'
0
 
flavoCommented:
change WMMSADBA_COMMON_EQUIP.rownum rownumber,

to

WMMSADBA_COMMON_EQUIP.rownum,
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
Mike EghtebasDatabase and Application DeveloperCommented:
Apparently, there is a routin buit into Oracle that gives you rownumber.  If this is so; I think Access doesn't have this functionality in queries.  But, I have built a function just to do that.  The only requirment is for you to identify a field in you SQL that it is sorted (Asc or Desc).

mike
0
 
elcastorCommented:
In Access, when defining an alias for a column, you should use the AS statement. This means that on the first column of your subquery you should write WMMSADBA_COMMON_EQUIP.rownum AS rownumber instead of WMMSADBA_COMMON_EQUIP.rownum rownumber.
Also, you can't use these aliases in your WHERE statement. So, WHERE row_number IN should be replaced by WMMSADBA_COMMON_EQUIP.rownum.
I think this should do the trick


0
 
ecuPiratesAuthor Commented:
ok, this is the query I tried:
SELECT * FROM
(SELECT  WMMSADBA_COMMON_EQUIP.rownum AS row_number, WMMSADBA_COMMON_EQUIP.COMMON_EQUIP_ID, WMMSADBA_COMMON_EQUIP.EQUIP_DESCR, WMMSADBA_COMMON_EQUIP.MANUFACTURER, WMMSADBA_COMMON_EQUIP.EQUIP_COMMENT1, WMMSADBA_COMMON_EQUIP.EQUIP_COMMENT2, WMMSADBA_COMMON_EQUIP.EQUIP_GROUP, WMMSADBA_COMMON_EQUIP.DRAWING_ID, WMMSADBA_COMMON_EQUIP.REFERENCE_FILE FROM  WMMSADBA_COMMON_EQUIP) WHERE WMMSADBA_COMMON_EQUIP.rownum IN (SELECT * FROM f3ComEqu);

Now it is asking for a parameter (Enter Parameter Value popup dialogue)

I could put in the exact rownumbers instead of pulling from the f3 table. How do you specify rownumber in Access? WHERE rownumber IN (1,5,10,80) ?????
0
 
flavoCommented:
yeah
0
 
ecuPiratesAuthor Commented:
It doesn't appear to like the rownum reference, still asking for a parameter:
SELECT  WMMSADBA_COMMON_EQUIP.COMMON_EQUIP_ID, WMMSADBA_COMMON_EQUIP.EQUIP_DESCR, WMMSADBA_COMMON_EQUIP.MANUFACTURER, WMMSADBA_COMMON_EQUIP.EQUIP_COMMENT1, WMMSADBA_COMMON_EQUIP.EQUIP_COMMENT2, WMMSADBA_COMMON_EQUIP.EQUIP_GROUP, WMMSADBA_COMMON_EQUIP.DRAWING_ID, WMMSADBA_COMMON_EQUIP.REFERENCE_FILE FROM  WMMSADBA_COMMON_EQUIP WHERE WMMSADBA_COMMON_EQUIP.rownum IN (38,76,115);
0
 
elcastorCommented:
What parameter is the system asking for?
0
 
ecuPiratesAuthor Commented:
rownum, it doesn't seem to recognize it. I tried rownum, row_num, rowid, rownumber...
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
You may consider responding to 4th comment above.

mike
0
 
elcastorCommented:
It seems like the rownum field isn't part of the WMMSADBA_COMMON_EQUIP table (at least not as far as MS Access is concerned). You can check this by opening this table in design mode. The system will indicate that you can't change anything as it is a linked table, but that's no problem. Just verify if the rownum column (or anything resembling this) is available.
0
 
ecuPiratesAuthor Commented:
ok, eghtebas, how do I get the function?
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
I will include only three fields to get started, you can add the others later.  But I need you to tell me what field it is going be sorted by:

SELECT fnRowNo() As RowNumber, COMMON_EQUIP_ID, EQUIP_DESCR, MANUFACTURER FROM WMMSADBA_COMMON_EQUIP Order By [?]

It will order and then number them

mike

Function fnRowNo() As Long
   
   fnRowNo=...

End Function

mike
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
I will use [DateField] to demonstrate the concept.  I have used this function many times for numbering and a different version of it for running sum.  We will add Where statement later.


SELECT fnRowNo([DateField]) As RowNumber, COMMON_EQUIP_ID, EQUIP_DESCR, MANUFACTURER FROM WMMSADBA_COMMON_EQUIP Order By [DateField]

Make sure DateField field is a required field and has valid date (use input mask if applies)
--------------
In a module, under module tab, paste:

Public lngRowNo As Long
Public dDate as Date

Function fnRowNo(DatVar As Date) As Long
   
   If Nz(DatVar,0)=0 or DatVar < dDate Then
       lngRowNo =1
   end if

   dDate = DatVar

   lngRowNo =1+lngRowNo
 
   fnRowNo=lngRowNo

End Function

mike
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
correction...

Function fnRowNo(DatVar As Date) As Long
   
   If Nz(DatVar,0)=0 or DatVar < dDate Then
       lngRowNo =1
   else
      lngRowNo =1+lngRowNo
  end if

   dDate = DatVar
   fnRowNo=lngRowNo

End Function
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
I am done for today.
0
 
ecuPiratesAuthor Commented:
Found another way, but thanks for all the help. I didn't try the function solution, but I'll make note of it for later. Thanks for everyones responses and assistance.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 6
  • 6
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now