Cursor: Derived column referencing

[Darren Reely]

Hi all,

I have a SQL cursor (below with code reference) in a Quick screen that I'd
rather use bins_rlsd_lots_v.* in the select statement instead of explicitly
listing each column.  But if I do this I can't use my derived value in a
sort clause. The derived column is 'not found' during the compile time
substitution phase.  When I do list each column in the cursor then
everything works as expected.  In either case I can display the value,
although that isn't its purpose.  The purpose is to have sorted columns
with a selected value floating to the top of a cluster.  Is there a way to
do this in straight QDesign?

Changing the positions in the cursor does NOT help.  Changing the cursor
name didn't help.  Prefixing the derived column name with the cursor name
didn't help.  

Is there a work around for this?


sql in mfg declare bins_rlsd_lots_v cursor for &
        (       select '1' as mysortorder, &
                        bins_rlsd_lots_v.* &
                from bins_rlsd_lots_v &
                where facility = :trunc(t_in_fac) &
                        AND test_facility = :trunc(t_in_test_fac) &
                        and pkg = :pkg of ilf_issue_sheet &
                        and lot_status not in ('HOLD','COMPLETE') &
                ::and_where_clause &
                union &
                select '9' as mysortorder, &
                        bins_rlsd_lots_v.* &
                from bins_rlsd_lots_v &
                where facility = :trunc(t_in_fac) &
                        AND (test_facility IS NULL 
                           OR test_facility <> :trunc(t_in_test_fac)) &
                        and pkg = :pkg of ilf_issue_sheet &
                        and lot_status not in ('HOLD','COMPLETE') &
                ::and_where_clause &
        ) ::order_clause

cursor bins_rlsd_lots_v primary occurs 10 noitems open read ;key lot_id

....

In my code I have SQL OPEN statements such as this one.

  if t_path = 1
  then SQL OPEN BINS_RLSD_LOTS_V &
       and_where_clause ( and bin_id = :trunc(t_push_bin_id) &
                          AND ms = :trunc(t_push_ms) ), &
       order_clause (order by mysortorder, test_facility, &
                              prod_line, bin_id, ms, lot_id)

The substitution looks good except I get the following error.

__ Sql after substitutions are applied:
*E* Error parsing SQL statement.
DMS-E-GENERAL, A general exception has occurred during operation 'prepare 
request'.
DMS-E-SS_PARSER, An error was detected during processing of the SQL
request.
DMS-E-SS_BAD_OBJECT, An invalid object name was detected in the SQL
request.
DMS-E-SS_BAD_COL, Column 'MYSORTORDER' was not found.


Thanks for any direction,

Darren

P.S.
We are running Oracle7 Server Release 7.3.4.4.0, 
               PH 813d1 (d2 didn't help),
               Sun Solaris 2.6 (SunOS 5.6).
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Subscribe: "subscribe powerh-l" in message body to majordomo@lists.swau.edu
Unsubscribe: "unsubscribe powerh-l" in message to majordomo@lists.swau.edu
This list is closed, thus to post to the list, you must be a subscriber.