Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2001
    Location
    Seattle, WA; USA
    Posts
    9

    Question Unanswered: Derived Column in Query

    Can somebody help me with this syntax? I am taking two working queries and putting them together such that one becomes a derived column in the other. I am guessing that I must just be missing some small syntax point (I don't use Oracle much) because this query runs fine in a SQL Server. I thought I had gotten all the syntax converted, but I'm getting an error: "ORA-00936: Missing Expression". Here's the statement:
    Code:
    SELECT A.CNTRCT_ID, A.VENDOR_ID,   
    	(Select X.XLATSHORTNAME 
    	From Sysadm.XLATTABLE X  
    	WHERE X.EFFDT = 
    		( SELECT MAX(EFFDT) 
    		FROM Sysadm.XLATTABLE  
    		WHERE X.FIELDNAME = FIELDNAME AND X.LANGUAGE_CD = LANGUAGE_CD AND X.FIELDVALUE = FIELDVALUE  
    		AND EFFDT <= TO_DATE('12/26/2001','MM-DD-YY')
    		)
    	And X.FIELDNAME = 'CNTRCT_STATUS' And X.FIELDVALUE= A.CNTRCT_STATUS
    	) as StatusExtended,
    A.CNTRCT_BEGIN_DT, A.CNTRCT_EXPIRE_DT, A.DESCR, B.NAME1  
    FROM Sysadm.PS_CNTRCT_HDR A, Sysadm.PS_VENDOR B  
    WHERE A.Z_PURCH_DOC_TYPE = 'B' AND A.CNTRCT_STATUS <> 'C' AND B.VENDOR_ID = A.VENDOR_ID AND B.SETID = A.VENDOR_SETID  
    AND A.CNTRCT_EXPIRE_DT BETWEEN TO_DATE('12/26/2001','MM/DD/YY') AND TO_DATE('1/25/2002','MM/DD/YY') 
    Order By B.NAME1
    Thanks for your help!

  2. #2
    Join Date
    Jun 2001
    Location
    The Netherlands
    Posts
    38
    Hi,

    A solution to your problem might be:

    SELECT A.CNTRCT_ID
    , A.VENDOR_ID
    , A.CNTRCT_BEGIN_DT
    , A.CNTRCT_EXPIRE_DT
    , A.DESCR
    , B.NAME1
    , C.StatusExtended
    FROM Sysadm.PS_CNTRCT_HDR A
    , Sysadm.PS_VENDOR B
    ,(Select X.FIELDVALUE, X.XLATSHORTNAME as StatusExtended
    From Sysadm.XLATTABLE X
    WHERE X.EFFDT =
    ( SELECT MAX(EFFDT)
    FROM Sysadm.XLATTABLE
    WHERE X.FIELDNAME = FIELDNAME AND X.LANGUAGE_CD = LANGUAGE_CD AND X.FIELDVALUE = FIELDVALUE
    AND EFFDT <= TO_DATE('12/26/2001','MM-DD-YY')
    )
    And X.FIELDNAME = 'CNTRCT_STATUS'
    ) C
    WHERE A.Z_PURCH_DOC_TYPE = 'B'
    AND A.CNTRCT_STATUS <> 'C'
    AND B.VENDOR_ID = A.VENDOR_ID
    AND B.SETID = A.VENDOR_SETID
    AND A.CNTRCT_EXPIRE_DT BETWEEN TO_DATE('12/26/2001','MM/DD/YY')
    AND TO_DATE('1/25/2002','MM/DD/YY')
    AND C.FIELDVALUE= A.CNTRCT_STATUS
    Order By B.NAME1

    An alternative solution would be:

    SELECT A.CNTRCT_ID
    , A.VENDOR_ID
    , A.CNTRCT_BEGIN_DT
    , A.CNTRCT_EXPIRE_DT
    , A.DESCR
    , B.NAME1
    , X.XLATSHORTNAME as StatusExtended
    FROM Sysadm.PS_CNTRCT_HDR A
    , Sysadm.PS_VENDOR B
    , Sysadm.XLATTABLE X
    WHERE A.Z_PURCH_DOC_TYPE = 'B'
    AND A.CNTRCT_STATUS <> 'C'
    AND B.VENDOR_ID = A.VENDOR_ID
    AND B.SETID = A.VENDOR_SETID
    AND A.CNTRCT_EXPIRE_DT BETWEEN TO_DATE('12/26/2001','MM/DD/YY')
    AND TO_DATE('1/25/2002','MM/DD/YY')
    AND X.EFFDT = ( SELECT MAX(EFFDT)
    FROM Sysadm.XLATTABLE
    WHERE X.FIELDNAME = FIELDNAME
    AND X.LANGUAGE_CD = LANGUAGE_CD
    AND X.FIELDVALUE = FIELDVALUE
    AND EFFDT <= TO_DATE('12/26/2001','MM-DD-YY')
    )
    AND X.FIELDNAME = 'CNTRCT_STATUS'
    Order By B.NAME1

    I don't have a PeopleSoft installation so I'm not able to test this. However it should point you in the right direction.

    Good luck,

    Ben

  3. #3
    Join Date
    Dec 2001
    Location
    Seattle, WA; USA
    Posts
    9
    Ben,

    Thanks for your solutions. The first one appears to be working properly. I need to do some further analysis to be sure. At least, as you said, they point me in a new direction. BTW, your second solution seems to be missing something because it is giving excess rows... it was missing "AND X.FIELDVALUE = A.CNTRCT_STATUS" in the WHERE clause.

    Out of curiosity then, is the approach I was taking with a derived column not a legal move in Oracle?

    Again, THANKS for the help!

    Mark

  4. #4
    Join Date
    Jun 2001
    Location
    The Netherlands
    Posts
    38
    Mark,

    Derived columns are, as far as I know, not part of the ANSI standard. Therefor every vendor will make his own implentation. Microsoft put it in the selected columns of the select statement (which to me looks logical) while Oracle opted for puting it in the from clause (which gives me a headache cause I can't memory the syntax).

    Ben

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •