SQL statement to extract attribute data from a single table in Oracle

[Anonymous User]

i have a single table in an Oracle database that has different unique feature class codes in the same table relating to main (CODE = 76000)  and pipe samples (CODE = 77030) taken against the mains.

I want to know how top grab attributes from the two feature classes without having to run multiple sql statements.

I have got to the same result by running multiple sql statements via the 'IN CLAUSE' so just want to further my skills.

I wanted to generate a single piece of SQL to grab the items in RED from a different feature class than 77030 i.e. the mains class  The ones in RED relate to the main that the pipe sample is attached to and come from a feature code of 76000.. I think I needed an inner join.

the S_IPID relates to the pipe sample and the S_PARENT_IPID relates to the main that the pipe sample is attached to. Hence we have the join, but I do not know how to grab the attributes in RED from the mains attributes

select S_IPID,
S_PARENT_IPID,
U_DATE_PIPE_SAMPLE,
S_XCOORD_A,
S_YCOORD_A,
U_CONDITION_GRADE,
U_GROUND_TYPE,
U_PIPE_SAMPLE_REF,
U_REASON,
U_STREET,
U_SURFACE_USE,
U_TOWN,
U_MATERIAL,
U_DIAMETER,
U_DATE_INSTALLED,
U_DATE_REHABILITATED
from sdf_asset_all
where S_FCODE=77030

[KevinDunlop · Accepted solution]

If I understand it right, you are looking for a SQL Statement that queries the same table but get the parent pipes values for each pipe which is also in that table.  Also remember if you are using a versioned table, you should be pulling from the sdf_asset_all_evw view instead of it's base table.  Since I don't table, I haven't test this but it should give you them main idea.

select samplePipe.S_IPID,
       samplePipe.S_PARENT_IPID, 
       samplePipe.U_DATE_PIPE_SAMPLE, 
       samplePipe.S_XCOORD_A, 
       samplePipe.S_YCOORD_A, 
       samplePipe.U_CONDITION_GRADE,
       samplePipe.U_GROUND_TYPE, 
       samplePipe.U_PIPE_SAMPLE_REF, 
       samplePipe.U_REASON, 
       samplePipe.U_STREET, 
       samplePipe.U_SURFACE_USE, 
       samplePipe.U_TOWN,
       mainPipe.U_MATERIAL,
       mainPipe.U_DIAMETER,
       mainPipe.U_DATE_INSTALLED,
       mainPipe.U_DATE_REHABILITATED 
from sdf_asset_all as samplePipe, 
     sdf_asset_all as mainPipe
where samplePipe.S_FCODE=77030
      and samplePipe.S_PARENT_IPID =mainPipe.S_IPID‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[Anonymous User]

i get the gist of it but when running this within TOAD I get an invalid syntax see attached

[KevinDunlop]

sorry, try removing the AS keywords.  They are not used with table names in Oracle.

[Anonymous User]

A big thanks Kevin this as now worked by taking out the 'as' from the clause

This one now saved in the memory bank for future reference.