Yeah, I ended up using a cursor to do it. Thanks Bill.
-Original Message-
From: Bill Grover [mailto:[EMAIL PROTECTED]]
Sent: Thursday, March 14, 2002 6:01 AM
To: CF-Talk
Subject: RE: SQL help please
I'm not sure you can do it with a simple select statement. If I understand
wha
le, MD 20850-1299WWW:http://www.euservices.com
__
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, March 13, 2002 6:10 PM
> To: CF-Talk
> Subject: Re: SQL help p
March 13, 2002 4:10 PM
Subject: Re: SQL help please
> Nope, that doesn't work.
>
> - Original Message -
> From: laszlo <[EMAIL PROTECTED]>
> Date: Wednesday, March 13, 2002 4:06 pm
> Subject: Re: SQL help please
>
> > select ... count(paycatid) as samepayc
Nope, that doesn't work.
- Original Message -
From: laszlo <[EMAIL PROTECTED]>
Date: Wednesday, March 13, 2002 4:06 pm
Subject: Re: SQL help please
> select ... count(paycatid) as samepaycatid group by paycatid
>
> laszlo
>
>
> [EMAIL PR
select ... count(paycatid) as samepaycatid group by paycatid
laszlo
[EMAIL PROTECTED] wrote:
> Anyone?
>
> Bueller? Bueller?
>
> - Original Message -
> From: [EMAIL PROTECTED]
> Date: Wednesday, March 13, 2002 2:51 pm
> Subject: SQL help please
>
>
Anyone?
Bueller? Bueller?
- Original Message -
From: [EMAIL PROTECTED]
Date: Wednesday, March 13, 2002 2:51 pm
Subject: SQL help please
> Brain not working Must help...
>
> Here's my data set:
>
> employeeid startd
Brain not working Must help...
Here's my data set:
employeeid startdate enddate paycatid
--- --- --- ---
936 2002-02-08 2002-02-08 1
936 2002-02-11 2002-02-11 1
936 2002-02-12 2002-02-12 1
936 2002-02-13 2002-02-13 11
936 2002-02-14 2002-02-14 1
936 2002-02-
Hi folks,
I've simplified the description of the problem but basically here's what I
need to do: given a group of people working for a number of different
divisions of a company I need to split those people into small teams,
ideally with no more than one person from each department in the same te
ginal Message -
From: "Joshua Tipton" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Tuesday, January 22, 2002 5:15 AM
Subject: sql Help
> set dateformat mdy
> declare @datevar smalldatetime
> set @datevar = 'select getdate()'
&g
t;[EMAIL PROTECTED]>
Sent: Tuesday, January 22, 2002 5:15 AM
Subject: sql Help
> set dateformat mdy
> declare @datevar smalldatetime
> set @datevar = 'select getdate()'
> select convert(smalldatetime, @datevar, 101)
>
>
> How come this is not working. I am trying
set dateformat mdy
declare @datevar smalldatetime
set @datevar = 'select getdate()'
select convert(smalldatetime, @datevar, 101)
How come this is not working. I am trying to get the current date stored in a
variable in a mm/dd/ format. Please Help.
Joshua Tipton
___
demiro Junior [mailto:[EMAIL PROTECTED]]
> Sent: Friday, January 11, 2002 6:27 AM
> To: CF-Talk
> Subject: SQL Help
>
>
> Hi again folks...
>
> How can I output a variable that was set within a CFQUERY containing
> first a insert statement, then a selec
ds.rti.org
-Original Message-
From: Allan Pichler [mailto:[EMAIL PROTECTED]]
Sent: Friday, January 11, 2002 8:40 AM
To: CF-Talk
Subject: RE: SQL Help
or you can add a trigger to the sql server that runs on insert
statements to
that table
SELECT @@IDENTITY as newID
@llan
-Origin
or you can add a trigger to the sql server that runs on insert
statements to
that table
SELECT @@IDENTITY as newID
@llan
-Original Message-
From: Stuart Miller [mailto:[EMAIL PROTECTED]]
Sent: 11. januar 2002 14:32
To: CF-Talk
Subject: RE: SQL Help
Your query will run in Query
TATUS)
VALUES
('OBS' , 20)
SELECT MAX(COD_REQ) AS ThePrimaryKey
FROM TAUX_001
-Original Message-
From: Waldemiro Junior [mailto:[EMAIL PROTECTED]]
Sent: 11 January 2002 11:27
To: CF-Talk
Su
Hi again folks...
How can I output a variable that was set within a CFQUERY containing
first a insert statement, then a select statement?
This is my query:
INSERT INTO TAUX_001
(DSC_CHKLST , IND_STATUS)
VALUES
('OBS' , 20)
SELECT @@IDENTITY AS 'COD_R
In SQL Server, identity fields, a number is never reused... deletes
do not reset the counter to the next available identity value.
When you think about what goes on under the covers (of SQL), it makes sense.
SQL would need to maintain a linked list (or somesuch) of deleted
identity values to b
Thanks a lot Bill
- Original Message -
From: "Bill Grover" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Thursday, January 10, 2002 4:17 PM
Subject: RE: SQL Help
> The system is working as designed. The identity column keeps
> in
, January 10, 2002 8:27 AM
> To: CF-Talk
> Subject: SQL Help
>
>
> Hi Folks.
>
> How can I know the next value to be inserted in an Identity
> field data
> type (MS SQL Server)?
> When I put this query: "SELECT MAX(COD_REQ) FROM TAUX_001" it returns
>
products that come out of Berkeley: LSD and [Unix]
BSD. We don't believe this to be a coincidence.
Doug Brown
- Original Message -
From: "Waldemiro Junior" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Thursday, January 10, 2002 5:27 AM
Sub
Hi Folks.
How can I know the next value to be inserted in an Identity field data
type (MS SQL Server)?
When I put this query: "SELECT MAX(COD_REQ) FROM TAUX_001" it returns
the value 10, which corresponds correctly to what its recorded on the
table, but when I insert something in it, after mak
QUERY
===
SELECT *
FROM surveys S, surveySub SB, categories C LEFT OUTER JOIN questions Q ON
(C.categoryID = Q.categoryID) LEFT OUTER JOIN questionsN QN ON (Q.questionID
= QN.questionID) LEFT OUTER JOIN questionsW QW ON (
.
Melville, NY
[EMAIL PROTECTED]
http://www.mscdirect.com
-
-Original Message-
From: Brian Ferrigno [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 10, 2001 11:52 AM
To: CF-Talk
Subject: SQL help for a brain that's not awake yet
I know the answ
I know the answer to my problem is easy I just can't seem to get my brain
functioning today.
My problem is that I want to get the number of times an integer ID appears
in a list of columns for every row in a table. The integer ID is from an
outer SQL statement that I am looping through.
An exa
-Rice
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Thursday, August 23, 2001 1:12 PM
To: CF-Talk
Subject: RE: SQL help/group in CFOUTPUT not working??
I thought that when you were using an ORDER BY with a Union Select you used
the column numbers instead
Thursday, August 23, 2001 1:00 PM
> To: CF-Talk
> Subject: RE: SQL help/group in CFOUTPUT not working??
>
>
>
> You may want to append an
>
> ORDER BY iCasacApptID
>
> to the second query in your UNION.
>
>
>
> -Original Message-
> From: Amanda Ste
You may want to append an
ORDER BY iCasacApptID
to the second query in your UNION.
-Original Message-
From: Amanda Stern [mailto:[EMAIL PROTECTED]]
Sent: Thursday, August 23, 2001 11:34 AM
To: CF-Talk
Subject: SQL help/group in CFOUTPUT not working??
hello, I've been wo
hello, I've been working on this query for a while and
cant get it to work the way I need it to, i am close
though.here is the problem - the cas table is
keyed by [iCasacApptID], the cats table has a
composite key of [iCasacApptID, cTreatSelectID]. So
when I output the results if iCasacApptI
ugust 23, 2001 8:34 AM
To: CF-Talk
Subject: SQL help/group in CFOUTPUT not working??
hello, I've been working on this query for a while and
cant get it to work the way I need it to, i am close
though.here is the problem - the cas table is
keyed by [iCasacApptID], the cats table has a
hello, I've been working on this query for a while and
cant get it to work the way I need it to, i am close
though.here is the problem - the cas table is
keyed by [iCasacApptID], the cats table has a
composite key of [iCasacApptID, cTreatSelectID]. So
when I output the results if iCasacApptI
Thanks. It worked.
>From: Glenn Olsen <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: CF-Talk <[EMAIL PROTECTED]>
>Subject: RE: SQL help
>Date: Mon, 30 Jul 2001 17:21:18 -0400
>
>Try ...
>
>DECLARE @MRID int
>SET @MRID = (SELECT MIN(ID
Try ...
DECLARE @MRID int
SET @MRID = (SELECT MIN(ID) AS MRID FROM HomePageReleases)
DELETE HomePageReleases
WHERE ID = @MRID
-Original Message-
From: S R [mailto:[EMAIL PROTECTED]]
Sent: Monday, July 30, 2001 5:02 PM
To: CF-Talk
Subject: SQL help
I'm trying to get thi
I'm trying to get this sql statement to work within sql server in one shot
and it seems to be ignoring the DELETE statement completely because when I
do a select * from HomePageReleases, nothing is deleted. Do you see anything
wrong?
SELECT MIN(ID) AS MRID FROM HomePageReleases
declare @MRID
This approach worked marvelously!!! Thank you very much John and Jim!!!
Erika
(with a *K*)
-Original Message-
From: John [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 09, 2001 11:59 PM
To: CF-Talk
Subject: RE: SQL help, pretty please...
Here is a different approach that would find
nal Message -
From: Erika L Walker
To: CF-Talk
Sent: Thursday, May 10, 2001 7:45 AM
Subject: SQL help, pretty please...
Some pointing in the right direction please
I generate an email based upon a user's attendance of a conference on a
particular day. The email is going
;Erika L Walker" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, May 09, 2001 9:45 PM
Subject: SQL help, pretty please...
> Some pointing in the right direction please
>
> I generate an email based upon a user's attendance of a confer
conferences.CODATE > (Database function to return todays date i.e.,
getdate(), now())
order by conferences.codate desc
John
with a "h" ;)
-Original Message-
From: Erika L Walker [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 09, 2001 10:46 PM
To: CF-Talk
Subject: SQL help, pretty ple
Some pointing in the right direction please
I generate an email based upon a user's attendance of a conference on a
particular day. The email is going to tell the user when the next conference
is.
I pull a list of all conferences.
Here is my SQL statement:
select CORFNBR, CODATE, COTIME
>= #CreateODBCDate(NOW())#
ORDER BY titledate ASC
Joe Hoffman mailto:[EMAIL PROTECTED]
National Institutes of Health
Center for Information Technology
Division of Computer System Services
-Original Message-
From: Jeff Fongemie [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 16, 2001 3
02))
ORDER BY date DESC, time DESC
Dave Livingston
[EMAIL PROTECTED]
-Original Message-
From: Jeff Fongemie [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 16, 2001 2:18 PM
To: CF-Talk
Subject: SQL help
*This message was transferred with a trial version of CommuniGate(tm) Pro*
Hello cf
Hello cf-talk,
I'm stuck over how to do this.
I have a table and each record has a date field (long date format).
I need to display the records on a page where the record with the
date closest to the current date when the page is viewed will
display first, then the records whose dates
Thanks for the quick answer - sorry for the slow reply. Works like a charm.
Duane
-Original Message-
From: Edward Smith [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, April 04, 2001 12:07 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Re: SQL: Help!
Database?
If it is Oracle, I
> SQL7:
>
> SELECT C.CompanyID, P.Product ID
> FROM Company C, Product P
> WHERE WHERE C.CompanyID=P.CompanyID
> AND NOT EXISTS
> (SELECT C.CompanyID, S.Product ID
> FROM Company C, StoreProducts S
> WHERE C.CompanyID=S.CompanyID)
Obviously you only want one "WHERE" statemen
Irk, I, of course, forgot to join Company and Products, et al...
Oracle:
SELECT C.CompanyID, P.Product ID
FROM Company C, Product P
WHERE C.CompanyID=P.CompanyID
MINUS
SELECT C.CompanyID, S.Product ID
FROM Company C, StoreProducts S
WHERE C.CompanyID=S.CompanyID
SQL7:
SELECT C.CompanyID, P.Pro
Database?
If it is Oracle, I might use the "MINUS" Operator:
SELECT C.CompanyID, P.Product ID
FROM Company C, Product P
MINUS
SELECT C.CompanyID, S.Product ID
FROM Company C, StoreProducts S
To get Company->Product Uniques
And swap the two queries to get StoreProducts uniques.
Not sure if an
I've got one heckuva mess on my hands. I am trying to straighten out a huge
mess of an ecommerce application I have inherited. It went into production
w/h zero testing a couple of months ago and now I am in firefighting mode).
This app relies heavily on three tables company, products, storeproduc
Mobile: 262.490.1376
<http://www.prairiesoftdev.com>
Amateurs practice until they get it right,
Experts practice until they can't get it wrong.
-Original Message-
From: DeVoil, Nick [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, March 07, 2001 7:51 AM
To: CF-Talk
Subject: RE: SQL H
, 2001 1:42 PM
To: CF-Talk
Subject: RE: SQL Help, Please
Philip,
Thanks for the help. Unfortunately, that didn't do the trick. The query
returns a -2. I've put in test records to this point, so I know that the
return should be 22.
Any other ideas?
Tha
Philip,
Upon further testing, your suggestion does work fine, however, I'm getting
the error whenever the times are on two different days; dispatch time was
23:25 and return to quarters time was 02:15. Will I have to change it from
short time format to a different date/time format?
Thanks,
Scott
Philip,
Thanks for the help. Unfortunately, that didn't do the trick. The query
returns a -2. I've put in test records to this point, so I know that the
return should be 22.
Any other ideas?
Thanks,
Scott
Scott Brader
Prairie Software Development LLC
101 East Sadd Street
PO Box 235
North Prairi
> I've built an application for a Fire/Rescue service to log their calls,
> meeting attendance, etc. (geared to small, volunteer agencies). One of the
> things that's very important for them is a sum of the hours spent
> on calls.
> Each call record has a uniquely generated call number that is the
Sorry for the OT but I'm at a loss on this one.
I've built an application for a Fire/Rescue service to log their calls,
meeting attendance, etc. (geared to small, volunteer agencies). One of the
things that's very important for them is a sum of the hours spent on calls.
Each call record has a uni
495432
> -Original Message-
> From: Phoeun Pha [mailto:[EMAIL PROTECTED]]
> Sent: Monday, January 22, 2001 11:59 AM
> To: CF-Talk
> Subject: Single Quotes in SQL HELP PLEASE!
>
>
> ok, I want to enter this character into a SQL table. '
>
> but i get
Ooops! I didn't read your query thoroughly enough. I just glanced at it and
escaped the single quote. My apologies. This is what you'll want to do:
INSERT INTO MyTABLE (
Stuff
)
VALUES (
)
Sorry about that. I'll have to pay more attention next time. :)
Benjamin S. Rogers
We
> you can use PreserveSingleQuoutes(#var#)
Actually, the preserve single quotes function should only be used when you
are writing whole the SQL expression (rather than just data values). If you
do this, you must take special care to escape all single quotes in your
single quoted value strings.
>
ginal Message-
From: Phoeun Pha [mailto:[EMAIL PROTECTED]]
Sent: January 22, 2001 11:59
To: CF-Talk
Subject: Single Quotes in SQL HELP PLEASE!
ok, I want to enter this character into a SQL table. '
but i get an error. how do I make it enter ' into the
> ok, I want to enter this character into a SQL table. '
>
> but i get an error. how do I make it enter ' into the database?
>
> INSERT INTO MyTABLE(Stuff)
> VALUES = '''
You might want to look into PreserveSingleQuotes()
Also, your SQL probably won't work;
INSERT INTO myTable (stuff)
VALUES
Sent: Monday, January 22, 2001 8:59 AM
To: CF-Talk
Subject: Single Quotes in SQL HELP PLEASE!
ok, I want to enter this character into a SQL table. '
but i get an error. how do I make it enter ' into the database?
INSERT INTO MyTABLE(Stuff)
VALUES = '''
~~~
you can use PreserveSingleQuoutes(#var#)
-Original Message-
From: Phoeun Pha [mailto:[EMAIL PROTECTED]]
Sent: Monday, January 22, 2001 10:59 AM
To: CF-Talk
Subject: Single Quotes in SQL HELP PLEASE!
ok, I want to enter this character into a SQL table. '
but i get an error. how
One more '
INSERT INTO MyTABLE(Stuff)
VALUES = ''''
-Original Message-
From: Phoeun Pha [mailto:[EMAIL PROTECTED]]
Sent: Monday, January 22, 2001 11:59 AM
To: CF-Talk
Subject: Single Quotes in SQL HELP PLEASE!
ok, I want to enter this character into a SQL
ok, I want to enter this character into a SQL table. '
but i get an error. how do I make it enter ' into the database?
INSERT INTO MyTABLE(Stuff)
VALUES = '''
~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://ww
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: January 19, 2001 14:51
To: CF-Talk
Subject: SQL help needed - Apples but not Oranges...
I'm trying to select clients who have purchased "Apples", but NOT "Oranges".
This query is returning clients who have bought eith
ems.orderid AND
OrderItems.productid = Products.id AND
Products.name LIKE '%Oranges%'
)
Thanks to all who have probably already responded here!
-ron
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> Sent: Friday, January 19, 2001 1:51 PM
'
AND Products.name NOT LIKE '%Oranges%'
Blessed Be,
--Katrina Chapman
http://www.katrinachapman.com
http://www.cfchick.com
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Friday, January 19, 2001 7:51 PM
To: CF-Talk
Subject: SQL help needed - Apples but
I'm trying to select clients who have purchased "Apples", but NOT "Oranges".
This query is returning clients who have bought either.
SELECT DISTINCT Clients.id, Clients.firstname, Clients.lastname
FROM Clients,Orders,OrderItems,Products
WHERE
(
Clients.id = Orders.clientid AND
Orders.id = Order
two different places.
Brian
-Original Message-
From: Todd Ashworth [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 03, 2001 4:32 PM
To: CF-Talk
Subject: OT: SQL Help
Sorry about posting this to the wrong list, but I'm having trouble getting
mail through to the SQL list.
I am ru
What are the columns that are somewhat linked between these tables?
- Original Message -
From: "Todd Ashworth" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, January 03, 2001 3:32 PM
Subject: OT: SQL Help
> Sorry about post
Sorry about posting this to the wrong list, but I'm having trouble getting
mail through to the SQL list.
I am running a query to see if there is an entry in the database for an
event that matches certain date criteria. One of the criteria is that one
of the dates that the event is scheduled on d
#news.Area#
#news.Status#
#news.Other#
end cf code
-Original Message-
From: Jim McAtee [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 25, 2000 11:57 AM
To: CF-Talk
Subject: Re: SQL Help
Richard,
Thank you for your help. Maybe if I give a much sim
#news.Area#
#news.Status#
#news.Other#
end cf code
-Original Message-
From: Jim McAtee [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 25, 2000 11:57 AM
To: CF-Talk
Subject: Re: SQL Help
Richard,
Thank you for your help. Maybe if I give a much sim
create a join which displays all the data? From there
you should be able to start adding WHERE clause items.
-Original Message-
From: Jim McAtee [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 25, 2000 12:57 PM
To: CF-Talk
Subject: Re: SQL Help
Richard,
Thank you for your help. Ma
y the first of which
would have news for 2000. Note that I'm selecting some other fields
from TABLE1, but just News from TABLE2.
Thanks,
Jim
-Original Message-
From: Richard Kern <[EMAIL PROTECTED]>
To: CF-Talk <[EMAIL PROTECTED]>
Date: Monday, September 25, 2000 11:12
than the FROM) clause using
*= (include all rows from first table) or =* (include all rows in second
table)
-Original Message-
From: Jim McAtee [mailto:[EMAIL PROTECTED]]
Sent: Sunday, September 24, 2000 4:10 PM
To: CF-Talk
Subject: SQL Help
Try it again, with a subject this time...
Can
> To: CF-Talk
> Subject: Re: SQL Help
>
>
> Yep. Left, right and inner joins all produce the same result.
>
> Jim
>
>
> -Original Message-
> From: Mark Warrick <[EMAIL PROTECTED]>
> To: CF-Talk <[EMAIL PROTECTED]>
> Date: Sunday,
Yep. Left, right and inner joins all produce the same result.
Jim
-Original Message-
From: Mark Warrick <[EMAIL PROTECTED]>
To: CF-Talk <[EMAIL PROTECTED]>
Date: Sunday, September 24, 2000 4:15 PM
Subject: RE: SQL Help
Silly question but have you tried a right outer joi
[EMAIL PROTECTED]]
> Sent: Sunday, September 24, 2000 3:10 PM
> To: CF-Talk
> Subject: SQL Help
>
>
> Try it again, with a subject this time...
>
> Can't figure out why the following query behaves like it does, but
> I'm guessing there must be someth
Try it again, with a subject this time...
Can't figure out why the following query behaves like it does, but
I'm guessing there must be something fundamental about how SQL
operates that I'm not comprehending.
I've got a one:many relationship between table A and table B. Table
B records represen
riginal Message-
From: Chris Carpenter [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 28, 2000 2:15 AM
To: coldfusion
Subject: SQL Help Please (in plain text...sorry...)
Hi,
I have a table with client details ('clients') that is related to another
table with category info ('cate
Hi,
I have a table with client details ('clients') that is related to another
table with category info ('category').
Now, each client could have more than 1 category in there CategoryID Field.
the user will be clicking on a link which will have one CategoryID as a URL
variable, and the state wher
chris,
turn off the html mail, post in plain text and we'll try.
thanks,
.jeff
--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_t
This is a multi-part message in MIME format.
--=_NextPart_000_0033_01BF98D5.78A071A0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi,
I have a table with client details ('clients') that is related to =
another table with category info ('
401 - 481 of 481 matches
Mail list logo