Subquery?

85 pts.
Tags:
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,

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 10  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • MizzGail
    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.
    85 pointsBadges:
    report
  • MizzGail
    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.
    85 pointsBadges:
    report
  • Dwaltr
    It is only valid inside of SQL*Plus. Are you using SQLPlus or are you using something else like SQL Developer or TOAD?
    900 pointsBadges:
    report
  • MizzGail
    I am using sqlplus from a unix server client server connecting to the oracle server.
    85 pointsBadges:
    report
  • Dwaltr
    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.
    900 pointsBadges:
    report
  • MizzGail
    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.
    85 pointsBadges:
    report
  • Dwaltr
    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;
    900 pointsBadges:
    report
  • Dwaltr
    Glad to hear it...
    900 pointsBadges:
    report
  • MizzGail
    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!
    85 pointsBadges:
    report
  • Dwaltr
    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.
    900 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following