See the Create View statement. QTY_INVOICED is just an alias for
L.QTY_VCHR
tgp_at_mci2000.com wrote in article <6q7t46$dp$1_at_nnrp1.dejanews.com>...
> Okay, I'm stumped. I admit it. There MUST be a simple answer to this, but
I
> cannot see it. Please post the simple answer, if you know it.
>
> I have this simple SQL statement:
>
> select sum(QTY_INVOICED)
> from PS_VCHR_LN_PO_VW
> where business_unit = 'JFFBT'
> and PO_ID = 'E-21880A' and LINE_NBR = 1
>
> (It was brought to my attention because it performs badly, if you want to
> know.) It selects from PS_VCHR_LN_PO_VW, which is a view. Yes, I checked
to
> make sure there isn't a table by the same name. There appears to be
precisely
> ONE object in this schema with this name.
>
> I've got this simple query that shows the TEXT field of DBA_VIEWS for a
given
> VIEW_NAME. For this view, the TEXT field contains:
>
> select /*+ choose */ L.BUSINESS_UNIT_PO, L.PO_ID, L.LINE_NBR,
> L.SCHED_NBR, L.BUSINESS_UNIT_RECV, L.RECEIVER_ID, L.RECV_LN_NBR,
> L.RECV_SHIP_SEQ_NBR, L.B USINESS_UNIT, L.VOUCHER_ID,
L.VOUCHER_LINE_NUM,
> L.QTY_VCHR, L.MERCHANDISE_AM T from PS_VOUCHER_LINE L, PS_VOUCHER V
> where V.BUSINESS_UNIT = L.B USINESS_UNIT and V.VOUCHER_ID =
L.VOUCHER_ID
> and V.ENTRY_STATUS <> 'X' a nd L.BUSINESS_UNIT_PO <> ' ' and L.PO_ID
<>
> ' ' group by L.BUSINESS_UNIT_PO , L.PO_ID, L.LINE_NBR, L.SCHED_NBR,
> L.BUSINESS_UNIT_RECV, L.RECEIVER_ID, L .RECV_LN_NBR,
L.RECV_SHIP_SEQ_NBR,
> L.BUSINESS_UNIT, L.VOUCHER_ID, L.VOUCHER_ LINE_NUM, L.QTY_VCHR,
> L.MERCHANDISE_AMT
>
> I have not edited this text in any way. I simply copy/pasted it from a
telnet
> session. Here's the part I don't understand: The column queried by the
SQL
> statement above, QTY_INVOICED, does NOT appear in this text anywhere.
Now, how
> in the heck can that be true?
>
> Now, from a completely different source, I have obtained what I strongly
> believe to be the source actually used to create the view, but I won't
> absolutely swear to it:
>
> select /*+ choose */
> L.BUSINESS_UNIT_PO,
> L.PO_ID,
> L.LINE_NBR,
> L.SCHED_NBR,
> L.BUSINESS_UNIT_RECV,
> L.RECEIVER_ID,
> L.RECV_LN_NBR,
> L.RECV_SHIP_SEQ_NBR,
> L.BUSINESS_UNIT,
> L.VOUCHER_ID,
> L.VOUCHER_LINE_NUM,
> L.QTY_VCHR,
> L.MERCHANDISE_AMT
> from
> PS_VOUCHER_LINE L,
> PS_VOUCHER V
> where
> V.BUSINESS_UNIT = L.BUSINESS_UNIT
> and V.VOUCHER_ID = L.VOUCHER_ID
> and V.ENTRY_STATUS <> 'X'
> and L.BUSINESS_UNIT_PO <> ' '
> and L.PO_ID <> ' '
> group by
> L.BUSINESS_UNIT_PO,
> L.PO_ID,
> L.LINE_NBR,
> L.SCHED_NBR,
> L.BUSINESS_UNIT_RECV,
> L.RECEIVER_ID,
> L.RECV_LN_NBR,
> L.RECV_SHIP_SEQ_NBR,
> L.BUSINESS_UNIT,
> L.VOUCHER_ID,
> L.VOUCHER_LINE_NUM,
> L.QTY_VCHR,
> L.MERCHANDISE_AMT
>
>
> Note that the two appear to be semi-identical, and the column in
question,
> QTY_INVOICED, doesn't appear here either.
>
> HOWEVER, if I go into SQL*Plus, and enter "desc ps_vchr_ln_po_vw", it
says:
>
> SQL> desc ps_vchr_ln_po_vw
> Name Null? Type
> ------------------------------- -------- ----
> BUSINESS_UNIT NOT NULL VARCHAR2(5)
> PO_ID NOT NULL VARCHAR2(10)
> LINE_NBR NOT NULL NUMBER(38)
> SCHED_NBR NOT NULL NUMBER(38)
> BUSINESS_UNIT_RECV NOT NULL VARCHAR2(5)
> RECEIVER_ID NOT NULL VARCHAR2(10)
> RECV_LN_NBR NOT NULL NUMBER(38)
> RECV_SHIP_SEQ_NBR NOT NULL NUMBER(38)
> BUSINESS_UNIT_AP NOT NULL VARCHAR2(5)
> VOUCHER_ID NOT NULL VARCHAR2(8)
> VOUCHER_LINE_NUM NOT NULL NUMBER(38)
> QTY_INVOICED NOT NULL NUMBER(15,4)
> AMT_INVOICED NOT NULL NUMBER(15,2)
>
> And QTY_INVOICED is there, just as plain as day.
>
> Now, I thought I understood Oracle fairly well, but I'm stumped. Please
help
> me see the error of my ways.
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
>
Received on Wed Aug 05 1998 - 01:13:21 CDT
