> Go the other way don't tear apart the old flat table. Create a
> report from the new data that pulls the pieces back together into a
> de-normalized flat table. That should just be normal joins and a fairly
> simple query.
Hmmm... had not thought about it from that direction; the WHERE cl
C. Hatton Humphrey wrote:
> Anyone have any better suggestions for a solution?
Go the other way don't tear apart the old flat table. Create a
report from the new data that pulls the pieces back together into a
de-normalized flat table. That should just be normal joins and a fairly
simple
My first guess would be to look for triggers or circular foreign key
references. Often times people will declare a cascading delete on a foreign
key so when you delete the record, it deletes records in tables that are
related to it. But if one of those other tables has a foreign key that
points bac
: Re: SQL Question
Depends on your database. In sql server, it's an identity field, in
Oracle, you'd use a sequence and do mysequence.nextval.
Also, count is probably a reserved word in whatever database you're
using, so don't name your column that.
You might also be able
Depends on your database. In sql server, it's an identity field, in
Oracle, you'd use a sequence and do mysequence.nextval.
Also, count is probably a reserved word in whatever database you're
using, so don't name your column that.
You might also be able to do something like:
Insert into myTable(
PROTECTED]
> Sent: Monday, October 30, 2006 3:36 PM
> To: CF-Community
> Subject: RE: SQL Question
>
> Yep. I got this off the HOF SQL list:
>
>
> Select DateCreated, FirstName, LastName, Email
> from dbo.IBS_ShowLitRequest
> where coalesce( firstname,''
Whoops, I put the real db name in there but you get the idea.
-Original Message-
From: Duane [mailto:[EMAIL PROTECTED]
Sent: Monday, October 30, 2006 3:36 PM
To: CF-Community
Subject: RE: SQL Question
Yep. I got this off the HOF SQL list:
Select DateCreated, FirstName, LastName, Email
mail
>From dbo.IBS_ShowLitRequest
Where firstname is null and lastname is null and coalesce( email,'' ) <> ''
group by firstname, lastname, email
-Original Message-
From: Nick McClure [mailto:[EMAIL PROTECTED]
Sent: Monday, October 30, 2006 3:24 PM
To: CF
Which date do you want?
You aren't going to be able to do this easily because the email is distinct,
but the date is not.
> -Original Message-
> From: Duane [mailto:[EMAIL PROTECTED]
> Sent: Monday, October 30, 2006 2:29 PM
> To: CF-Community
> Subject: RE: SQL Ques
The Dreaded Union?
> -Original Message-
> From: Duane [mailto:[EMAIL PROTECTED]
> Sent: Monday, October 30, 2006 2:29 PM
> To: CF-Community
> Subject: RE: SQL Question
>
> That won't work. You have to include all the fields in your select
> statement
> in
That won't work. You have to include all the fields in your select statement
in your group by clause.
Duane
-Original Message-
From: William Bowen [mailto:[EMAIL PROTECTED]
Sent: Monday, October 30, 2006 3:17 PM
To: CF-Community
Subject: Re: SQL Question
Group By Email?
On 10/
Group By Email?
On 10/30/06, Duane <[EMAIL PROTECTED]> wrote:
> Hi All,
>
> I have a sql question for anyone willing to help me get my head unstuck.
>
>
> I have the following sample data:
>
> RegDate FirstName LastNameEmail
> 10/1/2006 NULLNULL[EMAIL PR
What about a CASE statement to insert a constant into the name fields if
they are blank? Then, you can use a distinct to ensure you return only one
email address for null names?
SOmething like:
Select DISTINCT
CASE WHEN FirstName is null THEN 1 ELSE table.firstName END AS FirstName,
CASE WHEN Las
Correct.
I've often pined for a "list" grouping functionwhich would have helped
you in this situation. SOmething like:
select emp_id, list(location_id) as location_ids
from table
where whatever
grouped by emp_id
Then the output would be:
Emp ID Location_ids
1
G Money wrote:
> How so?
>
> I still don't think he's going to get the values in a list form in a single
> column using just SQL.
>
> On 9/12/06, Andrew Scott <[EMAIL PROTECTED]> wrote:
>
>>left outer join would have worked better
left outer join would not have worked at all. Try it.
simple j
How so?
I still don't think he's going to get the values in a list form in a single
column using just SQL.
On 9/12/06, Andrew Scott <[EMAIL PROTECTED]> wrote:
>
> left outer join would have worked better
>
>
>
> On 9/12/06, Adkins, Randy <[EMAIL PROTECTED]> wrote:
> >
> > Yes
> >
> > Select users
left outer join would have worked better
On 9/12/06, Adkins, Randy <[EMAIL PROTECTED]> wrote:
>
> Yes
>
> Select users.uid, users.name, locations.location
> From users
> inner join UserLoc on UserLoc.UID = users.uid
> inner join locations on UserLoc.LocID = locations.Locid
>
>
> Did not test it
I was using asp.net repeaters, and couldn't figure the nesting out. There
were 4 columns in the output that had to be nested.
Duane
-Original Message-
From: Nick McClure [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 12, 2006 9:42 AM
To: CF-Community
Subject: RE: SQL Question
You could have nested a while loop in the output loop.
> -Original Message-
> From: Duane [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, September 12, 2006 8:19 AM
> To: CF-Community
> Subject: RE: SQL Question
>
> I ended up using cursors and creating a temp table o
I ended up using cursors and creating a temp table on the fly.
Duane
-Original Message-
From: Zaphod Beeblebrox [mailto:[EMAIL PROTECTED]
Sent: Monday, September 11, 2006 10:20 PM
To: CF-Community
Subject: Re: SQL Question
you might be able to leverage the coalesce function like this
you might be able to leverage the coalesce function like this article:
http://www.sqlteam.com/item.asp?ItemID=2368
On 9/11/06, Duane <[EMAIL PROTECTED]> wrote:
> Hi All,
>
> Is there anyway within an sql query to return a sub query that has multiple
> records? Ex:
>
> Users:
> UID Name
> 1
Duane wrote:
>
> I'm trying to get around some pain in the ass stuff using the asp repeater
> control.
ASP? Ouch, there's your problem ;)
~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-da
I wish I could use CF for this :(
I'm trying to get around some pain in the ass stuff using the asp repeater
control.
Duane
-Original Message-
From: Rick Root [mailto:[EMAIL PROTECTED]
Sent: Monday, September 11, 2006 6:18 PM
To: CF-Community
Subject: Re: SQL Question
Duane
Adkins, Randy wrote:
>
> Select users.uid, users.name, locations.location
> From users
> inner join UserLoc on UserLoc.UID = users.uid
> inner join locations on UserLoc.LocID = locations.Locid
>
> Did not test it but something like that
It wouldn't work. It would return multiple rows for each
Duane wrote:
>
> Is it possible to return a record set that looks like:
> UID NameLocation
> 1 FredMain Office, Cafeteria
> 2 Bob Gymnasium
> 3 MaryCafeteria
Not that I'm aware of. You'd have to either build a stored procedure or
return the data like this:
UID
Yes
Select users.uid, users.name, locations.location
>From users
inner join UserLoc on UserLoc.UID = users.uid
inner join locations on UserLoc.LocID = locations.Locid
Did not test it but something like that
-Original Message-
From: Duane [mailto:[EMAIL PROTECTED]
Sent: Monday, Septe
d to be "Myrtle Beach
AFB FCU".
Military has a lot of credit unions.
- Matt
- Original Message -
From: "Jerry Johnson" <[EMAIL PROTECTED]>
To: "CF-Community"
Sent: Monday, April 03, 2006 3:14 PM
Subject: Re: Tim's House closing was (RE: SQL Question
bject: Re: Tim's House closing was (RE: SQL Question, Switch Case )
Does anyone know if this also used to be called the Naval Air Federal
Credit Union?
I once built a set of kiosks that functioned as mulitmedia-rich ATMs
without the money slot for the NAFCU.
On 4/3/06, Scott Stewart <[EM
Does anyone know if this also used to be called the Naval Air Federal
Credit Union?
I once built a set of kiosks that functioned as mulitmedia-rich ATMs
without the money slot for the NAFCU.
On 4/3/06, Scott Stewart <[EMAIL PROTECTED]> wrote:
> I've got all of my accounts and two mortgages thro
day, April 03, 2006 2:44 PM
To: CF-Community
Subject: Re: Tim's House closing was (RE: SQL Question, Switch Case )
Yep... I'm buying a new car and I'm going to try them (I've been a member
since 1990). They have fantastic rates. Also, they are the largest credi
F-Community"
Sent: Monday, April 03, 2006 2:06 PM
Subject: RE: Tim's House closing was (RE: SQL Question, Switch Case )
> If this one goes under, try Navy Federal Credit Union. You should be
> eligible under vet status. They're fast and will work with you.
>
> Glad to
2:25 PM
To: CF-Community
Subject: RE: Tim's House closing was (RE: SQL Question, Switch Case )
Can't do the VA thing, too much money, or this shit would have been dealt
with already.
--
Timothy Heald
Analyst, Architect, Developer
[EMAIL PROTECTED]
W: 202-228-8372
C: 703-300-3911
---
06 PM
To: CF-Community
Subject: RE: Tim's House closing was (RE: SQL Question, Switch Case )
If this one goes under, try Navy Federal Credit Union. You should be
eligible under vet status. They're fast and will work with you.
Glad to know that the insurance came through..
Thanks
puter.
-Original Message-
From: Loathe [mailto:[EMAIL PROTECTED]
Sent: Monday, April 03, 2006 2:01 PM
To: CF-Community
Subject: RE: Tim's House closing was (RE: SQL Question, Switch Case )
It didn't.
We lost our mortgage 2 hours before the closing.
Going with a new company now.
300-3911
>-Original Message-
>From: Scott Stewart [mailto:[EMAIL PROTECTED]
>Sent: Monday, April 03, 2006 10:12 AM
>To: CF-Community
>Subject: Tim's House closing was (RE: SQL Question, Switch Case )
>
>Thanks Tim,
>
>How'd the closing go?
>
>
#x27;s House closing was (RE: SQL Question, Switch Case )
Thanks Tim,
How'd the closing go?
Thanks
Scott
Scott A. Stewart
Webmaster/ Developer
GlobalNet Services, Inc
http://www.gnsi.com
11820 Parklawn Dr
Rockville, MD 20852
Voice: (301) 770-9610 x 335
Fax: (301) 770-9611
The info
>We don't know. I was trying to see if it was even possible before
>attempting.
I very much doubt it is going to help you. Stored procedures are faster because
they are preplanned and precompiled, but with all the LIKE statements it is
pretty much impossible to use a partial indexscan on the tab
ictly
prohibited. If you have received this communication in error, please notify
us immediately by replying to the message and deleting it from your
computer.
-Original Message-
From: Loathe [mailto:[EMAIL PROTECTED]
Sent: Sunday, April 02, 2006 9:10 PM
To: CF-Community
Subject: RE: SQL Que
2006 6:47 AM
To: CF-Community
Subject: Re: SQL Question, Switch Case
> Here's what I have, it was written by another developer but I've been
tasked
> with trying to make it run faster
>
>
> SELECT top 100
> INVN AS EntityId, ...
> FRO
Yeah. I do something like this sometimes:
Select *
from table
Where 1 = 1
and this = that
and that = this
I use where 1 = 1 sp I don'
> Here's what I have, it was written by another developer but I've been tasked
> with trying to make it run faster
>
>
> SELECT top 100
> INVN AS EntityId, ...
> FROM ENTITY_ALL
> WHERE 1 = 1
>
> AND
>
>
> ENTITY_ALL.INVN LIKE '%#attributes.Keywords#%'
>
.
>
> ((ENT
You can do something like the code below. The default case (for all) cannot
be handled by a case statement, sot it has to be handled by a separate query
(chosen by an if statement). Also, I am not on MS-SQL server any more
(currently using oracle) and I am not sure if I remember the concatenation
54 PM
> To: CF-Community
> Subject: RE: SQL Question, Switch Case
>
> Chris,
>
> Here's what I have, it was written by another developer but I've been
> tasked
> with trying to make it run faster
>
>
> SELECT top 100
> INVN AS EntityId,
&
cipient, you are hereby notified that any
dissemination, distribution, or copying of this communication is strictly
prohibited. If you have received this communication in error, please notify
us immediately by replying to the message and deleting it from your
computer.
-Original Message-
From:
Well it *would* work but its will probably not give you what you are looking
for in the format you have it (assumption on my part). Basically what the
where clause is saying is where @someVar = (the result of the case
statement)
So assuming in your example that @someVar is coming in with a value o
fy
us immediately by replying to the message and deleting it from your
computer.
-Original Message-
From: Chris Stoner [mailto:[EMAIL PROTECTED]
Sent: Friday, March 31, 2006 12:51 PM
To: CF-Community
Subject: Re: SQL Question, Switch Case
Or to more directly tie in with your questio
Or to more directly tie in with your question:
SELECT*
FROMMY_FAKE_TABLE
WHEREFAKE_COLUMN = CASE
WHEN FAKE_COLUMN_2 = 'A' THEN @ARGUMENT_A
WHEN FAKE_COLUMN_2 = 'B' THEN @ARGUMENT_B
ELSE @ARGUMENT_DEFAULT
END
On 3/31/06, Chris Stoner <[EMAIL
Yes to a point...such as you can do something like my example below (not
tested,obviously). The issue you will run into is that you wont be able to
have a where clause with multiple filters. You basically can use the case
statement to equate a specific column to another column depending on the
co
Supercool!
That worked to get the multiple (> 2) records located.
Thanks for you help!!!
On 7/29/05, Deanna Schneider <[EMAIL PROTECTED]> wrote:
> Try this.
>
> SELECT contentid
> FROM mytable
> GROUP BY contentid
> Having count(contentid) = 2
>
>
>
> On 7/28/05, William Bowen <[EMAIL PROTE
select content_id, count(*) as cnt
from your_table
group by content_id
having cnt > 1
On 7/28/05, William Bowen <[EMAIL PROTECTED]> wrote:
> there must be a way to do this but I just can't think of it...
>
> I've got a Boundary table that holds the list of tree nodes for a web site.
>
> the
Try this.
SELECT contentid
FROM mytable
GROUP BY contentid
Having count(contentid) = 2
On 7/28/05, William Bowen <[EMAIL PROTECTED]> wrote:
> there must be a way to do this but I just can't think of it...
>
> I've got a Boundary table that holds the list of tree nodes for a web site.
>
> the
Thank you for noticing. :)
On 7/7/05, Loathe <[EMAIL PROTECTED]> wrote:
> You sir are a sexy biotch :)
>
> -Original Message-
> From: Marlon Moyer [mailto:[EMAIL PROTECTED]
> Sent: Thursday, July 07, 2005 10:53 PM
> To: CF-Community
> Subject: Re: SQL Ques
You sir are a sexy biotch :)
-Original Message-
From: Marlon Moyer [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 07, 2005 10:53 PM
To: CF-Community
Subject: Re: SQL Question
I had a little extra code in the previous one.
insert into application_skills
SELECT AS candidate_id
I had a little extra code in the previous one.
insert into application_skills
SELECT AS candidate_id,
AS
skill_id,
as
proficiency_id,
skillType_id
FROMcomputerSkills_lookup where skill_id =
On 7/7/05, Marlon Moyer <[EMAIL PROTECTED]> wro
insert into application_skills
SELECT AS candidate_id,
AS
skill_id,
as
proficiency_id,
skillType_id
FROM(select skillType_id from computerSkills_lookup where skill_id =
) skills
On 7/7/05, Loathe <[EMAIL PROTECTED]> wrote:
> Is there anywa
ke
any sense to me and is forcing me to do something I don't want to. This
schema is mainly taken from a client's DB and some of it isn't exactly
normalized :)
Tim
-Original Message-
From: Tony Weeg [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 07, 2005 10:37 PM
To:
i guess its a dumb question but... you dont/cant know that value
prior to the query running?
im not sure ive EVER seen a select statement
comingling with an insert statement in one operation.
tw
On 7/7/05, Loathe <[EMAIL PROTECTED]> wrote:
> Oh,
>
> It's SQL Server 2000 btw.
>
> -Original
Oh,
It's SQL Server 2000 btw.
-Original Message-
From: Loathe [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 07, 2005 10:29 PM
To: CF-Community
Subject: SQL Question
Is there anyway to make something like this work:
insert into application_skills
(
something like this should work.:
SELECT user_id,
(SELECT count(*)
FROM uploads ul
WHERE ul.user_id = u.user_id
) AS uploads_made
FROM users u
ORDER BY uploads_made DESC
-Original Message-
From: Greg Creedon [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, August 21, 2001
What DB are you using ?
I think you can use
Select TOP N
in SQL server?
For Oracle you'd need something like
Select * from
(select a.firstname, b.lastname, count(*) AS uploads
FROM people a, uploads b
WHERE a.person_id=b.person_id
Group BY a.firstnam
> I have a general "how do you do that" SQL question.
>
> I have a table with links in it. One link per row, with URL link
> name, etc.
>
> The client wants to be able to specify the order in which the
> links appear.
> In other words, they want to set any link to show up first, another other
> l
What I do is allow the client to specify the sequence number. I inform
them that if they use the same sequence number twice then the URLs will
appear together, but the order in which they are displayed is not
guaranteed.
What I would say is that you could always provide a multi-select field
whic
62 matches
Mail list logo
