[ic] Help with query/change/condition problem.

Mike Heins interchange-users@icdevgroup.org
Mon Oct 14 08:47:01 2002


Quoting Tim Nelson (TNELSON@ECS-INC.com):
> I have a query that I am trying to get working to display the area and cat
> tables in a join.  When the area changes I want to put out a new heading
> for that area.  The [prefix-change] tag seems to be working EXCEPT on the
> first area ! !  So where I would expect to see:
>   
[snip]
> Here is the page, and ideas ?
> Thanks, Tim.
> 
> ---------------- page here ----------------
> [query prefix=box more=1 ml=50 list=1
> sql="select area.code,area.name,area.sort,cat.code,cat.name,cat.sort
> from area,cat where area.code = cat.sel [scratchd myarea]
> order by area.sort,area.code,cat.sort,cat.name"]

These types of queries are easier to deal with if you format them 
nicely:

  [query
	prefix=box
	more=1
	ml=50
	list=1
	sql="
	    SELECT
		area.code,
		area.name,
		area.sort,
		cat.code,
		cat.name,
		cat.sort
	    FROM  area,cat
	    WHERE area.code = cat.sel
	    [scratchd myarea]
	    ORDER BY area.sort,area.code,cat.sort,cat.name
	"
   ]

Because DBI can't differentiate between the column names in different
tables, you would want:

	    SELECT
		area.code,
		area.name,
		area.sort,
		cat.code as catcode,
		cat.name as catname,
		cat.sort as catsort
	    FROM  area,cat
	    ...

The probably:

 [box-change 0]
 [condition][box-param name][/condition]
 ...

adjusting all the parameter callouts as appropriate.

-- 
Mike Heins
Perusion -- Expert Interchange Consulting    http://www.perusion.com/
phone +1.513.523.7621      <mike@perusion.com>

Few blame themselves until they have exhausted all other possibilities.
 -- anonymous