brgdotnet
asked on
1 FROM DUAL wont work with additional columns ??
In TFS there is a sql script which is used to populate an ORACLE table after it has been created. The script contains about 200 insert
statements. As example, below is listed 3 of those sql statements. I was recently required to add two additional columns to the
PIP_INFO_GROUP table. So now, I also need to modify each insert statement so that a NULL value can be inserted into the two new columns.
However, I can't find a way to do that though, because at the end of each insert statement is the clause "1 FROM DUAL".
So how can I modify the set of sql statements to insert null values into the two new columns? See my second group of example sql commands
where I attempt to insert a NULL. Like I said if I run the second group of commands, I get an error message
-- Example 1, first group
INSERT INTO PIP_INFO_GROUP SELECT 'LAS VEGAS','NV','LVSA0000001', 1 FROM DUAL;
INSERT INTO PIP_INFO_GROUP SELECT 'DALLAS','TX','TXSA0000001 ',1 FROM DUAL;
INSERT INTO PIP_INFO_GROUP SELECT 'SAN FRANCISCO','CA','CASA00000 01',1 FROM DUAL;
-- Example 2, second group which inserts nulls into the last two additional columns.
-- I get an error message if I try to run the below code.
INSERT INTO PIP_INFO_GROUP SELECT 'LAS VEGAS','NV','LVSA0000001', 1 FROM DUAL,NULL,NULL;
INSERT INTO PIP_INFO_GROUP SELECT 'DALLAS','TX','TXSA0000001 ',1 FROM DUAL,NULL,NULL;
INSERT INTO PIP_INFO_GROUP SELECT 'SAN FRANCISCO','CA','CASA00000 01',1 FROM DUAL,NULL,NULL;
statements. As example, below is listed 3 of those sql statements. I was recently required to add two additional columns to the
PIP_INFO_GROUP table. So now, I also need to modify each insert statement so that a NULL value can be inserted into the two new columns.
However, I can't find a way to do that though, because at the end of each insert statement is the clause "1 FROM DUAL".
So how can I modify the set of sql statements to insert null values into the two new columns? See my second group of example sql commands
where I attempt to insert a NULL. Like I said if I run the second group of commands, I get an error message
-- Example 1, first group
INSERT INTO PIP_INFO_GROUP SELECT 'LAS VEGAS','NV','LVSA0000001',
INSERT INTO PIP_INFO_GROUP SELECT 'DALLAS','TX','TXSA0000001
INSERT INTO PIP_INFO_GROUP SELECT 'SAN FRANCISCO','CA','CASA00000
-- Example 2, second group which inserts nulls into the last two additional columns.
-- I get an error message if I try to run the below code.
INSERT INTO PIP_INFO_GROUP SELECT 'LAS VEGAS','NV','LVSA0000001',
INSERT INTO PIP_INFO_GROUP SELECT 'DALLAS','TX','TXSA0000001
INSERT INTO PIP_INFO_GROUP SELECT 'SAN FRANCISCO','CA','CASA00000
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER