Databases
StackOverflow2013
Postsdbo
POQualify clause in Oracle
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POQualify clause in Oracle
    primarykey
    data
    text
    <p>I'm working in Teradata to Oracle migration project. How can i modify the below query which is using QUALIFY in Teradata.</p> <p>//QUERY 1</p> <pre><code> SELECT S.ID as Id, S.MP_CD as Code, S.GM_CD as GmCode, S.GM_MSR_NBR as Mea_Year, S.STTS_CD as YearCode, S.TRMNTN_DTM as TerminationDate FROM PD.RVY S, LOAD_LOG TLL WHERE S.UPDTD_LOAD = TLL.LOG_KEY AND TLL.BLSH_CD = 'Y' AND S.STTS_CD IN ( 'C', 'P' ) QUALIFY ROW_NUMBER () OVER (PARTITION BY S.GM_CD ,S.MP_CD ,S.GM_MSR_NBR,S.STTS_CD ORDER BY S.SO_DTM DESC ) = 1; </code></pre> <p>//Query 2</p> <pre><code> SELECT SP.ID, SP.SO_DTM, SP.TAX_ID, SP.USER_ID, SP.FRST_NM, SP.LAST_NM, SP.PHONE_NBR, QSRP.TAX_ID, QSRP.ROW_ID, MAX(SP.SO_DTM) OVER (PARTITION BY SP.ID, SP.TAX_ID) MAX_SO_DTM FROM VOPR_RMSY SP,VOPR_RMSY_SPNS QSRP WHERE SP.ID =:URVYID AND QSRP.TAX_ID =:RPAXID AND SP.ID = QSRP.ID AND SP.TAX_ID = QSRP.TAX_ID AND SP.SO_DTM = QSRP.SO_DTM QUALIFY (SP.SO_DTM=MAX_SO_DTM AND QSRP.SO_DTM = MAX_SO_DTM) GROUP BY SP.ID,SP.SO_DTM,SP.TAX_ID,SP.USER_ID,SP.FRST_NM,SP.LAST_NM,SP.PHONE_NBR, QSRP.TAX_ID,QSRP.ROW_ID; </code></pre> <p>For this tried with HAVING instead of qualify but got an Error: </p> <blockquote> <p>ORA-00904: "MAX_SO_DTM": invalid identifier 00904. 00000 - "%s: invalid identifier"</p> </blockquote> <p>Seems like alias used for MAX is not working here....</p> <p>Any of your help is really appreciated! </p> <p>EDITED:</p> <pre><code>SELECT * FROM ( SP.ID, SP.SO_DTM, SP.TAX_ID, SP.USER_ID, SP.FRST_NM, SP.LAST_NM, SP.PHONE_NBR, QSRP.TAX_ID, QSRP.ROW_ID, MAX(SP.SO_DTM) OVER (PARTITION BY SP.ID, SP.TAX_ID) AS MAX_SO_DTM FROM VOPR_RMSY SP,VOPR_RMSY_SPNS QSRP WHERE SP.ID =:URVYID AND QSRP.TAX_ID =:RPAXID AND SP.ID = QSRP.ID AND SP.TAX_ID = QSRP.TAX_ID AND SP.SO_DTM = QSRP.SO_DTM GROUP BY SP.ID,SP.SO_DTM,SP.TAX_ID,SP.USER_ID,SP.FRST_NM,SP.LAST_NM,SP.PHONE_NBR, QSRP.TAX_ID,QSRP.ROW_ID; )dt WHERE (SP.SO_DTM=MAX_SO_DTM AND QSRP.SO_DTM = MAX_SO_DTM) </code></pre> <p>i know that i have to use alias dt for outer WHERE instead of SP and QSRP but here MAX_SO_DTM is compared with SO_DTM from two different tables. is there any other way to modify this?</p> <p>Thanks!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USuser2737399
    1. USuser2737399
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POQualify clause in Oracle
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload