Cursor: Derived column referencing

[Dan Osborne]

Darren,

Have you tried refering to the column by position instead of name i.e. order
by 1 if it is always going to be the first column?

Dan Osborne
Specialist Business Solutions Ltd.

> -----Original Message-----
> From: owner-powerh-l@sphere.swau.edu
> [mailto:owner-powerh-l@sphere.swau.edu]On Behalf Of Darren Reely
> Sent: February 9, 2000 6:31 PM
> To: 'powerh-l@lists.swau.edu'
> Subject: Cursor: Derived column referencing
>
>
> 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.
>

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
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.