I have a table in MS Access and ran a query to return some distinct record, it works fine but a similar table in Oracle 9i using similar query (sql) failed. Find below the MS Access Orignal table, Access Query (sql), and table after rurnning query and the error returned while trying to run same query on Oracle 9i:
Access Orignal table
Name Add Category Bala
ATM 12, OREMEJI STREET A1 LL
ATM1 3, BANA ROAD A3 QQ
ATM 5, BADORE A111 AC
ATM 6, SHOLA STREET A23 AA
ATM1 67, BANA AVENUE A9 BB
ATM2 45, KADOZO A12 WE
ATM3 13, HALIMAT A87 GG
ATM3 23, KAMARA A8 XX
ATM1 78, BOUNCERS A5 TT
ATM2 35, KUDIRAT ABIOLA WAY A2 WE
Access Query (sql)
SELECT DISTINCT ATM.Name, FIRST(ATM.BALA) AS BALA, FIRST(ATM.ADD) AS ADDRESS
FROM ATM
GROUP BY NAME;
table after rurnning query
Name BALA ADDRESS
ATM LL 12, OREMEJI STREET
ATM1 QQ 3, BANA ROAD
ATM2 WE 45, KADOZO
ATM3 GG 13, HALIMAT
error oracle 9i
ORA-00904: "LAST": invalid identifier
3 9210
Please use this query : -
-
SELECT DISTINCT ATM.Name, ATM.BALA AS BALA, ATM.ADD AS ADDRESS
-
FROM ATM
-
GROUP BY NAME,BALA,ADDRESS;
-
-
Are you sure you ran the same query as above in your oracle 9i??
QVeen72 1,445
Recognized Expert Top Contributor
Hi,
Not very sure "First" and "Last" can be used directly without Dense_Rank.
Anyway, if you are Interested with any one of the Names from the List, you can use "Min" or "Max" functions.. -
SELECT DISTINCT ATM.Name, Min(ATM.BALA) AS BALA, Min(ATM.ADD) AS ADDRESS
-
FROM ATM
-
GROUP BY ATM.NAME
-
Regards
Veena
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Sean C. |
last post by:
Helpful folks,
Most of my previous experience with DB2 was on s390 mainframe systems
and the optimizer on this platform always seemed very predictable and
consistent. Since moving to a WinNT/UDB 7.2 environment, the choices
the optimizer makes often seem flaky. But this last example really
floored me. I was hoping someone could explain why...
|
by: Random Person |
last post by:
Does anyone know how to use VBA to relink tables between two MS Access
databases? We have two databases, one with VBA code and the other with
data tables. The tables are referenced by linked tables in the database
where the code resides. If we move the database with the data tables to
a new directory, the links are no longer valid.
I...
|
by: Peter Frost |
last post by:
Please help
I don't know if this is possible but what I would really like to do is
to use On Error Goto to capture the code that is being executed when
an error occurs.
Any help would be much appreciated.
Thanks in advance
|
by: David MacKay |
last post by:
Dear Greater Py,
<motivation note="reading this bit is optional">
I am writing a command-line reader for python.
I'm trying to write something with the same brevity
as perl's one-liner
eval "\$$1=\$2" while @ARGV && $ARGV=~ /^(\w+)=(.*)/ && shift;
|
by: Andrew Poulos |
last post by:
A friend of mine has built as simple site for use on his company's
intranet. It uses JavaScript to read comma-delimited information from a
text file and displays it in tabular form.
When I looked at the source there is js like this:
function forwardClick() {
if (sitelist.recordset.AbsolutePosition !=
sitelist.recordset.RecordCount) {...
| |
by: alexandre.brisebois |
last post by:
Hi, I am using access 2003, I would like to know if there is an option
to reorganize the tables in a maner that is readable, as we can do in
sql sever 2000 or 2005.
I have been given a database to look a and I am loosing tremendious
amounts of time trying to organize it so that I could view it.
Regards,
Alexandre Brisebois
|
by: lnatz |
last post by:
Hi,
Is there an equivalent to the perl command chomp in C? And if there is
no exact equivalent command, how would I go about removing the "\n" at
the end of a stdin?
Thank you,
Natalie
|
by: rick |
last post by:
hi
Is there a DB2 equivalent of Informix.Extend function?
or please help me the best way to create one
Thanks
rick
|
by: code green |
last post by:
I want to compare two text fields and return records where they differ. But I need to ignore the last 15 characters of the first string. Is there an equivalent to this function in Access
SUBSTRING(field1,-1,15).
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| |
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...
|
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...
|
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...
|
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...
|
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...
| |