Question

Subquery?

Oracle, SQLPlus, Subquery

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,

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