diecasthft01
asked on
Adding data to new table with CF and Oracle
Good day everyone....I have a problem with an Oracle (SQL) query and Coldfusion. I'm basically trying to query an Oracle database from multiple tables and build a new table. With that new table I want a sequential number in the ID field, starting at 1 and I want to start off slow to verify my data as I add more fields. So I have a simple piece of code below that does a count on my main table, and then I can add the data (count) to my ew table:
<cfquery name="getArt" datasource="PDBDEV">
SELECT COUNT(*) AS numItems
FROM BUDGET_MIPRS_SENT
</cfquery>
<cfloop from="1" to="#getArt.numItems#" index="i">
<cfquery name="qrySubmitForm" datasource="PDBDEV">
INSERT INTO WATERFALL_DATA (WATERFALL_DATA.ID) VALUES (#i#)
</cfquery>
But now, I want to add a field from the same table...and that fails so I believe I'm missing something. If I add the PROJ_NO field from the BUDGET_MIPRS_SENT table, it makes me add a grouping. And then if I add a group to the query, all I get is one record when I should get about 2000.
<cfquery name="getArt" datasource="PDBDEV">
SELECT COUNT(*) AS numItems,
PROJ_NO
FROM BUDGET_MIPRS_SENT
GROUP BY PROJ_NO
</cfquery>
So I'm guessing its not as simple as adding data fields?? Any guidance would be greatly appreciated.
<cfquery name="getArt" datasource="PDBDEV">
SELECT COUNT(*) AS numItems
FROM BUDGET_MIPRS_SENT
</cfquery>
<cfloop from="1" to="#getArt.numItems#" index="i">
<cfquery name="qrySubmitForm" datasource="PDBDEV">
INSERT INTO WATERFALL_DATA (WATERFALL_DATA.ID) VALUES (#i#)
</cfquery>
But now, I want to add a field from the same table...and that fails so I believe I'm missing something. If I add the PROJ_NO field from the BUDGET_MIPRS_SENT table, it makes me add a grouping. And then if I add a group to the query, all I get is one record when I should get about 2000.
<cfquery name="getArt" datasource="PDBDEV">
SELECT COUNT(*) AS numItems,
PROJ_NO
FROM BUDGET_MIPRS_SENT
GROUP BY PROJ_NO
</cfquery>
So I'm guessing its not as simple as adding data fields?? Any guidance would be greatly appreciated.
A COUNT(*) will only return one row.
If you want a count for each project, add that to your select:
SELECT PROJ_NO, COUNT(*) AS numItems,
PROJ_NO
FROM BUDGET_MIPRS_SENT
GROUP BY PROJ_NO
I'm not sure what you are trying to do but when I see things like "build a new table", I think there are better ways.
Can you not write a single query to return the values you want and not need another table?
If you want a count for each project, add that to your select:
SELECT PROJ_NO, COUNT(*) AS numItems,
PROJ_NO
FROM BUDGET_MIPRS_SENT
GROUP BY PROJ_NO
I'm not sure what you are trying to do but when I see things like "build a new table", I think there are better ways.
Can you not write a single query to return the values you want and not need another table?
What version of Oracle are run running? Version 12 supports IDENTITY columns. On earlier versions you'll probably want to do the sequence/trigger approach that most everybody's done.
I might be missing something, but why does it need to be multiple steps?
INSERT INTO waterfall_data
(id,
proj_no,
proj_count)
SELECT ROWNUM,
proj_no,
Count(*)
FROM budget_miprs_sent
GROUP BY proj_no ASKER
So the idea behind a new table with data from other tables is that this data will be used to support a new task, and some of the data will need to be manipulated. I don't want the users to manipulate the real data, just this new tables data to create their "what if" scenarios.
If I have an existing table, and let's say it has 10 project numbers in it, I want the new table to list those 10 project numbers and sequentially add a number (1 through 10) in the new tables ID field. Seems like the sequence/trigger option is the only way to do this. I'm trying that out now. I am using 12c oracle.
If I have an existing table, and let's say it has 10 project numbers in it, I want the new table to list those 10 project numbers and sequentially add a number (1 through 10) in the new tables ID field. Seems like the sequence/trigger option is the only way to do this. I'm trying that out now. I am using 12c oracle.
>>Seems like the sequence/trigger option is the only way to do this. I'm trying that out now. I am using 12c oracle.
Nope. You can generate the number on insert as part of the select. The rownum query by johnsone should do it. If not, ROW_NUMBER or RANK window functions will.
If you want copy/paste code, please provide some sample input data and expected results.
Nope. You can generate the number on insert as part of the select. The rownum query by johnsone should do it. If not, ROW_NUMBER or RANK window functions will.
If you want copy/paste code, please provide some sample input data and expected results.
ASKER
When trying the options listed above, I either got errors about GROUP BY functions, or it wouldn't list the ID numbers sequentially. I really thought
INSERT INTO waterfall_data
(id,
proj_no,
proj_count)
SELECT ROWNUM,
proj_no,
Count(*)
FROM budget_miprs_sent
GROUP BY proj_no
Should work......but I get the "not a group by" error. I also tried the
SELECT PROJ_NO, COUNT(*) AS numItems,
PROJ_NO
FROM BUDGET_MIPRS_SENT
GROUP BY PROJ_NO
and that gave me the project numbers, but it listed every ID as either a 1 or a 2, so I had duplicates of the ID number. All I really want is:
ID 1 Proj no 43665
ID 2 Proj no 45677
ID 3 Proj no 23997
and so on........
INSERT INTO waterfall_data
(id,
proj_no,
proj_count)
SELECT ROWNUM,
proj_no,
Count(*)
FROM budget_miprs_sent
GROUP BY proj_no
Should work......but I get the "not a group by" error. I also tried the
SELECT PROJ_NO, COUNT(*) AS numItems,
PROJ_NO
FROM BUDGET_MIPRS_SENT
GROUP BY PROJ_NO
and that gave me the project numbers, but it listed every ID as either a 1 or a 2, so I had duplicates of the ID number. All I really want is:
ID 1 Proj no 43665
ID 2 Proj no 45677
ID 3 Proj no 23997
and so on........
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That seems like it works!!!! I have one question. I'm not sure I understand the idea of the field proj_count. The insert places a 1 in each field. No complaints though....it works as I needed it to. Thanks a lot!!!
Glad you got what you needed.
>>not sure I understand the idea of the field proj_count
I don't understand the actual requirements so I cannot explain the need for proj_count. Just going by a query you posted using ROWNUM and changed it to ROW_NUMBER.
>>not sure I understand the idea of the field proj_count
I don't understand the actual requirements so I cannot explain the need for proj_count. Just going by a query you posted using ROWNUM and changed it to ROW_NUMBER.
And yes, when you add a "group by" clause to an Oracle query, it will return only one row for each distinct value that you "group by" (or distinct combination of values, if you "group by" multiple columns).