This gives you the error...?
CASE
WHEN cc.cc_type_ID = 1 THEN @careCB + 1
WHEN cc.cc_type_ID = 2 THEN @careFBC + 1
WHEN cc.cc_type_ID = 3 THEN @careSBC + 1
WHEN cc.cc_type_ID = 4 THEN @careVACC + 1
WHEN cc.cc_type_ID = 5 THEN @careOFCC + 1
ELSE 0
END AS myvar
On Thu, Dec 13, 2012 at 3:45 PM,
Except I need those values individually as part of the return..
On 12/13/2012 4:49 PM, John M Bliss wrote:
This gives you the error...?
CASE
WHEN cc.cc_type_ID = 1 THEN @careCB + 1
WHEN cc.cc_type_ID = 2 THEN @careFBC + 1
WHEN cc.cc_type_ID = 3 THEN @careSBC + 1
WHEN cc.cc_type_ID = 4
DOH!! end of the day, yeah... cc.cc_type_id isn't part of a group by or
aggregate function..
and I need those end values as part of the returned record set
On 12/13/2012 4:49 PM, John M Bliss wrote:
This gives you the error...?
CASE
WHEN cc.cc_type_ID = 1 THEN @careCB + 1
WHEN
Mike Kear wrote:
The first two examples are selecting the literal value 'mike' and '1'
In the first example, you are telling SQL to give the column
containing 'mike' a name of 'name'.
aha.. literal was the word I was looking for. I did a search for sql
select literal and it led me to the
The first two examples are selecting the literal value 'mike' and '1'
In the first example, you are telling SQL to give the column
containing 'mike' a name of 'name'.
A practical example of where you might use this behaviour might be :
SELECT 'Invoice' as doctype, invoiceno, invoicedate,
I was curious if anyone knows how you describe the following SQL
functionality:
...
The last one is obvious as it's SQL arithmetic, but what are the first
two examples? Are those also examples of SQL arithmetic as well? I
can't find this kind of SQL functionality described or documented
I've always known it as selecting a literal value. So SELECT 1 is select
the literal value 1.
On Mon, Feb 16, 2009 at 7:41 PM, Mike Soultanian msoul...@csulb.edu wrote:
I was curious if anyone knows how you describe the following SQL
functionality:
SELECT 'mike' as name
returns a single
Cool. That did the trick. Thanks to all!
-Original Message-
From: Gaulin, Mark [mailto:[EMAIL PROTECTED]
Sent: Friday, January 25, 2008 2:08 PM
To: CF-Talk
Subject: RE: SQL Question -- Order by a column's value?
Yes, you can do this with a CASE statement. The syntax may depend on you
Yes, you can do this with a CASE statement. The syntax may depend on
you db, but on SQL Server
ORDER BY
CASE Colleges
WHEN 'Harvard' THEN 1
WHEN 'Princeton' THEN 2
WHEN 'Dartmouth' THEN 3
ELSE 100
END
This would
If you are using SQL Server, you can use something like this to get the
previous:
SELECT TOP 1 idNumber
FROM Table
WHERE idNumber #myNumber#
or to get the next:
SELECT TOP 1 idNumber
FROM Table
WHERE idNumber #myNumber#
M!ke
-Original Message-
From: Scott Stewart [mailto:[EMAIL
: Dawson, Michael [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 15, 2008 1:23 PM
To: CF-Talk
Subject: RE: SQL Question, get previous record
If you are using SQL Server, you can use something like this to get the
previous:
SELECT TOP 1 idNumber
FROM Table
WHERE idNumber #myNumber#
or to get the next
:23 PM
To: CF-Talk
Subject: RE: SQL Question, get previous record
If you are using SQL Server, you can use something like this to get the
previous:
SELECT TOP 1 idNumber
FROM Table
WHERE idNumber #myNumber#
or to get the next:
SELECT TOP 1 idNumber
FROM Table
WHERE idNumber #myNumber#
M!ke
If you already have the result set, and it is ordered by the id, then you
could just use this
myQueryResult.id[currentrow+/-1]
to fetch the previous/next id number
very pseudo code here, but hopefully you get the drift.
but not quite sure if this is what you're asking?
On Jan 15, 2008 1:20
You are correct. Thanks!
-Original Message-
From: Brad Wood [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 15, 2008 1:28 PM
To: CF-Talk
Subject: RE: SQL Question, get previous record
I think you'll want an order by on those:
to get the previous:
SELECT TOP 1 idNumber
FROM Table
but what if i want
like '% ';
and
not like '% money'
where money could be any last name or character.
On Jan 3, 2008 10:17 AM, Greg Morphis [EMAIL PROTECTED] wrote:
the SQL statement like requires a %..
for example..
select * from froo where name like 'G%'
will return all names that starts
the SQL statement like requires a %..
for example..
select * from froo where name like 'G%'
will return all names that starts with G..
So try something like
select * from tbl where name like '% ';
That will catch anything with a trailing space.
Just a heads up..
On Jan 3, 2008 9:03 AM, morchella
'% ' shouldnt return ' money'
it should only return enteries with a trailing space..
for example
create table testtbl ( name varchar2(10));
insert into testtbl values ('Greg ');
insert into testtbl values ('Greg M');
insert into testtbl values ('Greg Mo');
insert into testtbl values ('Gary ');
You may need to get into database character functions. I believe they
all have them, but they all implement them slightly differently. You
will need to consult appropriate documentation for you database
management system.
But you should be able to do something like this concept.
SELECT
just fond out why
man this db is so messed up...
'Joe Garth '
so i would i look for NOT LIKE '% % '
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
On 1/3/08, Paul Ihrig [EMAIL PROTECTED] wrote:
just fond out why
man this db is so messed up...
'Joe Garth '
so i would i look for NOT LIKE '% % '
OR...
ltrim(rtrim(contact)) NOT LIKE '% %'
~|
Adobe® ColdFusion® 8
If what you are trying to do is eliminate trailing spaces why not just do:
Update contacts set contact = rtrim(ltrim(contact))
-Original Message-
From: morchella [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 03, 2008 9:03 AM
To: CF-Talk
Subject: sql question: contains space' '
ok..
thank you!
SELECT id, Contact, Address, City, State, Zip
FROM Leads
WHERE (Contact LIKE '') OR
(Contact LIKE ' ') OR
(Contact NOT LIKE '% % ')
AND (LTRIM(RTRIM(Contact)) NOT LIKE '% %')
ORDER BY id
now to fix 20,000+ records!
Mark
i am trying to find where only the lastname was added to the field Contact.
some 2000+ records. then do a match on a known field like email or phone
then update the Contact field with the combined 'fname lname' from a xls
spread sheet.
then after all the names are combined i will just add 2
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:
There are a number of ways to do this. The typical way that I get this done is
(im going to use your example of rows 31-50 for a total of 20 rows)
SELECT TOP 20 *
FROM [tableName]
WHERE [primaryKeyField] not in (
SELECT top 30 [primaryKeyField]
FROM [tableName]
WHERE [criteriaField]
How about you simply use WHERE dateCreated = '4/2/2007'
Basically, lose the padding zeros.
~Che
-Original Message-
From: Chad Gray [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 03, 2007 9:59 AM
To: CF-Talk
Subject: SQL question
I am using MS SQL and have a field with data type
I wrote a blog post concerning this a while back:
http://www.stillnetstudios.com/2007/01/20/comparing-dates-without-times-in-sql-server/
Hope that helps.
-Ryan
Chad Gray wrote:
I am using MS SQL and have a field with data type DateTime.
I want to find all records with the day 4/2/2007?
If I
I tried that also and no records are returned.
-Original Message-
From: Che Vilnonis [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 03, 2007 10:01 AM
To: CF-Talk
Subject: RE: SQL question
How about you simply use WHERE dateCreated = '4/2/2007'
Basically, lose the padding zeros.
~Che
How about using...
WHERE dateCreated LIKE '%4/2/2007%'
-Original Message-
From: Chad Gray [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 03, 2007 10:05 AM
To: CF-Talk
Subject: RE: SQL question
I tried that also and no records are returned.
-Original Message-
From: Che
Chad Gray wrote:
I want to find all records with the day 4/2/2007?
WHERE DATEDIFF(day, dateCreated,'4/3/2007')=0
~|
Create Web Applications With ColdFusion MX7 Flex 2.
Build powerful, scalable RIAs. Free Trial
I tried that too and no records are returned.
-Original Message-
From: Che Vilnonis [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 03, 2007 10:09 AM
To: CF-Talk
Subject: RE: SQL question
How about using...
WHERE dateCreated LIKE '%4/2/2007%'
-Original Message-
From: Chad Gray
and no records are returned.
-Original Message-
From: Che Vilnonis [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 03, 2007 10:01 AM
To: CF-Talk
Subject: RE: SQL question
How about you simply use WHERE dateCreated = '4/2/2007'
Basically, lose the padding zeros.
~Che
-Original
Ooh.. I like that one
-Original Message-
From: Paul Hastings [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 03, 2007 9:10 AM
To: CF-Talk
Subject: Re: SQL question
Chad Gray wrote:
I want to find all records with the day 4/2/2007?
WHERE DATEDIFF(day, dateCreated,'4/3/2007')=0
[mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 03, 2007 10:10 AM
To: CF-Talk
Subject: Re: SQL question
Chad Gray wrote:
I want to find all records with the day 4/2/2007?
WHERE DATEDIFF(day, dateCreated,'4/3/2007')=0
AH! This works!
Thanks!
-Original Message-
From: Paul Hastings [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 03, 2007 10:10 AM
To: CF-Talk
Subject: Re: SQL question
Chad Gray wrote:
I want to find all records with the day 4/2/2007?
WHERE DATEDIFF(day, dateCreated,'4/3/2007')=0
#
AND
MONTH(dateCol)=#monthVar#
AND
YEAR(dateCol)=#yearVal#
-Original Message-
From: Chad Gray [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 03, 2007 9:12 AM
To: CF-Talk
Subject: RE: SQL question
I tried that too and no records are returned.
-Original Message-
From: Che Vilnonis
The trick with date and MSSQL is using and . The string '04/02/2007'
is seen by MSSQL as '04/02/2007 00:00:00', so your condition will return
only those records with that exact timestamp. You have to use:
WHERE dateCreated = '04/02/2007'
AND dateCreateted '04/03/2007'
Steve Brownlee
People people people :)
I have seen casting, converting, date-diffing, LIKE'ing,
MONTH()/Day()/Year()'ing Please do not run functions on your
date/time fields. Running a function on a column in general is extremely
slow. Date/time stamps can be used quite nicely with out them:
DECLARE @date
Ben,
Ok... Nicely done. What about BETWEEN ... Any benefits there?
WHERE date_created BETWEEN @date AND @date + 1
-Mark
-Original Message-
From: Ben Nadel [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 03, 2007 9:26 AM
To: CF-Talk
Subject: RE: SQL question
People people people
).
..
Ben Nadel
Certified Advanced ColdFusion MX7 Developer
www.bennadel.com
Need ColdFusion Help?
www.bennadel.com/ask-ben/
-Original Message-
From: Mark A Kruger [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 03, 2007 10:30 AM
To: CF-Talk
Subject: RE: SQL question
Ben,
Ok... Nicely done
Ah Gotcha.
-Original Message-
From: Ben Nadel [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 03, 2007 9:35 AM
To: CF-Talk
Subject: RE: SQL question
BETWEEN is good, but it is doubly-inclusive meaning that it is like doing
both = and =. In this case, it might turn up records where
: SQL question
People people people :)
I have seen casting, converting, date-diffing, LIKE'ing,
MONTH()/Day()/Year()'ing Please do not run functions on your
date/time fields. Running a function on a column in general is extremely
slow. Date/time stamps can be used quite nicely with out them
I just tried this and I get no records.
dateCreated = 04/02/2007 AND dateCreated (04/02/2007 + 1)
Is this method compatible with MS SQL?
-Original Message-
From: Chad Gray [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 03, 2007 10:41 AM
To: CF-Talk
Subject: RE: SQL question
year?
Thanks for the clean elegant solution. I will try it out.
-Original Message-
From: Ben Nadel [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 03, 2007 10:26 AM
To: CF-Talk
Subject: RE: SQL question
People people people :)
I have seen casting, converting, date-diffing
Subject: RE: SQL question
On the (@date + 1) how do you know it is adding one day?
Out of curiosity how do you add one year?
Thanks for the clean elegant solution. I will try it out.
~|
Deploy Web Applications Quickly across
?
-Original Message-
From: Chad Gray [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 03, 2007 10:41 AM
To: CF-Talk
Subject: RE: SQL question
On the (@date + 1) how do you know it is adding one day?
Out of curiosity how do you add one year?
Thanks for the clean elegant solution. I
/
-Original Message-
From: Chad Gray [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 03, 2007 10:46 AM
To: CF-Talk
Subject: RE: SQL question
I just tried this and I get no records.
dateCreated = 04/02/2007 AND dateCreated (04/02/2007 + 1)
Is this method compatible with MS SQL
int.
So I probably have to cast as a date in order to get this to work.
-Original Message-
From: Joe Rinehart [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 03, 2007 10:48 AM
To: CF-Talk
Subject: Re: SQL question
Chad,
Try
WHERE
dateCreated = @someDate AND dateCreated
:00 AM
To: CF-Talk
Subject: RE: SQL question
Thanks Ben, and Joe here is what finally worked for me.
dateCreated = '04/03/2007' AND dateCreated DATEADD(DAY, 1,
'04/03/2007')
With
dateCreated = '04/03/2007' AND dateCreated ('04/03/2007' + 1)
I get this error:
Conversion failed when converting
Wold moving the M.i_recid IS NULL to the JOIN help?
SELECT
F.pid,
F.acrostic,
F.recid,
F.recordthread,
F.aed_onset,
F.d_form
FROM
vfrm_sae F
LEFT OUTER JOIN
v_sae_jna_mr M
ON
F.recordthread = M.i_recordThread AND
On what engine? If this is MSSQL, try running the query tuning advisor.
There maybe some updates to indexes or statistics that will speed it up.
-Original Message-
From: Jerry Barnes [mailto:[EMAIL PROTECTED]
Sent: Monday, April 02, 2007 12:22 PM
To: CF-Talk
Subject: OT: SQL Question
I've always read that you use nvarchar for multilingual data. Keep in mind,
nvarchar takes up twice as much space in the db since it makes an alotment
for languages that have extended characters.
~C
-Original Message-
From: Doug Brown [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 29,
: SQL Question?
I've always read that you use nvarchar for multilingual data. Keep in mind,
nvarchar takes up twice as much space in the db since it makes an alotment
for languages that have extended characters.
~C
-Original Message-
From: Doug Brown [mailto:[EMAIL PROTECTED]
Sent: Thursday
I believe so...
-Original Message-
From: Doug Brown [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 29, 2007 4:03 PM
To: CF-Talk
Subject: RE: SQL Question?
Ok, so if my data will only be supporting the English language I should just
use varhcar or char since n uses 2 bytes for one
, March 29, 2007 4:22 PM
To: CF-Talk
Subject: RE: SQL Question?
I believe so...
-Original Message-
From: Doug Brown [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 29, 2007 4:03 PM
To: CF-Talk
Subject: RE: SQL Question?
Ok, so if my data will only be supporting the English language I should
Doug Brown wrote:
I understand several things about SQL when it comes to getting information
out of it, but never really have understood which data types to use for what
specific data. I know what ones suppose to hold what kind of data as far as
integer data, character data, monetary data,
Doug Brown wrote:
Ok, so if my data will only be supporting the English language I should just
use varhcar or char since n uses 2 bytes for one character. Correct?
never say never. unless you're going to be dealing in TB of data, better safe
than sorry.
Bruce,
One way to accomplish this is to query your (unique) records and populate
another table with the same structure with that data. Once it is done, then
you can re-populate that table from the table you created. Hope that makes
sense.
Doug B.
- Original Message -
From: Bruce
Without looking too hard, would using a TOP 1 in the sub select work?
-Original Message-
From: Bruce Sorge [mailto:[EMAIL PROTECTED]
Sent: 01 February 2007 17:26
To: CF-Talk
Subject: SQL QUestion
I have a lot of duplicate information in a table. I know how to query to
find the dupes,
Bruce Sorge wrote:
I have a lot of duplicate information in a table. I know how to query to
find the dupes, but I am having problems with deleting them (there are
thousands). I tried this:
DELETE
FROM CODES
WHERE Code =
(SELECT Code,
COUNT(Code) AS NumOccurrences
FROM Codes
GROUP BY
OK, I figured it out pretty much. So now I have this:
CREATE TABLE #tempduplicatedata
(
Code NVARCHAR(20)
)
--Identify and save dup data into temp table
INSERT INTO #tempduplicatedata
SELECT Code FROM Codes
GROUP BY Code
HAVING COUNT(Code) 1
--Confirm number of dup rows
SELECT @@ROWCOUNT AS
I actually bloged about this earlier this month. See here:
http://www.ruslansivak.com/index.cfm/2007/1/10/Deleting-duplicate-rows-from-
SQL-Server
Russ
-Original Message-
From: Bruce Sorge [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 01, 2007 12:26 PM
To: CF-Talk
Subject:
Bruce Sorge wrote:
I have a lot of duplicate information in a table. I know how to query to
find the dupes, but I am having problems with deleting them (there are
thousands). I tried this:
create a clone of your table but make your key duplicated column as a unique
key setting the index to
How many records are coming back?
How are you calling it?
I have ran tests before for where running exec sp_name in side of a
cfquery was faster than cfstoredproc.
Just a thought.
Also are you sure the stored proc is really taking 20 seconds to run OR
is the CF page just taking 17 seconds to
You may want to recompile the sp... It may have been compiled before the
statistics for the tables involved were updated.
You could check the query plan of the sp vs. the query to see how they
differ. (Not sure what db you are using...)
Mark
-Original Message-
From: Tim Do
Bruce Sorge wrote:
OK, I figured it out pretty much. So now I have this:
CREATE TABLE #tempduplicatedata
(
Code NVARCHAR(20)
)
--Identify and save dup data into temp table
INSERT INTO #tempduplicatedata
INSERT INTO #tempduplicatedata (code)
SELECT Code FROM Codes
GROUP BY Code
Damn! Always the simple shit that gets me.
Thanks.
On 2/1/07, Jochem van Dieten [EMAIL PROTECTED] wrote:
Bruce Sorge wrote:
OK, I figured it out pretty much. So now I have this:
CREATE TABLE #tempduplicatedata
(
Code NVARCHAR(20)
)
--Identify and save dup data into temp table
That looks like the right/only way to do it as far as I know.
Mark
-Original Message-
From: Chad Gray [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 30, 2006 11:02 AM
To: CF-Talk
Subject: OT: SQL question
I am not a MS SQL guru and this bit of SQL is about as advanced as I
You could also do:
SELECT MAX(thedate)
FROM yourtable
!//--
andy matthews
web developer
certified advanced coldfusion programmer
ICGLink, Inc.
[EMAIL PROTECTED]
615.370.1530 x737
--//-
-Original Message-
From: Chad Gray [mailto:[EMAIL PROTECTED]
select top 1 *
from tablename
order by datefield desc
On 10/22/06, Chad Gray [EMAIL PROTECTED] wrote:
Im a little burnt out need some help.
If I query a table that tracks the number of times a book was checked out and
returned. I want to get the most recent item in the table to find
Thanks Kris! That makes sense.
-Original Message-
From: Kris Jones [mailto:[EMAIL PROTECTED]
Sent: Sunday, October 22, 2006 10:12 AM
To: CF-Talk
Subject: Re: SQL Question
select top 1 *
from tablename
order by datefield desc
On 10/22/06, Chad Gray [EMAIL PROTECTED] wrote
Thank you all for your suggestions, after half a day of hair pulling, I figured
out what's going on.. actually the database table given to me wasn't
consistent. There really were no listings for some folders. That was the
reason...
Otherwise to achieve what I wanted, this will work:
select
Thank you all for your suggestions, after half a day of hair pulling, I figured
out what's going on.. actually the database table given to me wasn't
consistent. There really were no listings for sme folders. that was the
reason...
Otherwise to achieve what I wanted, this will work:
select
SQL Server sucks at regexps, you have some rudimentary tools at your
disposal with the LIKE statement, but none that I could get to work, I
have in the past found a regexp stored procedure but that's just dog slow...
This is the like statement I tried:
SELECT * FROM listing
WHERE dir LIKE
On 8/2/06, Brian Dumbledore [EMAIL PROTECTED] wrote:
I am using MS-SQL, I couldn't get this to work..
I tried, patindex,charindex, like combinations, none worked.
I have directory paths in a table, given a starting path of a directory, I
want to get its first level elements.
eg:
table
select *
From tablename
where patindex('%\%\%',dir) = 0
And patindex('%\%',dir) 0
-Original Message-
From: Brian Dumbledore [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 02, 2006 4:31 PM
To: CF-Talk
Subject: sql question.
I am using MS-SQL, I couldn't get this to work..
I
-Talk
Sent: Wed Aug 02 21:56:24 2006
Subject: Re: sql question.
SQL Server sucks at regexps, you have some rudimentary tools at your
disposal with the LIKE statement, but none that I could get to work, I
have in the past found a regexp stored procedure but that's just dog slow...
This is the like
Agreeing with everyone who has already posted, this might not be the best
place to be doing this... But one more option to play with:
WHERE
!--- Get paths with at least one slash. ---
[field] LIKE '_%\_%'
!--- Exclude files with 2 char extension. ---
AND
[field] NOT LIKE '%.__'
use a subquery
update table set status = 'approved'
where sku = cfqueryparam value=#url.sku#' and cfsqltype=cf_sql_varchar /
and statuswhen = (
select max(statuswhen)
from status
where ... --include where clauses
)
On 6/12/06, Chad Gray [EMAIL PROTECTED] wrote:
How would I write the SQL to
UPDATE
tablename
SET
status = 'approved'
WHERE
SKU = #URL.SKU# AND
statusWhen = (SELECT MAX(statusWhen) FROM tablename)
i think that should do it...
(to answer the actual question, I'm not sure if the MAX() function
will work as you originally asked)
On 6/12/06, Chad Gray
Chad Gray wrote:
How would I write the SQL to update a record that has the most recent date?
Say I want to change the status field to Approved where the date is the
most recent and SKU = 12345
Will the Max function work in the Where section of the SQL or is there
another function to use
Chad,
You can do a sub query:
UPDATE table
SET STATUS = 'approved',
WHERE SKU = #URL.SKU# AND StatusWhen =
(
SELECT MAX(StatusWhen) FROM table
)
But it might be better to make something with more feedback:
// Declare the ID to be updated
DECLARE @id INT;
// Get the ID to be updated
Thanks for the answers... they all appear to do what I need.
Thanks again,
Chad
~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:243204
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription:
On Monday 12 June 2006 15:54, Ben Nadel wrote:
But it might be better to make something with more feedback:
Or use two queries and a transaction
cftransaction
cfquery name=qMax
SELECT MAX(StatusWhen) as maxDate FROM table
/cfq...
cfquery name=qUpd
UPDATE table
SET STATUS = 'approved',
WHERE SKU
On 4/19/06, David Elliott [EMAIL PROTECTED] wrote:
Below is a part of my code
And my question might be a simple onethe line that says cfif equip_idle
IS NOT ,equip_idle/cfif...does this mean I'll only have any entry if
their is a value in it?
Yes, but a better way I think is to
Thanks Ryan.
Ryan Guill [EMAIL PROTECTED] wrote: On 4/19/06, David Elliott wrote:
Below is a part of my code
And my question might be a simple onethe line that says
,equip_idle...does this mean I'll only have any entry if their is a value in
it?
Yes, but a better way I think is
Dave,
I am not sure I follow 100%, but yes, the CFIF statements will stop the
values from being added. However, the column will still be in the database
for that new record and will have whatever default value you have assigned
to that column (or NULL if no default value has been set and the
No problem.
On 4/19/06, David Elliott [EMAIL PROTECTED] wrote:
Thanks Ryan.
Ryan Guill [EMAIL PROTECTED] wrote: On 4/19/06, David Elliott wrote:
Below is a part of my code
And my question might be a simple onethe line that says
,equip_idle...does this mean I'll only have any
1 - 100 of 521 matches
Mail list logo
