SOT: SQL question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 In a table something like this: userid, course john ethics john english mary english mary math mary bio I need to find a) which user has taken the most courses in total b) which course was taken the most often Ideas? c -BEGIN PGP SIGNATURE- Version: 6.5.8ckt http://irfaiad.virtualave.net/ Comment: PGP Signed for message verification and/or encryption Comment: KeyID: 0xD68B61E851046CFD iQA/AwUBOd0ksNaLYehRBGz9EQJjVQCfUA600RImF5Y7NRoha4BGnHzrV/QAoKWx 9BIcf5UEne847/fTRg/Ryv/y =CpJ4 -END PGP SIGNATURE- -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
SOT: SQL question
How do I get the ID of the last record in a table (mySQL db)? Cutter [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
SOT: SQL question
Ok, I'm back to this. Have an ISP who is using the outdated MySQL 3.23, which does not support Union statements in it's syntax. I need to combine the data of two separate but similar statements so that I may sort on a certain field. These two tables are not Joined by any keys, though it could be perceived that way (the pk of each is auto-numbered so there are many matching values, don't want to eliminate anything in my results). Any ideas? Cutter [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
SOT: SQL QUESTION
SQL SERVER 2000 CFMX 6.1 I have three tables called: OPPORTUNITY, OPPORTUNITY_TEXT, TEXT_TYPE OPPORTUNITY_TEXT has two columns making the PK: TEXT_TYPE_ID and OPPORTUNITY_ID. In addition there is a column called OPPORTUNITY_TEXT_DATA which holds the actual text I am after [varchar(6000)]. What I am trying to do is pull back two specific records if they exist but as one record. Let me elaborate Currently, I can say the following: SELECT OPPORTUNITY_ID AS C1, TEXT_TYPE_ID AS C2, OPPORTUNITY_TEXT_DATA AS C3 FROM OPPORTUNITY_TEXT WHERE OPPORTUNITY_ID = 1 AND (TEXT_TYPE_ID = 12 OR TEXT_TYPE_ID = 13) Which returns two records as expected. - C1 | C2 | C3 --|---|-- 1 | 12 | A - 1 | 13 | B - What I would like to do is join on itself and alias the columns so I can get the output to be something like so that I can avoid all the conditional logic in my page that would be needed to output the correct value in the correct form field: Alias1 | Alias2 |--- A | B Does that make sense? Am I missing the boat or something here. Open for suggestions and assistance. Thanks! Mike [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
SOT: SQL question
I have four (4) tables. One table holds IDs that reference info in the other three (3) tables by ID. I am trying to pull the information for an item, and pull the info specific to that item from the three tables. What's wrong with this picture? select i.intBoutItemID as ID, c.txtBoutCat as Cat, s.txtBoutSubCat as SubCat, d.txtDesignName as Name, i.blActive as Active from tblBoutItem i, tblBoutCat c, tblBoutSubCat s, tblBoutDesign d where c.intBoutCatID = i.intBoutCatID and s.intBoutSubCatID = i.intBoutSubCatID and d.intBoutDesignID = i.intBoutDesignID Order by d.txtDesignName [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
SOT: SQL Question
Given the following: SELECT R.TEAMING_AGREEMENT_ID, R.ROUTE_GROUP_ID, B.BUSINESS_NAME, T.OPPORTUNITY_ID, D.DOCUMENT_NAME, D.DOCUMENT_EXTENSION FROM TEAMING_AGREEMENT_ROUTING R INNER JOIN TEAMING_AGREEMENT T ON (R.TEAMING_AGREEMENT_ID = T.TEAMING_AGREEMENT_ID) INNER JOIN COMMUNITY_BUSINESS B ON (T.COMMUNITY_BUSINESS_ID = B.COMMUNITY_BUSINESS_ID) INNER JOIN TEAMING_AGREEMENT_TYPE TAT ON (T.TEAMING_AGREEMENT_TYPE_ID = TAT.TEAMING_AGREEMENT_TYPE_ID) INNER JOIN TEAMING_AGREEMENT_DOCUMENT D ON (T.TEAMING_AGREEMENT_ID = D.TEAMING_AGREEMENT_ID) WHERE T.OPPORTUNITY_ID = 33 GROUP BY B.BUSINESS_NAME, R.TEAMING_AGREEMENT_ID, R.ROUTE_GROUP_ID, T.OPPORTUNITY_ID, D.DOCUMENT_NAME, D.DOCUMENT_EXTENSION If I need to get two of documents from the TEAMING_AGREEMENT_DOCUMENT table (different types: DOC_TYPE = 1 and DOC_TYPE =2), how can I do this and get the desired number of records returned. If I remove the joined in TEAMING_AGREEMENT_DOCUMENT table I return 5 records, which is correct, but when I try and get the two documents associated I return 10 records. Is there anyway to qualify the selection of the documents? I hope this makes sense grouping? sub query? Thanks! Mike [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
SOT: SQL Question
This one's been bugging me for a day now. I don't think this is possible, but before I gave up I thought I would ask. (Yes Rick, I googled it first) As usual, the real scenario is much more complicated, but this is a simple example that shows the concept. Let's say I had a table with multiple products per order and the date they were ordered: order_num product_namedatetime_created 1 apples 9/1/2007 1 oranges 9/10/2007 1 bananas 9/20/2007 2 apples 9/5/2007 2 pears 9/15/2007 2 kiwi9/25/2007 Is it possible with a SINGLE select statement to simply get a distinct list of orders represented with the LAST product ordered like so: order_num product_namedatetime_created 1 bananas 9/20/2007 2 kiwi9/25/2007 The knee jerk reaction is to group by order_num, and then use the max aggregate on datetime_created. That's fine, but then you can't get the corresponding product_name. To get the product name in the select list you have to add it in the group by which then itemizes all the products and you no longer have a distinct list of orders. Using a derived table doesn't even help. I can't do "top 1" with an "order by datetime_ordered desc" because I am reporting across multiple orders. I am on MS SQL Server 2005. The only ways I can find to do this are: 1) Cursor or while loop over orders getting last product row by agonizing row 2) Create function to return last product on the order, and "cross apply" it. The function still has to run RBAR for every order though. 3) Initial select into a temp table with max(datetime_created). Update temp table in a second pass with product_name joining on datetime_created. I don't like joining to a date because it guaranteed unique. Ideas? Thanks. ~Brad ~| Check out the new features and enhancements in the latest product release - download the "What's New PDF" now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289743 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
SOT: SQL Question
Hey, I'm wondering if anyone would know T-SQL I could use to accomplish the following (I don't have the luxury of writing an SQL function for this, unfortunately): I have a column called 'path' in a database (Oracle) that has the following types of values in it: Example 1: c:\foldername\documentname.ext (standard format) Example 2: c:\foldername\subfolder\my.document.name.ext(multiple periods) Example 3: c:\foldername\documentname (no extension) What I want to do is be able to extract the extension from the path in a select statement. I know I can do this easily in CF code, I'm trying to avoid looping through a large query and doing it if at all possible. Here's what I have so far, but it's too simple and doesn't work on example 2 above: select decode(inStr(Path,'.'),0,'',subStr(path,inStr(path,'.')+1,3)) from document I suppose I need to find a way to do something like a 'rightInStr()' or something like that. A way to get the position of the LAST period. Or if someone has some other idea of how I can do it in T-SQL, maybe using some CASE statements or something? I'm not great with CASE and SQL. Please, if anyone can help, I would greatly appreciate it! Sincerely, Dave Phillips WebTech Staffing, LLC ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:269408 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
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) JobFileAccessLevel is a field in the database table that contains a comma delimited list of access levels. Maybe I'm barking up the wrong tree entirely. What I'm trying to do is. ..WHERE jobFileJob=#session.jobId# and jobFileAccessLevel CONTAINS '#session.UserRole#' But I don't think 'contains' exists. Thanks, Mark ~| 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: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296263 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SOT: SQL question
Cutter (CF-Talk) wrote: > How do I get the ID of the last record in a table (mySQL db)? Define 'last'. Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: SOT: SQL question
'last' = the last/most recent/highest autonumbered ID in the primary key of the table. Cutter Jochem van Dieten wrote: > Cutter (CF-Talk) wrote: > > > How do I get the ID of the last record in a table (mySQL db)? > > Define 'last'. > > Jochem > > -- > I don't get it > immigrants don't work > and steal our jobs > - Loesje > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: SOT: SQL question
Cutter (CF-Talk) wrote: > 'last' = the last/most recent/highest autonumbered ID in the primary key > of the table. http://www.mysql.com/doc/en/Getting_unique_ID.html Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: SOT: SQL question
Cutter, On 2/23/2004 at 14:07, you wrote: CCT> Ok, I'm back to this. Have an ISP who is using the outdated MySQL CCT> 3.23, which does not support Union statements in it's syntax. I CCT> need to combine the data of two separate but similar statements CCT> so that I may sort on a certain field. These two tables are not CCT> Joined by any keys, though it could be perceived that way (the pk CCT> of each is auto-numbered so there are many matching values, don't CCT> want to eliminate anything in my results). Any ideas? Query of Queries? http://livedocs.macromedia.com/coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/using_recordsets.htm ~ Ubqtous ~ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: SOT: SQL question
I've tried a query of query situation, but it is not returning all records from both tables, and I believe it may be because both tables have a key value that match. Here are my queries and the end results: **qHoliday** select h.intHolidayPlusID as ID, h.txtHolidayPlusTitle as Title, h.dtStartDate as SDate, h.dtEndDate as EDate from tblholidayplus h where h.dtStartDate >= #createodbcdate(variables.originalurldate)# or #createodbcdate(url.date)# <= h.dtEndDate order by h.dtStartDate limit 7 **Results of qHoliday** EDATE ID SDATE TITLE 1 {ts '2004-02-24 00:00:00'} 2 {ts '2004-02-24 00:00:00'} Fat Tuesday 2 {ts '2004-02-17 00:00:00'} 3 {ts '2004-03-17 00:00:00'} St Patricks Day 3 {ts '2004-04-01 00:00:00'} 4 {ts '2004-04-01 00:00:00'} April Fools Day **qHoliday2** select s.intSpEventsID as ID, s.txtSpEventsTitle as Title, d.dtDatesas SDate, s.dtEndTimeas EDate from tblspevents s, tbleventdates d where s.intEventDatesID = d.intEventDatesID and d.dtDates >= #createodbcdate(variables.originalurldate)# order by d.dtDates limit 7 **Results of qHoliday2** EDATE ID SDATE TITLE 1 {ts '1970-01-01 02:00:00'} 3 {ts '2004-02-24 00:00:00'} Mardi Gras Party **qEventList QofQ** select * from qHoliday, qHoliday2 order by SDate **Results of qEventList** EDATE EDATE ID ID SDATE SDATE TITLE TITLE 1 {ts '2004-02-24 00:00:00'} {ts '2004-02-24 00:00:00'} 2 2 {ts '2004-02-24 00:00:00'} {ts '2004-02-24 00:00:00'} Fat Tuesday Fat Tuesday 2 {ts '2004-02-17 00:00:00'} {ts '2004-02-17 00:00:00'} 3 3 {ts '2004-03-17 00:00:00'} {ts '2004-03-17 00:00:00'} St Patricks Day St Patricks Day 3 {ts '2004-04-01 00:00:00'} {ts '2004-04-01 00:00:00'} 4 4 {ts '2004-04-01 00:00:00'} {ts '2004-04-01 00:00:00'} April Fools Day April Fools Day Ubqtous wrote: > Cutter, > > On 2/23/2004 at 14:07, you wrote: > > CCT> Ok, I'm back to this. Have an ISP who is using the outdated MySQL > CCT> 3.23, which does not support Union statements in it's syntax. I > CCT> need to combine the data of two separate but similar statements > CCT> so that I may sort on a certain field. These two tables are not > CCT> Joined by any keys, though it could be perceived that way (the pk > CCT> of each is auto-numbered so there are many matching values, don't > CCT> want to eliminate anything in my results). Any ideas? > > Query of Queries? > > http://livedocs.macromedia.com/coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/using_recordsets.htm > > ~ Ubqtous ~ > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: SOT: SQL question
Cutter, On 2/23/2004 at 14:50, you wrote: CCT> **qEventList QofQ** CCT> select * CCT> fromqHoliday, CCT> qHoliday2 CCT> order bySDate maybe try this in your QoQ: select * from qHoliday union select * from qHoliday2 order by SDate ~ Ubqtous ~ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: SOT: SQL question
As listed in the original post, my ISP is using MySQL 3.23 (which has no support for union)... Cutter Ubqtous wrote: > Cutter, > > On 2/23/2004 at 14:50, you wrote: > > CCT> **qEventList QofQ** > CCT> select * > CCT> fromqHoliday, > CCT> qHoliday2 > CCT> order bySDate > > maybe try this in your QoQ: > > select * from qHoliday > union > select * from qHoliday2 > order by SDate > > ~ Ubqtous ~ > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: SOT: SQL question
Yes but Query of queries DOES support the union key word. -Mark -Original Message- From: Cutter (CF-Talk) [mailto:[EMAIL PROTECTED] Sent: Monday, February 23, 2004 2:01 PM To: CF-Talk Subject: Re: SOT: SQL question As listed in the original post, my ISP is using MySQL 3.23 (which has no support for union)... Cutter Ubqtous wrote: > Cutter, > > On 2/23/2004 at 14:50, you wrote: > > CCT> **qEventList QofQ** > CCT> select * > CCT> fromqHoliday, > CCT> qHoliday2 > CCT> order bySDate > > maybe try this in your QoQ: > > select * from qHoliday > union > select * from qHoliday2 > order by SDate > > ~ Ubqtous ~ > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: SOT: SQL question
Cutter, On 2/23/2004 at 15:01, you wrote: CCT> As listed in the original post, my ISP is using MySQL 3.23 (which CCT> has no support for union)... The QoQ doesn't touch the database, rather it's using the record sets from the queries you run before hand. The query results are still in server memory when you run the QoQ, which is why MySQL isn't touched. 1. Run qHoliday against MySQL 2. Run qHoliday2 against MySQL 3. Run QoQ with a UNION clause to combine the results of qHoliday & qHoliday2. ~ Ubqtous ~ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: SOT: SQL question
Cutter (CF-Talk) wrote: > I have four (4) tables. One table holds IDs that reference info in the > other three (3) tables by ID. I am trying to pull the information for an > item, and pull the info specific to that item from the three tables. > What's wrong with this picture? > > select i.intBoutItemID as ID, > c.txtBoutCat as Cat, > s.txtBoutSubCat as SubCat, > d.txtDesignName as Name, > i.blActive as Active > from tblBoutItem i, > tblBoutCat c, > tblBoutSubCat s, > tblBoutDesign d > where c.intBoutCatID = i.intBoutCatID > and s.intBoutSubCatID = i.intBoutSubCatID > and d.intBoutDesignID = i.intBoutDesignID > Order by d.txtDesignName Nothing: http://developer.mimer.com/validator/parser99/index.tml So why don't you tell us what is wrong? Jochem -- When you don't want to be surprised by the revolution organize one yourself - Loesje [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: SOT: SQL Question
> why do you have to use a single select statement? Thats pretty > limiting there.. 1) To see if it can be done 2) It seemed that it may perform best to make a single pass at the table as opposed to two passes. (That might not be true-- it was just a thought) The query below works, but does not return a distinct list of orders. If multiple products were added at the same time, I get all of them. I'm really looking for a top 1 sort of deal I guess. ~Brad -Original Message- From: Greg Morphis [mailto:[EMAIL PROTECTED] Sent: Friday, September 28, 2007 11:47 AM To: CF-Talk Subject: 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 ~| ColdFusion is delivering applications solutions at at top companies around the world in government. Find out how and where now http://www.adobe.com/cfusion/showcase/index.cfm?event=finder&productID=1522&loc=en_us Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289755 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SOT: SQL Question
why do you have to use a single select statement? Thats pretty limiting there.. On 9/28/07, Brad Wood <[EMAIL PROTECTED]> wrote: > This one's been bugging me for a day now. I don't think this is > possible, but before I gave up I thought I would ask. (Yes Rick, I > googled it first) > > As usual, the real scenario is much more complicated, but this is a > simple example that shows the concept. > > Let's say I had a table with multiple products per order and the date > they were ordered: > > order_num product_namedatetime_created > 1 apples 9/1/2007 > 1 oranges 9/10/2007 > 1 bananas 9/20/2007 > 2 apples 9/5/2007 > 2 pears 9/15/2007 > 2 kiwi9/25/2007 > > Is it possible with a SINGLE select statement to simply get a distinct > list of orders represented with the LAST product ordered like so: > > order_num product_namedatetime_created > 1 bananas 9/20/2007 > 2 kiwi9/25/2007 > > The knee jerk reaction is to group by order_num, and then use the max > aggregate on datetime_created. That's fine, but then you can't get the > corresponding product_name. To get the product name in the select list > you have to add it in the group by which then itemizes all the products > and you no longer have a distinct list of orders. > > Using a derived table doesn't even help. I can't do "top 1" with an > "order by datetime_ordered desc" because I am reporting across multiple > orders. > > I am on MS SQL Server 2005. The only ways I can find to do this are: > > 1) Cursor or while loop over orders getting last product row by > agonizing row > > 2) Create function to return last product on the order, and "cross > apply" it. The function still has to run RBAR for every order though. > > 3) Initial select into a temp table with max(datetime_created). Update > temp table in a second pass with product_name joining on > datetime_created. I don't like joining to a date because it guaranteed > unique. > > Ideas? > > Thanks. > > ~Brad > > ~| ColdFusion is delivering applications solutions at at top companies around the world in government. Find out how and where now http://www.adobe.com/cfusion/showcase/index.cfm?event=finder&productID=1522&loc=en_us Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289748 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
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 On 9/28/07, Greg Morphis <[EMAIL PROTECTED]> wrote: > why do you have to use a single select statement? Thats pretty limiting > there.. > > On 9/28/07, Brad Wood <[EMAIL PROTECTED]> wrote: > > This one's been bugging me for a day now. I don't think this is > > possible, but before I gave up I thought I would ask. (Yes Rick, I > > googled it first) > > > > As usual, the real scenario is much more complicated, but this is a > > simple example that shows the concept. > > > > Let's say I had a table with multiple products per order and the date > > they were ordered: > > > > order_num product_namedatetime_created > > 1 apples 9/1/2007 > > 1 oranges 9/10/2007 > > 1 bananas 9/20/2007 > > 2 apples 9/5/2007 > > 2 pears 9/15/2007 > > 2 kiwi9/25/2007 > > > > Is it possible with a SINGLE select statement to simply get a distinct > > list of orders represented with the LAST product ordered like so: > > > > order_num product_namedatetime_created > > 1 bananas 9/20/2007 > > 2 kiwi9/25/2007 > > > > The knee jerk reaction is to group by order_num, and then use the max > > aggregate on datetime_created. That's fine, but then you can't get the > > corresponding product_name. To get the product name in the select list > > you have to add it in the group by which then itemizes all the products > > and you no longer have a distinct list of orders. > > > > Using a derived table doesn't even help. I can't do "top 1" with an > > "order by datetime_ordered desc" because I am reporting across multiple > > orders. > > > > I am on MS SQL Server 2005. The only ways I can find to do this are: > > > > 1) Cursor or while loop over orders getting last product row by > > agonizing row > > > > 2) Create function to return last product on the order, and "cross > > apply" it. The function still has to run RBAR for every order though. > > > > 3) Initial select into a temp table with max(datetime_created). Update > > temp table in a second pass with product_name joining on > > datetime_created. I don't like joining to a date because it guaranteed > > unique. > > > > Ideas? > > > > Thanks. > > > > ~Brad > > > > ~| Enterprise web applications, build robust, secure scalable apps today - Try it now ColdFusion Today ColdFusion 8 beta - Build next generation apps Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289751 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SOT: SQL Question
how about something that looks at the right 4 characters, and if the first of those 4 is a period, then get the right 3. I don't have SQL Server on this box, but it would be something like: select case left(right(path,4),1) when '.' then right(path,3) else '' end Again, don't have it in front of me, but, give it a try. Cheers, Kris > I have a column called 'path' in a database (Oracle) that has the following > types of values in it: > > Example 1: c:\foldername\documentname.ext (standard format) > Example 2: c:\foldername\subfolder\my.document.name.ext(multiple > periods) > Example 3: c:\foldername\documentname (no extension) > > What I want to do is be able to extract the extension from the path in a > select statement. I know I can do this easily in CF code, I'm trying to > avoid looping through a large query and doing it if at all possible. Here's > what I have so far, but it's too simple and doesn't work on example 2 above: > > select decode(inStr(Path,'.'),0,'',subStr(path,inStr(path,'.')+1,3)) from > document ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:269410 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SOT: SQL Question
Kris Jones wrote: > select case left(right(path,4),1) when '.' then right(path,3) else '' end > This will work on SQL Server (assuming all of your extensions are three characters), and possibly on Oracle(I think it now supports CASE). In your message you wanted a T-SQL solution, but said your database was Oracle. Did you mean to say a PL/SQL or possible just SQL solution? T-SQL is the proprietary version of SQL specific to Sybase and SQL Server. ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:269416 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SOT: SQL Question
Jim, Yeah, I typoed. Sorry, worked with SQL Server a lot. I did mean a PL/SQL solution, however, I cannot write a PL/SQL function to utilize, so whatever SQL I use, it has to work inside a To my knowledge, Oracle doesn't have a right() function, and the sql provided didn't work in Oracle. Also, I could have extensions with any number of digits, so I'm not sure that will work anyway. Still looking for a solid solution. Thanks, Dave Return-Path: <[EMAIL PROTECTED]> Sat Feb 10 14:30:57 2007 Received: from houseoffusion.com [64.118.74.249] by mail.bizbreeze.com with SMTP; Sat, 10 Feb 2007 14:30:57 -0500 Received: from LOCALHOST by LOCALHOST with ESMTP id 4A591C6C01270B47B496A2614E792BB1 Sat, 10 Feb 2007 14:34:57 -0500 Subject: Re: SOT: SQL Question From: Jim Wright <[EMAIL PROTECTED]> Content-Type: text/plain Precedence: bulk Reply-To: cf-talk@houseoffusion.com To: CF-Talk Date: Sat, 10 Feb 2007 14:34:11 -0500 References: <[EMAIL PROTECTED]> Message-ID: <[EMAIL PROTECTED]> X-SmarterMail-Spam: SPF_None X-Rcpt-To: <[EMAIL PROTECTED]> Kris Jones wrote: > select case left(right(path,4),1) when '.' then right(path,3) else '' end > This will work on SQL Server (assuming all of your extensions are three characters), and possibly on Oracle(I think it now supports CASE). In your message you wanted a T-SQL solution, but said your database was Oracle. Did you mean to say a PL/SQL or possible just SQL solution? T-SQL is the proprietary version of SQL specific to Sybase and SQL Server. ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:269438 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SOT: SQL Question
Actually you can write an anonymous PL/SQL block inside a cfquery tag. There's a bug wherein line returns will cause an error in the JDBC driver, so the block must be all on one line, but it will work. Anyway, SUBSTR() will work like a right() function when -1 is used as the starting position: http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions119a.htm And here's the CASE syntax for Oracle: http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/expressions5a.htm On 2/11/07, Dave Phillips <[EMAIL PROTECTED]> wrote: > Jim, > > Yeah, I typoed. Sorry, worked with SQL Server a lot. I did mean a PL/SQL > solution, however, I cannot write a PL/SQL function to utilize, so whatever > SQL I use, it has to work inside a > > To my knowledge, Oracle doesn't have a right() function, and the sql provided > didn't work in Oracle. Also, I could have extensions with any number of > digits, so I'm not sure that will work anyway. > > Still looking for a solid solution. > > Kris Jones wrote: > > select case left(right(path,4),1) when '.' then right(path,3) else '' end > > > > This will work on SQL Server (assuming all of your extensions are three > characters), and possibly on Oracle(I think it now supports CASE). In > your message you wanted a T-SQL solution, but said your database was > Oracle. Did you mean to say a PL/SQL or possible just SQL solution? > T-SQL is the proprietary version of SQL specific to Sybase and SQL Server. -- CFAJAX docs and other useful articles: http://www.bifrost.com.au/blog/ ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:269440 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SOT sql question
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) JobFileAccessLevel is a field in the database table that contains a comma delimited list of access levels. Maybe I'm barking up the wrong tree entirely. What I'm trying to do is. ...WHERE jobFileJob=#session.jobId# and jobFileAccessLevel CONTAINS '#session.UserRole#' But I don't think 'contains' exists. Thanks, Mark ~| 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: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296267 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: 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 = #session.jobId# AND '#session.UserRole#' IN > (JobFileAccessLevel) > > JobFileAccessLevel is a field in the database table that contains a comma > delimited list of access levels. you're doing it backwards :) SELECT foo FROM bar WHERE foobar IN (a,b,c,d) says "give me records where the value of foobar is either a OR b OR c Or d. "IN", is a shorthand way of writing out a number of OR statements. it'd be wrong of me to not state here that generally speaking, storing comma delimited values in a relational database table is (again, generally) not a good idea and defeats the purpose of a relational database. those values should (likely) be broken out into a new table as individual rows (with an ID column having a FK relationship back to the original table). -- "Scientists tell us that the fastest animal on earth, with a top speed of 120 feet per second, is a cow that has been dropped out of a helicopter." - Dave Barry ~| 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: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296268 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SOT sql question
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 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) > > > > JobFileAccessLevel is a field in the database table that contains a comma > delimited list of access levels. > > > > Maybe I'm barking up the wrong tree entirely. What I'm trying to do is. > > > > ..WHERE jobFileJob=#session.jobId# and jobFileAccessLevel CONTAINS > '#session.UserRole#' > > > > But I don't think 'contains' exists. > > > > Thanks, > > > > Mark > > > > > ~| 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: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296275 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SOT sql question
My mechanic likes to tell me that I should inform him what my car is/is not doing and let him do the diagnosing. Maybe I should try that with this problem also. Using a form, 'JobFileUpload.cfm', files are uploaded to the server to a folder based on JobId and a then a record of jobId, file name, file type and access level/levels is inserted in the database table 'jobFiles'. 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. On JobFileList.cfm, I want to filter on jobId and 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: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 = #session.jobId# AND '#session.UserRole#' IN > (JobFileAccessLevel) > > JobFileAccessLevel is a field in the database table that contains a comma > delimited list of access levels. you're doing it backwards :) SELECT foo FROM bar WHERE foobar IN (a,b,c,d) says "give me records where the value of foobar is either a OR b OR c Or d. "IN", is a shorthand way of writing out a number of OR statements. it'd be wrong of me to not state here that generally speaking, storing comma delimited values in a relational database table is (again, generally) not a good idea and defeats the purpose of a relational database. those values should (likely) be broken out into a new table as individual rows (with an ID column having a FK relationship back to the original table). -- "Scientists tell us that the fastest animal on earth, with a top speed of 120 feet per second, is a cow that has been dropped out of a helicopter." - Dave Barry ~| 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: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296279 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SOT sql question
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 a normalized database scheme with this data in a related table it would be a trivial join and where clause to get the exact records you want. > On JobFileList.cfm, I want to filter on jobId and the current users access > level which is stored in #session.PlumUserRoles# > > Any idea how I might do that? If you insist on using this database design you are going to have to use the LIKE operator in the where clause with a bunch of possibilities. Lets say you want to look for an AccessLevel of '3'. You are going to have to write a where clause something like. WHERE accessLevel LIKE '%,3,$' OR accessLevel LIKE '%,3' OR accessLevel LIKE '3,%' ect. And this does not even address the problems of making sure you do not match threes in the possible values of '13', '32', '33' ect. Contrast this with this. SELECT stuff FROM aTable JOIN bTable ON aTable.key = bTable.fKey WHERE bTable.JobFileAccessLevel = 3 Personally I find the latter much cleaner. ~| 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: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296284 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SOT sql question
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 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 a normalized database scheme with this data in a related table it would be a trivial join and where clause to get the exact records you want. > On JobFileList.cfm, I want to filter on jobId and the current users access level which is stored in #session.PlumUserRoles# > > Any idea how I might do that? If you insist on using this database design you are going to have to use the LIKE operator in the where clause with a bunch of possibilities. Lets say you want to look for an AccessLevel of '3'. You are going to have to write a where clause something like. WHERE accessLevel LIKE '%,3,$' OR accessLevel LIKE '%,3' OR accessLevel LIKE '3,%' ect. And this does not even address the problems of making sure you do not match threes in the possible values of '13', '32', '33' ect. Contrast this with this. SELECT stuff FROM aTable JOIN bTable ON aTable.key = bTable.fKey WHERE bTable.JobFileAccessLevel = 3 Personally I find the latter much cleaner. ~| 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: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296290 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SOT: SQL question (Resolved)
This is why I love this list! You guys (and the few gals) are the bomb! Cutter Mark A. Kruger - CFG wrote: > Yes but Query of queries DOES support the union key word. > > -Mark > > -Original Message- > From: Cutter (CF-Talk) [mailto:[EMAIL PROTECTED] > Sent: Monday, February 23, 2004 2:01 PM > To: CF-Talk > Subject: Re: SOT: SQL question > > As listed in the original post, my ISP is using MySQL 3.23 (which has no > support for union)... > > Cutter > > Ubqtous wrote: > > > Cutter, > > > > On 2/23/2004 at 14:50, you wrote: > > > > CCT> **qEventList QofQ** > > CCT> select * > > CCT> fromqHoliday, > > CCT> qHoliday2 > > CCT> order bySDate > > > > maybe try this in your QoQ: > > > > select * from qHoliday > > union > > select * from qHoliday2 > > order by SDate > > > > ~ Ubqtous ~ > > > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
SOT: SQL Question - Could CF make this easier?
I am creating a drop-down list of items, but I want to keep several items from appearing in the drop-down. Is there a more efficient way to accomplish this (there will be several more exclusions): SELECT DISTINCT Description FROMVCT ORDER BY Description WHERE description != 'ANISE' AND description != 'APPLES' AND description != 'ASPARAGUS' AND description != 'BEETS' AND description != 'BROCCOLI' -- Jillian ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SOT: SQL Question - Could CF make this easier?
You can do it easier with SQL by using NOT IN ... WHERE Description NOT IN ('ANISE', 'APPLES',.) HTH Dick On Wednesday, June 11, 2003, at 11:37 AM, Jillian Carroll wrote: > I am creating a drop-down list of items, but I want to keep several > items > from appearing in the drop-down. > > Is there a more efficient way to accomplish this (there will be > several more > exclusions): > > > SELECT DISTINCT > Description > FROM VCT > ORDER BY > Description > WHERE description != 'ANISE' > AND description != 'APPLES' > AND description != 'ASPARAGUS' > AND description != 'BEETS' > AND description != 'BROCCOLI' > > > -- > Jillian > > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4