aspose file tools*
The moose likes JDBC and the fly likes Problem regarding query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Problem regarding query" Watch "Problem regarding query" New topic
Author

Problem regarding query

Mohini Dhanaskar
Ranch Hand

Joined: Mar 27, 2012
Posts: 54
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

Joined: Oct 21, 2008
Posts: 1107

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

Joined: Apr 09, 2012
Posts: 24
select *
from RegisterComplaint R, ConsumerRegistration C
where R.CONSUMER-ID=C.CONSUMER-ID;
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1848
    
  16

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?


No more Blub for me, thank you, Vicar.
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

to follow on from Chris
column lists
Mohini Dhanaskar
Ranch Hand

Joined: Mar 27, 2012
Posts: 54
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

Joined: Oct 21, 2008
Posts: 1107

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

Joined: Mar 15, 2012
Posts: 80
mohini this error comes (ORA-00904 when you are not using valid identifier.remove(-) from the column name.put (_) .then try it should work.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Problem regarding query