How to write multiple select statement for create table in oracle spatial?

Question

I'm trying to create geom table based on another table having lat lon. I'm trying to create two column one with Spherical Mercator( SRID 4326, geographic coordinate system) and other column with projected coordinate system (SRID 3857) in the table. Below is my query.

create table KP_SPTL(
                            select mdsys.sdo_geometry(
                                                        2003, 
                                                        4326, 
                                                        NULL, 
                                                        SDO_ELEM_INFO_ARRAY(1,3,3), 
                                                        SDO_ORDINATE_ARRAY(B.LL_LAT, B.LL_LON, B.UR_LAT, B.UR_LON)
                                                      ) as KP_GCS,
                              select mdsys.sdo_geometry(
                                                        2003, 
                                                        3857, 
                                                        NULL, 
                                                        SDO_ELEM_INFO_ARRAY(1,3,3), 
                                                        SDO_ORDINATE_ARRAY(B.LL_LAT, B.LL_LON, B.UR_LAT, B.UR_LON)
                                                      ) as KP_PCS,
                                                      B.COMPANY, B.ADDRS,B_CDE
                              FROM KP_STAGE B);

I get following error

Error report:
SQL Error: ORA-00936: missing expression
00936. 00000 -  "missing expression"

However if I remove the second select statement it works fine, I mean if the sql query is as below

create table KP_SPTL(
                            select mdsys.sdo_geometry(
                                                        2003, 
                                                        4326, 
                                                        NULL, 
                                                        SDO_ELEM_INFO_ARRAY(1,3,3), 
                                                        SDO_ORDINATE_ARRAY(B.LL_LAT, B.LL_LON, B.UR_LAT, B.UR_LON)
                                                      ) as KP_GCS,
                                                      B.COMPANY, B.ADDRS,B_CDE
                              FROM KP_STAGE B);

How to use the second select statement, if its normal query we can use select from statement. but how to use in this case?

Answer 1

I don't know what is oracle Spatial At all. but just by Lookin SQL, I think, the below would work.!

mdsys.sdo_geometry() returns a TYPE sdo_geometry, and it can be used in A SELECT clause multiple times. We do not need to accompany a SELECT every time.

Its is like SELECT B.COMPANY, B.ADDRS,B_CDE .. you don't need to prefix a SELECT always!

General syntax of an SQL could be SELECT <types/columns> FROM <view/table> WHERE <conditions>

create table KP_SPTL(
                            select mdsys.sdo_geometry(
                                                        2003, 
                                                        4326, 
                                                        NULL, 
                                                        SDO_ELEM_INFO_ARRAY(1,3,3), 
                                                        SDO_ORDINATE_ARRAY(B.LL_LAT, B.LL_LON, B.UR_LAT, B.UR_LON)
                                                      ) as KP_GCS,
                              mdsys.sdo_geometry(
                                                        2003, 
                                                        3857, 
                                                        NULL, 
                                                        SDO_ELEM_INFO_ARRAY(1,3,3), 
                                                        SDO_ORDINATE_ARRAY(B.LL_LAT, B.LL_LON, B.UR_LAT, B.UR_LON)
                                                      ) as KP_PCS,
                                                      B.COMPANY, B.ADDRS,B_CDE
                              FROM KP_STAGE B);