Re: (ot) SQL Question - flattening data

2009-11-15 Thread Dominic Watson
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 >

Re: (ot) SQL Question - flattening data

2009-11-15 Thread 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 rick,TR rick,HS joe,AL joe,FU Bob,FM And then use cfoutput with query and group (a very rough output here):

RE: (ot) SQL Question - flattening data

2009-11-14 Thread Dave Phelan
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

Re: (ot) SQL Question - flattening data

2009-11-13 Thread Rick Root
>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

RE: (ot) SQL Question - flattening data

2009-11-13 Thread DURETTE, STEVEN J (ATTASIAIT)
] 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

RE: (ot) SQL Question - flattening data

2009-11-13 Thread Dave Phelan
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

(ot) SQL Question - flattening data

2009-11-13 Thread Rick Root
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

Re: Semi-OT: SQL question...Select first item for each person for each day

2009-02-24 Thread Judah McAuley
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

Re: Semi-OT: SQL question...Select first item for each person for each day

2009-02-24 Thread Dan Baughman
-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

Re: Semi-OT: SQL question...Select first item for each person for each day

2009-02-24 Thread Judah McAuley
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 &

Re: Semi-OT: SQL question...Select first item for each person for each day

2009-02-24 Thread Judah McAuley
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

Re: Semi-OT: SQL question...Select first item for each person for each day

2009-02-24 Thread Brian Peddle
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 >

Re: Semi-OT: SQL question...Select first item for each person for each day

2009-02-24 Thread Ben Conner
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

RE: Semi-OT: SQL question...Select first item for each person for each day

2009-02-24 Thread brad
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

Semi-OT: SQL question...Select first item for each person for each day

2009-02-24 Thread Judah McAuley
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

RE: (ot) SQL question...

2008-08-19 Thread Che Vilnonis
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

RE: (ot) SQL question...

2008-08-19 Thread Mark Kruger
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

RE: (ot) SQL question...

2008-08-19 Thread Dave Phillips
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

(ot) SQL question...

2008-08-19 Thread Che Vilnonis
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

Re: OT: SQL Question -- Order by a column's value?

2008-01-26 Thread Brian Kotek
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

Re: OT: SQL Question -- Order by a column's value?

2008-01-25 Thread Ian Skinner
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

RE: OT: SQL Question -- Order by a column's value?

2008-01-25 Thread Dawson, Michael
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

Re: OT: SQL Question -- Order by a column's value?

2008-01-25 Thread Ian Skinner
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

Re: OT: SQL Question -- Order by a column's value?

2008-01-25 Thread Crow T. Robot
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

Re: OT: SQL Question -- Order by a column's value?

2008-01-25 Thread Ian Skinner
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

Re: OT: SQL Question -- Order by a column's value?

2008-01-25 Thread Charlie Griefer
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

RE: OT: SQL Question -- Order by a column's value?

2008-01-25 Thread Che Vilnonis
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

Re: OT: SQL Question -- Order by a column's value?

2008-01-25 Thread Todd
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

Re: OT: SQL Question -- Order by a column's value?

2008-01-25 Thread Dominic Watson
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

Re: OT: SQL Question -- Order by a column's value?

2008-01-25 Thread Todd
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

OT: SQL Question -- Order by a column's value?

2008-01-25 Thread Che Vilnonis
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 ~

Re: OT: SQL Question

2007-04-02 Thread Jerry Barnes
Thanks for your replies. I didn't get a chance to play with anything this afternoon due to meetings. I'll try to implement something tommorrow and fill you all in on the results. The recordsets aren't that big. About 9k records in one table and 3k in the other.

Re: OT: SQL Question

2007-04-02 Thread Jochem van Dieten
Jerry Barnes wrote: > The following query is slow. I'd like to speed it up a bit. Any > suggestions would be appreciated. > > > SELECT >F.pid, >F.acrostic, >F.recid, >F.recordthread, >F.aed_onset, >F.d_form > FROM >vfrm

Re: OT: SQL Question

2007-04-02 Thread Dean Lawrence
Has the i_recid field in the v_sae_jna_mr table been indexed? If your table is very large, this could slow your performance. Dean -- __ Dean Lawrence, CIO/Partner Internet Data Technology 888.GET.IDT1 ext. 701 * fax: 888.438.4381 http://www.idatatech.com/ C

RE: OT: SQL Question

2007-04-02 Thread Bader, Terrence C CTR MARMC, 231
: SQL Question How about something like this: SELECT F.pid, F.acrostic, F.recid, F.recordthread, F.aed_onset, F.d_form FROM vfrm_sae F WHERE NOT EXISTS (SELECT 1 FROM v_sae_jna_mr WHERE recid=F.recordthread) Not sure it'll be much faster, but it's worth a try. Ch

Re: OT: SQL Question

2007-04-02 Thread Kris Jones
How about something like this: SELECT F.pid, F.acrostic, F.recid, F.recordthread, F.aed_onset, F.d_form FROM vfrm_sae F WHERE NOT EXISTS (SELECT 1 FROM v_sae_jna_mr WHERE recid=F.recordthread) Not sure it'll be much faster, but it's worth a try. Cheers, Kris > The following

OT: SQL Question

2007-04-02 Thread Jerry Barnes
The following query is slow. I'd like to speed it up a bit. Any suggestions would be appreciated. SELECT F.pid, F.acrostic, F.recid, F.recordthread, F.aed_onset, F.d_form FROM vfrm_sae F LEFT OUTER JOIN v_sae_jna_mr M ON

OT: sql question

2007-02-01 Thread Tim Do
I have a stored procedure that is taking 15-20 seconds to run. However, if I take the query inside the stored procedure and run it... it only takes 2-3 seconds. I've read something about parameter sniffing but not sure if it applies to my stored procedure. Here is my sp: CREATE PROC dbo.get_

Re: OT: SQL question

2006-11-30 Thread Jochem van Dieten
Chad Gray wrote: > > I am basically doing a query on a table of catalogs and elements > inside of the catalog. Each element has a history of status changes > (new, in progress, done etc). So each element always has a corresponding value in the history table. > SELECT c.CatalogID, c.CatalogNa

OT: SQL question

2006-11-30 Thread Chad Gray
I am not a MS SQL guru and this bit of SQL is about as advanced as I get. I am basically doing a query on a table of catalogs and elements inside of the catalog. Each element has a history of status changes (new, in progress, done etc). I want to get the most current status for the element fro

OT: SQL Question

2006-03-04 Thread Jennifer Gavin-Wear
using a "between": where getdate() between tbl.fromdate and tbl.todate i'm getting a problem using the getdate, says it expects a column whereas i want to use the getdate any ideas anyone please? thanks, Jenny -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.

OT: SQL Question. Compare one list to another

2005-12-05 Thread John Wilker
I've got a table with a column that holds a comma seperated list of keywords. I'm just not seeing the solution right now. I've got a keyword search where the user can put in a comma seperated list of keywords. I need the query to search the DB to see if any of the keywords match the keywords in th

RE: OT SQL question-SOLVED

2005-02-08 Thread Eric Creese
danke! -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 08, 2005 2:12 PM To: CF-Talk Subject: Re: OT SQL question Eric Creese wrote: > I want to verify email addresses that are entered into one of my apps. > Unfortunately I already inh

Re: OT SQL question

2005-02-08 Thread Jochem van Dieten
Eric Creese wrote: > I want to verify email addresses that are entered into one of my apps. > Unfortunately I already inherited close to 100k email address. So I want to > do the following in SQL via a stored procedure so I can write the bad > addresses out to an error table. Need to check if th

RE: OT SQL question

2005-02-08 Thread Eric Creese
Thanks but I also need to try to test the TDLs like .com, .net, .uk... -Original Message- From: Qasim Rasheed [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 08, 2005 1:19 PM To: CF-Talk Subject: Re: OT SQL question I think you can write a UDF to validate email addresses. Here is

Re: OT SQL question

2005-02-08 Thread Qasim Rasheed
I think you can write a UDF to validate email addresses. Here is link http://vyaskn.tripod.com/handling_email_addresses_in_sql_server.htm On Tue, 8 Feb 2005 12:55:42 -0600, Eric Creese <[EMAIL PROTECTED]> wrote: > I want to verify email addresses that are entered into one of my apps. > Unfortu

OT SQL question

2005-02-08 Thread Eric Creese
I want to verify email addresses that are entered into one of my apps. Unfortunately I already inherited close to 100k email address. So I want to do the following in SQL via a stored procedure so I can write the bad addresses out to an error table. Need to check if there is an @ sign, if the TD

Re: OT: Sql question

2005-02-04 Thread Umer Farooq
Well.. oneway to do it is to create a new relationship table.. i.e tblRelatedTrails > relationID > trailID > relatedTrailID and when doing a select for review you can do a sub select on the relatedTrails table.. and use IN() anotherway is to use the geo info of the trails.. and sele

RE: OT: Sql question

2005-02-04 Thread John Munyan
From: Umer Farooq [mailto:[EMAIL PROTECTED] Sent: Fri 2/4/2005 12:54 PM To: CF-Talk Subject: Re: OT: Sql question John Munyan wrote: > I have a question about how a relationship would be best modeled in SQL. > Currently I have a hiking website, which hosts trail r

Re: OT: Sql question

2005-02-04 Thread Umer Farooq
John Munyan wrote: > I have a question about how a relationship would be best modeled in SQL. > Currently I have a hiking website, which hosts trail reviews. People can add > their own comments which are associated with the hike. > > For instance maybe I hiked snow lake on 12/1/05 and also 6/

OT: Sql question

2005-02-04 Thread John Munyan
I have a question about how a relationship would be best modeled in SQL. Currently I have a hiking website, which hosts trail reviews. People can add their own comments which are associated with the hike. For instance maybe I hiked snow lake on 12/1/05 and also 6/1/05. However, the user rev

Re: ot: sql question

2004-09-22 Thread Tony Weeg
ED]> > Date: Wed, 22 Sep 2004 09:59:03 -0400 > Subject: ot: sql question > To: CF-Talk <[EMAIL PROTECTED]> > > is it true that we cannot use go in sql statements using cfquery? > > -- > tony > > Tony Weeg > > macromedia cer

Re: ot: sql question

2004-09-22 Thread Qasim Rasheed
As far as I know you cannot. - Original Message - From: Tony Weeg <[EMAIL PROTECTED]> Date: Wed, 22 Sep 2004 09:59:03 -0400 Subject: ot: sql question To: CF-Talk <[EMAIL PROTECTED]> is it true that we cannot use go in sql statements using cfquery? -- tony Tony Weeg

ot: sql question

2004-09-22 Thread Tony Weeg
is it true that we cannot use go in sql statements using cfquery? -- tony Tony Weeg macromedia certified cold fusion developer email: tonyweeg [at] gmail [dot] com blog: http://www.revolutionwebdesign.com/blog/ cool tool: http://www.antiwrap.com [Todays Threads] [This Message] [Subscriptio

OT: SQL question. Someone help!!!

2004-04-16 Thread Jeff Waris
I am having a hard time finding the right syntax in SQL. Backend Database is SQL Server 7 What I am trying to acomplish. I have some duplicate records that I need to get rid of, the problem is that all these records already have a unique key, but the rest of the fields are the same. I was hoping

ot: sql question

2004-02-23 Thread Tony Weeg
hi there. say I have a database, mssql. and I know 1 column that should be unique, and actually I have duplicates in there. is there a query that I can write that will select them all, find the duplicates and remove only 1 of the duplicate records? thanks. ...tony tony weeg senior web applica

RE: ot: sql question

2003-10-01 Thread Tony Weeg
ieten [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 11:10 AM To: CF-Talk Subject: Re: ot: sql question Tony Weeg wrote: > how would I use HAVING to select out where the difference between > reportsInDatabase and DistinctTimes is above 15% > select r.IpAddressNumb

Re: ot: sql question

2003-10-01 Thread Jochem van Dieten
Tony Weeg wrote: > how would I use HAVING to select out where the difference between > reportsInDatabase and DistinctTimes is above 15% > select r.IpAddressNumber, v.VehicleIp, > Count(r.ReportId) as ReportsInDatabase, > Count(DISTINCT Time) as DistinctTimes, v.VehicleName, c.companyName > fro

ot: sql question

2003-10-01 Thread Tony Weeg
how would I use HAVING to select out where the difference between reportsInDatabase and DistinctTimes is above 15% im stuck and its just not clicking this morningthanks if you can figure this out select r.IpAddressNumber, v.VehicleIp, Count(r.ReportId) as ReportsInDatabase, Count(DISTI

OT: SQL Question

2003-09-26 Thread Cutter (CF-Talk)
I know this is a little off list but I'm stumped, so if some guru out there could please give me a hand...I've got two tables, tblDates and tblEnt. tblEnt uses the intDateID field of tblDates. I am trying to pull the next 7 days where there is entertainment scheduled. tblEnt has a row for each day,

Re: OT: SQL Question

2003-08-14 Thread Stephen Hait
> I have a ColdFusion app that is dynamically managing a SQL Server 2K > database. What I am running into is the need to change a column > data type - specifically an ntext data type. Anybody got any > suggestions? > > It appears that I cannot used ALTER TABLE/COLUMN with an ntext data > type,

OT: SQL Question

2003-08-14 Thread Jeff Chastain
I have a ColdFusion app that is dynamically managing a SQL Server 2K database. What I am running into is the need to change a column data type - specifically an ntext data type. Anybody got any suggestions? It appears that I cannot used ALTER TABLE/COLUMN with an ntext data type, so what other

OT sql question (xp_cmdshell)

2003-07-16 Thread Tim Do
Hello All, I'm trying to use xp_cmdshell to run a dts job. This is what I have: exec master..xp_cmdshell "DTSRun /S ServerName /U Username /P Password /N [Load pr_Labor Table]" but getting this error: Error string: The specified DTS Package ('Name = '[Load pr_Labor Table]'; ID.VersionID =

Re: OT SQL Question

2003-03-03 Thread Stephen Hait
> Can someone assist me with a quick SQL statement. > > I have a table with 3 columns: A, B, C (A would be the primary key) > I need to swap the values in column B with the values in column C > and vice versa. This should work with MS SQL assuming columns b and c are the same data type: UPDATE

RE: OT SQL Question

2003-03-03 Thread Cantrell, Adam
TECTED] > Sent: Monday, March 03, 2003 8:36 AM > To: CF-Talk > Subject: OT SQL Question > > > Can someone assist me with a quick SQL statement. > > I have a table with 3 columns: A, B, C (A would be the primary key) > I need to swap the values in column B with the val

RE: OT SQL Question

2003-03-03 Thread webguy
yeap actually simplier... INSERT t1 SELECT a as a , b as c ,c as b FROM t1 TEST b4 you do it WG -Original Message- From: Tangorre, Michael [mailto:[EMAIL PROTECTED] Sent: 03 March 2003 14:44 To: CF-Talk Subject: RE: OT SQL Question SQL 7 does this still apply? -Original

RE: OT SQL Question

2003-03-03 Thread Tangorre, Michael
SQL 7 does this still apply? -Original Message- From: webguy [mailto:[EMAIL PROTECTED] Sent: Monday, March 03, 2003 9:42 AM To: CF-Talk Subject: RE: OT SQL Question SQL server ? use a format like this.. INSERT author_sales EXECUTE (' SELECT ''EXEC STRING&#

RE: OT SQL Question

2003-03-03 Thread webguy
ON titleauthor.title_id = titles.title_id INNER JOIN sales ON titles.title_id = sales.title_id WHERE authors.au_id like ''8%'' GROUP BY authors.au_id, authors.au_lname ') wg -Original Message- From: Tangorre, Michael [mailto:[EMAIL PROTECTED] Sent: 03 Mar

OT SQL Question

2003-03-03 Thread Tangorre, Michael
Can someone assist me with a quick SQL statement. I have a table with 3 columns: A, B, C (A would be the primary key) I need to swap the values in column B with the values in column C and vice versa. TIA, Mike ~| Archiv

RE: OT: SQL Question

2002-10-10 Thread Tony Carcieri
Thanks Everyone! I appreciate the help! Thanks, Tony -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 09, 2002 7:26 PM To: CF-Talk Subject: Re: OT: SQL Question Tony Carcieri wrote: > Hi all, > > Here's what I want t

Re: OT: SQL Question

2002-10-09 Thread Jochem van Dieten
Tony Carcieri wrote: > Hi all, > > Here's what I want to do: > UPDATE tablename > SET column = 0 > WHERE ID = ??? > > I want to specify a range of numbers (like 100-200) and increment it by 2. > So, 100, 102,104etc would only be updated and the rest wouldn't. WHERE ID BETWEEN 100 AN

OT: SQL Question

2002-10-09 Thread Tony Carcieri
Hi all, Here's what I want to do: UPDATE tablename SET column = 0 WHERE ID = ??? I want to specify a range of numbers (like 100-200) and increment it by 2. So, 100, 102,104etc would only be updated and the rest wouldn't. Any ideas? Thanks, T

RE: OT: SQL question

2002-08-01 Thread Alistair Davidson
o:[EMAIL PROTECTED]] Sent: 29 July 2002 21:27 To: CF-Talk Subject: Re: OT: SQL question Great. You did not provide your database or setup. I can only assume you are working on a mainframe hitting DB2. On Mon, 29 Jul 2002, Phillip B wrote: > I need to do this and don't know where to start

Re: OT: SQL question

2002-07-29 Thread Alex
Great. You did not provide your database or setup. I can only assume you are working on a mainframe hitting DB2. On Mon, 29 Jul 2002, Phillip B wrote: > I need to do this and don't know where to start. > > Compare part of a part number in one table to another table. The part numbers look >like

OT: SQL question

2002-07-29 Thread Phillip B
I need to do this and don't know where to start. Compare part of a part number in one table to another table. The part numbers look like this. 12345.123 12345.234 12345.654 02nt911dc.123 02nt911dc.054 02thq5 02thq7 The part before the dot is all I want to use to compare to the other table whic

OT: SQL Question

2002-04-29 Thread Bud
Howdy all. I have a client that wants me to build him a reporting section based on the order month. I know how to do a SQL to get like the total of orders for a particular customer. SELECT Cust_ID, SUM(Order_Total) AS Total FROM Orders GROUP BY Cust_ID But, is there a way to group on the month

RE: OT: SQL Question (Access) the solution

2001-03-30 Thread Mike Kear
egory, HNTQuestion.QuestionShort Bob -Original Message- From: Michael Kear [mailto:[EMAIL PROTECTED]] Sent: March 28, 2001 5:33 PM To: CF-Talk Subject: OT: SQL Question (Access) I'm sorry if this is off-topic, but I'm hoping for some help from people who know more about SQL than I

OT: SQL Question (Access)

2001-03-28 Thread Michael Kear
I'm sorry if this is off-topic, but I'm hoping for some help from people who know more about SQL than I do ... I have a hints'n'tips section on one of my sites, and it has 3 tables - tblCategories, tblQuestions and tblAnswers. I want to have an index page that lists the questions in their categ

Re: OT: SQL Question

2001-01-17 Thread Bud
On 1/16/01, Gieseman, Athelene penned: > > INSERT X_Invoices (Vendor, Inv_GL, Inv_City, Inv_Dept, Inv_Date, >Inv_No, Inv_Desc, ApprovedDate, Notes, Inv_Amount) > values ('#Vendor#', '#Inv_GL#', '#Inv_City#', '#Inv_Dept#', >'#Inv_Date#', '#Inv_No#', '#Inv_Desc#', '#ApprovedDate#', '#Not

RE: OT: SQL Question

2001-01-17 Thread Gieseman, Athelene
That was it! Thank you! -Original Message- From: Bud [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 16, 2001 4:18 PM To: CF-Talk Subject: Re: OT: SQL Question On 1/16/01, Gieseman, Athelene penned: > > INSERT X_Invoices (Vendor, Inv_GL, Inv_City, Inv_Dept, Inv_Date, &

Re: OT: SQL Question

2001-01-16 Thread Bud
On 1/16/01, Gieseman, Athelene penned: > > INSERT X_Invoices (Vendor, Inv_GL, Inv_City, Inv_Dept, Inv_Date, >Inv_No, Inv_Desc, ApprovedDate, Notes, Inv_Amount) > values ('#Vendor#', '#Inv_GL#', '#Inv_City#', '#Inv_Dept#', >'#Inv_Date#', '#Inv_No#', '#Inv_Desc#', '#ApprovedDate#', '#Not

OT: SQL Question

2001-01-16 Thread Gieseman, Athelene
I am getting the following SQL error: Microsoft][ODBC SQL Server Driver][SQL Server]There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement. This doe

OT: SQL question

2000-12-12 Thread Dave Hannum
Whew . . . I'm still in one piece after that wind last night . . . What's the best way to query a column in a table that has a comma delimited list? For example, in the query below: If B_xRef is a comma delimited list, what's the most effecient way to find all the Table_b rows that contain the

OT: SQL Question

2000-11-22 Thread Michel Vuijlsteke
It's been a very long day (a very long year in fact :), and I'm severely stumped on something I guess is dead easy. Using CF 4.5.1 and SQL Server, I have a list of names like so (fields firstname and lastname in a db): John Smith Jean Paul Gaultier Jane Doe I construct l

OT: SQL question

2000-10-30 Thread Corina S. Moore
Hi List, I am Learning SQL by trial and error. So far so good -- Until today. I have a feedback form that captures comments. Currently I am using the TEXT datatype in SQL. Today, a user entered a comment that was about 1500 characters long and the dB truncated the msg. What datatype

Re: ot: sql question

2000-09-28 Thread David Shadovitz
This hurts to look at. Is that the entire table? Or are there additional fields that make those rows unique? If it's the entire table, why do you have identical rows, and why do you want to retain them? If there's more to the table, extend your 'where' clause to identify the unique row that yo

Re: ot: sql question

2000-09-28 Thread David Cummins
Oh dear. Non-unique rows. Hmmm... Unless its possible to do this with cursors (I know nothing about them), I'd say you'd have to delete, then re-insert the data, or better yet add another column to make it unique... ;) David Cummins Gavin Myers wrote: > > here's what i'm doing > > delete from

ot: sql question

2000-09-27 Thread Gavin Myers
here's what i'm doing delete from class_registration where users_id='123123' and class_id='4242424' here's what the table looks like nameusers_idclass_id gavin 123123 4242424 gavin 123123 4242424 gavin 123123 4242424 gavin 123123 4242424 what i want to do is delete only

RE: OT - SQL question

2000-05-04 Thread Paul Wakefield
y 03, 2000 10:45 PM > To: [EMAIL PROTECTED] > Subject: OT - SQL question > > > Hey ... > > How do you do a query where all rows returned except in the case of > duplicates, based on a name column, in which only the first > is returned. > > So the query run agai

RE: OT - SQL question

2000-05-04 Thread Pete Freitag
] Subject: OT - SQL question Hey ... How do you do a query where all rows returned except in the case of duplicates, based on a name column, in which only the first is returned. So the query run against the following rows: NameOrderNum Fred21 Beth36 Fred76 Would return

OT - SQL question

2000-05-03 Thread PC
Hey ... How do you do a query where all rows returned except in the case of duplicates, based on a name column, in which only the first is returned. So the query run against the following rows: NameOrderNum Fred21 Beth36 Fred76 Would return : NameOrderNum Fred

Re: slightly OT - SQL question

2000-03-24 Thread Nick Call
Thanks for the excellent information. I am off to do battle with M$ SQL... Nick [EMAIL PROTECTED] - Original Message - From: "Darryl Davidson" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, March 24, 2000 11:13 AM Subject: Re: slightly OT - SQL question

RE: slightly OT - SQL question

2000-03-24 Thread Mike Sheldon
>>Here is where the problem comes in. If the destination server does not have the same DATA directory location and structure as the source server, you will receive an error when trying to perform a restore from within EM. The error indicates that we should use the 'MOVE' option. If you receive

Re: slightly OT - SQL question

2000-03-24 Thread Darryl Davidson
If you're talking about moving a database, a coworker ran into this recently. Here's what he found out, verbatim: (in other words, your mileage may definitely vary, no I haven't tried it, etc.) ---snip - From EM (Enterprise Manager), perform a backup of

slightly OT - SQL question

2000-03-24 Thread Nick Call
Help SQL Server 7.0 gurus I need to attach a SQL database to my SQL server. It was created on another machine, and you know how MS SQL hates foreign DB's. It was suggested that I try "attach_db", but all I get is the tables, not the data. I am NOT a SQL guru. Please help. I will be worki