|
Mailing Lists
|
Home /
Groups /
ColdFusion Talk (CF-Talk)
add a column in oracle
I'm trying to add a column in my DB in Oracle. I'm doing:Daniel Kessler 05/05/04 02:18 P Thanks everyone who replied. I have it working now.daniel kessler 05/05/04 05:02 P I don't have my big red book handy, but isn't there a restriction inHoward Fore 05/05/04 02:58 P As for your other question:Howard Fore 05/05/04 03:02 P Sorry, wrong database...Howard Fore 05/05/04 03:07 P Try putting quotes around the table and column names....Gonzo Rock 05/05/04 03:03 P >I'm trying to add a column in my DB in Oracle. I'm doing:Dave Carabetta 05/05/04 03:09 P >Sorry, wrong database...Dave Carabetta 05/05/04 03:30 P Here's a bit of a detailed explanation of Dave means. Something to note about using "where rownum < 4" in Oracle is that it doesn't pull back the "TOP" type records. It'll simply pull back the first 3 rows that exist first in the database, and not any specialized order you want.Jason.Gulledge 05/05/04 04:46 P heh actually it confused me a touch. I believe that I understandDaniel Kessler 05/06/04 01:45 P >heh actually it confused me a touch. I believe that I understandDave Carabetta 05/06/04 02:10 P Close, and you shouldn't use "select * from.."Jason.Gulledge 05/06/04 02:58 P Ah great thanks. I learned alot.Daniel Kessler 05/06/04 03:53 P I think you may have it reversed. Work from the inside out.Jason.Gulledge 05/06/04 04:01 P >Ah great thanks. I learned alot.Dave Carabetta 05/06/04 04:02 P I'm trying to add a column in my DB in Oracle. I'm doing: ALTER TABLE whatsnew ADD (image varchar2(100)); I also tried: ALTER TABLE whatsnew ADD column image varchar2(100); but I get the error about line 4 of the cfm. This is a reference to the text field from the form that contains the above line (alter table...): The error occurred in hhp/htdocs/HHPv1/WNFunctions/DatabaseFunctions/addsql.cfm: line 4 2 : <BODY> 3 : <CFQUERY NAME="SQLEXE" DATASOURCE="dpch"> 4 : #sql_statement# 5 : </CFQUERY> 6 : <B>SQL Statement Executed</B Also, can anyone tell me how to query for just 3 records in Oracle? I know how to do it in mySQL, but... thanks. -- Daniel Kessler Department of Public and Community Health University of Maryland Suite 2387 Valley Drive College Park, MD 20742-2611 301-405-2545 Phone www.phi.umd.edu Thanks everyone who replied. I have it working now. As for adding the column. It was the quotes " that helped. yay! ----- Excess quoted text cut - see Original Post for more ----- I don't have my big red book handy, but isn't there a restriction in Oracle where you can add columns but not drop them (or vice versa)? -- Howard Fore, mylists@hofo.com On May 5, 2004, at 2:16 PM, Daniel Kessler wrote: ----- Excess quoted text cut - see Original Post for more ----- As for your other question: SELECT TOP 3 FROM FOO -- Howard Fore, mylists@hofo.com On May 5, 2004, at 2:16 PM, Daniel Kessler wrote: ----- Excess quoted text cut - see Original Post for more ----- Sorry, wrong database... SELECT * FROM foo WHERE rownum < 4 -- Howard Fore, mylists@hofo.com On May 5, 2004, at 3:00 PM, Howard Fore wrote: ----- Excess quoted text cut - see Original Post for more ----- Try putting quotes around the table and column names.... At 11:16 AM 5/5/04, you wrote: ----- Excess quoted text cut - see Original Post for more ----- ----- Excess quoted text cut - see Original Post for more ----- 1) Make sure the "dpch" datasource has ALTER permissions in the MX Administrator (which it does by default, but it may have been changed). 2) Remove the semi-colon after the statment. MX (and I'm assuming JDBC) doesn't like semi-colons in SQL statements in MX. >Also, can anyone tell me how to query for just 3 records in Oracle? >I know how to do it in mySQL, but... > SELECT myColumn FROM ( SELECT myColumn FROM myTable WHERE myCondition = 'foo' ORDER BY myColumn ) WHERE rownum <= 3 Regards, Dave. >Sorry, wrong database... > >SELECT * FROM foo WHERE rownum < 4 > Note that this will not work if you have any sort of ORDER BY clause because Oracle uses the rownum clause before the ORDER BY. This is why I posted the other way of doing SQL Server's version of TOP. Regards, Dave. Here's a bit of a detailed explanation of Dave means. Something to note about using "where rownum < 4" in Oracle is that it doesn't pull back the "TOP" type records. It'll simply pull back the first 3 rows that exist first in the database, and not any specialized order you want. However, there is a fairly simple workaround. Using a subquery will give you the "TOP N" effect that MySQL and others. It works like this: Say you wanted to pull back 10 of the highest paid persons at your company. Here's the syntax. --This pulls back the highest ones first, in a "top" type order. select name, salary from ( ---this does your ordering for you select name, salary from employee_data order by salary desc ) where rownum < 11 Hope that helps clear it up, Jason Gulledge ALLTEL >Sorry, wrong database... > >SELECT * FROM foo WHERE rownum < 4 > Note that this will not work if you have any sort of ORDER BY clause because Oracle uses the rownum clause before the ORDER BY. This is why I posted the other way of doing SQL Server's version of TOP. Regards, Dave. _____ heh actually it confused me a touch. I believe that I understand what you're saying though I don't know what TOP type records are. Maybe I should specify my situation. I'm doing a "WHAT'S NEW" section of a web page. I want the 3 most recently entered items. I've adjusted my current query to my understanding. Is this right? <CFQUERY NAME="whatsnew" DATASOURCE="dpch"> ( select * FROM whats_new WHERE dateAdded > #dateAdd("d",-7,Now())# order by dateAdded DESC ) WHERE ROWNUM < 4 </CFQUERY> I originally had the ROWNUM < 4 along with the other WHERE with an AND. ----- Excess quoted text cut - see Original Post for more ----- -- Daniel Kessler Department of Public and Community Health University of Maryland Suite 2387 Valley Drive College Park, MD 20742-2611 301-405-2545 Phone www.phi.umd.edu ----- Excess quoted text cut - see Original Post for more ----- Not quite. Further, I loathe the use of * in queries. It should be something like: SELECT mycolumn1, mycolumn2 FROM ( SELECT mycolumn1, mycolumn2 FROM whats_new WHERE dateAdded > sysdate-7 ORDER BY dateAdded DESC ) WHERE rownum <= 3 I did a few things: 1) Put the correct syntax in to get the 3 most recent items 2) Removed the CF function you're using to get 7 days ago and used Oracle's built-in sysdate function instead 3) changed rownum < 4 to rownum <= 3. This one's just personal preference, but I don't have to "think" this way about how many records are coming back. I see "3" and I just know. 4) Intentionally removed the *. I hate using * even if I'm using all my columns because it's more of an effort for a subsequent developer to come in and read my code and know exactly what's coming back. Further, * is technically a bit slower because Oracle then has to use its system lookup tables to get the column names in the whats_new table. Regards, Dave. Close, and you shouldn't use "select * from.." <CFQUERY NAME="whatsnew" DATASOURCE="dpch"> --change this from select * to whatever else select * from ( select * FROM whats_new WHERE dateAdded > #dateAdd("d",-7,Now())# order by dateAdded DESC ) WHERE ROWNUM < 4 </CFQUERY> heh actually it confused me a touch. I believe that I understand what you're saying though I don't know what TOP type records are. Maybe I should specify my situation. I'm doing a "WHAT'S NEW" section of a web page. I want the 3 most recently entered items. I've adjusted my current query to my understanding. Is this right? <CFQUERY NAME="whatsnew" DATASOURCE="dpch"> ( select * FROM whats_new WHERE dateAdded > #dateAdd("d",-7,Now())# order by dateAdded DESC ) WHERE ROWNUM < 4 </CFQUERY> I originally had the ROWNUM < 4 along with the other WHERE with an AND. ----- Excess quoted text cut - see Original Post for more ----- -- Daniel Kessler Department of Public and Community Health University of Maryland Suite 2387 Valley Drive College Park, MD 20742-2611 301-405-2545 Phone www.phi.umd.edu _____ Ah great thanks. I learned alot. So, let me re-iterate a bit. - The first select is the general search where I'm selecting 3 from the total and I list the columns that I want. I want all of them, but it's still better to directly list them both for readability and accuracy. - The second select is pulled out of the first select's returns and it returns them ordered and such. Or do I have this reversed mentally? - I like the <= 3 for too. - Nice to know about the sysdate. I remembered that there was something like it the last time I used Oracle but had forgotten. Nice a clean to read. I learned alot, thanks. ----- Excess quoted text cut - see Original Post for more ----- -- Daniel Kessler Department of Public and Community Health University of Maryland Suite 2387 Valley Drive College Park, MD 20742-2611 301-405-2545 Phone www.phi.umd.edu I think you may have it reversed. Work from the inside out. The "sub select" (or inner select) pulls back *every* row where dateadded > sysdate-7 (by the way shouldn't you use "dateadded >=sysdate-7"?) Then the outer select simply returns the first three rows from that. Jason Gulledge. Ah great thanks. I learned alot. So, let me re-iterate a bit. - The first select is the general search where I'm selecting 3 from the total and I list the columns that I want. I want all of them, but it's still better to directly list them both for readability and accuracy. - The second select is pulled out of the first select's returns and it returns them ordered and such. Or do I have this reversed mentally? - I like the <= 3 for too. - Nice to know about the sysdate. I remembered that there was something like it the last time I used Oracle but had forgotten. Nice a clean to read. I learned alot, thanks. ----- Excess quoted text cut - see Original Post for more ----- -- Daniel Kessler Department of Public and Community Health University of Maryland Suite 2387 Valley Drive College Park, MD 20742-2611 301-405-2545 Phone www.phi.umd.edu _____ ----- Excess quoted text cut - see Original Post for more ----- This is the only one you got mixed up. The inner SELECT (the one in the FROM clause) actually runs first, and the the outer SELECT simply takes the top 3 records returned from the result set. >- I like the <= 3 for too. >- Nice to know about the sysdate. I remembered that there was >something like it the last time I used Oracle but had forgotten. >Nice a clean to read. > > >I learned alot, thanks. Very welcome. Regards, Dave.
|
September 09, 2010
|
Latest Fusion Authority Articles
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||
