Link to home
Start Free TrialLog in
Avatar of brgdotnet
brgdotnetFlag for United States of America

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','CASA0000001',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','CASA0000001',1 FROM DUAL,NULL,NULL;
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of johnsone
johnsone
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of brgdotnet

ASKER

Thank you.