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,
WebFocus 8.2, Windows
December 24, 2019, 07:05 AM
Tony A
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
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
December 27, 2019, 01:48 PM
Kykyn
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)
WebFocus 8.2, Windows
December 29, 2019, 01:58 PM
Waz
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.
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
December 30, 2019, 08:44 AM
jgelona
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,
In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
January 02, 2020, 02:12 AM
Kykyn
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.
WebFocus 8.2, Windows
January 02, 2020, 06:01 AM
Jean-Claude CARRIERE
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 :
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
January 02, 2020, 12:04 PM
Waz
quote:
SQL_SCRIPT
Awesome, forgot about this option.
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
January 03, 2020, 03:59 AM
Kykyn
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
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
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
Hallway
Prod: 8202M1
Test: 8202M4
Repository:
OS:
Outputs:
January 06, 2020, 04:15 AM
Kykyn
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)
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).
In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
January 07, 2020, 02:45 PM
Waz
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.
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
January 09, 2020, 05:06 AM
Kykyn
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