Creating SQL Script

[nikkiheave]

OK, as stated before, I'm new to Oracle. I'm trying to create an oracle script that diplays the contents of the tables. The following script displays what I need for it to diplay:

select co.bom_type, cp.project_name, cf.profile_name, co.factory_id, co.status, cp.status, cf.sample_volume,
cf.PLATE_LAYOUT_ID, cf.NUM_REPLICATES, co.LOT_DETAIL, co.TESTSET_ID
from cos_order co, cos_project cp, cos_profile cf
where co.project_id = cp.project_id
and co.profile_id= cf.profile_id


but I need it to be in a form that users can understand, an example is the following:

Order id = 12356
Project Name = My Project,
BOM Type = Well


How do I write this script using DBMS output statements?
Like I said I'm new, and I'm learning!

.

[gandolf989]

You can give columns different names in the output.
If there is a space in the name you will need the quotes
otherwise you do not need the double quotes.
You can also format the column size
col bom_type format a15

select
co.bom_type "Bill of Material",
cp.project_name "Project Name",
cf.profile_name "Profile Name",
co.factory_id "Factory Id",
co.status,
cp.status,
cf.sample_volume,
cf.PLATE_LAYOUT_ID,
cf.NUM_REPLICATES,
co.LOT_DETAIL,
co.TESTSET_ID
from cos_order co, cos_project cp, cos_profile cf
where co.project_id = cp.project_id
and co.profile_id= cf.profile_id

[nikkiheave]

OK, but say I need to say: Well for instance:
The bom_type can be either a plate or well.
How would I put it so the program would say bom_type = plate or well

or like Project_name = My project

?

[Kash]

If I understand you correctly, you want the query to retrieve only the records where the bom_type is 'well' or where the bom_type is 'plate' or only the records for a certain project

All you need to do is specify the criteria in the where portion of the sql statement below.

For example:
The query suggested by gandolf989:

select
co.bom_type "Bill of Material",
cp.project_name "Project Name",
cf.profile_name "Profile Name",
co.factory_id "Factory Id",
co.status,
cp.status,
cf.sample_volume,
cf.PLATE_LAYOUT_ID,
cf.NUM_REPLICATES,
co.LOT_DETAIL,
co.TESTSET_ID
from cos_order co, cos_project cp, cos_profile cf
where co.project_id = cp.project_id
and co.profile_id= cf.profile_id
and co.bom_type = 'WELL'

The last line will tell your query to return only records of bom_type = 'WELL'

Hope that helps.

KASH

[nikkiheave]

No, the bom_type can = well or plate

so how do I display it so that the user understands that its either well or plate?

[Kash]

When you run the query suggested by gandolf989 the results should look something like this:


Bill of Material Project Name Profile Name Factory Id etc....

WELL MYPROJECT PROFILE 10001
PLATE MYPROJECT PROFILE 10002
PLATE NEWPROJ OLD 10891

I don't have room to include all the columns and I don't know if the data is accurate but when the results are displayed, the client will be able to see that some of the records have a bom_type of "WELL" and some have a bom_type of "PLATE"

KASH