sql query

[avex]

hey everyone

i have always used the following SQL statement to show a particular database
entry.


SELECT Storename, ID, City
FROM review
Where ID = #ID#



where i passed the ID from the url or form or whatever.

However,.what if i want to show 2 reviews.ID 7 and ID 9.

how do i show this
and how would i construct a dynamic query that would allow for multiple
ID's(a cfloop?)

this is killing me!

i am going to buy Forta's SQL book today..

thanks

chad



--
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.

SQL Query

[Joshua Tipton]

I need this query to pull the reg_no, dis_no, m_id, and the y_id from
tbl_CTMCORPMONTHYEARRDD where it cannot be found in pagan table.  I wrote
this query and cannot get it to work please help.

Josh


SELECT tbl_CTMCORPMONTHYEARRDD.reg_no,
tbl_CTMCORPMONTHYEARRDD.dis_no,
tbl_CTMCORPMONTHYEARRDD.m_id,
tbl_CTMCORPMONTHYEARRDD.y_id
FROM tbl_CTMCORPMONTHYEARRDD INNER JOIN
pagan ON
tbl_CTMCORPMONTHYEARRDD.m_id <> pagan.m_id AND
tbl_CTMCORPMONTHYEARRDD.y_id <> pagan.y_id AND
tbl_CTMCORPMONTHYEARRDD.reg_no = pagan.reg_no AND
tbl_CTMCORPMONTHYEARRDD.dis_no = pagan.DIS_NO

__
Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. http://www.fusionauthority.com/ads.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

sql query

[Joshua Tipton]

I would like to run an update query to take a 9 digit social security number
and place the dashes in the correct spots.  IE.. 999-99-.  Can anyone
help me with this?

Joshua Tipton

__
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

SQL query

[Mario Martinez]

I need to choose a group of records from a table based in the fact that a
datetime field is not null .
how could I make the filtering in the where clause???.
I'm using transac-SQL.
Thanks in advance friends
regards
Mario

__
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
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

SQL Query

[Nick Le Mouton]

SELECT DISTINCT highlightDates.highlightID, HighlightDates.HighlightDate,
Highlights.*
FROM Highlights, HighlightDates
WHERE HighlightDates.HighlightDate >= #startweek# AND
HighlightDates.highlightdate <= #DateAdd('ww', 1, startweek)# AND
Highlights.HighlightID = HighLightDates.HighlightID AND Highlights.Status =
1

Can some one tell me why the query above doesn't work
I thought it would only look at highlightDates.highlightID and select a
distinct one of those, but it seems to be trying to do a distinct over all
of the selected fields

help! how can i make it select a distinct highlightID?
~~
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

SQL Query

[David Clay]

I am looking for help on a way to query a table fast and efficiently.  I am currently 
using a ODBC connection to SQL2000 Server with a simple CFQUERY tag doing the query.  
For a table with 18K records, it takes 14 seconds. 

I know this is not doing something correct!!  Any help designing new query statements 
or set up of CF or SQL2000 Servers would be helpful. 

Thank you for your time.

Dave Clay
Internet Facilitator
Trus Joist, A Weyerhaeuser Business
5995 Greenwood Plaza Blvd, Suite 100
Greenwood Village, CO 80111
303.770.8506
303.770.8506
   
   
   
   
   
   
   
   
 


~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

sql query

[Jones, Becky]

can anyone help me translate this FROM in my SQL query so CF will like it?
it works fine in SQL but bombs in CF with syntax errors.

FROM INT_AUX_LISTING

LEFT JOIN int_aux_listing as CompanyListing 
ON  (int_aux_listing.company_list_id =
CompanyListing.Listing_ID)
AND (int_aux_listing.Company_list_src_id =
CompanyListing.Listing_src_id))


left JOIN INT_AUX_LST_ADDR 
ON (INT_AUX_LISTING.LISTING_SRC_ID =
INT_AUX_LST_ADDR.LISTING_SRC_ID) 
AND (INT_AUX_LISTING.LISTING_ID =
INT_AUX_LST_ADDR.LISTING_ID)) 

left JOIN INT_AUX_ADDRESS 
ON (INT_AUX_LST_ADDR.ADDRESS_SRC_ID =
INT_AUX_ADDRESS.ADDRESS_SRC_ID) 
AND (INT_AUX_LST_ADDR.ADDRESS_ID =
INT_AUX_ADDRESS.ADDRESS_ID)

left JOIN INT_AUX_LST_PHONE 
ON (int_aux_listing.LISTING_ID =
INT_AUX_LST_PHONE.LISTING_ID) 
AND (int_aux_listing.LISTING_SRC_ID =
INT_AUX_LST_PHONE.LISTING_SRC_ID))

left JOIN INT_AUX_PHONE 
ON (INT_AUX_PHONE.PHONE_ID = INT_AUX_LST_PHONE.PHONE_ID) 
AND (INT_AUX_PHONE.PHONE_SRC_ID =
INT_AUX_LST_PHONE.PHONE_SRC_ID)) 

left join int_phone_typ
on (int_aux_phone.phone_typ_id = int_phone_typ.phone_typ_id)

left join int_relation_typ as Phone_Relation_TYP
on (int_aux_lst_phone.rltn_typ_id =
phone_relation_typ.rltn_typ_id)
i was working with the query builder and it seemed like it was using {
instead of (.  
thanks for your help
~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

SQL Query

[Pete]

Hi there

I have a query that I have developed which is working, however it is not
working correctly.

It is for some reason pulling 2 or 3 of the same records from the database
in some cases and only 1 record from the database in other cases.

Even after looking at the query for some time I cant see why this is
happening.

Here is the query below:

Select *
From
tbl_advertisers a,
photo p,
tbl_listing_type l,
photo_category pc
Where (a.advertiserid = p.advertiserid
Anda.listingid = l.listingid
Andp.catid = pc.id
Anda.listingid = #url.listingid#)

If anyone wants to see the results that I am getting at present here is the
URL:

http://202.157.179.158/csandbdirectory   only the beauty & spas link is
working at the time of sending this email.

Thanks in advance.

Pete





~|
Create robust enterprise, web RIAs.
Upgrade & integrate Adobe Coldfusion MX7 with Flex 2
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:266218
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

SQL Query

[Jason Smith]

This code works fine with a access database, since converting the entire cf 
application to a live SQL database it has been the beginning of a 
nightmare. I'm not real familiar with access but it seems they have some 
really strange ways to check dates and whatnot.


 
 
 
 
 
 SELECT 
Sum(tblItemPurchase.itemPurchasePrice) AS totalMonthlyPurchase
 FROM tblItemPurchase
 WHERE 
tblItemPurchase.itemPurchaseDate >= ###theMonth#/1/#theYear###
 
 
 
 
 
 
 
 GOALS
 
 THIS 
MONTH:
 
 

 

 GOAL:#DollarFormat(theGoal)#.

 SO 
FAR:#DollarFormat(getMonthlyPurchase.totalMonthlyPurchase)#

 TO 
GO:#DollarFormat(amountLeft)# 


 
 
 
 
 
 
  
 
 

If anyone can point me in the right direction or where to look for the 
needed information about converting a access based site to sql.

Thanx



Web Your Business Inc., - located in Loveland, Colorado; serving the World!
http://www.webyourbusiness.com/ - & - http://www.aaabusinesshosting.com/
Phone: 970-593-6260 - Fax: 970-593-6267 - Toll Free: 1-877-416-8655 


~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:187067
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Using a SQL query to drive another SQL query

[Allan . Davis]

I'm running into a bit of a problem making my code work; any suggestions?

I'm trying to get data from one SQL routine, and then feed that data into
another.

Here is the first one:

SELECT  ID, NAME
FROMMySource
WHERE   NAME LIKE '#FORM.junk1#%'

I'm then loading the ID values into an array, and trying to feed that array into
another SQL query, without any success.

  







SELECT  ID,SEQUENCE,TEXT
FROMMyOtherSource
WHERE   ID = '#Current#'




Unfortunately, I get

Error resolving parameter CURRENT

which leads me to believe that CF is trying to use the CURRENT variable before
the CFLOOP happens.

I apologize if I'm making a stupid mistake here; I'm still fairly new at CF, and
tackling a few projects that I'm willing to admit are over my head (I was teased
by a friend last week--"Your program has nested CFLOOPs, and you're using
CFQUERY and SQL to go get the data you want, but you don't know how to use a
 tag...??).  Any advice would be appreciated.

Allan Davis
[EMAIL PROTECTED]


Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

SQL query problem...

[Ryan Williams]

I have a SQL problem.  I know what information I want to retrieve from
the DB,
however, I do not know what precisely is the proper SQL syntax/function  is
in order to
achieve it.

   The query as it is currently  written in my CF template is:
select wl.*,p.*,wlg.*,wb.*,wsnd.*,wrcvd.*,ws.*
from wire_list wl
left join P_officers p on wl.reg_by = p.off_id
left join wire_status ws on wl.status_code = ws.status_code
left join wire_log wlg on wl.wire_id = wlg.wire_id
left join wire_senders wsnd on wl.sender_id = wsnd.sender_id
left join wire_rcv_modes wrcvd on wl.rcv_mode = wrcvd.rcv_mode
left join wire_banks wb on wl.bank_id = wb.bank_id
where wl.wire_id = (
 select max(right(wire_id,(len(wire_id)-2)))
 from wire_list)

The error this code generates when I attempt to run it in SQL query
analyzer:
Server: Msg 536, Level 16, State 1, Line 1
Invalid length parameter passed to the substring function.

The content of the wire_id column looks like this format:
ab999 and is of varchar datatype.

I did not code these tables and I am in the middle of re-coding the
entire site to be FuseBox compliant.  I am trying to select the top (max)
wire_id from the wire_list table, and display all records related
to the returned value of the max function, but the result that areretrieved
to date,
with the code "Select max(wire_id)" and no WHERE clause,
is ab99 when I know there are wire_id's in the table that have values
of ab999, etc.  It seems that SQL does not recognize that ab999 is the
larger value when compared to ab99.

The solution I am trying to apply with the code at the top of this email  is
to "get"
the "number" part of the wire_id column, minus the two letters, and then use
the max function on
the remainder to get the ab999 wire_id i know is in the table.  I looked
through the
SQL books online and it seems that SQL Sever does not have the ability to
convert
the varchar datatype (of the wire_id) column to the int data type that
the max function seems to need in order to work properly.

Does anyone know of a work around for this problem?  Or am I going totally
in  the wrong
direction with this logic?

Any help/ideas/comments would be greatly appreciated.  Thanks.

Ryan Williams
[EMAIL PROTECTED]

 ---
---
 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.


--
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.

OT: SQL Query

[Paul Giesenhagen]

I have a query that I am trying to get all my descriptions from my choiceTable that 
are not found in my productChoiceTable that have a productid of a url.product value.  
Basically if a choice id is found in the productChoiceTable that also has the 
url.productid in the productid column, it is discarded in the result.

Here is my table layout:

choiceTable
id, choice, description

productChoiceTable
id,choiceid,productid


SELECT c.id, c.description
FROM choiceTable c, productChoiceTable pc
WHERE c.id = pc.choiceid
AND pc.productid NOT IN ('#url.productid#')

Any suggestions apreciated!

Paul Giesenhagen
QuillDesign


__
Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. http://www.fusionauthority.com/ads.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Re: SQL Query

[Douglas Brown]

More than likely something like so...


SELECT   c.id, c.description
FROM   choiceTable c,productChoiceTable pc
WHERE   c.id = pc.choiceid
AND   pc.productid NOT IN
 ( SELECT productid
  FROM productChoiceTable
  WHERE productid = '#url.productid#' )




Douglas Brown
Email: [EMAIL PROTECTED]
- Original Message -
From: "Paul Giesenhagen" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Friday, July 26, 2002 7:22 AM
Subject: OT: SQL Query


> I have a query that I am trying to get all my descriptions from my
choiceTable that are not found in my productChoiceTable that have a productid
of a url.product value.  Basically if a choice id is found in the
productChoiceTable that also has the url.productid in the productid column, it
is discarded in the result.
>
> Here is my table layout:
>
> choiceTable
> id, choice, description
>
> productChoiceTable
> id,choiceid,productid
>
> 
> SELECT c.id, c.description
> FROM choiceTable c, productChoiceTable pc
> WHERE c.id = pc.choiceid
> AND pc.productid NOT IN ('#url.productid#')
>
> Any suggestions apreciated!
>
> Paul Giesenhagen
> QuillDesign
>
>
> 
__
This list and all House of Fusion resources hosted by CFHosting.com. The place for 
dependable ColdFusion Hosting.
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: SQL Query

[Christopher Olive]

try...

SELECT c.id, c.description
FROM
choicetable c
WHERE
c.productid = '#URL.productid#'
AND c.id NOT IN (
SELECT
pc.choiceid
FROM
productchoicetable pc
WHERE
pc.productid = '#URL.productid#'
)

-Original Message-
From: Paul Giesenhagen [mailto:[EMAIL PROTECTED]]
Sent: Friday, July 26, 2002 10:22 AM
To: CF-Talk
Subject: OT: SQL Query


I have a query that I am trying to get all my descriptions from my
choiceTable that are not found in my productChoiceTable that have a
productid of a url.product value.  Basically if a choice id is found in the
productChoiceTable that also has the url.productid in the productid column,
it is discarded in the result.

Here is my table layout:

choiceTable
id, choice, description

productChoiceTable
id,choiceid,productid


SELECT c.id, c.description
FROM choiceTable c, productChoiceTable pc
WHERE c.id = pc.choiceid
AND pc.productid NOT IN ('#url.productid#')

Any suggestions apreciated!

Paul Giesenhagen
QuillDesign



__
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: SQL Query

[S . Isaac Dealey]

> try...

> SELECT c.id, c.description
> FROM
>   choicetable c
> WHERE
>   c.productid = '#URL.productid#'
>   AND c.id NOT IN (
>   SELECT
>   pc.choiceid
>   FROM
>   productchoicetable pc
>   WHERE
>   pc.productid = '#URL.productid#'
>   )

or use not exists

SELECT c.id, c.description FROM choicetable c
WHERE NOT EXISTS (
SELECT pc.productid FROM productChoiceTable
WHERE pc.productid = #url.productid#
)

Might be faster -- not sure... You'd have to either compare the plans for
the two queries in enterprise manager or just run them several times and
compare the execution times...

This assumes you're using SQL Server


Isaac Dealey
Certified Advanced ColdFusion Developer

www.turnkey.to
954-776-0046
__
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
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Re: SQL Query

[Paul Giesenhagen]

Query worked great  thanks folks .. it was simple huh?

Paul Giesenhagen
QuillDesign



> > try...
>
> > SELECT c.id, c.description
> > FROM
> > choicetable c
> > WHERE
> > c.productid = '#URL.productid#'
> > AND c.id NOT IN (
> > SELECT
> > pc.choiceid
> > FROM
> > productchoicetable pc
> > WHERE
> > pc.productid = '#URL.productid#'
> > )
>
> or use not exists
>
> SELECT c.id, c.description FROM choicetable c
> WHERE NOT EXISTS (
> SELECT pc.productid FROM productChoiceTable
> WHERE pc.productid = #url.productid#
> )
>
> Might be faster -- not sure... You'd have to either compare the plans for
> the two queries in enterprise manager or just run them several times and
> compare the execution times...
>
> This assumes you're using SQL Server
>
>
> Isaac Dealey
> Certified Advanced ColdFusion Developer
>
> www.turnkey.to
> 954-776-0046
> 
__
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Re: sql query

[Rob Keniger]

on 2000-09-14 3:27 PM, avex at [EMAIL PROTECTED] wrote:


> However,.what if i want to show 2 reviews.ID 7 and ID 9.
> 
> how do i show this
> and how would i construct a dynamic query that would allow for multiple
> ID's(a cfloop?)

Store the IDs in a comma delimited list and use:


SELECT Storename, ID, City
FROM review
Where ID IN (#ID_LIST#)


HTH

Rob Keniger   [EMAIL PROTECTED]


big bang solutions
 p +61 7 3311 2733  f +61 7 3311 2744




--
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.

Re: sql query

[Dick Applebaum]

Where ID IN (#ID_1#,#ID_2#)


At 3:27 PM +1000 9/14/00, avex wrote:
>hey everyone
>
>i have always used the following SQL statement to show a particular database
>entry.
>
>
>SELECT Storename, ID, City
>FROM review
>Where ID = #ID#
>
>
>
>where i passed the ID from the url or form or whatever.
>
>However,.what if i want to show 2 reviews.ID 7 and ID 9.
>
>how do i show this
>and how would i construct a dynamic query that would allow for multiple
>ID's(a cfloop?)
>
>this is killing me!
>
>i am going to buy Forta's SQL book today..
>
>thanks
>
>chad
--
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.

Re: sql query

[lxy]

- Original Message -
From: "Dick Applebaum" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, September 14, 2000 3:58 PM
Subject: Re: sql query


>
> Where ID IN (#ID_1#,#ID_2#)
>
>
> At 3:27 PM +1000 9/14/00, avex wrote:
> >hey everyone
> >
> >i have always used the following SQL statement to show a particular
database
> >entry.
> >
> >
> >SELECT Storename, ID, City
> >FROM review
> >Where ID = #ID#
> >
CFQUERY Name="aoyama" Datasource="super">
SELECT Storename, ID, City
FROM review
Where ID in (7,9)


> >
> >where i passed the ID from the url or form or whatever.
> >
> >However,.what if i want to show 2 reviews.ID 7 and ID 9.
> >
> >how do i show this
> >and how would i construct a dynamic query that would allow for multiple
> >ID's(a cfloop?)
> >
> >this is killing me!
> >
> >i am going to buy Forta's SQL book today..
> >
> >thanks
> >
> >chad
> --

> 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.
>

--
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.

RE: sql query

[Anthony Geoghegan]

Hi Chad,


SELECT Storename, ID, City
FROM review
Where ID IN ('#ID1#, #ID2#')



Regards,
Anthony Geoghegan.
Lead Developer,
IFTN 
http://www.wow.ie
mailto:[EMAIL PROTECTED]


--
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.

SQL Query Syntax

[[EMAIL PROTECTED]]

Here with a more appropriate subject line...

-Original Message-
From: fleming [mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 28, 2000 5:30 PM
To: '[EMAIL PROTECTED]'
Subject: RE: Protecting Content / content leeching... A CF method?


Hello Everyone,

Does anyone know what syntax works for SQL Query using CF 4.5.1 (Solaris)
and Foxpro v3.0 ODBC connection for a statement like this:


SELECT pohdr.po_number, pohdr.vend_name, pohdr.vendor_id, pohdr.po_amount,
pohdr.org_po_dt, pohdr.pay_term, pohdr.ship_to_nm, pohdr.ord_status,
podtl.itm_glmoco, podtl.ln_sta, podtl.ln_no, podtl.prom_dock
FROM  pohdr, podtl

#where#
order by pohdr.po_number


When I test on my server with NT installation of cf 4.5.1, it works fine but
not on the client solaris server.  Any suggestions are very much
appreciated.

Thanks,
Nehal


--
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.

--
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.

OT SQL Query

[Paul Giesenhagen]

Ok, I have an operation that I need help writing a query for... it may be simple or it 
may be impossible... I will do my best to explain what I am wanting in advance, 
thanks for your help.

I have a table:  (owner and sub_object values are ID values in another table)

idownersub_object
1  AB
2  AC
3  AD
4  BY
5  BJ
6  CK
7  KL
8  KF
9  YW

This is a simple parent/child relationship, the owner is the parent and the sub_object 
is the child.  Parents can be children and children can become Parents...

These items are departments,

Department A has a sub-departments of B, C & D
AND
Sub-department B (now a parent) has sub-departments - Y and J
...AND 
sub-department Y (now a parent) has a sub-department W
...and so on .. forever down and down

A Known Factor:
OWNER A is the starting point (that is known) so B,C,D are the MAIN departments

I want to write a query or queries/outputs/loops whatever, that will display the 
following  ... So I want the output to look like this:

B
B/Y
B/Y/W
B/J
C
C/K
C/K/L
C/K/F

Side Note, each of the owner and sub_object values are defined in another table, so I 
will have to join two tables together to get the actual NAME of the department... I 
don't think that will matter but thought I should point it out.

Any help would be appreciated!

Paul Giesenhagen
QuillDesign
http://www.quilldesign.com
SiteDirector v2.0 - Commerce Builder




__
Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. http://www.fusionauthority.com/ads.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: SQL Query

[Dimple Goshar]

You can write this query using NOT IN operator


> -Original Message-
> From: Joshua Tipton [SMTP:[EMAIL PROTECTED]]
> Sent: Friday, April 26, 2002 6:19 PM
> To:   CF-Talk
> Subject:  SQL Query
> 
> I need this query to pull the reg_no, dis_no, m_id, and the y_id from
> tbl_CTMCORPMONTHYEARRDD where it cannot be found in pagan table.  I wrote
> this query and cannot get it to work please help.
> 
> Josh
> 
> 
> SELECT tbl_CTMCORPMONTHYEARRDD.reg_no,
> tbl_CTMCORPMONTHYEARRDD.dis_no,
> tbl_CTMCORPMONTHYEARRDD.m_id,
> tbl_CTMCORPMONTHYEARRDD.y_id
> FROM tbl_CTMCORPMONTHYEARRDD INNER JOIN
> pagan ON
> tbl_CTMCORPMONTHYEARRDD.m_id <> pagan.m_id AND
> tbl_CTMCORPMONTHYEARRDD.y_id <> pagan.y_id AND
> tbl_CTMCORPMONTHYEARRDD.reg_no = pagan.reg_no AND
> tbl_CTMCORPMONTHYEARRDD.dis_no = pagan.DIS_NO
> 
> 
__
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: SQL Query

[Jeff Brown]

wouldn't this be an OUTER JOIN LEFT?

v/r,
Jeff

-Original Message-
From: Joshua Tipton [mailto:[EMAIL PROTECTED]]
Sent: Friday, April 26, 2002 8:49 AM
To: CF-Talk
Subject: SQL Query


I need this query to pull the reg_no, dis_no, m_id, and the y_id from
tbl_CTMCORPMONTHYEARRDD where it cannot be found in pagan table.  I wrote
this query and cannot get it to work please help.

Josh


SELECT tbl_CTMCORPMONTHYEARRDD.reg_no,
tbl_CTMCORPMONTHYEARRDD.dis_no,
tbl_CTMCORPMONTHYEARRDD.m_id,
tbl_CTMCORPMONTHYEARRDD.y_id
FROM tbl_CTMCORPMONTHYEARRDD INNER JOIN
pagan ON
tbl_CTMCORPMONTHYEARRDD.m_id <> pagan.m_id AND
tbl_CTMCORPMONTHYEARRDD.y_id <> pagan.y_id AND
tbl_CTMCORPMONTHYEARRDD.reg_no = pagan.reg_no AND
tbl_CTMCORPMONTHYEARRDD.dis_no = pagan.DIS_NO


__
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Re: sql query

[BEN MORRIS]

All you need is the Insert() function, something like:

SSN = Insert("-", SSN, 3);
SSN = Insert("-", SSN, 6);

>>> "Joshua Tipton" <[EMAIL PROTECTED]> 05/01/02 09:14AM >>>
I would like to run an update query to take a 9 digit social security number
and place the dashes in the correct spots.  IE.. 999-99-.  Can anyone
help me with this?

Joshua Tipton


__
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
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: sql query

[Jeff Brown]

The example below seems easier, but you could also:




v/r,
Jeff
-Original Message-
From: BEN MORRIS [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 01, 2002 9:20 AM
To: CF-Talk
Subject: Re: sql query


All you need is the Insert() function, something like:

SSN = Insert("-", SSN, 3);
SSN = Insert("-", SSN, 6);

>>> "Joshua Tipton" <[EMAIL PROTECTED]> 05/01/02 09:14AM >>>
I would like to run an update query to take a 9 digit social security number
and place the dashes in the correct spots.  IE.. 999-99-.  Can anyone
help me with this?

Joshua Tipton



__
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Re: sql query

[Alex]

this has nothing to do with sql. you have to parse the 9 digit number and
set it equal to your reformatted number. Use left() or substring 

On Wed, 1 May 2002, Joshua Tipton wrote:

> I would like to run an update query to take a 9 digit social security number
> and place the dashes in the correct spots.  IE.. 999-99-.  Can anyone
> help me with this?
> 
> Joshua Tipton
> 
> 
__
Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. http://www.fusionauthority.com/ads.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: sql query

[Joshua Tipton]

Thanks everyone.

-Original Message-
From: Jeff Brown [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 01, 2002 9:28 AM
To: CF-Talk
Subject: RE: sql query


The example below seems easier, but you could also:




v/r,
Jeff
-Original Message-
From: BEN MORRIS [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 01, 2002 9:20 AM
To: CF-Talk
Subject: Re: sql query


All you need is the Insert() function, something like:

SSN = Insert("-", SSN, 3);
SSN = Insert("-", SSN, 6);

>>> "Joshua Tipton" <[EMAIL PROTECTED]> 05/01/02 09:14AM >>>
I would like to run an update query to take a 9 digit social security number
and place the dashes in the correct spots.  IE.. 999-99-.  Can anyone
help me with this?

Joshua Tipton




__
This list and all House of Fusion resources hosted by CFHosting.com. The place for 
dependable ColdFusion Hosting.
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: sql query

[BillyC]

You could do it in SQL, just like you said:

DECLARE @oldSsn char(9)
SET @oldSsn = '#ssn#'

DECLARE @newSsn char(11)
SET @newSsn = SUBSTRING(@oldSsn, 1, 3) + '-' + SUBSTRING(@oldSsn, 4, 5)
+ '-' + SUBSTRING(@oldSsn, 6, 9)

INSERT INTO myTable
(ssn)
VALUES
(@newSsn)

---
Billy Cravens



-Original Message-
From: Alex [mailto:[EMAIL PROTECTED]] 
Sent: Wednesday, May 01, 2002 8:57 AM
To: CF-Talk
Subject: Re: sql query

this has nothing to do with sql. you have to parse the 9 digit number
and
set it equal to your reformatted number. Use left() or substring 

On Wed, 1 May 2002, Joshua Tipton wrote:

> I would like to run an update query to take a 9 digit social security
number
> and place the dashes in the correct spots.  IE.. 999-99-.  Can
anyone
> help me with this?
> 
> Joshua Tipton
> 
> 

__
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Re: SQL query

[Dina Hess]

> I need to choose a group of records from a table based in the
fact that a
> datetime field is not null .

where date_variable IS NOT NULL

~ dina



__
Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. http://www.fusionauthority.com/ads.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Re: SQL query

[Mario Martinez]

thank you very much
regards
Mario
- Original Message -
From: "Dina Hess" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Tuesday, May 07, 2002 10:31 AM
Subject: Re: SQL query


> > I need to choose a group of records from a table based in the
> fact that a
> > datetime field is not null .
>
> where date_variable IS NOT NULL
>
> ~ dina
>
>
>
> 
__
This list and all House of Fusion resources hosted by CFHosting.com. The place for 
dependable ColdFusion Hosting.
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

SQL Query Question

[Nick Bourgeois]

I'm having trouble with this query.

There are two tables, issue and issueOption.

There is a one-to-many relationship between issue ("one") and issueOption
("many").  issID is the key that joins the two tables.

I want to select all records from issue that are not in the issueOption.  In
other words, get all issues without options.

Thanks in advance,

Nick

__
Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. http://www.fusionauthority.com/ads.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

SQL query question

[Dirk Sieber]

Hi everyone,

Okay, I've been struggling with this one for a while, and I'm sure there's a
solution, but I'm just not seeing it.

I've got a collection of tables, among which are document, and attrib_xref.

In the attrib_xref table, there's (among others) the following columns:
DocID
AttribID
AttribValue

Each document can have multiple attributes, so there may be many lines in
this table, with the same DocID, but different AttribID/AttribValue pairs.

What I'd like to be able to do is an "and" search for multiple attributes,
so I'd like to be able to say that I'm looking for the document with
DocID=x, where AttribID=12 and it's corresponding value is 'some text', AND
where there's also a second Attrib_XRef record with DocID=x, where
AttribID=24 and it's corresponding value is 'some other text'

I also need this to be extensible - ie, a person may specify one attribute
pair, or 2, or 3, or... etc.

Any suggestions (short of re-designing the DB - that's out of my control,
unfortunately).

If someone can point me in the right direction, I'd really appreciate it...

Thanks,
Dirk

__
This list and all House of Fusion resources hosted by CFHosting.com. The place for 
dependable ColdFusion Hosting.
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: SQL Query

[Gantz, Shlomy]

because you have Highlights.* ... 

shlomy

-Original Message-
From: Nick Le Mouton [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, November 13, 2001 5:51 PM
To: CF-Talk
Subject: SQL Query


SELECT DISTINCT highlightDates.highlightID, HighlightDates.HighlightDate,
Highlights.*
FROM Highlights, HighlightDates
WHERE HighlightDates.HighlightDate >= #startweek# AND
HighlightDates.highlightdate <= #DateAdd('ww', 1, startweek)# AND
Highlights.HighlightID = HighLightDates.HighlightID AND Highlights.Status =
1

Can some one tell me why the query above doesn't work
I thought it would only look at highlightDates.highlightID and select a
distinct one of those, but it seems to be trying to do a distinct over all
of the selected fields

help! how can i make it select a distinct highlightID?

~~
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: SQL Query

[Sicular, Alexander]

Try breaking up the Highlights.* into its columns.

Good luck,

Alexander Sicular
Chief Technology Architect
Neurological Institute of New York
Columbia University
as867 [at] columbia {dot} edu


|-Original Message-
|From: Nick Le Mouton [mailto:[EMAIL PROTECTED]] 
|Sent: Tuesday, November 13, 2001 5:51 PM
|To: CF-Talk
|Subject: SQL Query
|
|
|SELECT DISTINCT highlightDates.highlightID, 
|HighlightDates.HighlightDate,
|Highlights.*
|FROM Highlights, HighlightDates
|WHERE HighlightDates.HighlightDate >= #startweek# AND 
|HighlightDates.highlightdate <= #DateAdd('ww', 1, startweek)# 
|AND Highlights.HighlightID = HighLightDates.HighlightID AND 
|Highlights.Status = 1
|
|Can some one tell me why the query above doesn't work
|I thought it would only look at highlightDates.highlightID and 
|select a distinct one of those, but it seems to be trying to 
|do a distinct over all of the selected fields
|
|help! how can i make it select a distinct highlightID? 
|
~~
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: SQL Query

[Nick Le Mouton]

i broke them up into its columns, but i still get this message

ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, or image
data type cannot be selected as DISTINCT.

-Original Message-
From: Sicular, Alexander [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, 14 November 2001 11:59 a.m.
To: CF-Talk
Subject: RE: SQL Query


Try breaking up the Highlights.* into its columns.

Good luck,

Alexander Sicular
Chief Technology Architect
Neurological Institute of New York
Columbia University
as867 [at] columbia {dot} edu


|-Original Message-
|From: Nick Le Mouton [mailto:[EMAIL PROTECTED]]
|Sent: Tuesday, November 13, 2001 5:51 PM
|To: CF-Talk
|Subject: SQL Query
|
|
|SELECT DISTINCT highlightDates.highlightID,
|HighlightDates.HighlightDate,
|Highlights.*
|FROM Highlights, HighlightDates
|WHERE HighlightDates.HighlightDate >= #startweek# AND
|HighlightDates.highlightdate <= #DateAdd('ww', 1, startweek)#
|AND Highlights.HighlightID = HighLightDates.HighlightID AND
|Highlights.Status = 1
|
|Can some one tell me why the query above doesn't work
|I thought it would only look at highlightDates.highlightID and
|select a distinct one of those, but it seems to be trying to
|do a distinct over all of the selected fields
|
|help! how can i make it select a distinct highlightID?
|

~~
Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. http://www.fusionauthority.com/ads.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: SQL Query

[Bryan Love]

your assumption is correct.  When using distinct it it returns all distinct
RECORDS.  A record consists of everything in the SELECT clause.

You have two real options here:

1.  If you have a beefy CF server and there aren't too many rows in the db
with the same hightlightID then try this: remove the DISTINCT clause, add
'ORDER BY highlightID' and then use .
This will output a row to the page every time a new distinct highlightID is
encountered in the loop.

2.  If you have a weak CF server and a beefy Enterprise level DB then do
this (Oracle version here): 
|SELECT hd.highlightID,
|hd.HighlightDate,
|h.*
|FROM Highlights h, HighlightDates hd
|WHERE hd.HighlightDate >= #startweek# AND
|hd.highlightdate <= #DateAdd('ww', 1, startweek)#
|AND h.HighlightID = hd.HighlightID AND
|h.Status = 1 AND
hd.rowID IN (select min(rowID) from highlightdates group by highlightID)

In the above example you'll notice I used rowID - this is Oracle's way of
internally identifying rows and it always exists.  The MIN() function will
grab the first row that was inserted for each group of similar highlightIDs.
This is an inefficient query, but you don't have much choice - I would
recommend using the GROUP attribute from option 1 unless there are thousands
of rows.


 

Bryan Love Macromedia Certified Professional
Internet Application Developer / Database Analyst
Telecommunication Systems Inc.
[EMAIL PROTECTED]
 

"What father would hesitate to say 'if there must be trouble let it be in my
day, that my child may have peace'?"
- Thomas Paine, An American Crisis



-Original Message-
From: Nick Le Mouton [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, November 13, 2001 3:15 PM
To: CF-Talk
Subject: RE: SQL Query


i broke them up into its columns, but i still get this message

ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, or image
data type cannot be selected as DISTINCT.

-Original Message-
From: Sicular, Alexander [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, 14 November 2001 11:59 a.m.
To: CF-Talk
Subject: RE: SQL Query


Try breaking up the Highlights.* into its columns.

Good luck,

Alexander Sicular
Chief Technology Architect
Neurological Institute of New York
Columbia University
as867 [at] columbia {dot} edu


|-Original Message-
|From: Nick Le Mouton [mailto:[EMAIL PROTECTED]]
|Sent: Tuesday, November 13, 2001 5:51 PM
|To: CF-Talk
|Subject: SQL Query
|
|
|SELECT DISTINCT highlightDates.highlightID,
|HighlightDates.HighlightDate,
|Highlights.*
|FROM Highlights, HighlightDates
|WHERE HighlightDates.HighlightDate >= #startweek# AND
|HighlightDates.highlightdate <= #DateAdd('ww', 1, startweek)#
|AND Highlights.HighlightID = HighLightDates.HighlightID AND
|Highlights.Status = 1
|
|Can some one tell me why the query above doesn't work
|I thought it would only look at highlightDates.highlightID and
|select a distinct one of those, but it seems to be trying to
|do a distinct over all of the selected fields
|
|help! how can i make it select a distinct highlightID?
|


~~
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: SQL Query

[Nick Le Mouton]

for the record this is how it's working now, thanks for your help Bryan, i
used some of your ideas and structured them differently

SELECT *
FROM Highlights
WHERE Highlights.Status = 1 AND Highlights.HighlightID IN (SELECT DISTINCT
highlightDates.highlightID FROM HighlightDates WHERE
HighlightDates.HighlightDate >= #startweek# AND HighlightDates.highlightdate
<= #DateAdd('ww', 1, startweek)#)

=)

-Original Message-
From: Bryan Love [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, 14 November 2001 12:40 p.m.
To: CF-Talk
Subject: RE: SQL Query


your assumption is correct.  When using distinct it it returns all distinct
RECORDS.  A record consists of everything in the SELECT clause.

You have two real options here:

1.  If you have a beefy CF server and there aren't too many rows in the db
with the same hightlightID then try this: remove the DISTINCT clause, add
'ORDER BY highlightID' and then use .
This will output a row to the page every time a new distinct highlightID is
encountered in the loop.

2.  If you have a weak CF server and a beefy Enterprise level DB then do
this (Oracle version here):
|SELECT hd.highlightID,
|hd.HighlightDate,
|h.*
|FROM Highlights h, HighlightDates hd
|WHERE hd.HighlightDate >= #startweek# AND
|hd.highlightdate <= #DateAdd('ww', 1, startweek)#
|AND h.HighlightID = hd.HighlightID AND
|h.Status = 1 AND
hd.rowID IN (select min(rowID) from highlightdates group by highlightID)

In the above example you'll notice I used rowID - this is Oracle's way of
internally identifying rows and it always exists.  The MIN() function will
grab the first row that was inserted for each group of similar highlightIDs.
This is an inefficient query, but you don't have much choice - I would
recommend using the GROUP attribute from option 1 unless there are thousands
of rows.




Bryan Love Macromedia Certified Professional
Internet Application Developer / Database Analyst
Telecommunication Systems Inc.
[EMAIL PROTECTED]


"What father would hesitate to say 'if there must be trouble let it be in my
day, that my child may have peace'?"
- Thomas Paine, An American Crisis



-Original Message-
From: Nick Le Mouton [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, November 13, 2001 3:15 PM
To: CF-Talk
Subject: RE: SQL Query


i broke them up into its columns, but i still get this message

ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, or image
data type cannot be selected as DISTINCT.

-Original Message-
From: Sicular, Alexander [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, 14 November 2001 11:59 a.m.
To: CF-Talk
Subject: RE: SQL Query


Try breaking up the Highlights.* into its columns.

Good luck,

Alexander Sicular
Chief Technology Architect
Neurological Institute of New York
Columbia University
as867 [at] columbia {dot} edu


|-Original Message-
|From: Nick Le Mouton [mailto:[EMAIL PROTECTED]]
|Sent: Tuesday, November 13, 2001 5:51 PM
|To: CF-Talk
|Subject: SQL Query
|
|
|SELECT DISTINCT highlightDates.highlightID,
|HighlightDates.HighlightDate,
|Highlights.*
|FROM Highlights, HighlightDates
|WHERE HighlightDates.HighlightDate >= #startweek# AND
|HighlightDates.highlightdate <= #DateAdd('ww', 1, startweek)#
|AND Highlights.HighlightID = HighLightDates.HighlightID AND
|Highlights.Status = 1
|
|Can some one tell me why the query above doesn't work
|I thought it would only look at highlightDates.highlightID and
|select a distinct one of those, but it seems to be trying to
|do a distinct over all of the selected fields
|
|help! how can i make it select a distinct highlightID?
|



~~
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

SQL Query Problem

[Brian Ferrigno]

I am having a problem trying to think of the correct SQL statement I need.
It's late in the day and my brain is completely fried.

What I am tring to do is get the average price of one column based a an ID
that is passed to the SQL query. For example if a BrandID of 1 is passed
into the query it should output an average price of $4.00

The problem arises when one of the BrandID(BNID) columns doesn't contain any
of the #ID# numbers in it. The value that it returns is NULL which causes
the rest of the SQL statement to return NULL as a result even if the other
columns contain at least one #ID# in the BNID column. For example passing a
BrandID of 24 will return a resultset of NULL because it is not in the
second or third BRANDID column. 

Hopefully I made some sense. Below is the partial table design and SQL
statements I am using in my script. Any help would be great.


TABLE DESIGN
BNID1_3 Price1_3 BNID2_3 Price2_3 BNID3_3 Price3_3
1   $2.0010  $0.001   $5.00
24  $0.001   $5.0023  $0.00

SQL STATEMENT
select
(select avg(Price1_3) from temp1 where BNID1_3=#ID#)+
(select avg(Price2_3) from temp1 where BNID2_3=#ID#)+
(select avg(Price3_3) from temp1 where BNID3_3=#ID#) AS AVGPRICE



~~
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

SQL Query Statement

[Mark Leder]

I have two date fields (cal_date_start and cal_date_end).  The dates are
formatted as mm/dd/.
How would I write a WHERE clause when querying the db to retrieve the
records between the two dates (maybe using the "BETWEEN" statement)?
It's OK to get records on the actual start date and end date. - So if today
is 12/09/2001, its OK to retrieve records between 12/07/2001 and 12/16/2001.

In other words:  WHERE DateValue(Now()) BETWEEN cal_date_start AND
cal_date_end.

Thanks,
Mark
~~
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

SQL query help

[Darren Adams]

Hi guys

Can anyone help,  I just need to check that the date of a record in a
database is within a specific range.


#today#


select * from main_table
where  1=1 
 and datestamp BETWEEN '#date#' AND #today#
 
 and region = '#region#' 
 and material = '#material#'
 and vertical = '#vertical#'
 and target = '#target#'
 and product_service = '#product#'



This query give me an error in assignment.

I know that this should be easy but I cant think of it!!!

Cheers,
Darren Adams
Web Developer
Marketing Department
Systems Union

mail: [EMAIL PROTECTED]

__
Dedicated Windows 2000 Server
  PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
  Instant Activation · $99/Month · Free Setup
  http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

SQL query help

[Chad Gray]

I have a database similar to this:

SKU NAMECOLOR   STYLE
1   bar blue12"
2   bar blue6"
3   bar blue5"
4   bar red 12"
5   bar red 6"
6   bar red 5"


I want to find all the colors that 'bar' comes in.

If i do a query on this to find all the colors for the NAME 'bar'.  I get:
blue
blue
blue
red
red
red

How do i get results like this?
blue
red

Thanks in advanced
Chad


~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: SQL Query

[Bryan Love]

where's the query?


Bryan Love ACP
Internet Application Developer
[EMAIL PROTECTED]



-Original Message-
From: David Clay [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 24, 2001 1:50 PM
To: CF-Talk
Subject: SQL Query



I am looking for help on a way to query a table fast and efficiently.  I am
currently using a ODBC connection to SQL2000 Server with a simple CFQUERY
tag doing the query.  For a table with 18K records, it takes 14 seconds. 

I know this is not doing something correct!!  Any help designing new query
statements or set up of CF or SQL2000 Servers would be helpful. 

Thank you for your time.

Dave Clay
Internet Facilitator
Trus Joist, A Weyerhaeuser Business
5995 Greenwood Plaza Blvd, Suite 100
Greenwood Village, CO 80111
303.770.8506
303.770.8506
~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Re: SQL Query

[John Paitel]

Dave - what does your query look like currently?

Also, I've never used ODBC with SQL, but have you tried doing it with a 
connection via code, rather than the ODBC mechanism? With ODBC, you have to 
go through a layer of interpretation each way (If I understand the workings 
correctly), and this may be adding time.

John


At 02:50 PM 7/24/01 -0600, you wrote:

>I am looking for help on a way to query a table fast and efficiently.  I 
>am currently using a ODBC connection to SQL2000 Server with a simple 
>CFQUERY tag doing the query.  For a table with 18K records, it takes 14 
>seconds.
>
>I know this is not doing something correct!!  Any help designing new query 
>statements or set up of CF or SQL2000 Servers would be helpful.
>
>Thank you for your time.
>
>Dave Clay
>Internet Facilitator
>Trus Joist, A Weyerhaeuser Business
>5995 Greenwood Plaza Blvd, Suite 100
>Greenwood Village, CO 80111
>303.770.8506
>303.770.8506
> 
>
>
>
>
~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: SQL Query

[Mark Warrick]

May we see the query?

The best thing to do is to write your SQL statements to search on as few a number of 
fields as possible, especially when you're doing full-text searching.  Turning on 
full-text indexing in SQL will increase the performance a little bit.  Turning your 
SQL statements into stored procedures may also help.  Cacheing queries definitely 
helps.

Avoid using open LIKE statements wherever possible (LIKE %#var#%).  If you can't avoid 
using LIKE operators in your SQL statements, then at least try to limit the searching 
capability to BEGINS WITH, ENDS WITH, IS EXACTLY rather than ANY PART.

Also, avoid multi-table joins to increase performance.

Has anyone written a book that specifically covers this subject?  I'd buy it.  Ben?  
How about you buddy?

---mark

Mark Warrick - Fusioneers.com
Personal Email: [EMAIL PROTECTED]
Business Email: [EMAIL PROTECTED]
Phone: 714-547-5386
Efax: 801-730-7289
Personal URL: http://www.warrick.net
Business URL: http://www.fusioneers.com
ICQ: 125160 / AIM: markwarric


-Original Message-
From: David Clay [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 24, 2001 1:50 PM
To: CF-Talk
Subject: SQL Query



I am looking for help on a way to query a table fast and efficiently.  I am currently 
using a ODBC connection to SQL2000 Server with a simple CFQUERY tag doing the query.  
For a table with 18K records, it takes 14 seconds. 

I know this is not doing something correct!!  Any help designing new query statements 
or set up of CF or SQL2000 Servers would be helpful. 

Thank you for your time.

Dave Clay
Internet Facilitator
Trus Joist, A Weyerhaeuser Business
5995 Greenwood Plaza Blvd, Suite 100
Greenwood Village, CO 80111
303.770.8506
303.770.8506
~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: SQL Query

[Stuart Miller]

Can you post the SQL statments from your query, please?

-Original Message-
From: David Clay [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 24, 2001 9:50 PM
To: CF-Talk
Subject: SQL Query



I am looking for help on a way to query a table fast and efficiently.  I am
currently using a ODBC connection to SQL2000 Server with a simple CFQUERY
tag doing the query.  For a table with 18K records, it takes 14 seconds. 

I know this is not doing something correct!!  Any help designing new query
statements or set up of CF or SQL2000 Servers would be helpful. 

Thank you for your time.

Dave Clay
Internet Facilitator
Trus Joist, A Weyerhaeuser Business
5995 Greenwood Plaza Blvd, Suite 100
Greenwood Village, CO 80111
303.770.8506
303.770.8506
~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: SQL Query

[Philip Arnold - ASP]

> I am looking for help on a way to query a table fast and
> efficiently.  I am currently using a ODBC connection to SQL2000
> Server with a simple CFQUERY tag doing the query.  For a table
> with 18K records, it takes 14 seconds.
>
> I know this is not doing something correct!!  Any help designing
> new query statements or set up of CF or SQL2000 Servers would be helpful.

Without seeing your SQL, some comments;

Index the table to hell and back
Try to only search on Indexed fields
Never use SELECT *
Use the (nolock) acellerator (sp?)

Some queries are actually faster to do specific tables in one query, then do
the second, then use CF5's Query a Query to join them

HTH

Philip Arnold
Director
Certified ColdFusion Developer
ASP Multimedia Limited
T: +44 (0)20 8680 1133

"Websites for the real world"

**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
**



~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: SQL Query

[Thomas Chiverton]

> Index the table to hell and back

Indexing too many fields, or the wrong combinations, can actualy hurt
performance.
It's best to put a few benchmarks in place, and add a few indices at a time.

> Never use SELECT *

Unless in your database 'select *' is quicker than 'select
field,wibble,rar'.

~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: SQL Query

[Andy Ewings]

> Index the table to hell and back

>>Indexing too many fields, or the wrong combinations, can actualy hurt
>>performance.
>>It's best to put a few benchmarks in place, and add a few indices at a
time.

True.indexes improve speed of selects but slow down inserts,updates and
deletes as the whole index needs to be rebuilt every time data is changed.

> Never use SELECT *

>>Unless in your database 'select *' is quicker than 'select
>>field,wibble,rar'.

As far as I'm aware SELECT * is quicker than selecting all fields in your
table but if you don't want all the fields then you should be specific as
this will be quicker.  However it is still good practice to not use SELECT *
as you may want all of the fields at anyone given time but in 6months time
another developer may have added columns to the table for other purposes
that you don't need in your select statement


-- 
Andrew Ewings
Project Manager
Thoughtbubble Ltd 
http://www.thoughtbubble.net 
-- 
United Kingdom 
http://www.thoughtbubble.co.uk/ 
Tel: +44 (0) 20 7387 8890 
-- 
New Zealand 
http://www.thoughtbubble.co.nz/ 
Tel: +64 (0) 9 488 9131 
-- 
The information in this email and in any attachments is confidential and
intended solely for the attention and use of the named addressee(s). Any
views or opinions presented are solely those of the author and do not
necessarily represent those of Thoughtbubble. This information may be
subject to legal, professional or other privilege and further distribution
of it is strictly prohibited without our authority. If you are not the
intended recipient, you are not authorised to disclose, copy, distribute, or
retain this message. Please notify us on +44 (0)207 387 8890.



-Original Message-
From: Thomas Chiverton [mailto:[EMAIL PROTECTED]]
Sent: 25 July 2001 13:06
To: CF-Talk
Subject: RE: SQL Query


> Index the table to hell and back

Indexing too many fields, or the wrong combinations, can actualy hurt
performance.
It's best to put a few benchmarks in place, and add a few indices at a time.

> Never use SELECT *

Unless in your database 'select *' is quicker than 'select
field,wibble,rar'.
~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: SQL Query

[Neil Clark]

Query of Query in CF5 is always a good one too.



~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: SQL Query

[Thomas Chiverton]

> > Index the table to hell and back
> 
> >>Indexing too many fields, or the wrong combinations, can 
> >>actualy hurt
> >>performance.
> >>It's best to put a few benchmarks in place, and add a few 
> >>indices at a
> >>time.
> 
> True.indexes improve speed of selects

Mostly. Theres no point indexing rar,wibble,foo, if all your where clauses
use rar,foo,theother for instance.

> > Never use SELECT *
> 
> >>Unless in your database 'select *' is quicker than 'select
> >>field,wibble,rar'.
> 
> As far as I'm aware SELECT * is quicker than selecting all 
> fields in your
> table 

I'm sure I've heard otherwise...

> but if you don't want all the fields then you should be 
> specific as
> this will be quicker. 

And result in smaller server memoray usage too.

~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Re: SQL Query

[Wjreichard]

No ... having too many indexes can actually lead to decreased selection 
speed. This is because SQL generates an execution plan in which it examines 
the available indexes and picks the one(s) it thinks is best.

The way to maximize selection speed for a single query is to create and test 
individual indexes and benchmark them. Don't forget to try various composite 
indexes ... wide index containing more than a single column which typically 
mach your Where and Order By clauses. 

Note, that the available indexes are used for all queries against the table. 
So optimization for a specific query could decrease speed of other queries.

Also, SQL provides the Index Tuning Wizard which allows you to select and 
create an optimal set of indexes.

Cheers,
Bill 

In a message dated 7/25/01 8:14:24 AM Eastern Daylight Time, 
[EMAIL PROTECTED] writes:


> > Index the table to hell and back
> 
> >>Indexing too many fields, or the wrong combinations, can actualy hurt
> >>performance.
> >>It's best to put a few benchmarks in place, and add a few indices at a
> time.
> 
> True.indexes improve speed of selects but slow down inserts,updates and
> deletes as the whole index needs to be rebuilt every time data is changed.
> 





~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Re: SQL Query

[G]

- Original Message -
From: "Philip Arnold - ASP" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, July 25, 2001 6:55 AM
Subject: RE: SQL Query


> > I am looking for help on a way to query a table fast and
> > efficiently.  I am currently using a ODBC connection to SQL2000
> > Server with a simple CFQUERY tag doing the query.  For a table
> > with 18K records, it takes 14 seconds.
> >
> > I know this is not doing something correct!!  Any help designing
> > new query statements or set up of CF or SQL2000 Servers would be
helpful.
>
> Without seeing your SQL, some comments;
>
> Index the table to hell and back
> Try to only search on Indexed fields
> Never use SELECT *
> Use the (nolock) acellerator (sp?)
>
> Some queries are actually faster to do specific tables in one query, then
do
> the second, then use CF5's Query a Query to join them
>

Be wary of the NOLOCK command.  Not only does it tell the database not to
administer a lock for the current read, but it also ignores other locks.  So
from what I can tell, this won't make your query run any faster, just ensure
that this particular query won't be locked out and won't lock anyone else
out.  This is a dangerous situation for a large scale application with
hundreds of concurrent users updating, adding, and deleting data
simultaneously.  The locks are present for a reason, namely, data integrity.

Brian


~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Re: SQL Query

[David Fafard]

From: <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, July 25, 2001 8:36 AM
Subject: Re: SQL Query




> Also, SQL provides the Index Tuning Wizard which allows you to select and
> create an optimal set of indexes.
>
> Cheers,
> Bill



I agree.. typically I save all my web queries to a text file. Then go into
MS SQL Server and using the Index Tuning Wizard, create my indexes
from my individual SQL statements. The wizard will let you know if that
index exists already and give you benchmarks for SQL. Helpful.

Dave


~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: SQL Query

[Andy Ewings]

Yep I realise thisjust trying to make the point as to when and why you'd
use indexesI also think you should limit the amount of fields use in
your index.  Don't just put them in their for the hell of it.  If you
include fileds in your where clause that are in an index then this will
improve performance.  Obviously in real life it works the other way
round.so you vuild your indexes around your where clauses.

-- 
Andrew Ewings
Project Manager
Thoughtbubble Ltd 
http://www.thoughtbubble.net 
-- 
United Kingdom 
http://www.thoughtbubble.co.uk/ 
Tel: +44 (0) 20 7387 8890 
-- 
New Zealand 
http://www.thoughtbubble.co.nz/ 
Tel: +64 (0) 9 488 9131 
-- 
The information in this email and in any attachments is confidential and
intended solely for the attention and use of the named addressee(s). Any
views or opinions presented are solely those of the author and do not
necessarily represent those of Thoughtbubble. This information may be
subject to legal, professional or other privilege and further distribution
of it is strictly prohibited without our authority. If you are not the
intended recipient, you are not authorised to disclose, copy, distribute, or
retain this message. Please notify us on +44 (0)207 387 8890.



-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: 25 July 2001 13:36
To: CF-Talk
Subject: Re: SQL Query


No ... having too many indexes can actually lead to decreased selection 
speed. This is because SQL generates an execution plan in which it examines 
the available indexes and picks the one(s) it thinks is best.

The way to maximize selection speed for a single query is to create and test

individual indexes and benchmark them. Don't forget to try various composite

indexes ... wide index containing more than a single column which typically 
mach your Where and Order By clauses. 

Note, that the available indexes are used for all queries against the table.

So optimization for a specific query could decrease speed of other queries.

Also, SQL provides the Index Tuning Wizard which allows you to select and 
create an optimal set of indexes.

Cheers,
Bill 

In a message dated 7/25/01 8:14:24 AM Eastern Daylight Time, 
[EMAIL PROTECTED] writes:


> > Index the table to hell and back
> 
> >>Indexing too many fields, or the wrong combinations, can actualy hurt
> >>performance.
> >>It's best to put a few benchmarks in place, and add a few indices at a
> time.
> 
> True.indexes improve speed of selects but slow down inserts,updates
and
> deletes as the whole index needs to be rebuilt every time data is changed.
>
~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: SQL Query

[Thomas Chiverton]

> Yep I realise thisjust trying to make the point as to 
> when and why you'd
> use indexes

Well, you should always start any performance tuning by deciding on some
metrics.

~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: SQL Query

[Bud]

On 7/25/01, Philip Arnold - ASP penned:
>Try to only search on Indexed fields
>Never use SELECT *

I usually get quicker queries when using SELECT * if I need to get 
the values of all or most of the fields. Of course it's the other way 
around of you've got 20 columns in your table and only need the 
values from a few of them. I used to always add all the column names 
but now I test it both ways and go with the quickest.
-- 

Bud Schneehagen - Tropical Web Creations

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
ColdFusion Solutions / eCommerce Development
[EMAIL PROTECTED]
http://www.twcreations.com/
954.721.3452

~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: SQL Query

[Andy Ewings]

yeh but what happens if the table grows with time?

-- 
Andrew Ewings
Project Manager
Thoughtbubble Ltd 
http://www.thoughtbubble.net 
-- 
United Kingdom 
http://www.thoughtbubble.co.uk/ 
Tel: +44 (0) 20 7387 8890 
-- 
New Zealand 
http://www.thoughtbubble.co.nz/ 
Tel: +64 (0) 9 488 9131 
-- 
The information in this email and in any attachments is confidential and
intended solely for the attention and use of the named addressee(s). Any
views or opinions presented are solely those of the author and do not
necessarily represent those of Thoughtbubble. This information may be
subject to legal, professional or other privilege and further distribution
of it is strictly prohibited without our authority. If you are not the
intended recipient, you are not authorised to disclose, copy, distribute, or
retain this message. Please notify us on +44 (0)207 387 8890.



-Original Message-
From: Bud [mailto:[EMAIL PROTECTED]]
Sent: 25 July 2001 15:31
To: CF-Talk
Subject: RE: SQL Query


On 7/25/01, Philip Arnold - ASP penned:
>Try to only search on Indexed fields
>Never use SELECT *

I usually get quicker queries when using SELECT * if I need to get 
the values of all or most of the fields. Of course it's the other way 
around of you've got 20 columns in your table and only need the 
values from a few of them. I used to always add all the column names 
but now I test it both ways and go with the quickest.
-- 

Bud Schneehagen - Tropical Web Creations

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
ColdFusion Solutions / eCommerce Development
[EMAIL PROTECTED]
http://www.twcreations.com/
954.721.3452
~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: SQL Query

[Philip Arnold - ASP]

OK, to answer a few of the points made, and why I made the original comments

> I usually get quicker queries when using SELECT * if I need to get
> the values of all or most of the fields. Of course it's the other way
> around of you've got 20 columns in your table and only need the
> values from a few of them. I used to always add all the column names
> but now I test it both ways and go with the quickest.

The reason you shouldn't use SELECT * is that the database has to do another
query before returning the one you've made - it has to ask the table what
fields it contains, and then return you that this - 99.9% of the time, it's
faster to not ask the database engine to do this
The only time I've seen it slower is when you pass it around 40+ field names
and one row, and then the SQL passed is so large that this is slower...

> Be wary of the NOLOCK command.  Not only does it tell the database not to
> administer a lock for the current read, but it also ignores other
> locks.  So
> from what I can tell, this won't make your query run any faster,
> just ensure
> that this particular query won't be locked out and won't lock anyone else
> out.  This is a dangerous situation for a large scale application with
> hundreds of concurrent users updating, adding, and deleting data
> simultaneously.  The locks are present for a reason, namely, data
> integrity.

With SQL Server, all it does is tell it to pass the data back before the
previous locking status - so, instead of checking each row to see if it's
locked, it passes the "current" data - if you're searching a very large
table, then what does it matter if you're 20-30 seconds out of date
I've found that the speed increase of using NOLOCK is around 30% - if that's
not worth your while, then you must be running one hell of a fast database
engine 
Do some tests returning several thousand records and check the query speed -
I can (virtually) guarantee that with NOLOCK you'll get a marked improvement
for the loss of the very latest updates...

> Mostly. Theres no point indexing rar,wibble,foo, if all your where clauses
> use rar,foo,theother for instance.

True, but my comment was meant as a general point, not an "Always index all
fields" - the point being that if you have indexes on the most commonly
searched field, the select speed increases.

Philip Arnold
Director
Certified ColdFusion Developer
ASP Multimedia Limited
T: +44 (0)20 8680 1133

"Websites for the real world"

**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
**



~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

SQL Query Headache...

[Steve Reich]

This works...


SELECT messages.messageid, messages.title AS mesTitle,
messages.userid, messages.body, messages.datestamp,
users.userid, users.fname, users.lname, users.company,
users.copyReplies
FROM messages JOIN
users ON (users.userid = messages.userid)
WHERE messages.parent = 0 AND messages.active = 1
AND messages.body LIKE '%roar%' OR messages.body LIKE '%boby%'
ORDER BY messages.datestamp desc



This doesn't... (Return error: [Microsoft][ODBC SQL Server Driver][SQL
Server]Invalid column name 'roar'.) I don't understand why it is seeing
'roar' as a db column



SELECT messages.messageid, messages.title AS mesTitle,
messages.userid, messages.body, messages.datestamp,
users.userid, users.fname, users.lname, users.company,
users.copyReplies
FROM messages JOIN
users ON (users.userid = messages.userid)
WHERE messages.parent = 0 AND messages.active = 1
#KeyWords#
ORDER BY messages.datestamp desc



What am I missing here?



~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Re: SQL Query

[G]

>
> With SQL Server, all it does is tell it to pass the data back before the
> previous locking status - so, instead of checking each row to see if it's
> locked, it passes the "current" data - if you're searching a very large
> table, then what does it matter if you're 20-30 seconds out of date
> I've found that the speed increase of using NOLOCK is around 30% - if
that's
> not worth your while, then you must be running one hell of a fast database
> engine 
> Do some tests returning several thousand records and check the query
speed -
> I can (virtually) guarantee that with NOLOCK you'll get a marked
improvement
> for the loss of the very latest updates...
>

That's interesting.  I didn't know that NOLOCK could increase performance.
I see your point about the trade off too.  I guess it would depend on the
application. My current project is purely reporting on data that is being
inserted, but never updatedso using NOLOCK should be benign.  However,
my previous work was on a very large application with potentially hundreds
of different processes inserting, updating, and inserting data in "real
time".  In this scenario, the use of NOLOCK carries an unacceptable amount
of risk with it.

Brian


~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: SQL Query

[David Clay]

from the application.cfm page:


SELECT *
FROM COMPANY
ORDER BY Name



for the cfm page:


Choose a Company...

   #Name#



Thanks for your help

Dave Clay
Internet Facilitator
Trus Joist, A Weyerhaeuser Business
5995 Greenwood Plaza Blvd, Suite 100
Greenwood Village, CO 80111
303.770.8506
303.770.8506

>>> [EMAIL PROTECTED] 07/25/01 03:29AM >>>
Can you post the SQL statments from your query, please?

-Original Message-
From: David Clay [mailto:[EMAIL PROTECTED]] 
Sent: Tuesday, July 24, 2001 9:50 PM
To: CF-Talk
Subject: SQL Query



I am looking for help on a way to query a table fast and efficiently.  I am
currently using a ODBC connection to SQL2000 Server with a simple CFQUERY
tag doing the query.  For a table with 18K records, it takes 14 seconds. 

I know this is not doing something correct!!  Any help designing new query
statements or set up of CF or SQL2000 Servers would be helpful. 

Thank you for your time.

Dave Clay
Internet Facilitator
Trus Joist, A Weyerhaeuser Business
5995 Greenwood Plaza Blvd, Suite 100
Greenwood Village, CO 80111
303.770.8506
303.770.8506
~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: SQL Query

[Kwang Suh]

Don't use *.  Explicitly select the columns you need!!  Gawd, I wish SQL
never had *.  What a bane.

Use the attribute blockfactor in your cfquery.

Is the Name column indexed?  If not, then you'll have horrendous performance
from the ORDER BY clause you have.

-Original Message-
From: David Clay [mailto:[EMAIL PROTECTED]]
Sent: July 25, 2001 12:56 PM
To: CF-Talk
Subject: RE: SQL Query


from the application.cfm page:


SELECT *
FROM COMPANY
ORDER BY Name



for the cfm page:


Choose a Company...

   #Name#



Thanks for your help

Dave Clay
Internet Facilitator
Trus Joist, A Weyerhaeuser Business
5995 Greenwood Plaza Blvd, Suite 100
Greenwood Village, CO 80111
303.770.8506
303.770.8506

>>> [EMAIL PROTECTED] 07/25/01 03:29AM >>>
Can you post the SQL statments from your query, please?

-Original Message-
From: David Clay [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 24, 2001 9:50 PM
To: CF-Talk
Subject: SQL Query



I am looking for help on a way to query a table fast and efficiently.  I am
currently using a ODBC connection to SQL2000 Server with a simple CFQUERY
tag doing the query.  For a table with 18K records, it takes 14 seconds.

I know this is not doing something correct!!  Any help designing new query
statements or set up of CF or SQL2000 Servers would be helpful.

Thank you for your time.

Dave Clay
Internet Facilitator
Trus Joist, A Weyerhaeuser Business
5995 Greenwood Plaza Blvd, Suite 100
Greenwood Village, CO 80111
303.770.8506
303.770.8506
~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

SQL Query Quandary

[Jim McAtee]

I've got three tables describing lodging properties and their amenities, as
shown below.  If a lodging property has more than one amenity (for example,
hot tub, min-ibar, fireplace, etc) then there are multiple records for that
property in the property_amenities table.  Pretty basic stuff.

properties
-
propertyid  autonumber
propertynametext

amenities
-
amenityid   autonumber
amenitydescription  text

property_amenities
-
property_amenityid  autonumber
propertyid  number
amenityid   number


I need to form a search for all properties that meet the criteria of having
all of the amenities that a user asks for.  Not an OR, but, logically an
AND - all properties that have ALL of the amenities ask for.  The user
interface is a form with check boxes, so that when the form is submitted I
have a comma delimited list of amenityid's to work with.  Given the above
table structure, I have no idea how to form this query.

Thanks for any assistance,
Jim
~~
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

OT:Advanced SQL Query

[Dave Sueltenfuss]

Good Morning,

Sorry for the off-topic post

I have a complex SQL statement that I am trying to write, and need a
little help. I have a table with three field, employee, action, and
value.
I want to make a crosstab view, with the value of action being the
column names, and the values of value being the column data

I know I can do this by hard coding the action values into a view, but I
am wondering is there is a way to dynamically create this, so I do not
need to modify it when additional actions are added to the process

Any help is appreciated

Thanks in advance

Dave Sueltenfuss 
Application Developer 
Certified ColdFusion MX Developer 
Arch Wireless 
Phone: 508-870-6711 
Fax: 508-870-8011 
Email: [EMAIL PROTECTED] 


~|
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

OT: SQL Query

[Jeff Chastain]

I have written way to much code today and this is not coming to me as fast
as it should.
 
I have two tables with a one to many relationship.  I need to pull all the
records from table one with one field in the resulting recordset being a
count of how many corresponding records are in the second table and another
field being the date of the latest corresponding record in the second table.
 
Table 1 -
  - key (one)
  - name
  - alias
  - etc...
 
Table 2 - 
  - key (many)
  - thread (count how many distinct ones exist per key)
  - timeStamp (get latest per key)
 
Hopefully this make sense and is simple to somebody else, I am just to the
point of having difficulty adding 2 + 2.
 
Thanks
-- Jeff


~|
Archives: http://www.houseoffusion.com/lists.cfm?link=t:4
Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. 
http://www.fusionauthority.com/ads.cfm

OT: SQL Query

[Mike Mertsock]

Nice SQL Jochem...Also to clarify, Jeff, since you said that you need to get all the 
fields from table one: The GROUP BY statement needs to include *all* of your 
non-aggregate-function fields, in a comma-delimited list. So if you're selecting 
t1.key, t1.name, table1.alias in addition to the max and the count fields, you would 
need 

GROUP BY t1.key, t1.name, table1.alias

Mike Mertsock
Alfred University Web Team

>Jeff Chastain wrote:
>>  
>> I have two tables with a one to many relationship.  I need to pull all the
>> records from table one with one field in the resulting recordset being a
>> count of how many corresponding records are in the second table and another
>> field being the date of the latest corresponding record in the second table.
>>  
>> Table 1 -
>>   - key (one)
>>   - name
>>   - alias
>>   - etc...
>>  
>> Table 2 - 
>>   - key (many)
>>   - thread (count how many distinct ones exist per key)
>>   - timeStamp (get latest per key)
>
>SELECT
>   t1.key,
>   COUNT(t2.key) AS recordNumber
>   MAX(t2.timeStamp) AS latest
>FROM
>   t1 INNER JOIN t2 ON t1.key = t2.key
>GROUP BY
>   t1.key
>
>This presumes there is at least one entry in t2 for each entry in t1.
>
>Jochem
~|
Archives: http://www.houseoffusion.com/lists.cfm?link=t:4
Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

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

OT: SQL Query

[Paul Giesenhagen]

I need to write a query and really don't know where to start -- here is the description.

I have a number of reports all dated, but dated by day (no time).
Some of the reports are on the same day If I have an ID value, I need to grab the report before and the report after that ID and it needs to be by date.

Here is what the data looks like:

ReportID  |  ReportDate  |  Report |
19/10/2003
29/11/2003
39/11/2003
49/11/2003
59/12/2003
89/12/2003
99/14/2003
1110/01/2003 etc..

If I am looking at ReportID (5), then I want to know that reportID 4 is previous and reportID 8 is next

So my output should be
Previous = 4
This >
Next = 8

Oh, and I need to know if previous is Nothing or Next is nothing (ie there isn't anymore eitherway).

ANY help would be greatly appreciated!

Paul Giesenhagen
QuillDesign


 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

RE: SQL Query

[Matthew Walker]

Do you mean that if the next report is on the next day then it doesn't show?
i.e.

Previous = 8
This >
Next = nothing



-Original Message-
From: Paul Giesenhagen [mailto:[EMAIL PROTECTED] 
Sent: Friday, 17 October 2003 5:02 p.m.
To: CF-Talk
Subject: OT: SQL Query

I need to write a query and really don't know where to start -- here is the
description.

I have a number of reports all dated, but dated by day (no time).
Some of the reports are on the same day If I have an ID value, I need to
grab the report before and the report after that ID and it needs to be by
date.

Here is what the data looks like:

ReportID  |  ReportDate  |  Report |
19/10/2003
29/11/2003
39/11/2003
49/11/2003
59/12/2003
89/12/2003
99/14/2003
1110/01/2003 etc..

If I am looking at ReportID (5), then I want to know that reportID 4 is
previous and reportID 8 is next

So my output should be
Previous = 4
This >
Next = 8

Oh, and I need to know if previous is Nothing or Next is nothing (ie there
isn't anymore eitherway).

ANY help would be greatly appreciated!

Paul Giesenhagen
QuillDesign


  _  


 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

OT: SQL Query

[Ryan Roskilly]

I have the following query


 SELECT  tbl_event.Event_ID, tbl_event.Event_LocationID,
tbl_event.Event_Date, tbl_event.Event_Title,
   tbl_location.location_Name
 FROM tbl_event, tbl_location
 WHERE   1 = 1
  AND (tbl_event.Event_LocationID = tbl_location.location_ID
OR  tbl_event.Event_LocationID = 1000)
  AND tbl_event.Event_date > #now()#
  AND tbl_event.Event_date < #dateadd("m",5,now())#
 ORDER BY tbl_event.Event_Date ASC


Returns correct data but I need to pull only the top 3 records for each of
the 13 possible tbl_Event.Event_LocationID

Is this even possible in one query?

Thanks

-Ryan

 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

OT: SQL Query

[Paul Giesenhagen]

I have three tables and I know the guideID.  The Fusion table below brings the guides and the lakes together.  If a Guide is a guide at 1 or more lakes, the lakeID and the guideID are put into a row into the fusion table.  Guides can be associated with many lakes and many lakes associated with a guide.

I want to display a series of checkboxes for the list of ALL lakes .. and then denote (check) the checkboxes of the lakes that the KNOWN guideID is a part of.  

So If I am viewing guideID = 1001 then I want to see all the lakes, but distinguish which lakes are associated with guideID = 1001

Can this be done in a single query or am I going to need some CF integration.?

Lakes Table:

lakeID, lakeName

Guide Table

guideID, companyName

Fusion Table

fusionID, lakeID, guideID

Thanks
Paul Giesenhagen
QuillDesign


 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

Re:OT: SQL Query

[Harley Friedman]

Lakes Table: 
 
lakeID, lakeName 

Guide Table 
 
guideID, companyName 

Fusion Table 
 
fusionID, lakeID, guideID 

Several solutions:
1. The two query solution:

Select LakeID, LakeName
>From Lakes


Select LakeID
>From FusionTable
Where GuideID=



Then in your form:

CHECKED>#LakeName#


2. One query solution


Select l.LakeID, l.LakeName,f.GuideID
>From Lakes l
Left Outer Join FusionTable f on f.LakeID=l.LakeID AND f.GuideID=(put the Guide ID here)


Then:

CHECKED>#LakeName#


It's late, and those are off the top of my head.
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

OT: SQL Query

[Paul Giesenhagen]

I tried to ask this on Experts Exchange without any luck ... maybe one of you guys may be able to help out.

I am trying to build some pricing levels into our application and having a hard time with SQL query, here is an explination.

I have 2 tables:

LEVELS

levelID, code, description, status

LEVELPRICING

levelPriceID, levelID, productID, Price

I am running the following query:
=
SELECT l.levelID, l.code, l.description, p.price
FROM #request.levelTable# l
RIGHT OUTER JOIN #request.levelPricingTable# p
  ON l.levelID = p.levelID
WHERE (p.productID = #url.productID#
OR p.productID IS NULL)
AND l.status = 1
=

Basically I have the tables above (let me populate some data:

Levels
levelID, code, description, status
1 555 Wholesale    1
2 444 Gold Member 1
3222 Silver Member 0

LevelPricing
levelPriceID, levelID, productID, Price
1  1 18 30.00
2  1 29 25.00
3  2 18 25.00
4  2  4227.00

I want to pull out WHOLESALE AND GOLD MEMBER (Silver is status 0 so it is turned off).

I should be pulling out 2 records  IF I am passing URL.PRODUCTID = 18, I should have 30.00 and 25.00 for wholesale and gold.

IF my url.productID is 0 (a new product page).  Then I STILL want to pull out the two rows for WHOLESALE and GOLD MEMBER, but the price should be NULL (since there is not a record for productID 0 in the levelPricing table.

SO for productID  = 18
l.levelID, l.code, l.description, p.price
1 555    Wholesale    30.00
2444 Gold Member 25.00

For ProductID = 0
l.levelID, l.code, l.description, p.price
1 555 Wholesale    NULL
2444  Gold Member NULL

On the product building page I would like to list out all the various descriptions of level pricings from the levels table.  IF the productID matches up, I would like to output the associated price with the level.  IF productID is 0 (or a new record), I would like it to list out the levels.description with a NULL price.

This query is not working, if there are prices associated with the productID it shows the levels, but if there are not pricing associated with the productID it doesn't show the various levels.

Any suggestions? I have tried multiple variations without avail. (Needs to work in MS Access and MS SQL)

Paul Giesenhagen
QuillDesign
417-885-1375
http://www.quilldesign.com
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

RE: SQL Query

[Christian Watt]

You need to take your Where clause out and include it on the join. I
believe that since you are including the outer joined table in the
where, you are not going to return any records that don't match the
Where statement.

 
FROM #request.levelTable# l
RIGHT OUTER JOIN #request.levelPricingTable# p ON l.levelID = p.levelID
and (p.productID = #url.productID# OR p.productID IS NULL)

	-Original Message-
	From: Paul Giesenhagen [mailto:[EMAIL PROTECTED] 
	Sent: Friday, August 13, 2004 4:50 PM
	To: CF-Talk
	Subject: OT: SQL Query
	
	
	I tried to ask this on Experts Exchange without any luck ...
maybe one of you guys may be able to help out.
	
	I am trying to build some pricing levels into our application
and having a hard time with SQL query, here is an explination.
	
	I have 2 tables:
	
	LEVELS
	
	levelID, code, description, status
	
	LEVELPRICING
	
	levelPriceID, levelID, productID, Price
	
	I am running the following query:
	=
	SELECT l.levelID, l.code, l.description, p.price
	FROM #request.levelTable# l
	RIGHT OUTER JOIN #request.levelPricingTable# p
	  ON l.levelID = p.levelID
	WHERE (p.productID = #url.productID#
	OR p.productID IS NULL)
	AND l.status = 1
	=
	
	Basically I have the tables above (let me populate some data:
	
	Levels
	levelID, code, description, status
	1 555 Wholesale    1
	2 444 Gold Member 1
	3222 Silver Member 0
	
	LevelPricing
	levelPriceID, levelID, productID, Price
	1  1 18 30.00
	2  1 29 25.00
	3  2 18 25.00
	4  2  4227.00
	
	I want to pull out WHOLESALE AND GOLD MEMBER (Silver is status 0
so it is turned off).
	
	I should be pulling out 2 records  IF I am passing URL.PRODUCTID
= 18, I should have 30.00 and 25.00 for wholesale and gold.
	
	IF my url.productID is 0 (a new product page).  Then I STILL
want to pull out the two rows for WHOLESALE and GOLD MEMBER, but the
price should be NULL (since there is not a record for productID 0 in the
levelPricing table.
	
	SO for productID  = 18
	l.levelID, l.code, l.description, p.price
	1 555    Wholesale    30.00
	2444 Gold Member 25.00
	
	For ProductID = 0
	l.levelID, l.code, l.description, p.price
	1 555 Wholesale    NULL
	2444  Gold Member NULL
	
	On the product building page I would like to list out all the
various descriptions of level pricings from the levels table.  IF the
productID matches up, I would like to output the associated price with
the level.  IF productID is 0 (or a new record), I would like it to list
out the levels.description with a NULL price.
	
	This query is not working, if there are prices associated with
the productID it shows the levels, but if there are not pricing
associated with the productID it doesn't show the various levels.
	
	Any suggestions? I have tried multiple variations without avail.
(Needs to work in MS Access and MS SQL)
	
	Paul Giesenhagen
	QuillDesign
	417-885-1375
	http://www.quilldesign.com 

 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: SQL Query

[Paul Giesenhagen]

I tried your example query, and recieved a "join _expression_ not supported" error.  I assume that is an MS Access error ..

I am trying to make this work with both MS SQL Server and MS Access (I can use two queries if necessary).  Currently I am working in Access for this application.

Paul Giesenhagen
QuillDesign
417-885-1375
http://www.quilldesign.com

  - Original Message - 
  From: Christian Watt 
  To: CF-Talk 
  Sent: Friday, August 13, 2004 5:02 PM
  Subject: RE: SQL Query

  You need to take your Where clause out and include it on the join. I
  believe that since you are including the outer joined table in the
  where, you are not going to return any records that don't match the
  Where statement.

  FROM #request.levelTable# l
  RIGHT OUTER JOIN #request.levelPricingTable# p ON l.levelID = p.levelID
  and (p.productID = #url.productID# OR p.productID IS NULL)

  -Original Message-
  From: Paul Giesenhagen [mailto:[EMAIL PROTECTED] 
  Sent: Friday, August 13, 2004 4:50 PM
  To: CF-Talk
  Subject: OT: SQL Query

  I tried to ask this on Experts Exchange without any luck ...
  maybe one of you guys may be able to help out.

  I am trying to build some pricing levels into our application
  and having a hard time with SQL query, here is an explination.

  I have 2 tables:

  LEVELS
  
  levelID, code, description, status

  LEVELPRICING
  
  levelPriceID, levelID, productID, Price

  I am running the following query:
  =
  SELECT l.levelID, l.code, l.description, p.price
  FROM #request.levelTable# l
  RIGHT OUTER JOIN #request.levelPricingTable# p
    ON l.levelID = p.levelID
  WHERE (p.productID = #url.productID#
  OR p.productID IS NULL)
  AND l.status = 1
  =

  Basically I have the tables above (let me populate some data:

  Levels
  levelID, code, description, status
  1 555 Wholesale    1
  2 444 Gold Member 1
  3222 Silver Member 0

  LevelPricing
  levelPriceID, levelID, productID, Price
  1  1 18 30.00
  2  1 29 25.00
  3  2 18 25.00
  4  2  4227.00

  I want to pull out WHOLESALE AND GOLD MEMBER (Silver is status 0
  so it is turned off).

  I should be pulling out 2 records  IF I am passing URL.PRODUCTID
  = 18, I should have 30.00 and 25.00 for wholesale and gold.

  IF my url.productID is 0 (a new product page).  Then I STILL
  want to pull out the two rows for WHOLESALE and GOLD MEMBER, but the
  price should be NULL (since there is not a record for productID 0 in the
  levelPricing table.

  SO for productID  = 18
  l.levelID, l.code, l.description, p.price
  1 555    Wholesale    30.00
  2444 Gold Member 25.00

  For ProductID = 0
  l.levelID, l.code, l.description, p.price
  1 555 Wholesale    NULL
  2444  Gold Member NULL

  On the product building page I would like to list out all the
  various descriptions of level pricings from the levels table.  IF the
  productID matches up, I would like to output the associated price with
  the level.  IF productID is 0 (or a new record), I would like it to list
  out the levels.description with a NULL price.

  This query is not working, if there are prices associated with
  the productID it shows the levels, but if there are not pricing
  associated with the productID it doesn't show the various levels.

  Any suggestions? I have tried multiple variations without avail.
  (Needs to work in MS Access and MS SQL)

  Paul Giesenhagen
  QuillDesign
  417-885-1375
  http://www.quilldesign.com 
  
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: SQL Query

[Paul Giesenhagen]

I tried your example query, and recieved a "join _expression_ not supported" error.  I assume that is an MS Access error ..

I am trying to make this work with both MS SQL Server and MS Access (I can use two queries if necessary).  Currently I am working in Access for this application.

Paul Giesenhagen
QuillDesign
417-885-1375
http://www.quilldesign.com

  - Original Message - 
  From: Christian Watt 
  To: CF-Talk 
  Sent: Friday, August 13, 2004 5:02 PM
  Subject: RE: SQL Query

  You need to take your Where clause out and include it on the join. I
  believe that since you are including the outer joined table in the
  where, you are not going to return any records that don't match the
  Where statement.

  FROM #request.levelTable# l
  RIGHT OUTER JOIN #request.levelPricingTable# p ON l.levelID = p.levelID
  and (p.productID = #url.productID# OR p.productID IS NULL)
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

RE: SQL Query

[Christian Watt]

Paul,
    I just test this code in MSSQL.  Access my not like the or, but this
one works for ProductID of 0 or 18 and works the way you want.  I am not
sure if access will like it.  You don't have to stipulate the ProductID
as null since you are doing an outer join, also, sorry I didn't catch
this last time, but you want a left outer join, not right.

 
SELECT l.LevelID, l.Code, l.description, p.Price
FROM Levels l LEFT OUTER JOIN
  LevelPricing p ON l.LevelID = p.LevelID AND
p.ProductID = #url.ProductID#
WHERE (l.Status = 1)

 
Christian

 -Original Message-
From: Paul Giesenhagen [mailto:[EMAIL PROTECTED] 
Sent: Friday, August 13, 2004 5:13 PM
To: CF-Talk
Subject: Re: SQL Query

	I tried your example query, and recieved a "join _expression_ not
supported" error.  I assume that is an MS Access error ..
	
	I am trying to make this work with both MS SQL Server and MS
Access (I can use two queries if necessary).  Currently I am working in
Access for this application.
	
	Paul Giesenhagen
	QuillDesign
	417-885-1375
	http://www.quilldesign.com
	
	  - Original Message - 
	  From: Christian Watt 
	  To: CF-Talk 
	  Sent: Friday, August 13, 2004 5:02 PM
	  Subject: RE: SQL Query
	
	  You need to take your Where clause out and include it on the
join. I
	  believe that since you are including the outer joined table in
the
	  where, you are not going to return any records that don't
match the
	  Where statement.
	
	  FROM #request.levelTable# l
	  RIGHT OUTER JOIN #request.levelPricingTable# p ON l.levelID =
p.levelID
	  and (p.productID = #url.productID# OR p.productID IS NULL) 

 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: SQL Query

[Paul Giesenhagen]

Same error on that one ... I guess I could use the query below for MS SQL and then run a loop with MS Access .. it just seems so simple ... it should work with access.

Paul Giesenhagen
QuillDesign
417-885-1375
http://www.quilldesign.com

  - Original Message - 
  From: Christian Watt 
  To: CF-Talk 
  Sent: Friday, August 13, 2004 5:29 PM
  Subject: RE: SQL Query

  Paul,
  I just test this code in MSSQL.  Access my not like the or, but this
  one works for ProductID of 0 or 18 and works the way you want.  I am not
  sure if access will like it.  You don't have to stipulate the ProductID
  as null since you are doing an outer join, also, sorry I didn't catch
  this last time, but you want a left outer join, not right.

  SELECT l.LevelID, l.Code, l.description, p.Price
  FROM Levels l LEFT OUTER JOIN
LevelPricing p ON l.LevelID = p.LevelID AND
  p.ProductID = #url.ProductID#
  WHERE (l.Status = 1)

  Christian

  -Original Message-
  From: Paul Giesenhagen [mailto:[EMAIL PROTECTED] 
  Sent: Friday, August 13, 2004 5:13 PM
  To: CF-Talk
  Subject: Re: SQL Query

  I tried your example query, and recieved a "join _expression_ not
  supported" error.  I assume that is an MS Access error ..

  I am trying to make this work with both MS SQL Server and MS
  Access (I can use two queries if necessary).  Currently I am working in
  Access for this application.

  Paul Giesenhagen
  QuillDesign
  417-885-1375
  http://www.quilldesign.com

    - Original Message - 
    From: Christian Watt 
    To: CF-Talk 
    Sent: Friday, August 13, 2004 5:02 PM
    Subject: RE: SQL Query

    You need to take your Where clause out and include it on the
  join. I
    believe that since you are including the outer joined table in
  the
    where, you are not going to return any records that don't
  match the
    Where statement.

    FROM #request.levelTable# l
    RIGHT OUTER JOIN #request.levelPricingTable# p ON l.levelID =
  p.levelID
    and (p.productID = #url.productID# OR p.productID IS NULL) 
  
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

SQL Query problem

[Mark Leder]

This is giving me fits.  In a membership listing, each member could have
many transactions (one to many relationship). I want to retrieve each member
ID, and their corresponding most recent (MAX) transactionID.  The memberID
joins the two tables.

I've tried several ways without success. The statement below below returns
ALL the transaction records for any given individual member, not just the
most recent transactionID.  What am I doing wrong?

SELECT M.firstName, M.middleName, M.lastName, M.email, M.company, M.city,
M.zip, M.memberID, M.memberLevelID
FROM #REQUEST.prefix#_Members_List M
WHERE M.memberID =
	(SELECT MAX(T.transactionID) AS transactionID, T.paidThru,
T.transactionDate,  T.memberID) 
	FROM #REQUEST.prefix#_Members_TransactionLog T 
	WHERE T.memberID = M.memberID) AND 
	
...more filters ...

GROUP BY M.memberID, M.firstName, M.middleName, M.lastName, M.email,
M.company, M.city, M.zip, M.memberLevelID, T.paidThru, T.transactionDate,
T.memberID 

ORDER BY M.lastName
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

SQL Query Problem

[bhattach]

Hello,
I am trying to workout a way that will allow me to
run dynamic queries and display the results.
I have created an Access DB table Called "QueryTable" to
store all my queries in it.
The fileds are: qid (Auto Number), QueryName (Text),
SqlQuery (Memo).I have created a few select/insert queries
and have stored them in the db.
So far, so good.

I am then trying to run the queries on a cfm template and
display the results in  tag.

I have the following two templates:

Page1.cfm: (I am using this template to select the query
from my QueryTable).
Page2.cfm: This is where I have two queries:A & B.
Query A gets me the complete SQL statement based on the
QueryId selected in Page1.cfm.

Query B: This is where I am using the SQL statement
obtained previously from Query A to search my Book
Catalogue.
I am trying to Insert my SQL Statement between
the  tags for QueryB.
The Codes:
Page2.cfm

Select QUERYID,SQLQUERY from QueryTable where
Queryid=#Form.queryid#



 #A.SQLQUERY#



#SQLQUERY#

My problem is with is piece of code in QueryB.
It is creating an ODBC error.

Can someone please help.

Thanks in advance. Have a good day!

Morph
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

SOT: SQL Query

[brobborb]

hey guys, what do u think the performance difference is in MS SQL Server 2000

Querying from a table of 500,000 rows  or querying from a table of 1 million rows?
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

RE: SQL Query

[Barney Boisvert]

If you're WHERE clause uses an index, it should be log(2) times slower for
the actual row selection, but parsing the query, doing JOINs, packaging the
data, etc will all be the same, so the actual time difference will be less
than that.

Cheers,
barneyb

> -Original Message-
> From: brobborb [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, April 06, 2004 12:38 PM
> To: CF-Talk
> Subject: SOT: SQL Query
> 
> hey guys, what do u think the performance difference is in MS 
> SQL Server 2000
> 
> Querying from a table of 500,000 rows  or querying from a 
> table of 1 million rows?
> 
>
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

RE: SQL Query

[Barney Boisvert]

Let me rephrase "If you're WHERE clause uses an index" to be "If your WHERE
clause uses a single indexed field".

Cheers,
barneyb

> -Original Message-
> From: Barney Boisvert [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, April 06, 2004 12:53 PM
> To: CF-Talk
> Subject: RE: SQL Query
> 
> If you're WHERE clause uses an index, it should be log(2) 
> times slower for
> the actual row selection, but parsing the query, doing JOINs, 
> packaging the
> data, etc will all be the same, so the actual time difference 
> will be less
> than that.
> 
> Cheers,
> barneyb
> 
> > -Original Message-
> > From: brobborb [mailto:[EMAIL PROTECTED] 
> > Sent: Tuesday, April 06, 2004 12:38 PM
> > To: CF-Talk
> > Subject: SOT: SQL Query
> > 
> > hey guys, what do u think the performance difference is in MS 
> > SQL Server 2000
> > 
> > Querying from a table of 500,000 rows  or querying from a 
> > table of 1 million rows?
> > 
> > 
> 
>
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

RE: SQL Query

[Paul Kenney]

Depends on a lot of things...

1.	how many fields make up your search criteria?
2.	What is the datatype of those fields?
3.	Are those fields indexed, and if so are they being used?
4.	What indexes are being used by your query?
5.	What kinds of indexes are being used?
6.	Are those indexes optimized?
7.	What is the distribution of values within that index?
8.	Are you running the query with an implicit or explicit transaction?
9.	How many users are concurrently trying to access those rows (row lock
contention)?
10.	What isolation level is your transaction using?
11.	I'm sure there is more...

When in doubt, just fill the table with more data and see for yourself.

Paul Kenney
[EMAIL PROTECTED]
916-212-4359

> -Original Message-
> From: brobborb [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, April 06, 2004 12:38 PM
> To: CF-Talk
> Subject: SOT: SQL Query
>
>
> hey guys, what do u think the performance difference is in MS
> SQL Server 2000
>
> Querying from a table of 500,000 rows  or querying from a
> table of 1 million rows?
>
>
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

RE: SQL Query

[Tony Weeg]

if correctly indexed, none.

I have a table that is 1.57 millions rows, we index on an integer field, and
I can return to a cf page, a recordset with 100+ rows in milliseconds

its all about the indexing. 

tw

-Original Message-
From: brobborb [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, April 06, 2004 3:38 PM
To: CF-Talk
Subject: SOT: SQL Query

hey guys, what do u think the performance difference is in MS SQL Server
2000

Querying from a table of 500,000 rows  or querying from a table of 1 million
rows?
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

Re: SQL Query

[brobborb]

Yes, indexing helps a wholebunch!  But how does one practice indexing correctly?  Which fields should be indexed?  Right now, all the identity fields are indexed.  Was wondering if there is aything else that should be index
  - Original Message - 
  From: Tony Weeg 
  To: CF-Talk 
  Sent: Tuesday, April 06, 2004 3:03 PM
  Subject: RE: SQL Query

  if correctly indexed, none.

  I have a table that is 1.57 millions rows, we index on an integer field, and
  I can return to a cf page, a recordset with 100+ rows in milliseconds

  its all about the indexing. 

  tw

  -Original Message-
  From: brobborb [mailto:[EMAIL PROTECTED] 
  Sent: Tuesday, April 06, 2004 3:38 PM
  To: CF-Talk
  Subject: SOT: SQL Query

  hey guys, what do u think the performance difference is in MS SQL Server
  2000

  Querying from a table of 500,000 rows  or querying from a table of 1 million
  rows?
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

Re: SQL Query

[brobborb]

How do you optimize an index?  I tried dumping the old one and creating a new one.  Something like that.

I will have to read more on the isolation levels.  They are a bit confusing to me!
  - Original Message - 
  From: Paul Kenney 
  To: CF-Talk 
  Sent: Tuesday, April 06, 2004 3:02 PM
  Subject: RE: SQL Query

  Depends on a lot of things...

  1. how many fields make up your search criteria?
  2. What is the datatype of those fields?
  3. Are those fields indexed, and if so are they being used?
  4. What indexes are being used by your query?
  5. What kinds of indexes are being used?
  6. Are those indexes optimized?
  7. What is the distribution of values within that index?
  8. Are you running the query with an implicit or explicit transaction?
  9. How many users are concurrently trying to access those rows (row lock
  contention)?
  10. What isolation level is your transaction using?
  11. I'm sure there is more...

  When in doubt, just fill the table with more data and see for yourself.

  Paul Kenney
  [EMAIL PROTECTED]
  916-212-4359

  > -Original Message-
  > From: brobborb [mailto:[EMAIL PROTECTED]
  > Sent: Tuesday, April 06, 2004 12:38 PM
  > To: CF-Talk
  > Subject: SOT: SQL Query
  >
  >
  > hey guys, what do u think the performance difference is in MS
  > SQL Server 2000
  >
  > Querying from a table of 500,000 rows  or querying from a
  > table of 1 million rows?
  >
  >
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

RE: SQL Query

[Tony Weeg]

a rule of thumb @ my company

index on integer fields and date fields only

tony

-Original Message-
From: brobborb [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, April 06, 2004 4:10 PM
To: CF-Talk
Subject: Re: SQL Query

Yes, indexing helps a wholebunch!  But how does one practice indexing
correctly?  Which fields should be indexed?  Right now, all the identity
fields are indexed.  Was wondering if there is aything else that should be
index
  - Original Message -
  From: Tony Weeg
  To: CF-Talk
  Sent: Tuesday, April 06, 2004 3:03 PM
  Subject: RE: SQL Query

  if correctly indexed, none.

  I have a table that is 1.57 millions rows, we index on an integer field,
and
  I can return to a cf page, a recordset with 100+ rows in milliseconds

  its all about the indexing. 

  tw

  -Original Message-
  From: brobborb [mailto:[EMAIL PROTECTED]
  Sent: Tuesday, April 06, 2004 3:38 PM
  To: CF-Talk
  Subject: SOT: SQL Query

  hey guys, what do u think the performance difference is in MS SQL Server
  2000

  Querying from a table of 500,000 rows  or querying from a table of 1
million
  rows?
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

RE: SQL Query

[Barney Boisvert]

In general, if you use a field in JOINs, or in the WHERE, HAVING, GROUP BY
or ORDER BY clauses, you might benefit from indexing.  The flip side is that
UPDATEs, INSERTs and DELETEs that affect indexed fields run slower, because
they don't have to just change the data, they also have to mess with the
index.

I usually start with indexes on the primary key and foreign keys (which you
can't avoid) and then ignore the rest of the fields until I start finding
bottlenecks.  Occasionally I find them during development, but it's usually
during QA and load testing where they start to appear.  It also helps to
have the queries written first, before you add indexes, because you can see
how the fields are actually used, rather than the guesses you have to make
before they're written.

Cheers,
barneyb

> -Original Message-
> From: brobborb [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, April 06, 2004 1:10 PM
> To: CF-Talk
> Subject: Re: SQL Query
> 
> Yes, indexing helps a wholebunch!  But how does one practice 
> indexing correctly?  Which fields should be indexed?  Right 
> now, all the identity fields are indexed.  Was wondering if 
> there is aything else that should be index
>   - Original Message - 
>   From: Tony Weeg 
>   To: CF-Talk 
>   Sent: Tuesday, April 06, 2004 3:03 PM
>   Subject: RE: SQL Query
> 
> 
>   if correctly indexed, none.
> 
>   I have a table that is 1.57 millions rows, we index on an 
> integer field, and
>   I can return to a cf page, a recordset with 100+ rows in 
> milliseconds
> 
>   its all about the indexing. 
> 
>   tw
> 
>   -Original Message-
>   From: brobborb [mailto:[EMAIL PROTECTED] 
>   Sent: Tuesday, April 06, 2004 3:38 PM
>   To: CF-Talk
>   Subject: SOT: SQL Query
> 
>   hey guys, what do u think the performance difference is in 
> MS SQL Server
>   2000
> 
>   Querying from a table of 500,000 rows  or querying from a 
> table of 1 million
>   rows?
> 
> 
>
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

RE: SQL Query

[Jochem van Dieten]

Tony Weeg said:
> if correctly indexed, none.

That is not entirely true, but for databases where the index is cached
in RAM and the data pages are on disk it is a reasonable aproximation.

Jochem
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

RE: SQL Query

[Tony Weeg]

ive made up my mind...im going to leave a little space at the end of each
post
that reads like this:

Blah blah blah (my answer)

Jochem please finish:
[insert jochems real thoughts and any RFC's that pertain hitherto:]

:) sorry jochem. I had to.

tony

-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, April 06, 2004 4:30 PM
To: CF-Talk
Subject: RE: SQL Query

Tony Weeg said:
> if correctly indexed, none.

That is not entirely true, but for databases where the index is cached in
RAM and the data pages are on disk it is a reasonable aproximation.

Jochem
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

RE: SQL Query

[Casey C Cook]

Why index on integer fields and date fields only ?

CC
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

SQL Query Problems

[Me Here]

OK, this query is driving me crazy, returns too many records in CFMX and MS SQL (Query Analyzer)

SELECT tbl_products.product_ID, tbl_products.product_Name, tbl_products.product_ShortDescription, tbl_prdtimages.prdctImage_FileName
FROM tbl_products INNER JOIN
  tbl_prdtimages ON tbl_products.product_ID = tbl_prdtimages.prdctImage_ProductID INNER JOIN
  tbl_products_rel ON tbl_products.product_ID = tbl_products_rel.rel_product_id
WHERE (tbl_products.product_OnWeb = '1') AND (tbl_products.product_ID = 7000 OR
  tbl_products.product_ID = 6560) AND (tbl_prdtimages.prdctImage_ImgTypeID = '1')

I do not understand why. The only thing different in this query as the another query that is use within the appllication is the addition of the "OR tbl_products.product_id = 6868"

I know that I will probably not get an answer, but any help would be appreciated, even educated guesses at this point. If I take out the or clause and use either one of those product_id's I get 1 record back, this should only return 2 records, it returns 1 for id "7000" and 12 for "6868"

Damn mondays.
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

SQL Query Analyzer

[Kahng, Lucius]

I'm trying to retrieve a long text field from out ColdFusion SQL database
using SQL Query Analyzer, however, the program truncates the field contents.
Is there a way to display the full contents of long text fields? (Instead of
having to use CFML to output a the whole table's column contents!)

Thanks!
Luc
__
Get Your Own Dedicated Windows 2000 Server
  PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
  Instant Activation · $99/Month · Free Setup
  http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

SQL query question

[Debbie Morris]

Since everyone should be in a SQL Join state of mind...here's another one.

I have a weird issue that I haven't been able to narrow down yet. I'm trying to 
add a new field to one of my tables to store some additional information, but 
once I add the column, my previously working query breaks.

Here's the query (obviously the person that originally wrote it is in the 'the 
fewer characters, the better' camp):

SELECT p.*, pt.*, pm.Type AS mtype, s.fname AS sfname, s.lname AS slname, 
pa.DateActive AS PenAmountDate, pa.MonthlyAmount AS PenAmt, pa.Note AS 
PenAmtNote, s.SSN AS sssn, s.DOB AS sdob, spm.Type AS smt

FROM   PensionerMedicalType spm
INNER JOIN PensionerSpouse s ON spm.MedicalID = s.MedicalType 
RIGHT OUTER JOIN PensionersActive p 
INNER JOIN PensionerType pt ON p.PensionerType = pt.PensionerTID 
LEFT OUTER JOIN PensionAmounts pa ON p.PenActID = pa.PenID 
LEFT OUTER JOIN PensionerMedicalType pm ON p.MedicalType = pm.MedicalID ON 
s.SpouseID = p.Spouse
WHERE  (p.PenActID =  AND 
pa.active = 1)  


As soon as I add a column named 'lifeInsType' to the PensionersActive (p) 
table, I get the following error when the same query runs:

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver]Value can not be converted to requested type.

What am I overlooking? 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341801
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

SQL Query Problem

[Jenny Gavin-Wear]

Two tables each containing a shared primary key ID.

I am trying to create a query that lists records from table B that are not
in table A.

Many thanks,

Jenny




~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:345493
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

SQL Query Error

[Robert Flesher]

Can anyone tell me why i'd be getting the following
error when trying to execute this code???
code:
INSERT INTO tlbtables (nameoftable, name)
VALUES ('value1', 'value2')
error:couldn't find output table tbltables

I have verified that the table tbltables is there and
does have data in it.  Can anyone help me out?
-Rob

__
Do You Yahoo!?
Send instant messages & get email alerts with Yahoo! Messenger.
http://im.yahoo.com/
--
Archives: http://www.eGroups.com/list/cf-talk
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.

SQL query problem

[John Fix 3rd]

I'm trying to do a query where I have data in two different formats...

SELECT Customer, TransNumber, Date
FROM test.Header
Where Customer = "#session.customer#"

The problem I have is that the value of #session.customer# contains the
customer number (260) with no leading characters while the test.header
Customer field comes back with leading 000 characters (000260). If I hard
code it as "WHERE Customer = "000260" it works. My guess is that I need to
do something to pad session.customer with leading zeros up the the length of
the Customer field (which is 6 characters).  I can't find a manipulation
function that does this... any ideas?

Thanks!

John

--
Archives: http://www.eGroups.com/list/cf-talk
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.

SQL Query Question

[Tim Bahlke]

I have the following SP:

CREATE PROCEDURE [n_speaker_list] AS

SELECT DISTINCT s.f_name, s.l_name, s.pro_title, c.co_name, sm.subject, 
a.agenda_title, s.sp_file
FROM speakers s, company c, subject_matter sm, agenda a
WHERE a.time_slot = s.time_slot
AND s.subject_id = sm.subject_id
AND s.company_id = c.company_id
AND s.sp_file is not null
ORDER BY s.f_name, s.l_name

I want to pull all records where the s.f_name and s.l_name are distinct but not the 
rest of the string.

Can someone please enlighten me on how to do this?

Thanks,
Tim Bahlke



Tim Bahlke



Access your e-mail anywhere, at any time.
Get your FREE BellSouth Web Mail account today!
http://webmail.bellsouth.net


Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message 
with 'unsubscribe' in the body to [EMAIL PROTECTED]

RE: sql query

[Darryl Lyons]

Why don't you make it a stored procedure?

-Original Message-
From: Jones, Becky [mailto:[EMAIL PROTECTED]]
Sent: Friday, February 23, 2001 7:57 AM
To: CF-Talk
Subject: sql query


can anyone help me translate this FROM in my SQL query so CF will like it?
it works fine in SQL but bombs in CF with syntax errors.

FROM INT_AUX_LISTING

LEFT JOIN int_aux_listing as CompanyListing 
ON  (int_aux_listing.company_list_id =
CompanyListing.Listing_ID)
AND (int_aux_listing.Company_list_src_id =
CompanyListing.Listing_src_id))


left JOIN INT_AUX_LST_ADDR 
ON (INT_AUX_LISTING.LISTING_SRC_ID =
INT_AUX_LST_ADDR.LISTING_SRC_ID) 
AND (INT_AUX_LISTING.LISTING_ID =
INT_AUX_LST_ADDR.LISTING_ID)) 

left JOIN INT_AUX_ADDRESS 
ON (INT_AUX_LST_ADDR.ADDRESS_SRC_ID =
INT_AUX_ADDRESS.ADDRESS_SRC_ID) 
AND (INT_AUX_LST_ADDR.ADDRESS_ID =
INT_AUX_ADDRESS.ADDRESS_ID)

left JOIN INT_AUX_LST_PHONE 
ON (int_aux_listing.LISTING_ID =
INT_AUX_LST_PHONE.LISTING_ID) 
AND (int_aux_listing.LISTING_SRC_ID =
INT_AUX_LST_PHONE.LISTING_SRC_ID))

left JOIN INT_AUX_PHONE 
ON (INT_AUX_PHONE.PHONE_ID = INT_AUX_LST_PHONE.PHONE_ID) 
AND (INT_AUX_PHONE.PHONE_SRC_ID =
INT_AUX_LST_PHONE.PHONE_SRC_ID)) 

left join int_phone_typ
on (int_aux_phone.phone_typ_id = int_phone_typ.phone_typ_id)

left join int_relation_typ as Phone_Relation_TYP
on (int_aux_lst_phone.rltn_typ_id =
phone_relation_typ.rltn_typ_id)
i was working with the query builder and it seemed like it was using {
instead of (.  
thanks for your help
~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists