Rows to columns
0
I am new to Oracle. I know MySQL. I wrote below query in MySQL:
Tables:
CREATE TABLE Ass (
asset_id BIGINT(20) NOT NULL,
caption VARCHAR(64) DEFAULT NULL,
description VARCHAR(4000) DEFAULT NULL,
NAME VARCHAR(1024) DEFAULT NULL,
PRIMARY KEY (asset_id)
)ENGINE=INNODB ;
CREATE TABLE agl_ownership (
ownership_id BIGINT(20) NOT NULL,
owned_object_id BIGINT(20) NOT NULL,
owner_id BIGINT(20) NOT NULL,
ownershiptype_id BIGINT(20) NOT NULL,
PRIMARY KEY (ownership_id)
) ENGINE=INNODB ;
CREATE TABLE agl_ownershiptype (
ownershiptype_id BIGINT(20) NOT NULL,
NAME VARCHAR(255) NOT NULL,
display_name VARCHAR(255) DEFAULT NULL,
TYPE VARCHAR(32) DEFAULT NULL,
solution_flags BIGINT(20) DEFAULT NULL,
systemtenant_id BIGINT(20) NOT NULL,
PRIMARY KEY (ownershiptype_id)
) ENGINE=INNODB ;
Data:
INSERT INTO Ass
(asset_id,caption,description,NAME)
VALUES
(4503599627380496,'My Test Entity','sdfsadf','My Test Entity'),
(4503599627380497,'222','sadfsadff','222' ),
(4503599627381496,'my latest','sdfas','my latest'),
(49539595901085456,'Test Service Type Entity','This is for Testing','Test Service Type Entity'),
(207165582859052816,'New Entity','new entity for mobile device','New Entity'),
(211669182486423312,'111','sadasd','111'),
(211669182486424312,'My Latest Entity 11','sadfsad','My Latest Entity 11'),
(211669182486424313,'New Entity for Test','asdfdsaf','New Entity for Test'),
(216172782113793808,'my test11','sdfdsaf','my test11'),
(680043543732954896,'To Find Owners Entity','sadfdsafdsa','To Find Owners Entity');
INSERT INTO agl_ownership
(ownership_id,owned_object_id,owner_id,ownershiptype_id)
VALUES
(1441151880758571720,4503599627380496,495395959010766560,1445655480385940221),
(1441151880758571723,4503599627380497,495395959010766560,1445655480385940221),
(1441151880758572729,4503599627381496,495395959010766560,1445655480385940217),
(1441151880758572720,4503599627381496,495395959010766560,1445655480385940221),
(1441151880758572728,4503599627381496,495395959010767560,1445655480385940222),
(1441151880758572730,4503599627381496,495395959010767562,1445655480385940220),
(1441151880758570720,49539595901085456,495395959010765560,1445655480385940221),
(1441151880758570727,49539595901085456,495395959010767560,1445655480385940220),
(1441151880758570728,49539595901085456,495395959010767562,1445655480385940222),
(1441151880758569720,207165582859052816,495395959010766560,1445655480385940221),
(1441151880758570733,207165582859052816,495395959010767560,1445655480385940222),
(1441151880758570734,207165582859052816,495395959010767562,1445655480385940220),
(1441151880758571722,211669182486423312,495395959010766560,1445655480385940221),
(1441151880758572738,211669182486424312,495395959010766560,1445655480385940220),
(1441151880758572737,211669182486424312,495395959010766560,1445655480385940221),
(1441151880758572740,211669182486424312,495395959010767560,1445655480385940217),
(1441151880758572739,211669182486424312,495395959010767562,1445655480385940222),
(1441151880758572749,211669182486424313,495395959010766560,1445655480385940221),
(1441151880758572750,211669182486424313,495395959010767562,1445655480385940222),
(1441151880758572747,216172782113793808,495395959010766560,1445655480385940218),
(1441151880758572746,216172782113793808,495395959010766560,1445655480385940221),
(1441151880758572748,216172782113793808,495395959010767562,1445655480385940222),
(1441151880758568720,391813167581245152,517913957147607041,1445655480385940230),
(1441151880758568721,391813167581245152,517913957147607041,1445655480385940231),
(1441151880758568722,391813167581245153,517913957147607041,1445655480385940230),
(1441151880758568723,391813167581245153,517913957147607041,1445655480385940231),
(1441151880758568724,391813167581245154,517913957147607041,1445655480385940230),
(1441151880758568725,391813167581245154,517913957147607041,1445655480385940231),
(1441151880758568726,391813167581245155,517913957147607041,1445655480385940230),
(1441151880758568727,391813167581245155,517913957147607041,1445655480385940231),
(1441151880758568728,391813167581245156,517913957147607041,1445655480385940230),
(1441151880758568729,391813167581245156,517913957147607041,1445655480385940231),
(1441151880758572742,680043543732954896,495395959010766560,1445655480385940221),
(1441151880758572743,680043543732954896,495395959010767560,1445655480385940217),
(1441151880758572744,680043543732954896,495395959010767560,1445655480385940220),
(1441151880758572745,680043543732954896,495395959010767562,1445655480385940222),
(1441151880758570723,905223525101503696,495395959010766560,1445655480385940225),
(1441151880758570721,905223525101503696,495395959010766560,1445655480385940226),
(1441151880758570724,905223525101503696,495395959010766560,1445655480385940227),
(1441151880758570722,905223525101503696,517913957147607041,1445655480385940229),
(1441151880758570730,905223525101503698,495395959010766560,1445655480385940225),
(1441151880758570732,905223525101503698,495395959010766560,1445655480385940226),
(1441151880758570729,905223525101503698,495395959010766560,1445655480385940227),
(1441151880758570731,905223525101503698,517913957147607041,1445655480385940229),
(1441151880758572727,905223525101504696,495395959010766560,1445655480385940224),
(1441151880758572724,905223525101504696,495395959010766560,1445655480385940225),
(1441151880758572722,905223525101504696,495395959010766560,1445655480385940226),
(1441151880758572725,905223525101504696,495395959010766560,1445655480385940227),
(1441151880758572726,905223525101504696,495395959010767560,1445655480385940228),
(1441151880758572723,905223525101504696,517913957147607041,1445655480385940229),
(1441151880758572733,905223525101504698,495395959010766560,1445655480385940224),
(1441151880758572736,905223525101504698,495395959010766560,1445655480385940225),
(1441151880758572732,905223525101504698,495395959010766560,1445655480385940226),
(1441151880758572735,905223525101504698,495395959010766560,1445655480385940227),
(1441151880758572734,905223525101504698,495395959010767560,1445655480385940228),
(1441151880758572731,905223525101504698,517913957147607041,1445655480385940229),
(1441151880758570725,1215971899390043920,495395959010766560,1445655480385940221),
(1441151880758570735,1215971899390043921,495395959010767560,1445655480385940221),
(1441151880758571724,1215971899390044921,495395959010766560,1445655480385940221),
(1441151880758572721,1215971899390045920,495395959010766560,1445655480385940221),
(1441151880758572741,1215971899390045921,495395959010766560,1445655480385940221);
INSERT INTO agl_ownershiptype
(ownershiptype_id,NAME,display_name,TYPE,solution_flags,systemtenant_id)
VALUES
(1445655480385939216,'Incident Viewer','Incident Viewer','ASSET',127,535928355657089024),
(1445655480385939217,'Business Owner','Business Owner','ASSET',127,535928355657089024),
(1445655480385939218,'Custom Owner1','Custom Owner1','ASSET',127,535928355657089024),
(1445655480385939219,'Custom Owner2','Custom Owner2','ASSET',127,535928355657089024),
(1445655480385939220,'Executive Owner','Executive Owner','ASSET',127,535928355657089024),
(1445655480385939221,'Primary Owner','Primary Owner','ASSET',127,535928355657089024),
(1445655480385939222,'Security Owner','Security Owner','ASSET',127,535928355657089024),
(1445655480385939223,'Vendor Contact','Vendor Contact','ASSET',127,535928355657089024),
(1445655480385939224,'Policy Approver','Policy Approver','POLICYSET',93,535928355657089024),
(1445655480385939225,'Policy Author','Policy Author','POLICYSET',93,535928355657089024),
(1445655480385939226,'Policy Primary Owner','Primary Owner','POLICYSET',93,535928355657089024),
(1445655480385939227,'Policy Requestor','Policy Requestor','POLICYSET',93,535928355657089024),
(1445655480385939228,'Policy Reviewer','Policy Reviewer','POLICYSET',93,535928355657089024),
(1445655480385939229,'Policy Viewer','Policy Viewer','POLICYSET',93,535928355657089024),
(1445655480385939230,'Reader','Reader','OBJECTGROUP',127,535928355657089024),
(1445655480385939231,'Writer','Writer','OBJECTGROUP',127,535928355657089024),
(1445655480385939232,'Reader','Reader','ATTACHMENTCOLLECTION',127,535928355657089024),
(1445655480385939233,'Writer','Writer','ATTACHMENTCOLLECTION',127,535928355657089024),
(1445655480385940216,'Incident Viewer','Incident Viewer','ASSET',127,535928355657089025),
(1445655480385940217,'Business Owner','Business Owner','ASSET',127,535928355657089025),
(1445655480385940218,'Custom Owner1','Custom Owner1','ASSET',127,535928355657089025),
(1445655480385940219,'Custom Owner2','Custom Owner2','ASSET',127,535928355657089025),
(1445655480385940220,'Executive Owner','Executive Owner','ASSET',127,535928355657089025),
(1445655480385940221,'Primary Owner','Primary Owner','ASSET',127,535928355657089025),
(1445655480385940222,'Security Owner','Security Owner','ASSET',127,535928355657089025),
(1445655480385940223,'Vendor Contact','Vendor Contact','ASSET',127,535928355657089025),
(1445655480385940224,'Policy Approver','Policy Approver','POLICYSET',93,535928355657089025),
(1445655480385940225,'Policy Author','Policy Author','POLICYSET',93,535928355657089025),
(1445655480385940226,'Policy Primary Owner','Primary Owner','POLICYSET',93,535928355657089025),
(1445655480385940227,'Policy Requestor','Policy Requestor','POLICYSET',93,535928355657089025),
(1445655480385940228,'Policy Reviewer','Policy Reviewer','POLICYSET',93,535928355657089025),
(1445655480385940229,'Policy Viewer','Policy Viewer','POLICYSET',93,535928355657089025),
(1445655480385940230,'Reader','Reader','OBJECTGROUP',127,535928355657089025),
(1445655480385940231,'Writer','Writer','OBJECTGROUP',127,535928355657089025),
(1445655480385940232,'Reader','Reader','ATTACHMENTCOLLECTION',127,535928355657089025),
(1445655480385940233,'Writer','Writer','ATTACHMENTCOLLECTION',127,535928355657089025);
Query:
SELECT a.asset_id AS AssetId,a.name AssetName,
MAX(IF(aot.name = 'Primary Owner', asu.userid, 'NO')) PrimaryOwner,
MAX(IF(aot.name = 'Business Owner', asu.userid, 'NO')) BusinessOwner,
MAX(IF(aot.name = 'Business Owner', asu.userid, 'NO')) ExecutiveOwner,
MAX(IF(aot.name = 'Security Owner', asu.userid, 'NO')) SecurityOwner
FROM agl_asset a
INNER JOIN agl_ownership ao ON (ao.owned_object_id=asset_id)
INNER JOIN agl_ownershiptype aot ON (aot.ownershiptype_id=ao.ownershiptype_id)
INNER JOIN agl_systemuser asu ON (asu.systemuser_id=ao.owner_id)
GROUP BY a.asset_id ;
This is correct as of my requirement it is working fine in MySQL.
How can I write same thing in Oracle 11g?
oracle oracle-11g-r2 plsql
asked Jan 7 '15 at 4:55
Pydi RajuPydi Raju
1892515
bumped to the homepage by Community♦ 16 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
0
I am new to Oracle. I know MySQL. I wrote below query in MySQL:
Tables:
CREATE TABLE Ass (
asset_id BIGINT(20) NOT NULL,
caption VARCHAR(64) DEFAULT NULL,
description VARCHAR(4000) DEFAULT NULL,
NAME VARCHAR(1024) DEFAULT NULL,
PRIMARY KEY (asset_id)
)ENGINE=INNODB ;
CREATE TABLE agl_ownership (
ownership_id BIGINT(20) NOT NULL,
owned_object_id BIGINT(20) NOT NULL,
owner_id BIGINT(20) NOT NULL,
ownershiptype_id BIGINT(20) NOT NULL,
PRIMARY KEY (ownership_id)
) ENGINE=INNODB ;
CREATE TABLE agl_ownershiptype (
ownershiptype_id BIGINT(20) NOT NULL,
NAME VARCHAR(255) NOT NULL,
display_name VARCHAR(255) DEFAULT NULL,
TYPE VARCHAR(32) DEFAULT NULL,
solution_flags BIGINT(20) DEFAULT NULL,
systemtenant_id BIGINT(20) NOT NULL,
PRIMARY KEY (ownershiptype_id)
) ENGINE=INNODB ;
Data:
INSERT INTO Ass
(asset_id,caption,description,NAME)
VALUES
(4503599627380496,'My Test Entity','sdfsadf','My Test Entity'),
(4503599627380497,'222','sadfsadff','222' ),
(4503599627381496,'my latest','sdfas','my latest'),
(49539595901085456,'Test Service Type Entity','This is for Testing','Test Service Type Entity'),
(207165582859052816,'New Entity','new entity for mobile device','New Entity'),
(211669182486423312,'111','sadasd','111'),
(211669182486424312,'My Latest Entity 11','sadfsad','My Latest Entity 11'),
(211669182486424313,'New Entity for Test','asdfdsaf','New Entity for Test'),
(216172782113793808,'my test11','sdfdsaf','my test11'),
(680043543732954896,'To Find Owners Entity','sadfdsafdsa','To Find Owners Entity');
INSERT INTO agl_ownership
(ownership_id,owned_object_id,owner_id,ownershiptype_id)
VALUES
(1441151880758571720,4503599627380496,495395959010766560,1445655480385940221),
(1441151880758571723,4503599627380497,495395959010766560,1445655480385940221),
(1441151880758572729,4503599627381496,495395959010766560,1445655480385940217),
(1441151880758572720,4503599627381496,495395959010766560,1445655480385940221),
(1441151880758572728,4503599627381496,495395959010767560,1445655480385940222),
(1441151880758572730,4503599627381496,495395959010767562,1445655480385940220),
(1441151880758570720,49539595901085456,495395959010765560,1445655480385940221),
(1441151880758570727,49539595901085456,495395959010767560,1445655480385940220),
(1441151880758570728,49539595901085456,495395959010767562,1445655480385940222),
(1441151880758569720,207165582859052816,495395959010766560,1445655480385940221),
(1441151880758570733,207165582859052816,495395959010767560,1445655480385940222),
(1441151880758570734,207165582859052816,495395959010767562,1445655480385940220),
(1441151880758571722,211669182486423312,495395959010766560,1445655480385940221),
(1441151880758572738,211669182486424312,495395959010766560,1445655480385940220),
(1441151880758572737,211669182486424312,495395959010766560,1445655480385940221),
(1441151880758572740,211669182486424312,495395959010767560,1445655480385940217),
(1441151880758572739,211669182486424312,495395959010767562,1445655480385940222),
(1441151880758572749,211669182486424313,495395959010766560,1445655480385940221),
(1441151880758572750,211669182486424313,495395959010767562,1445655480385940222),
(1441151880758572747,216172782113793808,495395959010766560,1445655480385940218),
(1441151880758572746,216172782113793808,495395959010766560,1445655480385940221),
(1441151880758572748,216172782113793808,495395959010767562,1445655480385940222),
(1441151880758568720,391813167581245152,517913957147607041,1445655480385940230),
(1441151880758568721,391813167581245152,517913957147607041,1445655480385940231),
(1441151880758568722,391813167581245153,517913957147607041,1445655480385940230),
(1441151880758568723,391813167581245153,517913957147607041,1445655480385940231),
(1441151880758568724,391813167581245154,517913957147607041,1445655480385940230),
(1441151880758568725,391813167581245154,517913957147607041,1445655480385940231),
(1441151880758568726,391813167581245155,517913957147607041,1445655480385940230),
(1441151880758568727,391813167581245155,517913957147607041,1445655480385940231),
(1441151880758568728,391813167581245156,517913957147607041,1445655480385940230),
(1441151880758568729,391813167581245156,517913957147607041,1445655480385940231),
(1441151880758572742,680043543732954896,495395959010766560,1445655480385940221),
(1441151880758572743,680043543732954896,495395959010767560,1445655480385940217),
(1441151880758572744,680043543732954896,495395959010767560,1445655480385940220),
(1441151880758572745,680043543732954896,495395959010767562,1445655480385940222),
(1441151880758570723,905223525101503696,495395959010766560,1445655480385940225),
(1441151880758570721,905223525101503696,495395959010766560,1445655480385940226),
(1441151880758570724,905223525101503696,495395959010766560,1445655480385940227),
(1441151880758570722,905223525101503696,517913957147607041,1445655480385940229),
(1441151880758570730,905223525101503698,495395959010766560,1445655480385940225),
(1441151880758570732,905223525101503698,495395959010766560,1445655480385940226),
(1441151880758570729,905223525101503698,495395959010766560,1445655480385940227),
(1441151880758570731,905223525101503698,517913957147607041,1445655480385940229),
(1441151880758572727,905223525101504696,495395959010766560,1445655480385940224),
(1441151880758572724,905223525101504696,495395959010766560,1445655480385940225),
(1441151880758572722,905223525101504696,495395959010766560,1445655480385940226),
(1441151880758572725,905223525101504696,495395959010766560,1445655480385940227),
(1441151880758572726,905223525101504696,495395959010767560,1445655480385940228),
(1441151880758572723,905223525101504696,517913957147607041,1445655480385940229),
(1441151880758572733,905223525101504698,495395959010766560,1445655480385940224),
(1441151880758572736,905223525101504698,495395959010766560,1445655480385940225),
(1441151880758572732,905223525101504698,495395959010766560,1445655480385940226),
(1441151880758572735,905223525101504698,495395959010766560,1445655480385940227),
(1441151880758572734,905223525101504698,495395959010767560,1445655480385940228),
(1441151880758572731,905223525101504698,517913957147607041,1445655480385940229),
(1441151880758570725,1215971899390043920,495395959010766560,1445655480385940221),
(1441151880758570735,1215971899390043921,495395959010767560,1445655480385940221),
(1441151880758571724,1215971899390044921,495395959010766560,1445655480385940221),
(1441151880758572721,1215971899390045920,495395959010766560,1445655480385940221),
(1441151880758572741,1215971899390045921,495395959010766560,1445655480385940221);
INSERT INTO agl_ownershiptype
(ownershiptype_id,NAME,display_name,TYPE,solution_flags,systemtenant_id)
VALUES
(1445655480385939216,'Incident Viewer','Incident Viewer','ASSET',127,535928355657089024),
(1445655480385939217,'Business Owner','Business Owner','ASSET',127,535928355657089024),
(1445655480385939218,'Custom Owner1','Custom Owner1','ASSET',127,535928355657089024),
(1445655480385939219,'Custom Owner2','Custom Owner2','ASSET',127,535928355657089024),
(1445655480385939220,'Executive Owner','Executive Owner','ASSET',127,535928355657089024),
(1445655480385939221,'Primary Owner','Primary Owner','ASSET',127,535928355657089024),
(1445655480385939222,'Security Owner','Security Owner','ASSET',127,535928355657089024),
(1445655480385939223,'Vendor Contact','Vendor Contact','ASSET',127,535928355657089024),
(1445655480385939224,'Policy Approver','Policy Approver','POLICYSET',93,535928355657089024),
(1445655480385939225,'Policy Author','Policy Author','POLICYSET',93,535928355657089024),
(1445655480385939226,'Policy Primary Owner','Primary Owner','POLICYSET',93,535928355657089024),
(1445655480385939227,'Policy Requestor','Policy Requestor','POLICYSET',93,535928355657089024),
(1445655480385939228,'Policy Reviewer','Policy Reviewer','POLICYSET',93,535928355657089024),
(1445655480385939229,'Policy Viewer','Policy Viewer','POLICYSET',93,535928355657089024),
(1445655480385939230,'Reader','Reader','OBJECTGROUP',127,535928355657089024),
(1445655480385939231,'Writer','Writer','OBJECTGROUP',127,535928355657089024),
(1445655480385939232,'Reader','Reader','ATTACHMENTCOLLECTION',127,535928355657089024),
(1445655480385939233,'Writer','Writer','ATTACHMENTCOLLECTION',127,535928355657089024),
(1445655480385940216,'Incident Viewer','Incident Viewer','ASSET',127,535928355657089025),
(1445655480385940217,'Business Owner','Business Owner','ASSET',127,535928355657089025),
(1445655480385940218,'Custom Owner1','Custom Owner1','ASSET',127,535928355657089025),
(1445655480385940219,'Custom Owner2','Custom Owner2','ASSET',127,535928355657089025),
(1445655480385940220,'Executive Owner','Executive Owner','ASSET',127,535928355657089025),
(1445655480385940221,'Primary Owner','Primary Owner','ASSET',127,535928355657089025),
(1445655480385940222,'Security Owner','Security Owner','ASSET',127,535928355657089025),
(1445655480385940223,'Vendor Contact','Vendor Contact','ASSET',127,535928355657089025),
(1445655480385940224,'Policy Approver','Policy Approver','POLICYSET',93,535928355657089025),
(1445655480385940225,'Policy Author','Policy Author','POLICYSET',93,535928355657089025),
(1445655480385940226,'Policy Primary Owner','Primary Owner','POLICYSET',93,535928355657089025),
(1445655480385940227,'Policy Requestor','Policy Requestor','POLICYSET',93,535928355657089025),
(1445655480385940228,'Policy Reviewer','Policy Reviewer','POLICYSET',93,535928355657089025),
(1445655480385940229,'Policy Viewer','Policy Viewer','POLICYSET',93,535928355657089025),
(1445655480385940230,'Reader','Reader','OBJECTGROUP',127,535928355657089025),
(1445655480385940231,'Writer','Writer','OBJECTGROUP',127,535928355657089025),
(1445655480385940232,'Reader','Reader','ATTACHMENTCOLLECTION',127,535928355657089025),
(1445655480385940233,'Writer','Writer','ATTACHMENTCOLLECTION',127,535928355657089025);
Query:
SELECT a.asset_id AS AssetId,a.name AssetName,
MAX(IF(aot.name = 'Primary Owner', asu.userid, 'NO')) PrimaryOwner,
MAX(IF(aot.name = 'Business Owner', asu.userid, 'NO')) BusinessOwner,
MAX(IF(aot.name = 'Business Owner', asu.userid, 'NO')) ExecutiveOwner,
MAX(IF(aot.name = 'Security Owner', asu.userid, 'NO')) SecurityOwner
FROM agl_asset a
INNER JOIN agl_ownership ao ON (ao.owned_object_id=asset_id)
INNER JOIN agl_ownershiptype aot ON (aot.ownershiptype_id=ao.ownershiptype_id)
INNER JOIN agl_systemuser asu ON (asu.systemuser_id=ao.owner_id)
GROUP BY a.asset_id ;
This is correct as of my requirement it is working fine in MySQL.
How can I write same thing in Oracle 11g?
oracle oracle-11g-r2 plsql
asked Jan 7 '15 at 4:55
Pydi RajuPydi Raju
1892515
bumped to the homepage by Community♦ 16 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
you can only specify one set of values in oracle insert or use INSERT INTO ... SELECT option. e.g. is below insert into my_table(col1, col2, col3) select 'A' col1,1 col2,4 col3 from dual union all select 'B' col1,2 col2,3 col3 from dual union all select 'C' col1,3 col2,2 col3 from dual union all select 'D' col1,4 col2,1 col3 from dual; This will insert 4 rows into my_table. (sorry couldn't figure out how to format code in comments).
– Raj
Jan 7 '15 at 13:38
1
Which part are you stuck on: CREATE TABLE, INSERT, or SELECT?
– Joshua Huber
Jan 7 '15 at 17:02
add a comment |
0
0
0
I am new to Oracle. I know MySQL. I wrote below query in MySQL:
Tables:
CREATE TABLE Ass (
asset_id BIGINT(20) NOT NULL,
caption VARCHAR(64) DEFAULT NULL,
description VARCHAR(4000) DEFAULT NULL,
NAME VARCHAR(1024) DEFAULT NULL,
PRIMARY KEY (asset_id)
)ENGINE=INNODB ;
CREATE TABLE agl_ownership (
ownership_id BIGINT(20) NOT NULL,
owned_object_id BIGINT(20) NOT NULL,
owner_id BIGINT(20) NOT NULL,
ownershiptype_id BIGINT(20) NOT NULL,
PRIMARY KEY (ownership_id)
) ENGINE=INNODB ;
CREATE TABLE agl_ownershiptype (
ownershiptype_id BIGINT(20) NOT NULL,
NAME VARCHAR(255) NOT NULL,
display_name VARCHAR(255) DEFAULT NULL,
TYPE VARCHAR(32) DEFAULT NULL,
solution_flags BIGINT(20) DEFAULT NULL,
systemtenant_id BIGINT(20) NOT NULL,
PRIMARY KEY (ownershiptype_id)
) ENGINE=INNODB ;
Data:
INSERT INTO Ass
(asset_id,caption,description,NAME)
VALUES
(4503599627380496,'My Test Entity','sdfsadf','My Test Entity'),
(4503599627380497,'222','sadfsadff','222' ),
(4503599627381496,'my latest','sdfas','my latest'),
(49539595901085456,'Test Service Type Entity','This is for Testing','Test Service Type Entity'),
(207165582859052816,'New Entity','new entity for mobile device','New Entity'),
(211669182486423312,'111','sadasd','111'),
(211669182486424312,'My Latest Entity 11','sadfsad','My Latest Entity 11'),
(211669182486424313,'New Entity for Test','asdfdsaf','New Entity for Test'),
(216172782113793808,'my test11','sdfdsaf','my test11'),
(680043543732954896,'To Find Owners Entity','sadfdsafdsa','To Find Owners Entity');
INSERT INTO agl_ownership
(ownership_id,owned_object_id,owner_id,ownershiptype_id)
VALUES
(1441151880758571720,4503599627380496,495395959010766560,1445655480385940221),
(1441151880758571723,4503599627380497,495395959010766560,1445655480385940221),
(1441151880758572729,4503599627381496,495395959010766560,1445655480385940217),
(1441151880758572720,4503599627381496,495395959010766560,1445655480385940221),
(1441151880758572728,4503599627381496,495395959010767560,1445655480385940222),
(1441151880758572730,4503599627381496,495395959010767562,1445655480385940220),
(1441151880758570720,49539595901085456,495395959010765560,1445655480385940221),
(1441151880758570727,49539595901085456,495395959010767560,1445655480385940220),
(1441151880758570728,49539595901085456,495395959010767562,1445655480385940222),
(1441151880758569720,207165582859052816,495395959010766560,1445655480385940221),
(1441151880758570733,207165582859052816,495395959010767560,1445655480385940222),
(1441151880758570734,207165582859052816,495395959010767562,1445655480385940220),
(1441151880758571722,211669182486423312,495395959010766560,1445655480385940221),
(1441151880758572738,211669182486424312,495395959010766560,1445655480385940220),
(1441151880758572737,211669182486424312,495395959010766560,1445655480385940221),
(1441151880758572740,211669182486424312,495395959010767560,1445655480385940217),
(1441151880758572739,211669182486424312,495395959010767562,1445655480385940222),
(1441151880758572749,211669182486424313,495395959010766560,1445655480385940221),
(1441151880758572750,211669182486424313,495395959010767562,1445655480385940222),
(1441151880758572747,216172782113793808,495395959010766560,1445655480385940218),
(1441151880758572746,216172782113793808,495395959010766560,1445655480385940221),
(1441151880758572748,216172782113793808,495395959010767562,1445655480385940222),
(1441151880758568720,391813167581245152,517913957147607041,1445655480385940230),
(1441151880758568721,391813167581245152,517913957147607041,1445655480385940231),
(1441151880758568722,391813167581245153,517913957147607041,1445655480385940230),
(1441151880758568723,391813167581245153,517913957147607041,1445655480385940231),
(1441151880758568724,391813167581245154,517913957147607041,1445655480385940230),
(1441151880758568725,391813167581245154,517913957147607041,1445655480385940231),
(1441151880758568726,391813167581245155,517913957147607041,1445655480385940230),
(1441151880758568727,391813167581245155,517913957147607041,1445655480385940231),
(1441151880758568728,391813167581245156,517913957147607041,1445655480385940230),
(1441151880758568729,391813167581245156,517913957147607041,1445655480385940231),
(1441151880758572742,680043543732954896,495395959010766560,1445655480385940221),
(1441151880758572743,680043543732954896,495395959010767560,1445655480385940217),
(1441151880758572744,680043543732954896,495395959010767560,1445655480385940220),
(1441151880758572745,680043543732954896,495395959010767562,1445655480385940222),
(1441151880758570723,905223525101503696,495395959010766560,1445655480385940225),
(1441151880758570721,905223525101503696,495395959010766560,1445655480385940226),
(1441151880758570724,905223525101503696,495395959010766560,1445655480385940227),
(1441151880758570722,905223525101503696,517913957147607041,1445655480385940229),
(1441151880758570730,905223525101503698,495395959010766560,1445655480385940225),
(1441151880758570732,905223525101503698,495395959010766560,1445655480385940226),
(1441151880758570729,905223525101503698,495395959010766560,1445655480385940227),
(1441151880758570731,905223525101503698,517913957147607041,1445655480385940229),
(1441151880758572727,905223525101504696,495395959010766560,1445655480385940224),
(1441151880758572724,905223525101504696,495395959010766560,1445655480385940225),
(1441151880758572722,905223525101504696,495395959010766560,1445655480385940226),
(1441151880758572725,905223525101504696,495395959010766560,1445655480385940227),
(1441151880758572726,905223525101504696,495395959010767560,1445655480385940228),
(1441151880758572723,905223525101504696,517913957147607041,1445655480385940229),
(1441151880758572733,905223525101504698,495395959010766560,1445655480385940224),
(1441151880758572736,905223525101504698,495395959010766560,1445655480385940225),
(1441151880758572732,905223525101504698,495395959010766560,1445655480385940226),
(1441151880758572735,905223525101504698,495395959010766560,1445655480385940227),
(1441151880758572734,905223525101504698,495395959010767560,1445655480385940228),
(1441151880758572731,905223525101504698,517913957147607041,1445655480385940229),
(1441151880758570725,1215971899390043920,495395959010766560,1445655480385940221),
(1441151880758570735,1215971899390043921,495395959010767560,1445655480385940221),
(1441151880758571724,1215971899390044921,495395959010766560,1445655480385940221),
(1441151880758572721,1215971899390045920,495395959010766560,1445655480385940221),
(1441151880758572741,1215971899390045921,495395959010766560,1445655480385940221);
INSERT INTO agl_ownershiptype
(ownershiptype_id,NAME,display_name,TYPE,solution_flags,systemtenant_id)
VALUES
(1445655480385939216,'Incident Viewer','Incident Viewer','ASSET',127,535928355657089024),
(1445655480385939217,'Business Owner','Business Owner','ASSET',127,535928355657089024),
(1445655480385939218,'Custom Owner1','Custom Owner1','ASSET',127,535928355657089024),
(1445655480385939219,'Custom Owner2','Custom Owner2','ASSET',127,535928355657089024),
(1445655480385939220,'Executive Owner','Executive Owner','ASSET',127,535928355657089024),
(1445655480385939221,'Primary Owner','Primary Owner','ASSET',127,535928355657089024),
(1445655480385939222,'Security Owner','Security Owner','ASSET',127,535928355657089024),
(1445655480385939223,'Vendor Contact','Vendor Contact','ASSET',127,535928355657089024),
(1445655480385939224,'Policy Approver','Policy Approver','POLICYSET',93,535928355657089024),
(1445655480385939225,'Policy Author','Policy Author','POLICYSET',93,535928355657089024),
(1445655480385939226,'Policy Primary Owner','Primary Owner','POLICYSET',93,535928355657089024),
(1445655480385939227,'Policy Requestor','Policy Requestor','POLICYSET',93,535928355657089024),
(1445655480385939228,'Policy Reviewer','Policy Reviewer','POLICYSET',93,535928355657089024),
(1445655480385939229,'Policy Viewer','Policy Viewer','POLICYSET',93,535928355657089024),
(1445655480385939230,'Reader','Reader','OBJECTGROUP',127,535928355657089024),
(1445655480385939231,'Writer','Writer','OBJECTGROUP',127,535928355657089024),
(1445655480385939232,'Reader','Reader','ATTACHMENTCOLLECTION',127,535928355657089024),
(1445655480385939233,'Writer','Writer','ATTACHMENTCOLLECTION',127,535928355657089024),
(1445655480385940216,'Incident Viewer','Incident Viewer','ASSET',127,535928355657089025),
(1445655480385940217,'Business Owner','Business Owner','ASSET',127,535928355657089025),
(1445655480385940218,'Custom Owner1','Custom Owner1','ASSET',127,535928355657089025),
(1445655480385940219,'Custom Owner2','Custom Owner2','ASSET',127,535928355657089025),
(1445655480385940220,'Executive Owner','Executive Owner','ASSET',127,535928355657089025),
(1445655480385940221,'Primary Owner','Primary Owner','ASSET',127,535928355657089025),
(1445655480385940222,'Security Owner','Security Owner','ASSET',127,535928355657089025),
(1445655480385940223,'Vendor Contact','Vendor Contact','ASSET',127,535928355657089025),
(1445655480385940224,'Policy Approver','Policy Approver','POLICYSET',93,535928355657089025),
(1445655480385940225,'Policy Author','Policy Author','POLICYSET',93,535928355657089025),
(1445655480385940226,'Policy Primary Owner','Primary Owner','POLICYSET',93,535928355657089025),
(1445655480385940227,'Policy Requestor','Policy Requestor','POLICYSET',93,535928355657089025),
(1445655480385940228,'Policy Reviewer','Policy Reviewer','POLICYSET',93,535928355657089025),
(1445655480385940229,'Policy Viewer','Policy Viewer','POLICYSET',93,535928355657089025),
(1445655480385940230,'Reader','Reader','OBJECTGROUP',127,535928355657089025),
(1445655480385940231,'Writer','Writer','OBJECTGROUP',127,535928355657089025),
(1445655480385940232,'Reader','Reader','ATTACHMENTCOLLECTION',127,535928355657089025),
(1445655480385940233,'Writer','Writer','ATTACHMENTCOLLECTION',127,535928355657089025);
Query:
SELECT a.asset_id AS AssetId,a.name AssetName,
MAX(IF(aot.name = 'Primary Owner', asu.userid, 'NO')) PrimaryOwner,
MAX(IF(aot.name = 'Business Owner', asu.userid, 'NO')) BusinessOwner,
MAX(IF(aot.name = 'Business Owner', asu.userid, 'NO')) ExecutiveOwner,
MAX(IF(aot.name = 'Security Owner', asu.userid, 'NO')) SecurityOwner
FROM agl_asset a
INNER JOIN agl_ownership ao ON (ao.owned_object_id=asset_id)
INNER JOIN agl_ownershiptype aot ON (aot.ownershiptype_id=ao.ownershiptype_id)
INNER JOIN agl_systemuser asu ON (asu.systemuser_id=ao.owner_id)
GROUP BY a.asset_id ;
This is correct as of my requirement it is working fine in MySQL.
How can I write same thing in Oracle 11g?
oracle oracle-11g-r2 plsql
asked Jan 7 '15 at 4:55
Pydi RajuPydi Raju
1892515
I am new to Oracle. I know MySQL. I wrote below query in MySQL:
Tables:
CREATE TABLE Ass (
asset_id BIGINT(20) NOT NULL,
caption VARCHAR(64) DEFAULT NULL,
description VARCHAR(4000) DEFAULT NULL,
NAME VARCHAR(1024) DEFAULT NULL,
PRIMARY KEY (asset_id)
)ENGINE=INNODB ;
CREATE TABLE agl_ownership (
ownership_id BIGINT(20) NOT NULL,
owned_object_id BIGINT(20) NOT NULL,
owner_id BIGINT(20) NOT NULL,
ownershiptype_id BIGINT(20) NOT NULL,
PRIMARY KEY (ownership_id)
) ENGINE=INNODB ;
CREATE TABLE agl_ownershiptype (
ownershiptype_id BIGINT(20) NOT NULL,
NAME VARCHAR(255) NOT NULL,
display_name VARCHAR(255) DEFAULT NULL,
TYPE VARCHAR(32) DEFAULT NULL,
solution_flags BIGINT(20) DEFAULT NULL,
systemtenant_id BIGINT(20) NOT NULL,
PRIMARY KEY (ownershiptype_id)
) ENGINE=INNODB ;
Data:
INSERT INTO Ass
(asset_id,caption,description,NAME)
VALUES
(4503599627380496,'My Test Entity','sdfsadf','My Test Entity'),
(4503599627380497,'222','sadfsadff','222' ),
(4503599627381496,'my latest','sdfas','my latest'),
(49539595901085456,'Test Service Type Entity','This is for Testing','Test Service Type Entity'),
(207165582859052816,'New Entity','new entity for mobile device','New Entity'),
(211669182486423312,'111','sadasd','111'),
(211669182486424312,'My Latest Entity 11','sadfsad','My Latest Entity 11'),
(211669182486424313,'New Entity for Test','asdfdsaf','New Entity for Test'),
(216172782113793808,'my test11','sdfdsaf','my test11'),
(680043543732954896,'To Find Owners Entity','sadfdsafdsa','To Find Owners Entity');
INSERT INTO agl_ownership
(ownership_id,owned_object_id,owner_id,ownershiptype_id)
VALUES
(1441151880758571720,4503599627380496,495395959010766560,1445655480385940221),
(1441151880758571723,4503599627380497,495395959010766560,1445655480385940221),
(1441151880758572729,4503599627381496,495395959010766560,1445655480385940217),
(1441151880758572720,4503599627381496,495395959010766560,1445655480385940221),
(1441151880758572728,4503599627381496,495395959010767560,1445655480385940222),
(1441151880758572730,4503599627381496,495395959010767562,1445655480385940220),
(1441151880758570720,49539595901085456,495395959010765560,1445655480385940221),
(1441151880758570727,49539595901085456,495395959010767560,1445655480385940220),
(1441151880758570728,49539595901085456,495395959010767562,1445655480385940222),
(1441151880758569720,207165582859052816,495395959010766560,1445655480385940221),
(1441151880758570733,207165582859052816,495395959010767560,1445655480385940222),
(1441151880758570734,207165582859052816,495395959010767562,1445655480385940220),
(1441151880758571722,211669182486423312,495395959010766560,1445655480385940221),
(1441151880758572738,211669182486424312,495395959010766560,1445655480385940220),
(1441151880758572737,211669182486424312,495395959010766560,1445655480385940221),
(1441151880758572740,211669182486424312,495395959010767560,1445655480385940217),
(1441151880758572739,211669182486424312,495395959010767562,1445655480385940222),
(1441151880758572749,211669182486424313,495395959010766560,1445655480385940221),
(1441151880758572750,211669182486424313,495395959010767562,1445655480385940222),
(1441151880758572747,216172782113793808,495395959010766560,1445655480385940218),
(1441151880758572746,216172782113793808,495395959010766560,1445655480385940221),
(1441151880758572748,216172782113793808,495395959010767562,1445655480385940222),
(1441151880758568720,391813167581245152,517913957147607041,1445655480385940230),
(1441151880758568721,391813167581245152,517913957147607041,1445655480385940231),
(1441151880758568722,391813167581245153,517913957147607041,1445655480385940230),
(1441151880758568723,391813167581245153,517913957147607041,1445655480385940231),
(1441151880758568724,391813167581245154,517913957147607041,1445655480385940230),
(1441151880758568725,391813167581245154,517913957147607041,1445655480385940231),
(1441151880758568726,391813167581245155,517913957147607041,1445655480385940230),
(1441151880758568727,391813167581245155,517913957147607041,1445655480385940231),
(1441151880758568728,391813167581245156,517913957147607041,1445655480385940230),
(1441151880758568729,391813167581245156,517913957147607041,1445655480385940231),
(1441151880758572742,680043543732954896,495395959010766560,1445655480385940221),
(1441151880758572743,680043543732954896,495395959010767560,1445655480385940217),
(1441151880758572744,680043543732954896,495395959010767560,1445655480385940220),
(1441151880758572745,680043543732954896,495395959010767562,1445655480385940222),
(1441151880758570723,905223525101503696,495395959010766560,1445655480385940225),
(1441151880758570721,905223525101503696,495395959010766560,1445655480385940226),
(1441151880758570724,905223525101503696,495395959010766560,1445655480385940227),
(1441151880758570722,905223525101503696,517913957147607041,1445655480385940229),
(1441151880758570730,905223525101503698,495395959010766560,1445655480385940225),
(1441151880758570732,905223525101503698,495395959010766560,1445655480385940226),
(1441151880758570729,905223525101503698,495395959010766560,1445655480385940227),
(1441151880758570731,905223525101503698,517913957147607041,1445655480385940229),
(1441151880758572727,905223525101504696,495395959010766560,1445655480385940224),
(1441151880758572724,905223525101504696,495395959010766560,1445655480385940225),
(1441151880758572722,905223525101504696,495395959010766560,1445655480385940226),
(1441151880758572725,905223525101504696,495395959010766560,1445655480385940227),
(1441151880758572726,905223525101504696,495395959010767560,1445655480385940228),
(1441151880758572723,905223525101504696,517913957147607041,1445655480385940229),
(1441151880758572733,905223525101504698,495395959010766560,1445655480385940224),
(1441151880758572736,905223525101504698,495395959010766560,1445655480385940225),
(1441151880758572732,905223525101504698,495395959010766560,1445655480385940226),
(1441151880758572735,905223525101504698,495395959010766560,1445655480385940227),
(1441151880758572734,905223525101504698,495395959010767560,1445655480385940228),
(1441151880758572731,905223525101504698,517913957147607041,1445655480385940229),
(1441151880758570725,1215971899390043920,495395959010766560,1445655480385940221),
(1441151880758570735,1215971899390043921,495395959010767560,1445655480385940221),
(1441151880758571724,1215971899390044921,495395959010766560,1445655480385940221),
(1441151880758572721,1215971899390045920,495395959010766560,1445655480385940221),
(1441151880758572741,1215971899390045921,495395959010766560,1445655480385940221);
INSERT INTO agl_ownershiptype
(ownershiptype_id,NAME,display_name,TYPE,solution_flags,systemtenant_id)
VALUES
(1445655480385939216,'Incident Viewer','Incident Viewer','ASSET',127,535928355657089024),
(1445655480385939217,'Business Owner','Business Owner','ASSET',127,535928355657089024),
(1445655480385939218,'Custom Owner1','Custom Owner1','ASSET',127,535928355657089024),
(1445655480385939219,'Custom Owner2','Custom Owner2','ASSET',127,535928355657089024),
(1445655480385939220,'Executive Owner','Executive Owner','ASSET',127,535928355657089024),
(1445655480385939221,'Primary Owner','Primary Owner','ASSET',127,535928355657089024),
(1445655480385939222,'Security Owner','Security Owner','ASSET',127,535928355657089024),
(1445655480385939223,'Vendor Contact','Vendor Contact','ASSET',127,535928355657089024),
(1445655480385939224,'Policy Approver','Policy Approver','POLICYSET',93,535928355657089024),
(1445655480385939225,'Policy Author','Policy Author','POLICYSET',93,535928355657089024),
(1445655480385939226,'Policy Primary Owner','Primary Owner','POLICYSET',93,535928355657089024),
(1445655480385939227,'Policy Requestor','Policy Requestor','POLICYSET',93,535928355657089024),
(1445655480385939228,'Policy Reviewer','Policy Reviewer','POLICYSET',93,535928355657089024),
(1445655480385939229,'Policy Viewer','Policy Viewer','POLICYSET',93,535928355657089024),
(1445655480385939230,'Reader','Reader','OBJECTGROUP',127,535928355657089024),
(1445655480385939231,'Writer','Writer','OBJECTGROUP',127,535928355657089024),
(1445655480385939232,'Reader','Reader','ATTACHMENTCOLLECTION',127,535928355657089024),
(1445655480385939233,'Writer','Writer','ATTACHMENTCOLLECTION',127,535928355657089024),
(1445655480385940216,'Incident Viewer','Incident Viewer','ASSET',127,535928355657089025),
(1445655480385940217,'Business Owner','Business Owner','ASSET',127,535928355657089025),
(1445655480385940218,'Custom Owner1','Custom Owner1','ASSET',127,535928355657089025),
(1445655480385940219,'Custom Owner2','Custom Owner2','ASSET',127,535928355657089025),
(1445655480385940220,'Executive Owner','Executive Owner','ASSET',127,535928355657089025),
(1445655480385940221,'Primary Owner','Primary Owner','ASSET',127,535928355657089025),
(1445655480385940222,'Security Owner','Security Owner','ASSET',127,535928355657089025),
(1445655480385940223,'Vendor Contact','Vendor Contact','ASSET',127,535928355657089025),
(1445655480385940224,'Policy Approver','Policy Approver','POLICYSET',93,535928355657089025),
(1445655480385940225,'Policy Author','Policy Author','POLICYSET',93,535928355657089025),
(1445655480385940226,'Policy Primary Owner','Primary Owner','POLICYSET',93,535928355657089025),
(1445655480385940227,'Policy Requestor','Policy Requestor','POLICYSET',93,535928355657089025),
(1445655480385940228,'Policy Reviewer','Policy Reviewer','POLICYSET',93,535928355657089025),
(1445655480385940229,'Policy Viewer','Policy Viewer','POLICYSET',93,535928355657089025),
(1445655480385940230,'Reader','Reader','OBJECTGROUP',127,535928355657089025),
(1445655480385940231,'Writer','Writer','OBJECTGROUP',127,535928355657089025),
(1445655480385940232,'Reader','Reader','ATTACHMENTCOLLECTION',127,535928355657089025),
(1445655480385940233,'Writer','Writer','ATTACHMENTCOLLECTION',127,535928355657089025);
Query:
SELECT a.asset_id AS AssetId,a.name AssetName,
MAX(IF(aot.name = 'Primary Owner', asu.userid, 'NO')) PrimaryOwner,
MAX(IF(aot.name = 'Business Owner', asu.userid, 'NO')) BusinessOwner,
MAX(IF(aot.name = 'Business Owner', asu.userid, 'NO')) ExecutiveOwner,
MAX(IF(aot.name = 'Security Owner', asu.userid, 'NO')) SecurityOwner
FROM agl_asset a
INNER JOIN agl_ownership ao ON (ao.owned_object_id=asset_id)
INNER JOIN agl_ownershiptype aot ON (aot.ownershiptype_id=ao.ownershiptype_id)
INNER JOIN agl_systemuser asu ON (asu.systemuser_id=ao.owner_id)
GROUP BY a.asset_id ;
This is correct as of my requirement it is working fine in MySQL.
How can I write same thing in Oracle 11g?
oracle oracle-11g-r2 plsql
oracle oracle-11g-r2 plsql
asked Jan 7 '15 at 4:55
Pydi RajuPydi Raju
1892515
asked Jan 7 '15 at 4:55
Pydi RajuPydi Raju
1892515
edited May 20 '15 at 3:10
Pydi Raju
asked Jan 7 '15 at 4:55
Pydi RajuPydi Raju
1892515
asked Jan 7 '15 at 4:55
Pydi RajuPydi Raju
1892515
asked Jan 7 '15 at 4:55
Pydi RajuPydi Raju
1892515
1892515
bumped to the homepage by Community♦ 16 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 16 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
you can only specify one set of values in oracle insert or use INSERT INTO ... SELECT option. e.g. is below insert into my_table(col1, col2, col3) select 'A' col1,1 col2,4 col3 from dual union all select 'B' col1,2 col2,3 col3 from dual union all select 'C' col1,3 col2,2 col3 from dual union all select 'D' col1,4 col2,1 col3 from dual; This will insert 4 rows into my_table. (sorry couldn't figure out how to format code in comments).
– Raj
Jan 7 '15 at 13:38
1
Which part are you stuck on: CREATE TABLE, INSERT, or SELECT?
– Joshua Huber
Jan 7 '15 at 17:02
add a comment |
you can only specify one set of values in oracle insert or use INSERT INTO ... SELECT option. e.g. is below insert into my_table(col1, col2, col3) select 'A' col1,1 col2,4 col3 from dual union all select 'B' col1,2 col2,3 col3 from dual union all select 'C' col1,3 col2,2 col3 from dual union all select 'D' col1,4 col2,1 col3 from dual; This will insert 4 rows into my_table. (sorry couldn't figure out how to format code in comments).
– Raj
Jan 7 '15 at 13:38
1
Which part are you stuck on: CREATE TABLE, INSERT, or SELECT?
– Joshua Huber
Jan 7 '15 at 17:02
you can only specify one set of values in oracle insert or use INSERT INTO ... SELECT option. e.g. is below insert into my_table(col1, col2, col3) select 'A' col1,1 col2,4 col3 from dual union all select 'B' col1,2 col2,3 col3 from dual union all select 'C' col1,3 col2,2 col3 from dual union all select 'D' col1,4 col2,1 col3 from dual; This will insert 4 rows into my_table. (sorry couldn't figure out how to format code in comments).
– Raj
Jan 7 '15 at 13:38
you can only specify one set of values in oracle insert or use INSERT INTO ... SELECT option. e.g. is below insert into my_table(col1, col2, col3) select 'A' col1,1 col2,4 col3 from dual union all select 'B' col1,2 col2,3 col3 from dual union all select 'C' col1,3 col2,2 col3 from dual union all select 'D' col1,4 col2,1 col3 from dual; This will insert 4 rows into my_table. (sorry couldn't figure out how to format code in comments).
– Raj
Jan 7 '15 at 13:38
1
1
Which part are you stuck on: CREATE TABLE, INSERT, or SELECT?
– Joshua Huber
Jan 7 '15 at 17:02
Which part are you stuck on: CREATE TABLE, INSERT, or SELECT?
– Joshua Huber
Jan 7 '15 at 17:02
add a comment |
1 Answer
1
active
oldest
votes
0
In your SELECT, change each IF to [DECODE][1]. Then you can change the = in DECODE's first expression to a comma, and it will work the same as MySQL's IF.
e.g., MAX(DECODE(aot.name, 'Primary Owner', asu.userid, 'NO'))
I think you'll also need to add a.name to your GROUP BY.
answered Feb 10 '15 at 16:26
Daren BeattieDaren Beattie
341
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f88701%2frows-to-columns%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
In your SELECT, change each IF to [DECODE][1]. Then you can change the = in DECODE's first expression to a comma, and it will work the same as MySQL's IF.
e.g., MAX(DECODE(aot.name, 'Primary Owner', asu.userid, 'NO'))
I think you'll also need to add a.name to your GROUP BY.
answered Feb 10 '15 at 16:26
Daren BeattieDaren Beattie
341
add a comment |
0
In your SELECT, change each IF to [DECODE][1]. Then you can change the = in DECODE's first expression to a comma, and it will work the same as MySQL's IF.
e.g., MAX(DECODE(aot.name, 'Primary Owner', asu.userid, 'NO'))
I think you'll also need to add a.name to your GROUP BY.
answered Feb 10 '15 at 16:26
Daren BeattieDaren Beattie
341
add a comment |
0
0
0
In your SELECT, change each IF to [DECODE][1]. Then you can change the = in DECODE's first expression to a comma, and it will work the same as MySQL's IF.
e.g., MAX(DECODE(aot.name, 'Primary Owner', asu.userid, 'NO'))
I think you'll also need to add a.name to your GROUP BY.
answered Feb 10 '15 at 16:26
Daren BeattieDaren Beattie
341
In your SELECT, change each IF to [DECODE][1]. Then you can change the = in DECODE's first expression to a comma, and it will work the same as MySQL's IF.
e.g., MAX(DECODE(aot.name, 'Primary Owner', asu.userid, 'NO'))
I think you'll also need to add a.name to your GROUP BY.
answered Feb 10 '15 at 16:26
Daren BeattieDaren Beattie
341
answered Feb 10 '15 at 16:26
Daren BeattieDaren Beattie
341
answered Feb 10 '15 at 16:26
Daren BeattieDaren Beattie
341
answered Feb 10 '15 at 16:26
Daren BeattieDaren Beattie
341
341
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f88701%2frows-to-columns%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
you can only specify one set of values in oracle insert or use INSERT INTO ... SELECT option. e.g. is below insert into my_table(col1, col2, col3) select 'A' col1,1 col2,4 col3 from dual union all select 'B' col1,2 col2,3 col3 from dual union all select 'C' col1,3 col2,2 col3 from dual union all select 'D' col1,4 col2,1 col3 from dual; This will insert 4 rows into my_table. (sorry couldn't figure out how to format code in comments).
– Raj
Jan 7 '15 at 13:38
1
Which part are you stuck on: CREATE TABLE, INSERT, or SELECT?
– Joshua Huber
Jan 7 '15 at 17:02