On Friday 30 Nov 2007, Ali Majdzadeh wrote:
> Hi everybody:
> I need to make some complex cfqueries and I wonder if there any easy to use
> software is out there I can use to make the sql statements easier
What O/S and what database ?
--
Tom Chiverton
Helping to vitalistically administrate next
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 [mailto:[EM
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 sYe
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]> wrot
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,
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.
~
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.
~~~
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.
http://labs/adobe.
@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
l
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
~~~
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
Sabai-dee.com
-- 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 yo
From: Will Tomlinson [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
>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
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, brands.b
> 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, product
> 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 purp
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
wh
> 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) sam
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 procedur
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 Octo
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 e
>>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
#dateformat(startdate,
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 s
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
***
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 f
other way around.
..:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.
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 Regar
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
: Greg 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 h
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)
>
> 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 feature
>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
> t
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
tableNam
>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 numb
Friday, September 28, 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
>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
~~
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
;t get
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
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
-
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 th
at had the max value 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
al Message-
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
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:
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:
http://www.houseoffusion.co
> 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 ver
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 ColdFu
: 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
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
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 a
Original Message-
> From: Hugh Fidgen [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, September 05, 2007 2:53 AM
> To: CF-Talk
> Subject: Re: SQL select where in headache
>
> Thanks
>
> WHERE PERMISSIO
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.
http://www.adobe.com/cfusion/
ok... I'm new to CF and I can't exactly remember how CFLOOP works but here
goes.
SELECT * FROM EVENTS
WHERE PERMISSIONS IN
(
#session.status#
,#session.status#
)
ORDER BY EVENT_DATE, EVENT_START ASC
Assuming I remembered how CFLOOP worke
o: CF-Talk
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 in
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 input in a form, so it depends if
the user selects one, or multiple options in t
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 input in a form, so it depends if
the user selects one, or multiple options in t
needs single quotes 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 p
> it
> is he is saying 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
> S
er 04, 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 th
Also, don't forget- cfqueryparam is your friend
mailto:[EMAIL PROTECTED]
> Sent: Tuesday, September 04, 2007 11:39 AM
> To: CF-Talk
> Subject: RE: SQL select where in headache
>
> if session.status is a numeric list and PERMISSIONS is a numeric value,
> remove the si
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 would
SELECT * FROM EVENTS
WHERE PERMISSIONS IN
ORDER BY EVENT_DATE, EVENT_START ASC
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 wondering if
> anyone could help.
>
> Code so far:
>
>
> SELECT * FROM E
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
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
>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
timesheet_id,id,date_added,entry_date,type,hours,minutes,no
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 throug
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:
ht
ROTECTED]
Sent: 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 n
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 ago
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
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
SEL
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 A
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 so l
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
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 t
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
tb
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.
quot;status <> 'Removed' OR Status =
NULL" part so your query logic 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: Thurs
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 fe
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 product
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
om: Bruce Sorge [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 09, 2007 11:18 AM
To: CF-Talk
Subject: Re: SQL status <> '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;
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
> 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
>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)
~
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
ORD
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.
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
> (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 < 08/02/
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
han
'2007-08-02', so that won't be picked up by less than or equal to.
-- Josh
- Original Message -
From: "C. W. B." <[EMAIL PROTECTED]>
To: "CF-Talk"
Sent: Wednesday, August 08, 2007 5:37 PM
Subject: Re: SQL Server problem getting records within
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
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
WH
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:
http://www.houseoffusion.com/groups/CF-Tal
> 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 WHER
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
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',
Looks like using the Val() function did the trick.
No more errors being generated :)
Thanks
Rick
~|
Create robust enterprise, web RIAs.
Upgrade to ColdFusion 8 and integrate with Adobe Flex
http://www.adobe.com/products/coldfusi
Someone might have to test this out, but my cf8 admin only let's me
disable the CFC Type Check (so it says), so cfqueryparam
wouldn't/shouldn't be affected by this.
Steve "Cutter" Blades
Adobe Certified Professional
Advanced Macromedia ColdFusion MX 7 Developer
_
http
601 - 700 of 4779 matches
Mail list logo
