Oracle Analytics - Similar Values-CodePudding

I have a customers database in which I need to filter each customer by age. In some cases the customer is registered with more than one dob.

So far I have this query. As an example I'm using just one locator

SELECT DISTINCT
Doc.PNRLOCATORID,
Doc.RESPAXID,
Doc.FLTNBR,
TO_DATE(Doc.DOCPAXDOB) as Data,
Doc.DOCPAXGENDER as Genero,
CONCAT(FLTNBR,(CONCAT(PNRLOCATORID,TO_CHAR(CONCAT(RESPAXID,(CASE WHEN (DOCCD IN ('I','P','F','C')) THEN 1 ELSE 0 END)))))) AS VAL

FROM G2S_TDB_P.ACSPAXDOCX Doc

WHERE Doc.DOCCD NOT IN ('I') AND DOC.PNRLOCATORID = 'LIHAVP'
ORDER BY VAL

Result

PNRLOCATORID RESPAXID   FLTNBR  DATA                GENERO  VAL
LIHAVP       1          7734    12/10/1988 00:00    F       7734LIHAVP11
LIHAVP       2          7734    16/04/1990 00:00    M       7734LIHAVP21
LIHAVP       2          7734    03/03/2000 00:00    M       7734LIHAVP21
LIHAVP       1          7735    12/10/1988 00:00    F       7735LIHAVP11
LIHAVP       2          7735    16/04/1990 00:00    M       7735LIHAVP21
LIHAVP       2          7735    03/03/2000 00:00    M       7735LIHAVP21

So, as you can see the customer #2 has two dob, how can I modify the query and select just the older data in all customers?

I created a unique key for each customer (Val), I think just selecting one value of 'Val' for each customer I´ll eliminate this duplicity. Just don't know how to do it.

If there is a easier method please tell me. I'm doing all of this on Oracle Analytics so I´m unable to create temporary tables.

CodePudding user response：

Until you fix the data (see mathguy's comment), you could use your current query as a CTE, add row_number analytic function which partitions data per each customer, sorted by date value so that the oldest comes first; then, in the main query, return only rows that rank as the highest.

Modifications to your query:

instead of nested concat function, use the double pipe || concatenation operator; makes code easier to read
don't to_date values that already are dates; docpaxdob column is DATE, is it not? If not, that's yet another thing you should fix - don't store dates as strings. If it is a VARCHAR2, then to_date isn't enough - you should provide format model as well
there's no point in including the I doccd value into the case expression because where clause excludes Is from the table

At the end, this is a query you might try to use:

 WITH temp
     AS (
        -- this is your current query, modified a little bit
        SELECT DISTINCT d.pnrlocatorid,
                        d.respaxid,
                        d.fltnbr,
                        d.docpaxdob                AS data,
                        d.docpaxgender             AS genero,
                        d.fltnbr
                        || d.pnrlocatorid
                        || To_char(d.respaxid
                                   || CASE
                                        WHEN( d.doccd IN( 'P', 'F', 'C' ) ) THEN
                                        1
                                        ELSE 0
                                      END)         val,
                        Row_number()
                          over(
                            PARTITION BY d.respaxid
                            ORDER BY d.docpaxdob ) rn
         FROM   g2s_tdb_p.acspaxdocx d
         WHERE  d.doccd NOT IN ( 'I' )
                AND d.pnrlocatorid = 'LIHAVP')
SELECT *
FROM   temp
WHERE  rn = 1
ORDER  BY val;

CodePudding user response：

Trying this query...

WITH temp
 AS (
    SELECT DISTINCT d.pnrlocatorid,
                    d.respaxid,
                    d.fltnbr,
                    d.docpaxdob                AS data,
                    d.docpaxgender             AS genero,
                    d.fltnbr
                    || d.pnrlocatorid
                    || To_char(d.respaxid
                               || CASE
                                    WHEN( d.doccd IN( 'P', 'F', 'C' ) ) THEN
                                    1
                                    ELSE 0
                                  END)         val,
                    Row_number()
                      over(
                        PARTITION BY d.respaxid
                        ORDER BY d.docpaxdob ) rn
     FROM   g2s_tdb_p.acspaxdocx d
     WHERE  d.doccd NOT IN ( 'I' )
            AND d.pnrlocatorid = 'LIHAVP')
SELECT *
FROM   temp
WHERE  rn = 1
ORDER  BY val

The system gives me this message

O driver Odbc retornou um erro (SQLExecDirectW).
Estado: HY000. Código: 43119. [nQSError: 43119] Falha na Consulta:
(HY000)
Estado: HY000. Código: 17001. [nQSError: 17001] Código de erro Oracle: 32034, mensagem: ORA-32034: unsupported use of WITH clause
at OCI call OCIStmtExecute.
(HY000)
Estado: HY000. Código: 17010. [nQSError: 17010] Falha de preparação da instrução SQL. (HY000)
Instrução SQL Executada: SET VARIABLE DISABLE_CACHE_SEED=1,DISABLE_XSA_CACHE_SEED=1,ENABLE_DIMENSIONALITY=1;SELECT
0 s_0,