Subquery?
Home > IT Answers > Oracle > Subquery?
MizzGail
85 pts.
0
Q:
Subquery?
Oracle, Subquery, SQLPlus
I used to think I was good with sql as I have used it for quite a few years with informix, but now I am trying to create an sqlplus with oracle and some things are not working the same.

I have 3 tables.

customer general
customer detail
location

I have used a join to list all the required customer general data based on a sub query against the customer detail records that have been update.

select substr(b.customername,1,10) name
from (
select a.customercode, a.custcreationdate, a.custlocation
from customer.detail a)
join customer.general b

a.custlocation is a two digit code that I want to lookup on another table to get the location name.
How do I do this?

I have the O'reilly oracle sqlplus book, but I'm not getting a good feel for using substrings and how to cut down the fields and the column they feed back. For example, in my informix query, I could ask for customername[1,10] and get back the first 10 positions of the name and the column in the report would also scale down. My report is cutting down the field value to 10 positions, but its still lusing the 40 position field for the header which is causing my report to wrap. How do I avoid that?

Any good reference books or websites?

thanks,
ASKED: Apr 17 2008  3:25 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Dwaltr
900 pts.
0
A:
 RATE THIS ANSWER
+1
Click to Vote:
  •   1
  •  0
  • AddThis Social Bookmark Button
OK, if I understand correctly you want a query to show the first 10 characters of a name and its location. My first thought is you don't specify the join column between customer.detail and customer.general. I am going to assume it is customercode. That makes the query look like this:

select substr(b.customername,1,10) name
from (
select a.customercode, a.custcreationdate, a.custlocation
from customer.detail) a,
customer.general b
where a.customercode = b.customercode;

or

select substr(b.customername,1,10) name
from (
select a.customercode, a.custcreationdate, a.custlocation
from customer.detail) a
join customer.general b on a.customercode = b.customercode.

Adding location also requires a join column. In customer.detail it looks like custlocation, I will assume the column in your location table is locationcode. Adding a third join looks like this:

select substr(b.customername,1,10) name, l.locationcode
from (
select a.customercode, a.custcreationdate, a.custlocation
from customer.detail) a,
customer.general b,
location l
where a.customercode = b.customercode
and a.custlocation = l.locationcode;

or

select substr(b.customername,1,10) name, l.locationcode
from (
select a.customercode, a.custcreationdate, a.custlocation
from customer.detail) a
join customer.general b on a.customercode = b.customercode.
join location l on a.custlocation = l.locationcode;

You will have to substitute the appropriate join and display columns.

As far as column width, if you are using sqlplus, you need to use the COLUMN command. Try this:
COLUMN name FORMAT A10;

This will limit the name column in your query to 10 characters all the time until you exit sqlplus. It affects any query with the name column in it, even if it is aliased to a different column in the database.

-Dave
Last Answered: Apr 17 2008  3:37 PM GMT by Dwaltr   900 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

MizzGail   85 pts.  |   Apr 17 2008  3:42PM GMT

AH. yes. forgot the ON statement. It is there. I am joining the first two on customer code.
I can do two joins in succession?
Let me try.

 

MizzGail   85 pts.  |   Apr 17 2008  3:50PM GMT

I got the 2nd join to work and its giving me the location name.
I tried using the column command as the first line in my query as

column locationname format A10 and it says its an invalid sql statement.

 

Dwaltr   900 pts.  |   Apr 17 2008  4:14PM GMT

It is only valid inside of SQL*Plus. Are you using SQLPlus or are you using something else like SQL Developer or TOAD?

 

MizzGail   85 pts.  |   Apr 17 2008  5:05PM GMT

I am using sqlplus from a unix server client server connecting to the oracle server.

 

Dwaltr   900 pts.  |   Apr 17 2008  5:45PM GMT

OK, at this point I am only guessing. The COLUMN command works using a column name or the alias. If you are using an alias, you must use the alias in the COLUMN statement. If you post your query, I may be able to do better.

 

MizzGail   85 pts.  |   Apr 17 2008  5:53PM GMT

1 column div.location.locname format A10
2* select locname from div.location
3 ;
column div.location.locname format A10
*
ERROR at line 1:
ORA-00900: invalid SQL statement

SQL>

I went back to just trying the one column to see if i can get this one peice to work.

 

Dwaltr   900 pts.  |   Apr 17 2008  6:19PM GMT

Couple of things I see:

1.) You are missing the semicolon at the end of the column statement. It is its own statement.
2.) You only specify the column name, not the owner, table, etc.

column locname format A10;

 

Dwaltr   900 pts.  |   Apr 17 2008  6:41PM GMT

Glad to hear it…

 

MizzGail   85 pts.  |   Apr 17 2008  6:59PM GMT

I thought the format was database.table.fieldname ? thats how I’ve been writing the queries. Everthing works but this one thing. I did try the semicol but it didn’t work either.

BUT…
I put in set linesize to 132 and now it doesn’t wrap. :0)

Thanks!

 

Dwaltr   900 pts.  |   Apr 17 2008  7:14PM GMT

That’s fine in the query (and it’s actually owner.table.column because with Oracle it’s one database per instance, unlike SQL Server and Sybase).

But the column command it is only the column or the alias if provided.

 
0