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
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
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):
Just read the original post properly - please ignore me.
Dominic
2009/11/15 Dominic Watson watson.domi...@googlemail.com
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,
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
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
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 data
I'm
]
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
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
Subject: RE: simple SQL Question
That is a left outer join. It's mixing new and old styles of joining tables.
Not sure if there's a benefit to the mix, but I reckon this is clearer:
SELECT a.id, b.name
FROM a
INNER JOIN b ON a.id = b.id
LEFT OUTER JOIN b ON a.id = b.id
Is this code actually
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
?
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.name
from a
inner join b
on a.id = b.id
where a.id
*= is actually used at my work place. Thanks for the answer.
From: Adrian Lynch cont...@adrianlynch.co.uk
To: cf-talk cf-talk@houseoffusion.com
Sent: Friday, August 21, 2009 5:59:17 PM
Subject: RE: simple SQL Question
That is a left outer join. It's mixing
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
@first_appointment_per_day tmp1
inner join @appointment app on tmp1.patient_id = app.patient_id
and tmp1.appointment_date = app.appointment_date
~Brad
Original Message
Subject: Semi-OT: SQL question...Select first item for each person for
each day
From: Judah McAuley ju...@wiredotter.com
Date
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
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
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
.appointment_date = app.appointment_date
~Brad
Original Message
Subject: Semi-OT: SQL question...Select first item for each person for
each day
From: Judah McAuley ju...@wiredotter.com
Date: Tue, February 24, 2009 1:53 pm
To: cf-talk cf-talk@houseoffusion.com
What I need
app on tmp1.patient_id = app.patient_id
and tmp1.appointment_date = app.appointment_date
~Brad
Original Message
Subject: Semi-OT: SQL question...Select first item for each person for
each day
From: Judah McAuley ju...@wiredotter.com
Date: Tue, February 24
app on tmp1.patient_id = app.patient_id
and tmp1.appointment_date = app.appointment_date
~Brad
Original Message
Subject: Semi-OT: SQL question...Select first item for each person for
each day
From: Judah McAuley ju...@wiredotter.com
Date: Tue, February 24, 2009 1:53
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
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:
SELECT
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
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
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
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/2009
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
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
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
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
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
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 ,
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
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 =
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
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
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_date is a
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 NVARCHAR
I need to see if a passed
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
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 number
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
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
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
-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
Subject: RE: Transact
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
-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 part.
I hope I
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
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 are typed as a BigInt
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) or
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
-
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
WHERE bit_column 128 = 128
This was essentially what I
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
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 that
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!
Jeff,
Ah, you changed the question :) You originally said AND, now it is OR!
I knew I was phrasing it incorrectly smile 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 heard
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,
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,
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 colleges
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
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
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
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 PM, Che
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
-
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 BY statement to say something like...
ORDER
, 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 called 'Ordinal
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
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
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
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
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
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
: 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
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)
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
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 =
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
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:56 AM, Mark Fuqua [EMAIL
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 had
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,
instead
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
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
: Re: SOT: SQL Question
select t.order_num, t.product_name, t.datetime_created
from test t,
(
select order_num, max(datetime_created) maxdt
from test
group by order_num
) t2
where t2.order_num = t.order_num
and t2.maxdt = t.datetime_created
1 query, but 2 selects = gets what you want
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
1 - 100 of 1004 matches
Mail list logo
