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
Solved! Go to 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
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
i get the gist of it but when running this within TOAD I get an invalid syntax see attached
sorry, try removing the AS keywords. They are not used with table names in Oracle.
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.