Forgive me in advance for the heresy, but try MS Access's query builder.
It is pretty easy to use and gives you three choices for viewing the
results...one of them is sql. That view gives you the sql you can cut and
paste anywhere.
Mark
-Original Message-
From: Ali Majdzadeh
-- find sql env
select @@version;
--Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
-- dll
create table #tmp (sYear int, sNum int);
-- dml test data population
insert into #tmp
values(1950,12);
insert into #tmp
values(1950,9);
insert into #tmp
values(1955,11);
-- dml solution
-- looks like
you could add 2 columns in your sql: 1 with left(serial_number_column,
4) [if the year part of sn is always 4 digits], the other with the rest
of the serial number, and sort the results first by one and hen y the
other. exact sql syntax would be db-specific
--
---
Azadi Saryev
ORDER BY LEFT(FieldName, 4), Right(FieldName, (LENGTH(FieldName)-4) )
That should do it, assuming the date part is always the same length.
=]
--
Alan Rother
Adobe Certified Advanced ColdFusion MX 7 Developer
Manager, Phoenix Cold Fusion User Group, AZCFUG.org
@Azadi Saryev d l
I need to use the data that is already there and it's not split up.
@Alan Rother
I had already tried that but it gives same results as reg order by asc
~|
Check out the new features and enhancements in the
nope urgg lol
How about this?
SORT BY val(left(serial, 4)), val(substring(serial, 3))
~|
Get involved in the latest ColdFusion discussions, product
development sharing, and articles on the Adobe Labs wiki.
How about this?
SORT BY val(left(serial, 4)), val(substring(serial, 3))
--
___
REUSE CODE! Use custom tags;
See http://www.contentbox.com/claude/customtags/tagstore.cfm
(Please send any spam to this address: [EMAIL PROTECTED])
Thanks.
If ordering by the Left() and Right() don't work, can you Select
Left(SerialNum,4) as sYear, Right(SerialNum,Length(SerialNum)-4) as sNum and
then order by sYear, sNum?
Been a while since I've worked in mysql, but several of the suggestions here
look like they should be working.
Thanks to all who tried, i got it working with the following:
order by left(serial, 4) asc, abs(mid(serial, 5,3)) asc
when in doubt visit the office lol
~|
Download the latest ColdFusion 8 utilities including Report Builder,
Dave,
Try Alan's solution again, but cast as integers.
something like:
order by cast(left(FieldName, 4) as int), cast(Right(FieldName,
(LENGTH(FieldName)-4) ) as int)
I haven't tested the above so you may need to adjust the syntax.
Antony
On Nov 29, 2007 7:59 AM, Dave l [EMAIL PROTECTED]
thanks but I already had gotten it and posted it as solved, i ended up with
order by left(serial, 4) asc, abs(mid(serial, 5,3)) asc
If ordering by the Left() and Right() don't work, can you Select
Left(SerialNum,4) as sYear, Right(SerialNum,Length(SerialNum)-4) as sNum and
then order by
What are the benefits on using a CHAR field versus VARCHAR?
Well, using char can be a bit speedier when querying, but takes up more disk
space. You want to use char when all the data in that field will be close to
the same length. the db will pad it, so it ends up taking up more space than if
[mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 27, 2007 8:53 PM
To: CF-Talk
Subject: Re: SQL Data Type
What are the benefits on using a CHAR field versus VARCHAR?
Well, using char can be a bit speedier when querying, but takes up more disk
space. You want to use char when all the data
no dice
cause it was 2am and didnt want to think about it no more
Dunno if this is right, just woke up, but I'll take a stab at it.
Looks like you need another join to the upsell table, then order by
its sort by column first.
SELECT brands.brand_id, brands.brand_name,
any good suggestions?
Dunno if this is right, just woke up, but I'll take a stab at it. Looks like
you need another join to the upsell table, then order by its sort by column
first.
SELECT brands.brand_id, brands.brand_name, brands.brand_logo,
products.product_id, products.brand_id,
Hopefully this is not off topic. We have a lot of CF sites
running our software that use SQL 2000. We are going to be
moving to SQL 2005 and are doing research on the licensing
options. It seems that there is a Standard license and a
Workgroup license of SQL 2005. For the purposes of
Steve Kahn wrote:
Anyone have a work around for this problem. Is there a way to write a query
different versus doing something on the cf admin side?
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]The incoming tabular data
stream (TDS) remote procedure call
Currently we utilize MS SQL 2000 with multiple databases. One
of our CF servers (one of the 6.1 boxes) recycled it's cached
ds connections and was no longer able to authenticate to the
db's. We were also no longer able to connect via Enterprise
Manager, receiving the (essentially) same
Dave,
Thanks for responding on this. After almost six hours on the phone with
MS, it looks like the best guess option is to restart services. Since
the release of MS SQL 2000 their support has received one call for this
issue, so they aren't really sure. We'll find out in another twenty min
It is early and my brain my not be functioning 100% but may need to add a
GROUP BY for the HAVING to function on some DBs.
On 10/15/07, James Smith [EMAIL PROTECTED] wrote:
With some DB's moving your criteria from the WHERE clause to the HAVING
clause will take care of it since HAVING is
With some DB's moving your criteria from the WHERE clause to the HAVING
clause will take care of it since HAVING is evaluated after the results have
been calculated... sometimes...
--
Jay
-Original Message-
From: [EMAIL PROTECTED] [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 13
Right now I am getting an error that PubYear is not a valid column
Then I'll try to make sure that PubYear is a valid column in table
crAdInfo ;-)
--
___
REUSE CODE! Use custom tags;
See http://www.contentbox.com/claude/customtags/tagstore.cfm
(Please send
Try this
SELECT AdINFO_Publication,
ADINFO_PUBLICATIONDATE_YEAR+ '-' +
ADINFO_PUBLICATIONDATE_MONTH + '-01' AS PubYear
FROMcrAdInfo
AND ADINFO_PUBLICATIONDATE_YEAR+ '-' +
ADINFO_PUBLICATIONDATE_MONTH + '-01' BETWEEN
On Wednesday 10 Oct 2007, [EMAIL PROTECTED] wrote:
i want to create a SQL query with a LIMIT option but it will produce an
error.
What error ?
On what database ?
--
Tom Chiverton
Helping to proactively iterate scalable markets
on: http://thefalken.livejournal.com
Stivn .. wrote:
i want to create a SQL query with a LIMIT option but it will produce an error.
What is the correct syntax for a SQL query with LIMIT option
That depends on your database. With MS databases you need to use TOP,
with Oracle rownum, with MySQL and PostgreSQL LIMIT. The syntax
.
..:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.
Bobby Hartsfield
http://acoderslife.com
-Original Message-
From: Dennis Powers [mailto:[EMAIL PROTECTED]
Sent: Monday, October 08, 2007 6:30 PM
To: CF-Talk
Subject: RE: SQL Select Question
What RDBMS? That would help mucho...
Doh! MS SQL2000
Best Regards,
Dennis Powers
UXB Internet
I apologize. I did not explain myself properly which I suspect is why I
can't figure this out.
Select top 6 imageID
From table
Where gameID = whatever
Order by newid()
This query will select 6 random images for a single GameID however, what I
am trying to do is select 1 random image for 6
What RDBMS? That would help mucho...
On 10/8/07, Dennis Powers [EMAIL PROTECTED] wrote:
I am having a total mental block with this select query and I am hoping
someone might be able to unstick my thought process;
I have a table Looking something like this (abbreviated for the list)
Morphis [mailto:[EMAIL PROTECTED]
Sent: Monday, October 08, 2007 6:14 PM
To: CF-Talk
Subject: Re: SQL Select Question
What RDBMS? That would help mucho...
On 10/8/07, Dennis Powers [EMAIL PROTECTED] wrote:
I am having a total mental block with this select query and I am hoping
someone might
Hi Dennis,
This should be very easy:
SELECT DISTING imageid, gameid
FROMtblX
ORDER BY imageid+Rand()
LIMIT 6
By the way, the LIMIT 6 is MySQL. You'd need to use TOP 6 in SQL Server.
Hope this helps.
CHeers
Matthew
I am having a total mental block with this select query and I am hoping
the product_name, and if I group by the product_name as well, it
itemizes all the products.
~Brad
-Original Message-
From: Dawson, Michael [mailto:[EMAIL PROTECTED]
Sent: Friday, September 28, 2007 11:54 AM
To: CF-Talk
Subject: RE: SQL Question
Have you tried using a derived table? I think
You might want to post this at [EMAIL PROTECTED] There are some
sharp SQL experts there.
Bruce
Brad Wood wrote:
Yes, I did several attempts at a derived table, but I still ran into the
same problem... I couldn't do a top 1 with order by datetime_created
desc because I am reporting across
Have you tried using a derived table? I think that should work.
I think it should work too, assuming there would _not_ be duplicate
datetime_created values per order_num. If there were, the query could return
multiple rows per order_num.
Janet
, 2007 11:58 AM
To: CF-Talk
Subject: RE: SQL Question
Just move the aggregate up to the from and do a join:
select *
from t myT, (select order_num, max(datetime_created) as max_dt_created
from t group by order_num)
where myT.order_num = .order_num
AND myT.datetime_created
have to determine the max
datetime_create, so you're going to have to use a subselect I think.
-- Andrew
-Original Message-
From: Brad Wood [mailto:[EMAIL PROTECTED]
Sent: Friday, September 28, 2007 12:46 PM
To: CF-Talk
Subject: RE: SQL Question
Doesn't look like MS SQL Server 2005
-
From: Brad Wood [mailto:[EMAIL PROTECTED]
Sent: Friday, September 28, 2007 11:46 AM
To: CF-Talk
Subject: RE: SQL Question
Doesn't look like MS SQL Server 2005 will let me compare more than one
column in a where clause.
Even if it did, this requires two selects. My understanding is the
sub
This solution is similar to Greg's in that I will get dupes if more than
one product is added at the same time.
Sql 2005 may have a better method for doing this, but you could use 2 derived
tables. One to grab the max date by order number, and the other to grab the max
record id per order
-
From: Andrew Clark [mailto:[EMAIL PROTECTED]
Sent: Friday, September 28, 2007 11:33 AM
To: CF-Talk
Subject: RE: SQL Question
Off the top of my head I get:
select *
from t myT
where (order_num, datetime_created) =
(select order_num, max(datetime_created) from t where order_num
This should be pretty simple actually =)
SELECT DISTINCT
Order_num,
datetime_created,
(SELECT product_name FROM tableName WHERE order_num = a.ordernum
AND record_ID = (SELECT MAX(record_ID) FROM tableName WHERE order_num =
a.order_num)) as LastProduct
FROM
This should be pretty simple actually =)
SELECT DISTINCT
Order_num,
datetime_created,
(SELECT product_name FROM tableName WHERE order_num = a.ordernum
AND record_ID = (SELECT MAX(record_ID) FROM tableName WHERE order_num =
a.order_num)) as LastProduct
FROM
tableName A
Didn't they mention the table has something like 11 million rows.
Oops. That should have been
... would be better than a subquery
Janet
~|
ColdFusion 8 - Build next generation apps
today, with easy PDF and Ajax features
would work.
~Brad
-Original Message-
From: Brad Wood [mailto:[EMAIL PROTECTED]
Sent: Friday, September 28, 2007 11:46 AM
To: CF-Talk
Subject: RE: SQL Question
Doesn't look like MS SQL Server 2005 will let me compare more than one
column in a where clause
Off the top of my head I get:
select *
from t myT
where (order_num, datetime_created) =
(select order_num, max(datetime_created) from t where order_num
= myT.order_num group by order_num)
Note:
I tested this in postgres, not sql server...
-- Andrew
-Original Message-
From:
well i am trying to make a regular connection in cf admin to sql server 2000.
i can do it with.
Driver: ODBC Socket
but not with
Driver: Microsoft SQL Server
Connection verification failed for data source: master
An exception occurred when executing method verifydatasource.
The cause
To: CF-Talk
Subject: Re: sql sever 2000 XP cf8 question
well i am trying to make a regular connection in cf admin to sql server
2000.
i can do it with.
Driver: ODBC Socket
but not with
Driver: Microsoft SQL Server
Connection verification failed for data source: master An exception
i have tried the example here
http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=datasources_ADV_MJS_11.html
with no luck
~|
Enterprise web applications, build robust, secure
scalable apps today - Try it now
well i am trying to make a regular connection in cf admin to
sql server 2000.
i can do it with.
Driver: ODBC Socket
but not with
Driver: Microsoft SQL Server
Connection verification failed for data source: master An
exception occurred when executing method
finnaly
i just needed sp4 for mssql 2000
~|
Create robust enterprise, web RIAs.
Upgrade to ColdFusion 8 and integrate with Adobe Flex
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP
Archive:
Paul Ihrig wrote:
but it seems a little hoekey..
that's *more* than a little hokey.
any one else not want to make a odbc socket connection?
the ODBC bridge thing should be slower will certainly mess up any unicode in
your db.
got the right sql server user for your dsn? might try the SA
Thanks
WHERE PERMISSIONS LIKE '%#session.status#%'
Seems to have done the trick!
~|
Get the answers you are looking for on the ColdFusion Labs
Forum direct from active programmers and developers.
[mailto:[EMAIL PROTECTED]
Sent: Wednesday, September 05, 2007 2:53 AM
To: CF-Talk
Subject: Re: SQL select where in headache
Thanks
WHERE PERMISSIONS LIKE '%#session.status#%'
Seems to have done the trick!
~|
Create
if session.status is a numeric list and PERMISSIONS is a numeric value, remove
the single quotes:
WHERE PERMISSIONS IN (#session.status#)
From: Hugh Fidgen [mailto:[EMAIL PROTECTED]
Sent: Tue 9/4/2007 3:24 PM
To: CF-Talk
Subject: SQL select where in headache
cfquery name=eventlist1
SELECT * FROM EVENTS
WHERE PERMISSIONS IN cfqueryparam value=#session.status# list=true
ORDER BY EVENT_DATE, EVENT_START ASC
/cfquery
Try that...
On 9/4/07, Hugh Fidgen [EMAIL PROTECTED] wrote:
Hiya,
I've got a problem selecting data from a table and I was
I think it is because of the single quotes around your comma delimited
numbers.
When I try to run a similar query, I get a data conversion error. If the
column in the db is an INT, you do not need the single ticks around the
var. That is only for varchar. Also, if it is varchar, each item
Also, don't forget- cfqueryparam is your friend
cfqueryparam value=#session.status# separator=, list=Yes
-Original Message-
From: Adkins, Randy [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 04, 2007 11:39 AM
To: CF-Talk
Subject: RE: SQL select where in headache
, 2007 1:46 PM
To: CF-Talk
Subject: Re: SQL select where in headache
I think it is because of the single quotes around your comma delimited
numbers.
When I try to run a similar query, I get a data conversion error. If the
column in the db is an INT, you do not need the single ticks around the
var
the PERMISSIONS column in the db is also sometimes a list.
Best advice I have is to normalize the db first.
-Original Message-
From: Doug R [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 04, 2007 1:46 PM
To: CF-Talk
Subject: Re: SQL select where in headache
I think it is because
around it ie..('x','y','z') but numeric types do
not.
Eric
Original Message
Subject: RE: SQL select where in headache
From: Dave Francis [EMAIL PROTECTED]
Date: Tue, September 04, 2007 2:22 pm
To: CF-Talk cf-talk@houseoffusion.com
I don't know the answer (my SQL is very, very
Hiya,
Sorry i think i've caused some confusion here.
#session.status# is always a single character, EG P
PERMISSIONS is sometimes a single character, or sometimes a comma delimited
list. It's populated via a select multiple input in a form, so it depends if
the user selects one, or multiple
Hiya, Sorry i think i've caused some confusion here.
#session.status# is always a single character, EG P
PERMISSIONS is sometimes a single character, or sometimes a comma delimited
list. It's populated via a select multiple input in a form, so it depends if
the user selects one, or multiple
Subject: Re: SQL select where in headache
Hiya,
Sorry i think i've caused some confusion here.
#session.status# is always a single character, EG P
PERMISSIONS is sometimes a single character, or sometimes a comma
delimited list. It's populated via a select multiple input in a form, so
it depends
ok... I'm new to CF and I can't exactly remember how CFLOOP works but here
goes.
cfset status_count = 1
cfquery name=eventlist1
SELECT * FROM EVENTS
WHERE PERMISSIONS IN
(
CFLOOP
CFIF status_count EQ 1
#session.status#
cfset status_count = 2
CFELSE
How about
INSERT INTO new_table SELECT * FROM old_table
On 8/31/07, daniel kessler [EMAIL PROTECTED] wrote:
I have a table and I'd like to duplicate all of it's data to another table.
I've set the second table to have the same structure. How do I go about
transferring that data through
How about
INSERT INTO new_table SELECT * FROM old_table
Yep, that was it - thank you.
I had done:
insert into timesheets_tmp
(timesheet_id,id,date_added,entry_date,type,hours,minutes,notes,signoff)
(
select
I meant to mention that it's Oracle.
~|
ColdFusion 8 - Build next generation apps
today, with easy PDF and Ajax features - download now
http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf
Archive:
INSERT INTO tbl2 (col1, col2, etc)
SELECT col1, col2, etc
FROM tbl1
Should copy everything from tbl1 to tbl2. This presumes that tbl2 is empty.
Cheers,
Judah
daniel kessler wrote:
I have a table and I'd like to duplicate all of it's data to another table.
I've set the second table to have
Nice job Brad. All it needs now is a sound track and a way to claim my free
iPod.
What application have you found for it?
-Original Message-
From: Brad Wood [mailto:[EMAIL PROTECTED]
Sent: Monday, August 20, 2007 5:15 PM
To: CF-Talk
Subject: SQL Color Coding
Ok, so a couple months
: Wednesday, August 22, 2007 9:12 AM
To: CF-Talk
Subject: RE: SQL Color Coding
Nice job Brad. All it needs now is a sound track and a way to claim my
free
iPod.
What application have you found for it?
~|
Check out the new features
Billy Cox wrote:
If I understand the question properly, here is today's date in literal
format:
'2007-08-10'
That is what I expected, but this failed.
SELECT aField
FROM aTable
WHERE aDateTimeField = '2005-07-01'
Apparently Oracle needs a DATE command in there. This is what worked
SELECT
I think this is the fastest way you can do it in mssql
SELECT
a,b,c,d
FROM
tbl1
WHERE
tbl1.a = [passed in a]
AND NOT EXISTS (
SELECT 'x'
FROMtbl2
WHERE tbl2.a = tbl1.a
DATE '-mm-dd'
I guess I can caulk this up to Oracle not my poor memory. I know in the
other DBMS I have used - I did not have to use DATE.
Thank You.
~|
ColdFusion 8 - Build next generation apps
today, with easy PDF and
If I understand the question properly, here is today's date in literal
format:
'2007-08-10'
-Original Message-
From: Ian Skinner [mailto:[EMAIL PROTECTED]
Sent: Friday, August 10, 2007 9:28 AM
To: CF-Talk
Subject: SQL Date Literal Syntax
I think I'm getting old. I've been using
(CONVERT(varchar, dbo.tblUserTransactionLog.Processed, 101) =
\'08/01/2007\') AND
Yes, that works but could hinder indexes. Josh is correct about date
comparisons. If you want all records with a _date_ of 08/01/2007 a better way
to write that query is:
--- Notice the less than
Are they null? Null is always not ever equal to anything.
Perhaps you want and (isnull(status,'') 'Removed') (MS SQL)
~Brad
-Original Message-
From: Daniel Kessler [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 09, 2007 9:29 AM
To: CF-Talk
Subject: SQL status 'Removed'
I have a
Sorry, I should've thought to designate the database - which is Oracle.
This didn't work in Oracle.
thank you for replying.
daniel
Are they null? Null is always not ever equal to anything.
Perhaps you want and (isnull(status,'') 'Removed') (MS SQL)
So is the field NULL if there are no records there? If so, then I think that
you just add an OR clause.
SELECT service_population,agency,city,state,int_agency_id, ROWNUM AS
r, COUNT(agency) OVER() AS rowcount
FROM internships
WHERE 1=1 AND dept = 'knes' AND status 'Removed' OR Status = NULL
Sorry, I should've thought to designate the database - which is Oracle.
This didn't work in Oracle.
thank you for replying.
daniel
Are they null? Null is always not ever equal to anything.
Perhaps you want and (isnull(status,'') 'Removed') (MS SQL)
Are they null? Null is always not ever equal to anything.
If you're not using MS SQL, try the coalesce function
AND Coalesce(status, '') 'Removed'
Another variation is
AND (Status 'Removed' OR Status IS NULL)
status 'Removed' OR Status = NULL
This also didn't work. I received no error, but no more items showed than
before.
daniel
~|
Enterprise web applications, build robust, secure
scalable apps today - Try it now ColdFusion
Sorry, I think it should be IS NULL. NULL does not equate to anything so =
will not work.
Example:
select * from suppliers
where supplier_name IS NULL;
On 8/9/07, daniel kessler wrote:
status 'Removed' OR Status = NULL
This also didn't work. I received no error, but no more items showed
[mailto:[EMAIL PROTECTED]
Sent: Thursday, August 09, 2007 11:18 AM
To: CF-Talk
Subject: Re: SQL status lt;gt; 'Removed'
Sorry, I think it should be IS NULL. NULL does not equate to anything so =
will not work.
Example:
select * from suppliers
where supplier_name IS NULL;
On 8/9/07, daniel kessler
Unfortunately, my Reply posts didn't show, so I'm going to answer
these again.
Are they null? Null is always not ever equal to anything.
Perhaps you want and (isnull(status,'') 'Removed') (MS SQL)
Sorry, I should've mentioned the database is Oracle. So this didn't
work and produced an
See the replies here
http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:52888
http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:52889
~|
Check out the new features and enhancements in the
latest
Actually, in Oracle, it is.
On 8/9/07, Scott Stewart [EMAIL PROTECTED] wrote:
Null isn't the same as an empty field.
--
mxAjax / CFAjax docs and other useful articles:
http://www.bifrost.com.au/blog/
~|
Check out the new
is not what you want it to be.
So:
AND (status 'Removed' OR Status IS NULL) ORDER ...
Mark
-Original Message-
From: Daniel Kessler [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 09, 2007 11:51 AM
To: CF-Talk
Subject: re: SQL status 'Removed
Unfortunately, my Reply posts
correction...
tbl1
b,c,d
-Original Message-
From: Tim Do [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 09, 2007 11:37 AM
To: CF-Talk
Subject: OT: sql not in
how do I return values from tbl1 where values are NOT in tbl2.
tbl1
a,b,c,d
tbl2
a,b,c,d
only a is passed in.
SELECT
A,b,c,d
FROM
tbl1
WHERE
A NOT IN (SELECT distinct A from tbl2)
Or you could:
SELECT
A.a,
A.b,
A.c,
A.d
FROM
tbl1 A EXCEPTION JOIN tbl2 B ON A.a = B.a
This would return any non-matching records where tbl1.A has no matching
You can also use WHERE NOT EXISTS. I'd try to write an example for you but I
don't really understand what you're trying to do aside from the fact that
you want to select something from table 1 when it doesn't exist in table 2.
Are a,b,c,d column names? Data values? The way you described this is
Thanks, I was able to figure it out... just a left outer join and
checking for nulls that are returned from the left outer. Thanks for
looking!
-Original Message-
From: Brian Kotek [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 09, 2007 3:42 PM
To: CF-Talk
Subject: Re: sql not in
You
Use the WHERE not HAVING clause. Try something like
SELECT ...
FROM ...
WHERE dbo.tblSubscribers.SID = dbo.tblUserTransactionLog.SID
ANDdbo.tblUserTransactionLog.Processed = convert(datetime, '08/01/2007',
101)
AND dbo.tblUserTransactionLog.Processed = convert(datetime, '08/07/2007',
Hello Janet - thanks for the reply.
I should have mentioned that I need to get only the most recent transaction for
each user within the given time-frame. That's why I am using max(). Using
this aggregate function, I think, requires I use having, no?.
Thanks, CWB
Use the WHERE not HAVING
I should have mentioned that I need to get only the most recent
transaction for each user within the given time-frame. That's why I
am using max(). Using this aggregate function, I think, requires I
use having, no?.
No, if I'm understanding correctly its not required here. The WHERE
Yes, that looks correct.
~|
Create robust enterprise, web RIAs.
Upgrade to ColdFusion 8 and integrate with Adobe Flex
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP
Archive:
Hello again Janet, thank you for your help on this. It suare has had me
stumped.
So, like this?
SELECT
dbo.tblSubscribers.SID,
dbo.tblSubscribers.Username,
max(dbo.tblUserTransactionLog.Processed) AS LastTransaction
FROM
dbo.tblSubscribers,
dbo.tblUserTransactionLog
Hello again Janet...
I am so sorry to be bugging you again on this, but I am having trouble with
this new query - not doubt it's something I just don't understand.
If I set the dates in this query to 08/01/2007 and 08/02/2007 I only get
results from 08/01/2007. And, if I have them both set to
by less than or equal to.
-- Josh
- Original Message -
From: C. W. B. [EMAIL PROTECTED]
To: CF-Talk cf-talk@houseoffusion.com
Sent: Wednesday, August 08, 2007 5:37 PM
Subject: Re: SQL Server problem getting records within date range
Hello again Janet...
I am so sorry to be bugging
Ah Ha! I figured out the problem with the query. This following seems to work
as expected:
SELECT
dbo.tblSubscribers.SID,
dbo.tblSubscribers.Username,
max(dbo.tblUserTransactionLog.Processed) AS LastTransaction
FROM
dbo.tblSubscribers,
dbo.tblUserTransactionLog
WHERE
You can't prevent people from trying but you can code to prevent it from
messing with your database. Make sure you are using cfqueryparam every
place you can or use Stored Procs.
Rick King wrote:
Hey all,
I just received this email that is generated when there is an error on a site
I
On Monday 06 Aug 2007, [EMAIL PROTECTED] wrote:
Is this a SQL injection attack?
Yes.
Anything I can do?
Beyond the obvious ? No - it's a fact of life that internet visible sites will
be attacked.
You're using cfqueryparam by the looks of it, and that'll take care of most
kidz.
--
Tom
I am using cfqueryparam, so hopefully that'll be good enough.
Thanks
On Monday 06 Aug 2007, [EMAIL PROTECTED] wrote:
Is this a SQL injection attack?
Yes.
Anything I can do?
Beyond the obvious ? No - it's a fact of life that internet visible
sites will
be attacked.
You're using
601 - 700 of 4661 matches
Mail list logo