Hi,

Do you know if there is an alternative on WF to the 'LAST' function when you are working with VERTICA databases

I have to use the LAST function to retrieve the value of my variable in the previous record, but the LAST function is not compatible with VERTICA.

So if you have an idea I am interested.

I wish you a merry Christmas.

thank you for your help and happy new year

Cédric


For Example, my code that works when i don't want a VERTICA database on output :

  
DEFINE FILE FDAPPS5
 PLACE/I8 = IF SEM_CONTACT NE LAST SEM_CONTACT THEN 1
            ELSE IF COD_SAS_CDC EQ LAST COD_SAS_CDC THEN (PLACE+1) ELSE 1;
END

TABLE FILE FDAPPS5
 PRINT
  NUM_GRP_CLI
  NUM_IP_FED
  NUM_TIE
  NUM_GUI
  NUM_BUR
  COD_FED
  COD_PMT_FED_ORI
  COD_BQE
  NUM_SOC
  COD_STE_GES
  NUM_DIR
  SCO_PEO_VOL
  COD_SAS_CDC
  DAT_CONTACT
  SEM_CONTACT
  AGE_CALC
  DAT_ANNIV
  DAT_NAI
  PLACE
 BY SEM_CONTACT NOPRINT
 BY COD_SAS_CDC NOPRINT
 BY HIGHEST SCO_PEO_VOL NOPRINT
 BY HIGHEST NUM_GRP_CLI NOPRINT
 BY HIGHEST 1 NUM_TIE NOPRINT
 WHERE PLACE LE 1
 ON TABLE HOLD AS FDAPPS6

END

This message has been edited. Last edited by: Kykyn,

You could try moving the define into a compute and using WHERE TOTAL.

The downside is that the WHERE will not be acted upon until the data is returned to WebFOCUS.

The inefficiency should be weighed against a number of things and only you (or your DBAs) will be able to say whether the processing cost is acceptable.

DEFINE FILE FDAPPS5
END

TABLE FILE FDAPPS5
 PRINT
  NUM_GRP_CLI
  NUM_IP_FED
  NUM_TIE
  NUM_GUI
  NUM_BUR
  COD_FED
  COD_PMT_FED_ORI
  COD_BQE
  NUM_SOC
  COD_STE_GES
  NUM_DIR
  SCO_PEO_VOL
  COD_SAS_CDC
  DAT_CONTACT
  SEM_CONTACT
  AGE_CALC
  DAT_ANNIV
  DAT_NAI
  COMPUTE PLACE/I11 = IF SEM_CONTACT NE LAST SEM_CONTACT THEN 1
            ELSE IF COD_SAS_CDC EQ LAST COD_SAS_CDC THEN PLACE + 1 ELSE 1;
 BY SEM_CONTACT NOPRINT
 BY COD_SAS_CDC NOPRINT
 BY HIGHEST SCO_PEO_VOL NOPRINT
 BY HIGHEST NUM_GRP_CLI NOPRINT
 BY HIGHEST 1 NUM_TIE NOPRINT
 WHERE TOTAL PLACE LE 1
 ON TABLE HOLD AS FDAPPS6
END

T

Hi,

Thanks a lot for your answer

Unfortunatly it doesn't work

when i try to obtain a VERTICA master on output, i have a bug :

  
TABLE FILE FDAPPS5
 PRINT
  NUM_GRP_CLI
  NUM_IP_FED
  NUM_TIE
  NUM_GUI
  NUM_BUR
  COD_FED
  COD_PMT_FED_ORI
  COD_BQE
  NUM_SOC
  COD_STE_GES
  NUM_DIR
  SCO_PEO_VOL
  COD_SAS_CDC
  DAT_CONTACT
  SEM_CONTACT
  AGE_CALC
  DAT_ANNIV
  DAT_NAI
    COMPUTE PLACE/I11 = IF SEM_CONTACT NE LAST SEM_CONTACT THEN 1
            ELSE IF COD_SAS_CDC EQ LAST COD_SAS_CDC THEN PLACE + 1 ELSE 1;
 BY SEM_CONTACT NOPRINT
 BY COD_SAS_CDC NOPRINT
 BY HIGHEST SCO_PEO_VOL NOPRINT
 BY HIGHEST NUM_GRP_CLI NOPRINT
 BY HIGHEST 1 NUM_TIE NOPRINT
 WHERE TOTAL PLACE LE &&LIMIT
 ON TABLE HOLD AS FDAPPS6 FORMAT SAME_DB
 END

(FOC2566) COMPUTE PLACE CANNOT BE CONVERTED TO SQL
(FOC2586) SELF-REFERENCING DEFINE PLACE CANNOT BE CONVERTED TO SQL
(FOC2622) OPTIMIZATION OF HOLD FORMAT SAME_DB CANNOT BE DONE:
(FOC2629) REQUEST IS IMPOSSIBLE TO CONVERT TO SQL (SEE SQLAGGR TRACE)

The issue seems to be that LAST cannot be converted to SQL, and needs to be for a FORMAT SAME_DB.

You have two choices.

1. Change the DEFINE/COMPUTE to get what you want with out using LAST. Possibly creating an extract and joining to get the LAST value.
2. Change SAME_DB to HOLD AS name FORMAT SQLVRT. You will need to work out how to create a temp table.

In Tony's example, change the COMPUTE as follows:

COMPUTE PLACE/I11 = IF SEM_CONTACT NE LAST SEM_CONTACT THEN 1 ELSE
                    IF COD_SAS_CDC EQ LAST COD_SAS_CDC THEN LAST PLACE + 1 ELSE 1;

This message has been edited. Last edited by: jgelona,

Hi,

to begin i want to wish you and your family a really good year for 2020!

And so, for my problem, in fact Waz, LAST cannot be converted to SQL, i posted my pb here beacause i thought maybe someone knowned a way or a function which could do the job as LAST but on SQL language.

i really would like having a VERTICA master on output, but i begin to think it will not be possible, so it doesn't matter, i will do that with a SEQUENTIAL master.

Thks a lot

Have a good day.

Selecting one or N records within a sorted subset of a request is a common need.
This is handle in SQL with RANK OVER PARTITION clause and the VERTICA documentation for it is :

https://www.vertica.com/docs/9...nalyticFunctions.htm

WebFOCUS is capable (sometimes if you are carefull with optilization) to generate this type of request. Here is one example
The main thing to remember is that you need to be able to sort your subset over a "unique key" to propery rank records and then be able to filter them.


TABLE FILE BQCITIE
PRINT
NUM_TIE
AGE_TIE
NUM_GUI_CTR
REF_CTR_EXI
COD_BQE_CTR AS 'COD_BQE_CTR'
BY NUM_GRP_CLI
BY HIGHEST 1 FLG_IP
BY LOWEST 1 REF_CTR_EXI
WHERE BQCITIE.FOLDERFILTER.EXCLUS_TIERS_décédés ;
WHERE BQCITIE.FOLDERFILTER.EXCLUS_TIERS_RISK_EetF ;
WHERE BQCITIE.FOLDERFILTER.EXCLUS_TIERS_PM ;
WHERE BQCITIE.FOLDERFILTER.EXCLUS_TIERS_NON_RESIDENT ;
WHERE BQCITIE.FOLDERFILTER.EXCLUS_TIERS_NPAI ;
WHERE BQCITIE.FOLDERFILTER.EXCLUS_TIERS_INTERDITS ;
WHERE BQCITIE.FOLDERFILTER.EXCLUS_TIERS_INCAPABLES ;
WHERE BQCITIE.FOLDERFILTER.EXCLUS_TIERS_SAL_GRP ;
WHERE COD_PRD EQ 'B 27400';
WHERE COD_ECV_CTR EQ '4';
WHERE NUM_GUI_CTR EQ '01001';
WHERE NUM_GRP_CLI IN ('GRP0300000826720', 'GRP0300000825495', 'GRP0300000820375','GRP0300000837270','GRP2000000989182','GRP2000000991365');
ON TABLE HOLD AS H1 FORMAT SQL_SCRIPT
END

TABLE FILE H1
PRINT *
BY NUM_GRP_CLI NOPRINT
END

would generate this

SELECT
T1."SK001_NUM_GRP_CLI",
T1."SK002_FLG_IP",
T1."SK003_REF_CTR_EXI",
T1."VB001_PRN_NUM_TIE",
T1."VB002_PRN_AGE_TIE",
T1."VB003_PRN_NUM_GUI_CTR",
T1."VB004_PRN_REF_CTR_EXI",
T1."VB005_PRN_COD_BQE"
FROM
( /* vvv */
SELECT
"SK001_NUM_GRP_CLI",
"SK002_FLG_IP",
"SK003_REF_CTR_EXI",
"VB001_PRN_NUM_TIE",
"VB002_PRN_AGE_TIE",
"VB003_PRN_NUM_GUI_CTR",
"VB004_PRN_REF_CTR_EXI",
"VB005_PRN_COD_BQE"
FROM (
SELECT
T1."NUM_GRP_CLI" AS "SK001_NUM_GRP_CLI",
T1."FLG_IP" AS "SK002_FLG_IP",
DENSE_RANK() OVER( PARTITION BY T1."NUM_GRP_CLI" ORDER BY T1."FLG_IP" DESC NULLS LAST) AS "RNK002",
T10."REF_CTR_EXI" AS "SK003_REF_CTR_EXI",
DENSE_RANK() OVER( PARTITION BY T1."NUM_GRP_CLI", T1."FLG_IP" ORDER BY T10."REF_CTR_EXI" ASC NULLS FIRST) AS "RNK003",
T1."NUM_TIE" AS "VB001_PRN_NUM_TIE",
(CASE (T1."DAT_NAI") WHEN DATE '0001-01-01' THEN NULL ELSE
TRUNC((EXTRACT(DAY FROM (TRUNC(DATE '2018-12-12') -
TRUNC(T1."DAT_NAI"))) / 365.25),0) END) AS
"VB002_PRN_AGE_TIE",
T10."NUM_GUI_CTR" AS "VB003_PRN_NUM_GUI_CTR",
T10."REF_CTR_EXI" AS "VB004_PRN_REF_CTR_EXI",
T12."COD_BQE" AS "VB005_PRN_COD_BQE"
FROM
( ( ( DAQ1V0.VDCQT10_TIE T1
LEFT OUTER JOIN
DAQ1V0.VDCQTXC_EQUTIE T10
ON T10."IDT_PI" = T1."IDT_PI" AND
(T10."DAT_FIN_REL" = DATE '9999-12-31') )
LEFT OUTER JOIN
DAQ1V0.VDSQC00_ICN T12
ON T12."IDT_AC" = T10."IDT_AC" )
LEFT OUTER JOIN
DAQ1V0.VDCQT14_RISTIE T38
ON T38."IDT_PI" = T1."IDT_PI" )
WHERE
(T1."NUM_GRP_CLI" IN('GRP0300000826720', 'GRP0300000825495',
'GRP0300000820375', 'GRP0300000837270', 'GRP2000000989182',
'GRP2000000991365')) AND
(T1."COD_SAL_GRP" NOT IN('G', 'C')) AND
(T1."COD_CPC_JUR" = ' ') AND
(T1."FLG_PND" <> 'O') AND
(T1."COD_PAY" = '000') AND
(T1."COD_TYP_TIE" = '1') AND
(T1."COD_RIS_FIN" NOT IN('E=', 'E+', 'E-', 'F')) AND
(((T1."COD_TYP_TIE" = '1') AND (T1."DAT_DCS" = DATE
'0001-01-01')) OR ((T1."COD_TYP_TIE" = '2') AND (T1."DAT_DSL"
= DATE '0001-01-01'))) AND
(T1."FLG_PCE_PDN_TIE" = 'O') AND
(T10."NUM_GUI_CTR" = '01001') AND
(T10."COD_ECV_CTR" = '4') AND
(T10."COD_PRD" = 'B 27400') AND
(T38."FLG_EXL_MKT" = 'N')
) X
WHERE
"RNK002" <= 1
AND "RNK003" <= 1
) /* ^^^ */ T1
ORDER BY
T1."SK001_NUM_GRP_CLI";

Based on you example I suggest you contact me directly with your example

quote:

SQL_SCRIPT

Awesome, forgot about this option.

i think may be my pb is not clearly explained

If i do the following code with a sequential master on output :

  
DEFINE FILE FDAPPS5
 PLACE/I8 = IF SEM_CONTACT NE LAST SEM_CONTACT THEN 1
            ELSE IF COD_SAS_CDC EQ LAST COD_SAS_CDC THEN (PLACE+1) ELSE 1;
END

TABLE FILE FDAPPS5
 PRINT
  NUM_TIE
  SCO_PEO_VOL
  COD_SAS_CDC
  SEM_CONTACT
  PLACE
 BY SEM_CONTACT NOPRINT
 BY COD_SAS_CDC NOPRINT
 BY HIGHEST SCO_PEO_VOL NOPRINT
 ON TABLE HOLD AS FDAPPS6
END

This is what i have in my master :

NUM_TIE--------SCO_PEO_VOL------------COD_SAS_CDC----------SEM_CONTACT------------PLACE
A---------------------------10-------------------C1001FH----------------S49----------------------------1
B---------------------------10-------------------C1001FH----------------S49----------------------------2
C---------------------------10-------------------C1001FH----------------S49----------------------------3
D---------------------------10-------------------C1001FH----------------S50----------------------------1
E---------------------------09-------------------C1001FH----------------S50----------------------------2
F---------------------------09-------------------C1001FH----------------S50----------------------------3
G---------------------------10-------------------C1001JM----------------S49----------------------------1
H---------------------------10-------------------C1001JM----------------S49----------------------------2
I---------------------------09-------------------C1001JM----------------S49----------------------------3
J---------------------------08-------------------C1001JM----------------S49----------------------------4
K---------------------------08-------------------C1001JM----------------S49----------------------------5
L---------------------------10-------------------C1001JM----------------S50----------------------------1
M---------------------------10-------------------C1001JM----------------S50----------------------------2
N---------------------------09-------------------C1001JM----------------S50----------------------------3



The goal of this code is to keep only 1, 2 or why not 3 lines by SEM_CONTACT/COD_SAS_CDC
that's why i create PLACE

For example, if i only want 2 lines, this is the code and the result :

  
DEFINE FILE FDAPPS5
 PLACE/I8 = IF SEM_CONTACT NE LAST SEM_CONTACT THEN 1
            ELSE IF COD_SAS_CDC EQ LAST COD_SAS_CDC THEN (PLACE+1) ELSE 1;
END

TABLE FILE FDAPPS5
 PRINT
  NUM_TIE
  SCO_PEO_VOL
  COD_SAS_CDC
  SEM_CONTACT
  PLACE
 BY SEM_CONTACT NOPRINT
 BY COD_SAS_CDC NOPRINT
 BY HIGHEST SCO_PEO_VOL NOPRINT
 WHERE PLACE LE 2
 ON TABLE HOLD AS FDAPPS6
END

NUM_TIE--------SCO_PEO_VOL------------COD_SAS_CDC----------SEM_CONTACT------------PLACE
A---------------------------10-------------------C1001FH----------------S49----------------------------1
B---------------------------10-------------------C1001FH----------------S49----------------------------2
D---------------------------10-------------------C1001FH----------------S50----------------------------1
E---------------------------09-------------------C1001FH----------------S50----------------------------2
G---------------------------10-------------------C1001JM----------------S49----------------------------1
H---------------------------10-------------------C1001JM----------------S49----------------------------2
L---------------------------10-------------------C1001JM----------------S50----------------------------1
M---------------------------10-------------------C1001JM----------------S50----------------------------2

But i don't know how to do that when i want a VERTICA master on output.

This message has been edited. Last edited by: Kykyn,

Have you tried using the PARTITION_REF function?

  
TABLE FILE FDAPPS5
 PRINT
  NUM_GRP_CLI
  NUM_IP_FED
  NUM_TIE
  NUM_GUI
  NUM_BUR
  COD_FED
  COD_PMT_FED_ORI
  COD_BQE
  NUM_SOC
  COD_STE_GES
  NUM_DIR
  SCO_PEO_VOL
  COD_SAS_CDC
  DAT_CONTACT
  SEM_CONTACT
  AGE_CALC
  DAT_ANNIV
  DAT_NAI
  COMPUTE PLACE/I8 = IF SEM_CONTACT NE PARTITION_REF(SEM_CONTACT, TABLE, -1) THEN 1
            ELSE IF COD_SAS_CDC EQ PARTITION_REF(SEM_CONTACT, TABLE, -1) THEN (PLACE+1) ELSE 1;
 BY SEM_CONTACT NOPRINT
 BY COD_SAS_CDC NOPRINT
 BY HIGHEST SCO_PEO_VOL NOPRINT
 BY HIGHEST NUM_GRP_CLI NOPRINT
 BY HIGHEST 1 NUM_TIE NOPRINT
 WHERE PLACE LE 1
 ON TABLE HOLD AS FDAPPS6

END

Hi,

Yes i tried this but that still does'nt work, despite of what i can see on vertica website :-(


10.12.00.209689 (FOC2566) COMPUTE PLACE CANNOT BE CONVERTED TO SQL
10.12.00.209711 (FOC2586) SELF-REFERENCING DEFINE PLACE CANNOT BE CONVERTED TO SQL
(FOC2622) OPTIMIZATION OF HOLD FORMAT SAME_DB CANNOT BE DONE:
(FOC2629) REQUEST IS IMPOSSIBLE TO CONVERT TO SQL (SEE SQLAGGR TRACE)



https://webfocusinfocenter.inf...d3-BDF2-2D536C15BA12

Your problem may very well be the "SELF REFERENCING DEFINE" error.

This statement is needs to be changed from this:

COMPUTE PLACE/I8 = IF SEM_CONTACT NE PARTITION_REF(SEM_CONTACT, TABLE, -1) THEN 1
            ELSE IF COD_SAS_CDC EQ PARTITION_REF(SEM_CONTACT, TABLE, -1) THEN (PLACE+1) ELSE 1;

to this

COMPUTE PLACE/I8 = IF SEM_CONTACT NE PARTITION_REF(SEM_CONTACT, TABLE, -1) THEN 1
            ELSE IF COD_SAS_CDC EQ PARTITION_REF(SEM_CONTACT, TABLE, -1) THEN (LAST PLACE+1) ELSE 1;

Notice the change from (PLACE+1) to (LAST PLACE+1).

Hi Kykyn,

Did you try Jean-Claude's suggestion of RANK OVER PARTITION ?

This will use Vertica native functions and avoid the use of LAST, and HOLD FORMAT SAME_DB should then work.

FYI,

The SQL can be injected multiple ways, either via Jean-Claude's example or using the DB_EXPR function.

Yo're right Waz, jean-claude's idea is the good to do what i want

for information this is my example :

Version if you want a SEQUENTIAL on output :

DEFINE FILE FDAPPS6
 PLACE/I8 = IF SEM_CONTACT NE LAST SEM_CONTACT THEN 1
            ELSE IF COD_SAS_CDC EQ LAST COD_SAS_CDC THEN (PLACE+1) ELSE 1;
END

TABLE FILE FDAPPS6
 PRINT list_of_vars
 BY SEM_CONTACT NOPRINT
 BY COD_SAS_CDC NOPRINT
 BY HIGHEST SCO_PEO_VOL NOPRINT
 WHERE PLACE LE 2
 ON TABLE HOLD AS FDAPPS9
END  

And the version if you want a VERTICA (but it also works for a SEQUENTIAL)

 
DEFINE FILE FDAPPS6
 CHAMP/A30=SCO_PEO_VOL | NUM_TIE;
END

TABLE FILE FDAPPS6
 PRINT list_of_vars
 BY SEM_CONTACT NOPRINT
 BY COD_SAS_CDC NOPRINT
 RANKED AS 'PLACE' BY HIGHEST CHAMP
 ON TABLE SET HOLDATTR CUBE
 ON TABLE HOLD AS FDAPPS7 FORMAT SAME_DB
 END
 -SET &NOMTAB='FDAPPS7';
 -IF &FOCERRNUM NE 0 THEN GOTO ERRSAMDB;
 -RUN
END

TABLE FILE FDAPPS7
 PRINT list_of_vars 
 BY SEM_CONTACT NOPRINT
 BY COD_SAS_CDC NOPRINT
 BY PLACE NOPRINT
 WHERE PLACE LE 2
 ON TABLE SET HOLDATTR CUBE
 ON TABLE HOLD AS FDAPPS9 FORMAT SAME_DB
 END
 -SET &NOMTAB='FDAPPS9';
 -IF &FOCERRNUM NE 0 THEN GOTO ERRSAMDB;
 -RUN
END 

big big thanks to JC!!