Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Problem regarding query

 
Mohini Dhanaskar
Ranch Hand
Posts: 55
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hello All,

i had created two tables in a same oracle database.

first one is RegisterComplaint and second is ConsumerRegistration.

There fields are as follows:

RegisterComplaint:

CID VARCHAR2(5)
COMPLAINT_TYPE VARCHAR2(40)
NOCOMP NUMBER(2,0)
NOPRBM NUMBER(4,0)
TIME NUMBER(6,2)
CONSUMER-ID NUMBER(10,0)

ConsumerRegistration:


CONSUMER-ID NUMBER(10,0)
FNAME VARCHAR2(4000)
LNAME VARCHAR2(4000)
CADDR VARCHAR2(4000)
AREA VARCHAR2(4000)
PHN-NO NUMBER(10,0)
WARDNO VARCHAR2(4000)
ZONE VARCHAR2(4000)


here,i want to write one query which fetch d data from both the columns where RegisterComplaint Consumer-ID id same
as ConsumerRegistration Consumer-ID.

Please help me out in d same.
 
Wendy Gibbons
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
you need to use the where clause

the syntax is
select <columns>
from <comma seperated table list with aliases>
where <colunm1 = column2 etc.>

a table alias is required here for both tables as you have a column called consumer-id on both tables
 
Ravikanth thota
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
select *
from RegisterComplaint R, ConsumerRegistration C
where R.CONSUMER-ID=C.CONSUMER-ID;
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Likes 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
rk thota wrote:select *
from RegisterComplaint R, ConsumerRegistration C
where R.CONSUMER-ID=C.CONSUMER-ID;


Don't use "SELECT *..." here because you will get two Consumer ID columns (the PK and the FK). Specify the individual columns you need instead.

In general, you should be specific about which columns you need to fetch in your query, especially in a join query as here, so you know exactly which columns are being retrieved and in what order (e.g. for mapping into Java), you don't get any nasty surprises if somebody changes the underlying table structure (e.g. to add a column that you are not handling in Java), and you don't waste resources fetching data that is never used.

Also, check your column names. If the Consumer ID column is really "CONSUMER-ID" (not CONSUMER_ID) then you will need to wrap it in double quotes i.e. SELECT "CONSUMER-ID" FROM..., or Oracle will think you're trying to subtract the value in a column called ID from the value in a column called CONSUMER.

It's better to avoid making column or table names case sensitive or using characters like "-" in names, as it just creates more scope for errors. If possible, use underscores in names e.g. CONSUMER_ID, not CONSUMER-ID, then you can simply write SELECT consumer_id FROM... and not worry about case-sensitivity or quotes.

Incidentally, the way you explained your requirement is basically the way you would write the SQL - "fetch X where ...", so why not just go ahead and learn some SQL?
 
Wendy Gibbons
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
to follow on from Chris
column lists
 
Mohini Dhanaskar
Ranch Hand
Posts: 55
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i tried the query,

Query:
select CID,complaint_type,fname,lname
from RegisterComplaint1 R, ConsumerRegistration C
where R.CONSUMER-ID=C.CONSUMER-ID;


getting error:

ORA-00904: "ID": invalid identifier
 
Wendy Gibbons
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Mohini Dhanaskar wrote: i tried the query,

Query:
select CID,complaint_type,fname,lname
from RegisterComplaint1 R, ConsumerRegistration C
where R.CONSUMER-ID=C.CONSUMER-ID;


getting error:

ORA-00904: "ID": invalid identifier


Please read chris's reply, he explains about this:

Also, check your column names. If the Consumer ID column is really "CONSUMER-ID" (not CONSUMER_ID) then you will need to wrap it in double quotes i.e. SELECT "CONSUMER-ID" FROM..., or Oracle will think you're trying to subtract the value in a column called ID from the value in a column called CONSUMER.
 
Eshwin Sukhdeve
Ranch Hand
Posts: 90
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
mohini this error comes (ORA-00904 when you are not using valid identifier.remove(-) from the column name.put (_) .then try it should work.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic