Here's my code syntax- I have tried to use spaces to make it more
readable...
Basically there is an IF condition and the complicated sub query, then
there is another IF condition and i am using the ValueList function in
this query here..
<CFIF Confirmed IS "" AND OrderList IS NOT "">
<CFQUERY NAME="SelectedOrders" DATASOURCE="#DBACCESS_DSN#"
USERNAME="#DBACCESS_USER#" PASSWORD="#DBACCESS_PASSWORD#">
SELECT SALESHEADER.SalesHDRID, ACCOUNTS_1.ParentAccountID,
ACCOUNTS_1.ItemPricingStructureID, SALESHEADER.AccountID,
ACCOUNTS_1.AccountLevel, ACCOUNTS_1.AccountName,
SALESHEADER.OrderDate,
SALESHEADER.DeliveryDate, SALESHEADER.PrivateOrderNumber,
SALESHEADER.InvoiceStatusID, SALESHEADER.OrderComment,
Sum( CASE WHEN [ItemCustSellPrice] Is Null THEN (CASE WHEN
[SkipItemUpdate]= 'True' THEN [ItemSellPrice] ELSE
[ItemSellPrice]*(1-[ACCOUNTS_1].[AccountDiscount]) END) ELSE
[ItemCustSellPrice]*[Quantity] END) AS OrderTotal,
Count(SALESDETAIL.SalesDTLID) AS NumberOfLines
FROM (ACCOUNTS AS ACCOUNTS_1 INNER JOIN SALESHEADER
ON ACCOUNTS_1.AccountID = SALESHEADER.AccountID) INNER JOIN
((((((ITEMS LEFT JOIN ITEMUOMS
ON ITEMS.ItemSellUOMID = ITEMUOMS.ItemUOMID) LEFT JOIN ITEMTAXCODES
ON
ITEMS.ItemSellTaxCode = ITEMTAXCODES.ItemTaxID) INNER JOIN
SALESDETAIL
ON ITEMS.ItemID = SALESDETAIL.ItemID) LEFT JOIN (ACCOUNTS RIGHT JOIN
ITEMSACCOUNT
ON ACCOUNTS.AccountID = ITEMSACCOUNT.AccountID) ON ITEMS.ItemID =
ITEMSACCOUNT.ItemID)
LEFT JOIN ITEMSACCOUNTCUSTOMERPRICE
ON ITEMSACCOUNT.ItemAccountID =
ITEMSACCOUNTCUSTOMERPRICE.ItemAccountID)
LEFT JOIN ITEMSACCOUNTPRICING ON ITEMSACCOUNT.ItemAccountID =
ITEMSACCOUNTPRICING.ItemAccountID)
ON SALESHEADER.SalesHDRID = SALESDETAIL.SalesHDRID
GROUP BY ACCOUNTS_1.ParentAccountID,
ACCOUNTS_1.ItemPricingStructureID, SALESHEADER.AccountID,
ACCOUNTS_1.AccountLevel, ACCOUNTS_1.AccountName,
SALESHEADER.OrderDate,
SALESHEADER.DeliveryDate, SALESHEADER.SalesHDRID,
SALESHEADER.PrivateOrderNumber,
SALESHEADER.InvoiceStatusID, SALESHEADER.OrderComment,
ITEMSACCOUNTPRICING.ItemPricingStructureID
HAVING SALESHEADER.InvoiceStatusID = 'OR'
AND SALESHEADER.SalesHDRID IN (#OrderList#)
AND ACCOUNTS_1.ParentAccountID = #SESSION.SelectedAccountID#
<CFIF LSIsDate(SelectDate)>
AND SALESHEADER.DeliveryDate >= #LSParseDateTime(SelectDate)#
AND SALESHEADER.DeliveryDate < #DateAdd("d",1,LSParseDateTime
(SelectDate))#
</CFIF>
AND ACCOUNTS_1.AccountLevel > 1
AND ITEMSACCOUNTPRICING.ItemPricingStructureID =
ACCOUNTS_1.ItemPricingStructureID
ORDER BY ACCOUNTS_1.AccountName
</CFQUERY>
</CFIF>
<CFIF Confirmed IS "CONFIRM" AND OrderList IS NOT "">
<CFQUERY NAME="CountShorts" DATASOURCE="#DBACCESS_DSN#"
USERNAME="#DBACCESS_USER#" PASSWORD="#DBACCESS_PASSWORD#">
SELECT Sum( CASE WHEN (SALESDETAIL.PackerID Is Not Null And
SALESDETAIL.PackerTime Is Not Null) THEN 0 ELSE
(CASE WHEN (SALESDETAIL.CheckerID Is Not Null And
SALESDETAIL.CheckerTime Is Not Null) THEN 0 ELSE
1 END) END) AS [Left]
FROM SALESDETAIL
WHERE SALESDETAIL.SalesHDRID IN (#ValueList
(SelectedOrders.SalesHDRID)#)
AND SALESDETAIL.Quantity <> 0
AND SALESDETAIL.Quantity Is Not Null
</CFQUERY>
---------------------------------------------------------------------------------
Steve Onnis wrote:
> The argument for ValueList is..
>
> ValueList(queryName.columnName)
>
> The error suggests that the query name you are passing in ( SelectedOrders )
> is not a query. This value is the value you use for the "name" attribute of
> the cfquery tag.
>
> So...
>
>
> <cfquery datasource="myDataSource" name="myQuery">
> SELECT myColumn
> FROM MyTable
> </cfquery>
>
> ...........then
>
> valueList(myQuery.myColumn)
>
>
>
> Steve
>
> -----Original Message-----
> From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf
> Of Scott Thornton
> Sent: Thursday, 13 November 2008 9:53 AM
> To: cfaussie
> Subject: [cfaussie] Referencing a query inside another query in ColdFusionMX
>
>
> Post your code please.... probably just a typo somewhere..
>
> >>> Khush <[EMAIL PROTECTED]> 13/11/2008 9:44 am >>>
>
> ok i tried the ValueList syntax as per Steve Onnis, but i get an error
> message -
>
> "The ValueList() function has an invalid parameter:
> SelectedOrders.SalesHDRID.
> Parameter 1 of function ValueList which is now
> (SelectedOrders.SalesHDRID) must be pointing to a valid query name."
>
> 'SelectedOrders' is a valid query name. So i am not sure what the
> cause of error is.
>
> Also i am writing this query in COldFusion and the backend is SQL
> Server Express 2005. 'SelectedOrders' for my test cases is returning
> less than 10 records. In the production environment it might return at
> the max a 100 records, so valuelist shoul;d definitely work...
>
> DOes anyone know y am i getting an error?
>
>
>
>
> Steve Onnis wrote:
> > SELECT salesdetail.packerid
> > FROM SALESDETAIL
> > WHERE SALESDETAIL.SalesHDRID IN
> > (#ValueList(SelectedOrders.SalesHDRID)#)
> >
> > That will output all the items returned from that column into your query
> >
> > -----Original Message-----
> > From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On
> Behalf
> > Of Khush
> > Sent: Wednesday, 12 November 2008 9:02 AM
> > To: cfaussie
> > Subject: [cfaussie] Referencing a query inside another query in
> ColdFusionMX
> >
> >
> > How do i reference a field (SalesHDRID) from a query (called
> > SelectedOrders) in the where condition of another query.
> >
> > My CF query looks somewhat like this-
> > SELECT salesdetail.packerid
> > FROM SALESDETAIL
> > WHERE SALESDETAIL.SalesHDRID IN (SelectedOrders.SalesHDRID)
> >
> > Now my query SelectedOrders is a really big and very complicated one,
> > so I am not going to just paste it in the IN clause...If anyone has
> > any ideas how to actually reference this sub query by it;s name in the
> > main query above, please let me know ASAP as i need this urgently...
> >
> >
> > THanks
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"cfaussie" group.
To post to this group, send email to cfaussie@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en
-~----------~----~----~----~------~----~------~--~---
