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 cc.cc_ty
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 =
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, Sco
Hey all I have a couple of questions with a query that I'm trying to put
together
What I need as part of this query is a running count of applications
that each value of cc_type_id
(there's five).
I'm trying to do it in the CASE statement below, however if I try to use
AS something in order to
Just read the original post properly - please ignore me.
Dominic
2009/11/15 Dominic Watson
> Depending on what you are doing with this data, seems to me that this
> should be done in the front end and not the db. So get your data in the
> original format:
>
> USER, CODE
>
> rick,AL
> rick,FR
>
Depending on what you are doing with this data, seems to me that this should
be done in the front end and not the db. So get your data in the original
format:
USER, CODE
rick,AL
rick,FR
rick,TR
rick,HS
joe,AL
joe,FU
Bob,FM
And then use cfoutput with query and group (a very rough output here):
You wrote a pivot query without using pivot. BTW, the aggregate for the pivot
query can be Count().
-Original Message-
From: Rick Root [mailto:rick.r...@gmail.com]
Sent: Friday, November 13, 2009 4:19 PM
To: cf-talk
Subject: Re: (ot) SQL Question - flattening data
>From
>From the documentation, pivot tables seem to require aggregate
functions... The generic description would seem to work but the
examples make it difficult to see how.
But... I figured out a solution! Using SQL Server's row_number() over
(partition by XXX order by XXX) I can make a subquery that
]
Sent: Friday, November 13, 2009 1:11 PM
To: cf-talk
Subject: RE: (ot) SQL Question - flattening data
Is there a particular reason to return them in this format? I would
think that the straight query output would be simpler to work with.
However, you can accomplish this either by using cursors to
X
Bob X
Crosstab queries can be a little hairy to build. IMHO, go with the cursors.
-Original Message-
From: Rick Root [mailto:rick.r...@gmail.com]
Sent: Friday, November 13, 2009 10:41 AM
To: cf-talk
Subject: (ot) SQL Question - flattening
I'm trying to flatten out some data using only SQL we currently
have a mainframe job that produces a datafeed for me uses cobol to
do the work of looping through all the entities and putting up to 5
record types in 5 "record type" fields in the output file. I'm trying
to figure out a way
ug 21, 2009 at 8:09 PM, Discover
Antartica wrote:
>
> *= is actually used at my work place. Thanks for the answer.
>
>
>
>
>
> From: Adrian Lynch
> To: cf-talk
> Sent: Friday, August 21, 2009 5:59:17 PM
> Subject: RE: simple SQL Qu
*= is actually used at my work place. Thanks for the answer.
From: Adrian Lynch
To: cf-talk
Sent: Friday, August 21, 2009 5:59:17 PM
Subject: RE: simple SQL Question
That is a left outer join. It's mixing new and old styles of joining tables.
Not su
e question?
Adrian
> -Original Message-
> From: Discover Antartica [mailto:discoverantart...@yahoo.com]
> Sent: 22 August 2009 00:50
> To: cf-talk
> Subject: simple SQL Question
>
>
> what does the *= mean in a query. For example:
>
> select a.id, b.na
what does the *= mean in a query. For example:
select a.id, b.name
from a
inner join b
on a.id = b.id
where a.id *= b.id
Thanks
~|
Want to reach the ColdFusion community with something they want? Let them know
on the
02-25 7:00'
>> union all select 2, 'McSteamy', '2009-02-25 7:45'
>>
>> insert into @first_appointment_per_day
>> (patient_id, appointment_date)
>> (select patient_id, min(appointment_date)
>> from @appointment
>> group by patient_id, conv
-25 7:00'
> > union all select 2, 'McSteamy', '2009-02-25 7:45'
> >
> > insert into @first_appointment_per_day
> > (patient_id, appointment_date)
> > (select patient_id, min(appointment_date)
> > from @appointment
> > group by patient
t patient_id, min(appointment_date)
> from @appointment
> group by patient_id, convert(varchar(10), appointment_date, 101))
>
> select app.patient_id, app.appointment_date, app.doctor
> from @first_appointment_per_day tmp1
> inner join @appointment app on tmp1.patient_id = app.patient_id
&
This is the sort of approach I was trying to think of, couldn't
remember derived tables. This query only grabs the earliest
appointment for each patient though not the earliest for every day.
But it is a good starting point, I'll try to massage a group by date
in there and see what I can come up w
Maybe
Select top 1 * from appointment where appointment between (date/time and
date/time) order by appointment_date
Ben Conner wrote:
> Hi Judah,
>
> I suppose there's more than one way to do this, but this should work...
>
> SELECT a.*
> FROM appointment a INNER JOIN
>
Hi Judah,
I suppose there's more than one way to do this, but this should work...
SELECT a.*
FROM appointment a INNER JOIN
(SELECT patient_id, MIN(appointment_date)
AS appointment_date
FROM appointment
ointment_date)
from @appointment
group by patient_id, convert(varchar(10), appointment_date, 101))
select app.patient_id, app.appointment_date, app.doctor
from @first_appointment_per_day tmp1
inner join @appointment app on tmp1.patient_id = app.patient_id
and tmp1.appointment_date = app.appointment_date
~Brad
I'm ill and having difficulty wrapping my head around an issue I know
I've done before, so I'm hoping that someone can help me out.
I have a table that lists patient's appointments.
Appointment
id
patient_id
doctor
appointment_date (datetime)
A patient can have 0...n appointments o
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
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 wrote:
>
> I was curious if anyone knows how you describe the following SQL
> functionality:
>
> SELECT 'mike' as name
>
> returns a single column n
> 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 d
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, am
I was curious if anyone knows how you describe the following SQL
functionality:
SELECT 'mike' as name
returns a single column named "name" with a single row containing "mike"
I also know you can do stuff like:
SELECT 1
Which returns a column named "1" with a single row containing "1", or:
S
A simple 'group by' clause ought to get you there.
With your first example, try this:
select id, max(name), max(date_due), max(date_modified)
from yourQry
group by id
order by date_modified desc
With your second example, with the composite key, try concatenating t
If you're not using SQL 2005, the following (slightly more complex) query
should simulate the RANK and PARTITION:
SELECT id, name, date_due, date_modified
FROM (
SELECT id, name, date_due, date_modified, (SELECT COUNT(id) FROM table T2 WHERE
T1.id = T2.id AND T2.date_modified >= T1.date_modified
> What database are you using?
We're still using SQL2000, so 2005 commands won't help me here...thanks...
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.double
Thanks, Gerald. This is close, except there is a bit of a problem. I need to
group this not by just one field but three fields, so the dataset actually
looks like this (fields 1 - 3 make up the unique key):
field 1 field 2 field 3 date_due date_modified
1 2 3 1/1/2009 11/18
Opps l messed up swap out "name" for ID in the WHERE
WHERE name IN ('SELECT DISTINCT name
SELECT TOP(date_due) as TOP_date_due, name, date_modified
FROM Table
WHERE name IN ('SELECT DISTINCT name
FROM Table
')
GROUP BY name, date_modified, date_due
ORDER BY da
SELECT id, name, date_due, date_modified
FROM SomeTable
WHERE
On Tue, Nov 18, 2008 at 3:38 PM, Sung Woo <[EMAIL PROTECTED]> wrote:
> Actually, your suggestion wouldn't work for the first set, either, as it
> would bring up 2 records for Smith and none for Woo.
>
>
I don't remember the syntax for the Sub query but something like this may
work:
SELECT TOP(date_due) as TOP_date_due, name, date_modified
FROM Table
WHERE ID IN ('SELECT DISTINCT name
FROM Table
')
GROUP BY name, date_modified, date_due
ORDER BY date_modified ,
What database are you using? In MS SQL 2005 you can use the RANK and PARTITION
keywords:
SELECT id, name, date_due, date_modified FROM (
SELECT id, name, date_due, date_modified, RANK() OVER (PARTITION BY id ORDER BY
date_modified DESC) AS userRank FROM table
) AS t2
WHERE t2.userRank <= 2
ORDER
Actually, your suggestion wouldn't work for the first set, either, as it would
bring up 2 records for Smith and none for Woo.
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Tr
Hi Rob,
That would work in this instance, but I need something a little more dynamic.
What if the dataset looked like this?
id name date_due date_modified
1 Woo 1/1/2009 11/18/2008 4:55PM
1 Woo 2/1/2009 11/18/2008 5:21PM
1 Woo 3/1/2009 11/18/2008 5:30PM
2 Smith 1/1/2009 11/18/200
select top 2 * from tables order by date asc
should get you there.
Rob
On Tue, Nov 18, 2008 at 5:00 PM, Sung Woo <[EMAIL PROTECTED]> wrote:
> There's probably a really simple answer to this. Here's the sample
> dataset:
>
> id namedate_duedate_modified
> 1 Woo 1/1/200
There's probably a really simple answer to this. Here's the sample dataset:
id namedate_duedate_modified
1 Woo 1/1/200911/18/2008 4:55PM
1 Woo 2/1/200911/18/2008 4:57PM
1 Woo 3/1/200911/18/2008 4:59PM
2 Smith 1/1/2009
database of ColdFusion errors at http://cferror.org/
-Original Message-
From: Scott Stewart
Sent: 29 October 2008 16:58
To: cf-talk
Subject: odd sql question
i have a table with a field (study_category.category_date) that is a
comma delimited list (2004,2005,2006,2007) stored as a NVARC
Scott Stewart wrote:
> i have a table with a field (study_category.category_date) that is a
> comma delimited list (2004,2005,2006,2007) stored as a NVARCHAR
>
> I need to see if a passed value is in the list:
>
> WHERE (#arguments.cat_date# in (study_category.category_date))
>
> arguments.cat_da
Pretty sure that's not going to work for you ... believe the argument on the
left of the IN has to be a column, not a variable.
In any case, your best bet is to have the category_date properly normalized out
into a separate table:
FROM study_category INNER JOIN
study_category_date ON study_cate
i have a table with a field (study_category.category_date) that is a
comma delimited list (2004,2005,2006,2007) stored as a NVARCHAR
I need to see if a passed value is in the list:
WHERE (#arguments.cat_date# in (study_category.category_date))
arguments.cat_date is a four digit number represen
Mark/Dave... thanks so much. That worked perfectly!
-Original Message-
From: Mark Kruger [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 19, 2008 10:19 AM
To: CF-Talk
Subject: RE: (ot) SQL question...
Che,
Well you could it inline... something like
Select sum(t.total) as total
Che,
Well you could it inline... something like
Select sum(t.total) as total, t.source
FROM
(
select count(*) as total, source
from listings
group by source
union all
select count(*) as total, source
from speclistings
group by source
union all
select count(*) as total, source
from psportl
BY source
ORDER BY source DESC
Sincerely,
Dave Phillips
http://www.dave-phillips.com
-Original Message-
From: Che Vilnonis [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 19, 2008 9:11 AM
To: CF-Talk
Subject: (ot) SQL question...
Hello all. I'm looking to output the total numb
Hello all. I'm looking to output the total number of entries, grouped by
source from 3 tables. I'd like to modify the sql below so that each source
shows up only once, yet tablulates the totals from all of the tables. Any
ideas. Thanks, Che.
---
select count(*) as total, source
fr
8:47 AM
To: CF-Talk
Subject: RE: Transact SQL question has me stumped
Dennis... Learn something new everyday. I guess I did not know there was
bit operator in CF :)
-Original Message-
From: Dennis Powers [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 05, 2008 1:11 PM
To: CF-Talk
Subjec
Dennis... Learn something new everyday. I guess I did not know there was
bit operator in CF :)
-Original Message-
From: Dennis Powers [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 05, 2008 1:11 PM
To: CF-Talk
Subject: RE: Transact SQL question has me stumped
Mark,
I knew I would
Jeff,
>> Ah, you changed the question :) You originally said AND, now it is OR!
I knew I was phrasing it incorrectly mixing Boolean and linguistic
"and"
>> WHERE myColumn & myMask > 0
This is much too easy and I can't believe I overlooked so simple a basic
Boolean solution. That sound you hear
Ah, you changed the question :) You originally said AND, now it is OR!
It doesn't change the problem much.
Step 1: Create your mask
Step 2: Zero out the bits we don't care about with
Step 3: If we are left with anything, we have records that contain a flag.
WHERE myColumn & myMask > 0
enjoy!
doh! That should be 2^0 + 2^7 (silly me)
> myMask = 2^1 + 2 ^8
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w
Archive
Jeff,
>> myMask = 2^1 + 2 ^8
>> SELECT mycolumns
>> FROM mytable
>> WHERE BigIntColumn & #myMask# = #myMask#
This was exactly what I was doing - irrespective of the CAST to change data
types - but it will only select records that have bit 1 AND Bit 8. What I
need to do is to select records th
Original Message-
From: Dennis Powers [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 05, 2008 2:02 PM
To: CF-Talk
Subject: RE: Transact SQL question has me stumped
>> You need a bitwise operator. Bit and is & in MS SQL
>> SELECT mycolumns
>> FROM mytable
>
I'm not Guru, but this should do it.
First you create a mask by turning on the bits you need.
myMask = 2^1 + 2 ^8
Then, bitwise AND (&) with your column. The result needs to be equal to your
mask to have all the specific bits turned on. NOTE: This assumes you don't care
about the value in the
>> You need a bitwise operator. Bit and is & in MS SQL
>> SELECT mycolumns
>> FROM mytable
>> WHERE bit_column & 128 = 128
This was essentially what I was doing but it does not work properly for
matching multiple bits in the "bit_column", Example: matching a row that
has Bit 1 and Bit 8 (129)
Original Message-
From: Dennis Powers [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 05, 2008 1:11 PM
To: CF-Talk
Subject: RE: Transact SQL question has me stumped
Mark,
I knew I would most likely not explain it properly. The existing database
has a column that contains values that ar
Mark,
I knew I would most likely not explain it properly. The existing database
has a column that contains values that are typed as a BigInt. I have a
filter that gets constructed from Bit values for example: the filter = 129
which was constructed from bit 1 binary + bit 8 Binary 1 + 128.
What
work :)
-Mark
-Original Message-
From: Dennis Powers [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 05, 2008 12:01 PM
To: CF-Talk
Subject: Transact SQL question has me stumped
I am hoping an SQL guru can assist me with what I am sure is a stupid little
oversight or misunderstanding on my p
I am hoping an SQL guru can assist me with what I am sure is a stupid little
oversight or misunderstanding on my part.
I hope I can explain this. I need to do a bit evaluation against data in the
database where the data is stored in a BigInit column. Within my code I
construct a bit filter and nee
I actually prefer to do this in the ORDER BY clause (keeping the ordering
logic in the ORDER BY instead of in the SELECT) but the end result is the
same. If you won't or can't add a sort column to the table, a CASE statement
is about the only other way to do this in the query itself.
On Jan 25, 20
Che Vilnonis wrote:
> I was trying to do that w/o adding another column. Can it be done?
Yes, see Crow's, Charlie's or my post on using CASE to create an inline
sort column with SQL.
~|
Adobe® ColdFusion® 8 software 8 is the m
Mark
-Original Message-
From: Che Vilnonis [mailto:[EMAIL PROTECTED]
Sent: Friday, January 25, 2008 1:41 PM
To: CF-Talk
Subject: OT: SQL Question -- Order by a column's value?
Suppose I have a small set of data with a column named "Colleges". Is
there a way to write an ORDER B
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 o
riday, January 25, 2008 12:55 PM
To: CF-Talk
Subject: Re: OT: SQL Question -- Order by a column's value?
Do you mean put them in a predifind order based on the college, other
than alphabetical?
If so, and if you have a lookup table for your colleges, you will have
to add a numerical column calle
There is no way to do it with pure SQL alone.
Dominic
Well actually you can do in pure SQL. SQL has code that can be used to create
dynamic columns and values on the fly in your record set and then one can order
on this set. It sort of depends on whether the desire order is permanent or
flex
Yes, you can do this using case statements in your order by:
example:
select * from viewoffers where [EMAIL PROTECTED]
order by
case status
when 'active' then 1
when 'rejected' then 2
else 99
end
Of course, this is really a kludge. The DB should be deisgned a little
better, but sometime
Che Vilnonis wrote:
> Suppose I have a small set of data with a column named "Colleges". Is there
> a way to write an ORDER BY statement to say something like...
> ORDER BY Colleges 'Harvard', Colleges 'Princeton', Colleges 'Dartmouth'???
>
> Just wondering... Che
If I understand your question cor
something like...
SELECT
Colleges,
CASE
when Colleges = 'Harvard' THEN 1
when Colleges = 'Princeton' THEN 2
when Colleges = 'Dartmouth' THEN 3
END AS collegeOrder
FROM
myTable
ORDER BY
collegeOrder
(not tested) :)
On Jan 25, 2008 10:41 AM, C
I was trying to do that w/o adding another column. Can it be done?
-Original Message-
From: Todd [mailto:[EMAIL PROTECTED]
Sent: Friday, January 25, 2008 1:50 PM
To: CF-Talk
Subject: Re: OT: SQL Question -- Order by a column's value?
Nope, add a sort_order column and sort your col
Sorry, add sort_order column and then do an ORDER BY sort_order and set all
the colleges in the appropriate sorting that you want it to be.
On Jan 25, 2008 1:49 PM, Todd <[EMAIL PROTECTED]> wrote:
>
> Nope, add a sort_order column and sort your colleges appropriately.
>
>
> On Jan 25, 2008 1:41 P
Do you mean put them in a predifind order based on the college, other than
alphabetical?
If so, and if you have a lookup table for your colleges, you will have to
add a numerical column called 'Ordinal' (or something else) with which you
can set their order. Then simply order by that in your SQL s
Nope, add a sort_order column and sort your colleges appropriately.
On Jan 25, 2008 1:41 PM, Che Vilnonis <[EMAIL PROTECTED]> wrote:
> Suppose I have a small set of data with a column named "Colleges". Is
> there
> a way to write an ORDER BY statement to say something like...
> ORDER BY Colleges
Suppose I have a small set of data with a column named "Colleges". Is there
a way to write an ORDER BY statement to say something like...
ORDER BY Colleges 'Harvard', Colleges 'Princeton', Colleges 'Dartmouth'???
Just wondering... Che
~
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
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 PM,
: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#
e-
From: 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#
[EMAIL PROTECTED]
Sent: Tuesday, January 15, 2008 1:21 PM
To: CF-Talk
Subject: SQL Question, get previous record
I have a basic table
It has a numeric primary key and a field that determines display order.
The records are displayed based on the record order. However the record
order may
I have a basic table
It has a numeric primary key and a field that determines display order.
The records are displayed based on the record order. However the record
order may not be continuous
(IE: 1, 2, 5, 7, 10)
The application calls for an up/down order change feature. What I need to b
Nuf said. I'll do it the right way.
-Original Message-
From: Ian Skinner [mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 09, 2008 1:12 PM
To: CF-Talk
Subject: Re: SOT sql question
Mark Fuqua wrote:
> I know I could do a another table with JobFileId's and Access levels,
Mark Fuqua wrote:
> I know I could do a another table with JobFileId's and Access levels, instead
> of a
> list of access levels, but it seems cleaner this way.
>
It is not. You have denormalized your data in such away that doing the
type of select you want to do is very difficult. If you
the current users access
level which is stored in #session.PlumUserRoles#
Any idea how I might do that?
Thanks,
Mark
-Original Message-
From: Charlie Griefer [mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 09, 2008 12:11 PM
To: CF-Talk
Subject: Re: SOT sql question
On Jan 9, 2008 8:
Mark,
I'll like to suggest you to use DataMgr. DataMgr is a great time saver tool
and could be the answer to your needs.
http://datamgr.riaforge.org/
Cheers
Marco Antonio C. Santos
On Jan 9, 2008 2:56 PM, Mark Fuqua <[EMAIL PROTECTED]> wrote:
> I think I need to be using 'IN' within my WHERE c
On Jan 9, 2008 8:56 AM, Mark Fuqua <[EMAIL PROTECTED]> wrote:
> I think I need to be using 'IN' within my WHERE clause but I can't seem to
> get it to work. I have a column with a comma delimited list. This is the
> latest attempt and it craps out too.
>
> SELECT..
> ...WHERE JobFileJob = #sessio
IN works the other way round I think - say you have a list of values and you
want to pull all records with any of those values:
WHERE name IN (will,john,ray)
-Original Message-
From: Mark Fuqua [mailto:[EMAIL PROTECTED]
Sent: 09 January 2008 16:57
To: CF-Talk
Subject: SOT sql question
I think I need to be using 'IN' within my WHERE clause but I can't seem to
get it to work. I have a column with a comma delimited list. This is the
latest attempt and it craps out too.
SELECT..
...WHERE JobFileJob = #session.jobId# AND '#session.UserRole#' IN
(JobFileAccessLevel)
Job
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
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!
wonde
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
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®
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
http://ad.doubleclic
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 field
'% ' 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 ');
ins
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 st
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
hey guys.
this is a 2 parter.
i have a table i need to fix. i have no real idea how to do this. the
problem is the Contact filed.
the first several thousand entries combine fnme & lname into this one field
from a xls file.
then maybe 20,000 entries only have a name with a space in the Contact
fi
> 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
1 - 100 of 1018 matches
Mail list logo
