I think 'as' fields have to be mentioned by position in some cases. I
don't remember whether your ordering items should be

QTY_SOLD=6;6 DESC

or

6=6;6 DESC

but it is something along these lines.

HTH,
Set

Rick Roen wrote:

> IBO 4.6A
> Win XP Pro SP2
>
> I'm getting an error when I let IBO set the ordering items:
>
> ---------------------------
> Error
> ---------------------------
> ISC ERROR CODE:335544569
>
> ISC ERROR MESSAGE:
> Dynamic SQL Error
> SQL error code = -206
> Column unknown
> QTY_SOLD
> At line 36, column 19.
>
> ---------------------------
> OK
> ---------------------------
>
> The SQL text (below) names ) this and some other fields, all of
> which have the same error. All of the failing ordering items are
> named with an "... as QTY_SOLD" in the SQL.
>
> Is there some other way to get IB to recognize these fields?
>
> Rick
>
> The ordering items:
>
> ITEMNUM=OI.ITEMNUM
> NAME=NAME
> DESCRIPTION=PT.SORT_ORDER,P.NAME;PT.SORT_ORDER,P.NAME DESC
> QTY_SOLD=QTY_SOLD;QTY_SOLD DESC
> AMT_SOLD=AMT_SOLD;AMT_SOLD DESC
> QTY_RETURNED=QTY_RETURNED;QTY_RETURNED DESC
> AMT_RETURNED=AMT_RETURNED;AMT_RETURNED DESC
> QTY_NET=QTY_NET;QTY_NET DESC
> TOTAL_SOLD=TOTAL_SOLD;TOTAL_SOLD DESC
> PERCENT_RETURNED=PERCENT_RETURNED;PERCENT_RETURNED DESC
>
> The SQL text:
>
> Select OI.ITEMNUM, P.NAME, PT.DESCRIPTION, OI.COST, OI.RETAIL,
> SUM(CASE
> when O.TERMSNUM <> :CREDITTERMS then OI.QTY_SHIP
> else 0
> end) as QTY_SOLD,
> SUM(CASE
> when O.TERMSNUM <> :CREDITTERMS then OI.QTY_SHIP * OI.Cost
> else 0
> end) as AMT_SOLD,
> SUM(CASE
> when O.TERMSNUM = :CREDITTERMS then OI.QTY_SHIP
> else 0
> end) as QTY_RETURNED,
>
> SUM(CASE
> when O.TERMSNUM = :CREDITTERMS then OI.QTY_SHIP * OI.Cost
> else 0
> end) as AMT_RETURNED,
> Sum( OI.QTY_SHIP) as QTY_NET,
> Sum( OI.QTY_SHIP * OI.COST) as TOTAL_SOLD,
> PT.SORT_ORDER
> from ORDERS O
> join ORDERITEMS OI on OI.INVOICENUM = O.INVOICENUM
> join PACKET P on P.ITEMNUM = OI.ITEMNUM
> join PACKET_TYPE PT on PT.IDNUM = P.PKT_GROUP
> where O.CUSTNUM = :CUSTNUM and
> O.SEED_YEAR = :SEED_YEAR and
> PT.ISPACKET = 1 and
> OI.QTY_SHIP <> 0
> Group by OI.ITEMNUM,
> P.NAME,
> PT.DESCRIPTION,
> OI.COST,
> OI.RETAIL,
> PT.SORT_ORDER