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,
Software/Hardware used:
Software/Hardware used:
ASKED:
April 17, 2008 3:25 PM
UPDATED:
April 17, 2008 7:14 PM
Answer Wiki:
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
To see all answers submitted to the Answer Wiki: View Answer History.
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.
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.
It is only valid inside of SQL*Plus. Are you using SQLPlus or are you using something else like SQL Developer or TOAD?
I am using sqlplus from a unix server client server connecting to the oracle server.
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.
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.
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;
Glad to hear it…
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!
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.