Home of the ColdFusion Community
|
Mailing Lists
|
Home /
Groups /
ColdFusion Talk (CF-Talk)
add a column in oracle
Author: Dave Carabetta
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32342#162474
----- 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.
Author: Jason.Gulledge
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32342#162473
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
_____
Author: Daniel Kessler
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32342#162471
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
Author: Jason.Gulledge
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32342#162458
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
_____
Author: Dave Carabetta
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32342#162444
----- 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.
Author: Daniel Kessler
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32342#162437
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
Author: daniel kessler
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32342#162366
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 -----
Author: Jason.Gulledge
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32342#162363
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.
_____
Author: Dave Carabetta
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32342#162353
>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.
Author: Dave Carabetta
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32342#162349
----- 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.
Author: Howard Fore
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32342#162348
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 -----
Author: Gonzo Rock
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32342#162347
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 -----
Author: Howard Fore
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32342#162346
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 -----
Author: Howard Fore
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32342#162345
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 -----
Author: Daniel Kessler
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32342#162338
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
|
May 24, 2012
|
Latest Fusion Authority Articles
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||
