Prev: Cf 8 / onRequestEnd / html cfgrid
Next: Help with CFWINDOW
From: mactech999 on 28 Dec 2007 10:34
ok I understand what you mean by consolidating the queries only thing is can i use AS CfgDisplay more than once in one query?
From: Dan Bracuk on 28 Dec 2007 11:09
[q][i]Originally posted by: [b][b]mactech999[/b][/b][/i]
ok For a 2005 Honda Civic this is the Result I get:

FHO-556 Front Disc Brake Rotor 2 F1 Sport Brake Rotors
Use with: SubModel: Hybrid
FHO-556 Front Disc Brake Rotor 2 F1 Sport Brake Rotors
Use with: 1.7L L4
HO-556 Front Disc Brake Rotor 2 AutoStar Drum & Rotor
Use with: SubModel: Hybrid
HO-556 Front Disc Brake Rotor 2 AutoStar Drum & Rotor
Use with: 1.7L L4
HO-584 Front Disc Brake Rotor 2 AutoStar Drum & Rotor
Use with: 2.0L L4
HOD-56 Rear Brake Drum 2 AutoStar Drum & Rotor
Use with: 1.7L L4


I would like it to be like this

FHO-556 Front Disc Brake Rotor 2 F1 Sport Brake Rotors
HO-556 Front Disc Brake Rotor 2 AutoStar Drum & Rotor
HOD-56 Rear Brake Drum 2 AutoStar Drum & Rotor
Use with: 1.7L L4

HO-584 Front Disc Brake Rotor 2 AutoStar Drum & Rotor
Use with: 2.0L L4

FHO-556 Front Disc Brake Rotor 2 F1 Sport Brake Rotors
HO-556 Front Disc Brake Rotor 2 AutoStar Drum & Rotor
Use with: SubModel: Hybrid
[/q]
So in other words, if a part number only shows up once, you want the Use With
stuff, otherwise you don't. Hopefully, it's a different field. You can do it
all with query of queries. Let's call your database call q1.

First, get all the part numbers that only show up once.
<cfquery name="q2" dbtype="query">
select part_num, part_name, count(part_num)
from q1
group by part_num, part_name
having count(part_num) =1

Next get the ones that appear more than once by changing the last line.

You should be able to take it from here.




From: mactech999 on 28 Dec 2007 11:19
It doesn't give me what I need

I don't want all the parts to be at the top and all the descriptions at the
bottom I want this:

lets say we have 3 partnumbers with the same description then they are grouped
together with the description bellow
and the other parts still show up even if they do not have a description

From: mactech999 on 28 Dec 2007 11:58
not quite here is my code that i use right now:

I would like to group the PartNo by CfgDisplay and only Display the CfgDisplay
on the last PartNo

like this

MI-355 Front Disc Brake Rotor 2 AutoStar Drum & Rotor
MID-28 Rear Brake Drum 2 AutoStar Drum & Rotor
ES658 Rear Drum Brake Shoe 1 Eurorotor Brake Shoes
Use with: 2.4L L4

Right now at the moment the Results come out like this:

PartNo Position Description Qty Grade

MI-355 Front Disc Brake Rotor 2 AutoStar Drum & Rotor
Use with: 2.4L L4
MID-28 Rear Brake Drum 2 AutoStar Drum & Rotor
Use with: 2.4L L4
ES658 Rear Drum Brake Shoe 1 Eurorotor Brake Shoes
Use with: 2.4L L4


<cfquery name="Recordset1" datasource="Aces">
SELECT DISTINCT Product.PartNo as Part_No,AcesData.*,
PartType.partterminologyname, Positions.position,Grade.GradeName
FROM Grade INNER JOIN ((Make INNER JOIN ([Year] INNER JOIN (((((Categories
INNER JOIN CodeMaster ON Categories.CategoryID = CodeMaster.CategoryID) INNER
JOIN LineCode ON Categories.CategoryID = LineCode.CategoryID) INNER JOIN
Subcategories ON CodeMaster.SubCategoryID = Subcategories.SubCategoryID) INNER
JOIN PartType ON (CodeMaster.PartTerminologyID = PartType.PartTerminologyID)
AND (Categories.CategoryID = PartType.CategoryID)) INNER JOIN (Model INNER JOIN
((AcesData INNER JOIN BaseVehicle ON AcesData.BaseVehicleID =
BaseVehicle.BaseVehicleID) INNER JOIN Positions ON AcesData.PositionID =
Positions.PositionID) ON Model.ModelID = BaseVehicle.ModelID) ON
PartType.PartTerminologyID = AcesData.PartTerminologyID) ON Year.YearID =
BaseVehicle.YearID) ON Make.MakeID = BaseVehicle.MakeID) INNER JOIN Product ON
(Product.ECatNo = AcesData.PartNo) AND (Product.LineCode = AcesData.MfrLabel)
AND (Positions.PositionID = Product.PositionID)) ON Grade.GradeID =
Product.GradeID
WHERE (((Year.YearID)= #Form.dateyear#) AND ((Make.MakeName)='#Form.Make#')
AND ((Model.modelname)='#Form.Model#') AND
((Categories.categoryname)='#Form.Cat#') AND
((Subcategories.subcategoryname)='#Form.SubCat#'))
Order By Positions.position Asc, Product.PartNo
</cfquery>
<cfif Recordset1.RecordCount GT 0 >
<table width="100%" height="188" border="0">
<tr>
<td width="200"><div align="left"><font
size="5"><strong>PartNo</strong></font></div></td>
<td><div align="left"><font
size="5"><strong>Position</strong></font></div></td>
<td><div align="left"><font
size="5"><strong>Description</strong></font></div></td>
<td><div align="right"><font
size="5"><strong>Qty</strong></font></div></td>
<td><div align="Center"><font
size="5"><strong>Grade</strong></font></div></td>
</tr>
<tr bordercolor="1">
<td height="21">&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td><div align="right"></div></td>
</tr>
<cfoutput query="Recordset1" startrow="#Startrow_Recordset1#"
maxrows="#Maxrows_Recordset1#">
<tr bordercolor="##000000">
<td height="10" > <div align="left"><font size="3"><strong> <cfif
Val(#Recordset1.Part_No#) GT 10000000 and Val(#Recordset1.Part_No#) LT
99999999><font size="3">
<cfif
Fileexists("/Inetpub/wwwroot/loginpage/Images1/#Recordset1.Part_No#.jpg")>
<a
href="Images1/#Recordset1.Part_No#.jpg">#Mid(Recordset1.Part_No,1,2)#.#Mid(Recor
dset1.Part_No,3,2)#.#Mid(Recordset1.Part_No,5,4)#</a>
<cfelse>

#Mid(Recordset1.Part_No,1,2)#.#Mid(Recordset1.Part_No,3,2)#.#Mid(Recordset1.Part
_No,5,4)#
</cfif></cfif>
<cfif Val(#Recordset1.Part_No#) LT 10000000> <font size="3">
<cfif
Fileexists("/Inetpub/wwwroot/loginpage/Images1/#Recordset1.Part_No#.jpg")>
<a
href="Images1/#Recordset1.Part_No#.jpg">#Recordset1.Part_No#</a>
<cfelse>
#Recordset1.Part_No#
</cfif></cfif>
</strong></font></div></td>
<td> <div align="left"><font size="3"><strong><font face="Arial,
Helvetica, sans-serif">#Recordset1.position#</font></strong></font></div></td>
<td> <div align="left"><font size="3"><strong><font face="Arial,
Helvetica,
sans-serif">#Recordset1.partterminologyname#</font></strong></font></div></td>
<td> <div align="right"><font size="3"><strong><font face="Arial,
Helvetica, sans-serif">#Recordset1.Qty#</font></strong></font></font></div></td>
<td> <div align="center"><font size="3"><strong><font face="Arial,
Helvetica,
sans-serif">#Recordset1.GradeName#</font></strong></font></font></div></td>
<tr>

<td height="20" colspan="5"> <div align="left">
<cfif #Recordset1.Aspiration# GT 0>
<cfquery name="rsasp" datasource="Aces">
Select Distinct AspirationName From Aspiration Where
AspirationID
= #Recordset1.Aspiration#
</cfquery>
<cfset CfgDisplay = CfgDisplay & "Aspiration :
#Trim(rsasp.AspirationName)# ">
</cfif>

<cfif #Recordset1.BedLength# Gt 0>
<cfquery name="rsBedLeg" datasource="Aces">
Select Distinct BedLength From BedLength Where BedLengthID =
#Recordset1.BedLength#
</cfquery>
<cfset CfgDisplay = CfgDisplay & "BedLength :
#Trim(rsBedLength.BedLength)# ">
</cfif>
<cfif #Recordset1.BedType# Gt 0>
<cfquery name="rsBedType" datasource="Aces">
Select Distinct BedType From BedType Where BedTypeID =
#Recordset1.BedType#
</cfquery>
<cfset CfgDisplay = CfgDisplay & "BedType :
#Trim(rsBedType.BedType)# ">
</cfif>
<cfif #Recordset1.BodyNumDoors# Gt 0>
<cfquery name="rsDoors" datasource="Aces">
Select Distinct BodyNumDoors From BodyNumDoors Where
BodyNumDoorsID
= #Recordset1.BodyNumDoors#
</cfquery>
<cfset CfgDisplay = CfgDisplay & "#Trim(rsDoors.BodyNumDoors)#
Doors &nbsp; ">
</cfif>
<cfif #Recordset1.BodyType# Gt 0>
<cfquery name="rsbodyType" datasource="Aces">
Select Distinct BodyTypeName From BodyType Where BodyTypeID =
#Recordset1.BodyType#
</cfquery>
<cfset CfgDisplay = CfgDisplay &
"#Trim(rsbodyType.BodyTypeName)# &nbsp; ">
</cfif>
<cfif #Recordset1.BrakeABS# Gt 0>
<cfquery name="rsbrakeabs" datasource="Aces">
Select Distinct BrakeABSName From BrakeABS Where BrakeABSID =
#Recordset1.BrakeABS#
</cfquery>
<cfset CfgDisplay = CfgDisplay &
"#Trim(rsbrakeabs.BrakeABSName)# &nbsp; ">
</cfif>
<cfif #Recordset1.BrakeSystem# Gt 0>
<cfquery name="rsbrakesys" datasource="Aces">
Select Distinct BrakeSystemName From BrakeSystem Where
BrakeSystemID
= #Recordset1.BrakeSystem#
</cfquery>
<cfset CfgDisplay = CfgDisplay & "BrakeSystem:
#Trim(rsbrakesys.BrakeSystemName)# &nbsp; ">
</cfif>
<cfif #Recordset1.CylinderHeadType# Gt 0>
<cfquery name="rscyl" datasource="Aces">
Select Distinct CylinderHeadTypeName From CylinderHeadType Where
CylinderHeadTypeID = #Recordset1.CylinderHeadType#
</cfquery>
<cfset CfgDisplay = CfgDisplay &
"#Trim(rscyl.CylinderHeadTypeName)# &nbsp; ">
</cfif>
<cfif #Recordset1.DriveType# Gt 0>
<cfquery name="rsdrive" datasource="Aces">
Select Distinct DriveTypeName From DriveType Where DriveTypeID =
#Recordset1.DriveType#
</cfquery>
<cfset CfgDisplay = CfgDisplay & "#Trim(rsdrive.DriveTypeName)#
&nbsp; ">
</cfif>
<cfif #Recordset1.EngineBase# Gt 0>
<cfquery name="rsEngine" datasource="Aces">
Select Distinct * From EngineBase Where EngineBaseID =
#Recordset1.EngineBase#
</cfquery>
<cfset CfgDisplay = CfgDisplay & "#Trim(rsEngine.Liter)#L
#Trim(rsEngine.BlockType)##Trim(rsEngine.Cylinders)# &nbsp; ">
</cfif>
<cfif #Recordset1.EngineDesignation# Gt 0>
<cfquery name="rsDesignation" datasource="Aces">
Select Distinct EngineDesignationName From EngineDesignation
Where
EngineDesignationID = #Recordset1.EngineDesignation#
</cfquery>
<cfset CfgDisplay = CfgDisplay & "Engine Designation:
#Trim(rsDesignation.EngineDesignationName)# &nbsp; ">
</cfif>
<cfif #Recordset1.EngineMf# Gt 0>
<cfquery name="rsEngineMf" datasource="Aces">
Select Distinct MfrName From Mfr Where MfrID =
#Recordset1.EngineMf#
</cfquery>
<cfset CfgDisplay = CfgDisplay & "#Trim(rsEngineMf.MfrName)#
Engine &nbsp; ">
</cfif>
<cfif #Recordset1.EngineVersion# Gt 0>
<cfquery name="rsVersion" datasource="Aces">
Select Distinct EngineVersion From EngineVersion Where
EngineVersionID
= #Recordset1.EngineVersion#
</cfquery>
<cfset CfgDisplay = CfgDisplay &
"#Trim(rsVersion.EngineVersion)# Engine &nbsp; ">
</cfif>
<cfif #Recordset1.EngineVin# Gt 0>
<cfquery name="rsEVin" datasource="Aces">
Select Distinct EngineVinName From EngineVin Where EngineVinID =
#Recordset1.EngineVin#
</cfquery>
<cfset CfgDisplay = CfgDisplay & "Engine Vin
#Trim(rsEVin.EngineVinName)# &nbsp; ">
</cfif>
<cfif #Recordset1.FrontBrakeType# Gt 0>
<cfquery name="rsFro
From: Ian Skinner on 28 Dec 2007 13:04
This would be trivial to do with a <cfquery...> and its "group"
parameter. It should be possible to build the record set you need one
way or another. Depending on your system and the requirements you
should be able to either:

1) Build the record set with SQL in a single <cfquery...> using Joins,
Unions, ect.

OR

2) Build the record set from multiple record sets using Query Of Query
with SQL Joins, Unions, ect.

OR

3) Using queryNew(), queryAddRow() and querySetCell() functions to
modify or build the required record set.

You could also achieve grouping with a build your own algorithm with the
right combination of loops and branches to build the output you need.
But I think the former record set solution and <cfoutput> is easier and
more reusable.
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Cf 8 / onRequestEnd / html cfgrid
Next: Help with CFWINDOW