Simple (?) question about views

From: <tgp_at_mci2000.com>
Date: Tue, 04 Aug 1998 21:06:14 GMT
Message-ID: <6q7t46$dp$1@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 Tue Aug 04 1998 - 16:06:14 CDT