20 Replies Latest reply: Mar 13, 2017 10:52 AM by Sunny Talwar RSS

    Auto_increment column

    souad ouertani

      I  loaded two tables in another table and I need to add a column of auto increment, I used a rowno () function and thar did not work

        • Re: Auto_increment column
          Sunny Talwar

          May be try RecNo() if you have Preceding Load on top of the table where you used RowNo()?

            • Re: Auto_increment column
              souad ouertani

                  UnQualify *;

                      [ASSURANCE]:

                      LOAD

                    rowno() as tassurance_id,

                      'PROJET_AVENIR' AS PRODUIT,

                   STAUT_CLIENT AS STATUT_CLIENT,

                   MARCHE,ANNEE & '-' & IF(MOIS ='janv.','01',IF(MOIS='févr.','02',IF(MOIS='mars','03',IF(MOIS='avr.','04',IF(MOIS='mai','05',IF(MOIS='juin','06',IF(MOIS='juil.','07',IF(MOIS='août','08',IF(MOIS='sept.','09',IF(MOIS='oct.','10',IF(MOIS='nov.','11','12'))))))))))) AS SITUATION, 

                   %ID_AGENCE_CTOS_CLIENT AS AGENCE,

                   AGE_RELATION,

                   DESCRIPTION_CATEGORIE_CLIENT,

                   %ID_CATEGORIE_CLIENT,

                   'TND' AS DEVISE,

                   'PROJET_AVENIR' AS NATURE,

                   'PRODUITS_FINANCIER' AS TYPE_DESC,

                   'ASSSURANCE' AS SOUS_TYPE_DESC,

                   'PROD_FIN' AS TYPE,

                   'ASS' AS SOUS_TYPE,

                   PROD_PA AS PROD_MOIS,

                   SEGMENT,

                  0 AS ATTRITION,

                   0 AS CONQUETE,   DESCRIPTION_MARCHE_CLIENT,

                   STATUT_PA AS STATUT_PROD,

                   ANCIENNETE_RELATION,

                   NOMBRE_PA AS STOCK_NOMBRE,

                   0 AS PRODUCTION_NOMBRE,

                   0 AS PRODUCTTION_VOLUME_TND,

                   0 AS PRODUCTTION_VOLUME_DEV,

                   ENCOURS_PA AS STOCK_VOLUME_DEV,

                    ENCOURS_PA AS STOCK_VOLUME_TND,

                    SOUS_MARCHE,

                   TYPE_SOUS_MARCHE,

                   SEGMENT_2015 as SEGMENT_N_1

                  

              FROM

               

               

               

               

              C:\pfe\DONNEES_RECAP\PA.qvd

              (qvd);

               

               

              LOAD

                    rowno() as tassurance_id,

                  'FAMILIA' AS PRODUIT,

                   STAUT_CLIENT AS STATUT_CLIENT,

                   MARCHE,ANNEE & '-' & IF(MOIS ='janv.','01',IF(MOIS='févr.','02',IF(MOIS='mars','03',IF(MOIS='avr.','04',IF(MOIS='mai','05',IF(MOIS='juin','06',IF(MOIS='juil.','07',IF(MOIS='août','08',IF(MOIS='sept.','09',IF(MOIS='oct.','10',IF(MOIS='nov.','11','12'))))))))))) AS SITUATION, 

                   %ID_AGENCE_CTOS_CLIENT AS AGENCE,

                   AGE_RELATION,

                   DESCRIPTION_CATEGORIE_CLIENT,

                   %ID_CATEGORIE_CLIENT,

                   'TND' AS DEVISE,

                   'FAMILIA' AS NATURE,

                   'PRODUITS_FINANCIER' AS TYPE_DESC,

                   'ASSSURANCE' AS SOUS_TYPE_DESC,

                   'PROD_FIN' AS TYPE,

                   'ASS' AS SOUS_TYPE,

                   PROD_FAMILIA AS PROD_MOIS,

                   SEGMENT,

                    0 AS ATTRITION,

                   0 AS CONQUETE,   DESCRIPTION_MARCHE_CLIENT,

                   STATUT_FAMILIA AS STATUT_PROD,

                   ANCIENNETE_RELATION,

                   NOMBRE_FAMILIA AS STOCK_NOMBRE,

                   0 AS PRODUCTION_NOMBRE,

                   0 AS PRODUCTTION_VOLUME_TND,

                   0 AS PRODUCTTION_VOLUME_DEV,

                  0 AS STOCK_VOLUME_DEV,

                   0 AS STOCK_VOLUME_TND,

                    SOUS_MARCHE,

                   TYPE_SOUS_MARCHE,

                   SEGMENT_2015 as SEGMENT_N_1

                  

              FROM

               

               

              C:\pfe\DONNEES_RECAP\FAMILIA.qvd

              (qvd);

               

               

              LOAD

                 rowno() as tassurance_id,

                 'BENOUN' AS PRODUIT,

                   STAUT_CLIENT AS STATUT_CLIENT,

                   MARCHE,ANNEE & '-' & IF(MOIS ='janv.','01',IF(MOIS='févr.','02',IF(MOIS='mars','03',IF(MOIS='avr.','04',IF(MOIS='mai','05',IF(MOIS='juin','06',IF(MOIS='juil.','07',IF(MOIS='août','08',IF(MOIS='sept.','09',IF(MOIS='oct.','10',IF(MOIS='nov.','11','12'))))))))))) AS SITUATION, 

                   %ID_AGENCE_CTOS_CLIENT AS AGENCE,

                   AGE_RELATION,

                   DESCRIPTION_CATEGORIE_CLIENT,

                   %ID_CATEGORIE_CLIENT,

                   'TND' AS DEVISE,

                   'BENOUN' AS NATURE,

                   'PRODUITS_FINANCIER' AS TYPE_DESC,

                   'ASSSURANCE' AS SOUS_TYPE_DESC,

                   'PROD_FIN' AS TYPE,

                   'ASS' AS SOUS_TYPE,

                   PROD_BENOUN AS PROD_MOIS,

                   SEGMENT,

                   CONQUETE,

                   0 AS ATTRITION,   DESCRIPTION_MARCHE_CLIENT,

                   STATUT_BENOUN AS STATUT_PROD,

                   ANCIENNETE_RELATION,

                   NOMBRE_BENOUN AS STOCK_NOMBRE,

                   0 AS PRODUCTION_NOMBRE,

                   0 AS PRODUCTTION_VOLUME_TND,

                   0 AS PRODUCTTION_VOLUME_DEV,

                  0 AS STOCK_VOLUME_DEV,

                   0 AS STOCK_VOLUME_TND,

                    SOUS_MARCHE,

                   TYPE_SOUS_MARCHE,

                   SEGMENT_2015 as SEGMENT_N_1

                  

               

               

              FROM

               

               

              C:\pfe\DONNEES_RECAP\BENOUN.qvd

              (qvd);

              LOAD

                   rowno() as tassurance_id,

                   'INCENDIE' AS PRODUIT,

                   STAUT_CLIENT AS STATUT_CLIENT,

                   MARCHE,ANNEE & '-' & IF(MOIS ='janv.','01',IF(MOIS='févr.','02',IF(MOIS='mars','03',IF(MOIS='avr.','04',IF(MOIS='mai','05',IF(MOIS='juin','06',IF(MOIS='juil.','07',IF(MOIS='août','08',IF(MOIS='sept.','09',IF(MOIS='oct.','10',IF(MOIS='nov.','11','12'))))))))))) AS SITUATION, 

                   %ID_AGENCE_CTOS_CLIENT AS AGENCE,

                   AGE_RELATION,

                   DESCRIPTION_CATEGORIE_CLIENT,

                   %ID_CATEGORIE_CLIENT,

                   'TND' AS DEVISE,

                   'INCENDIE' AS NATURE,

                   'PRODUITS_FINANCIER' AS TYPE_DESC,

                   'ASSSURANCE' AS SOUS_TYPE_DESC,

                   'PROD_FIN' AS TYPE,

                   'ASS' AS SOUS_TYPE,

                   PROD_INCENDIE AS PROD_MOIS,

                   SEGMENT,

                   CONQUETE,

                   0 AS ATTRITION,   DESCRIPTION_MARCHE_CLIENT,

                   STATUT_INCENDIE AS STATUT_PROD,

                   ANCIENNETE_RELATION,

                   NOMBRE_INCENDIE AS STOCK_NOMBRE,

                   0 AS PRODUCTION_NOMBRE,

                   0 AS PRODUCTTION_VOLUME_TND,

                   0 AS PRODUCTTION_VOLUME_DEV,

                  0 AS STOCK_VOLUME_DEV,

                   0 AS STOCK_VOLUME_TND,

                    SOUS_MARCHE,

                   TYPE_SOUS_MARCHE,

                   SEGMENT_2015 as SEGMENT_N_1

                  

              FROM

              [C:\pfe\DONNEES_RECAP\INCENDIE.qvd]

              (qvd);

            • Re: Auto_increment column
              Vineeth Pujari

              how did you load the tables? can you post the script?

                • Re: Auto_increment column
                  souad ouertani

                      UnQualify *;

                          [ASSURANCE]:

                          LOAD

                        rowno() as tassurance_id,

                          'PROJET_AVENIR' AS PRODUIT,

                       STAUT_CLIENT AS STATUT_CLIENT,

                       MARCHE,ANNEE & '-' & IF(MOIS ='janv.','01',IF(MOIS='févr.','02',IF(MOIS='mars','03',IF(MOIS='avr.','04',IF(MOIS='mai','05',IF(MOIS='juin','06',IF(MOIS='juil.','07',IF(MOIS='août','08',IF(MOIS='sept.','09',IF(MOIS='oct.','10',IF(MOIS='nov.','11','12'))))))))))) AS SITUATION, 

                       %ID_AGENCE_CTOS_CLIENT AS AGENCE,

                       AGE_RELATION,

                       DESCRIPTION_CATEGORIE_CLIENT,

                       %ID_CATEGORIE_CLIENT,

                       'TND' AS DEVISE,

                       'PROJET_AVENIR' AS NATURE,

                       'PRODUITS_FINANCIER' AS TYPE_DESC,

                       'ASSSURANCE' AS SOUS_TYPE_DESC,

                       'PROD_FIN' AS TYPE,

                       'ASS' AS SOUS_TYPE,

                       PROD_PA AS PROD_MOIS,

                       SEGMENT,

                      0 AS ATTRITION,

                       0 AS CONQUETE,   DESCRIPTION_MARCHE_CLIENT,

                       STATUT_PA AS STATUT_PROD,

                       ANCIENNETE_RELATION,

                       NOMBRE_PA AS STOCK_NOMBRE,

                       0 AS PRODUCTION_NOMBRE,

                       0 AS PRODUCTTION_VOLUME_TND,

                       0 AS PRODUCTTION_VOLUME_DEV,

                       ENCOURS_PA AS STOCK_VOLUME_DEV,

                        ENCOURS_PA AS STOCK_VOLUME_TND,

                        SOUS_MARCHE,

                       TYPE_SOUS_MARCHE,

                       SEGMENT_2015 as SEGMENT_N_1

                      

                  FROM

                   

                   

                   

                   

                  C:\pfe\DONNEES_RECAP\PA.qvd

                  (qvd);

                   

                   

                  LOAD

                        rowno() as tassurance_id,

                      'FAMILIA' AS PRODUIT,

                       STAUT_CLIENT AS STATUT_CLIENT,

                       MARCHE,ANNEE & '-' & IF(MOIS ='janv.','01',IF(MOIS='févr.','02',IF(MOIS='mars','03',IF(MOIS='avr.','04',IF(MOIS='mai','05',IF(MOIS='juin','06',IF(MOIS='juil.','07',IF(MOIS='août','08',IF(MOIS='sept.','09',IF(MOIS='oct.','10',IF(MOIS='nov.','11','12'))))))))))) AS SITUATION, 

                       %ID_AGENCE_CTOS_CLIENT AS AGENCE,

                       AGE_RELATION,

                       DESCRIPTION_CATEGORIE_CLIENT,

                       %ID_CATEGORIE_CLIENT,

                       'TND' AS DEVISE,

                       'FAMILIA' AS NATURE,

                       'PRODUITS_FINANCIER' AS TYPE_DESC,

                       'ASSSURANCE' AS SOUS_TYPE_DESC,

                       'PROD_FIN' AS TYPE,

                       'ASS' AS SOUS_TYPE,

                       PROD_FAMILIA AS PROD_MOIS,

                       SEGMENT,

                        0 AS ATTRITION,

                       0 AS CONQUETE,   DESCRIPTION_MARCHE_CLIENT,

                       STATUT_FAMILIA AS STATUT_PROD,

                       ANCIENNETE_RELATION,

                       NOMBRE_FAMILIA AS STOCK_NOMBRE,

                       0 AS PRODUCTION_NOMBRE,

                       0 AS PRODUCTTION_VOLUME_TND,

                       0 AS PRODUCTTION_VOLUME_DEV,

                      0 AS STOCK_VOLUME_DEV,

                       0 AS STOCK_VOLUME_TND,

                        SOUS_MARCHE,

                       TYPE_SOUS_MARCHE,

                       SEGMENT_2015 as SEGMENT_N_1

                      

                  FROM

                   

                   

                  C:\pfe\DONNEES_RECAP\FAMILIA.qvd

                  (qvd);

                   

                   

                  LOAD

                     rowno() as tassurance_id,

                     'BENOUN' AS PRODUIT,

                       STAUT_CLIENT AS STATUT_CLIENT,

                       MARCHE,ANNEE & '-' & IF(MOIS ='janv.','01',IF(MOIS='févr.','02',IF(MOIS='mars','03',IF(MOIS='avr.','04',IF(MOIS='mai','05',IF(MOIS='juin','06',IF(MOIS='juil.','07',IF(MOIS='août','08',IF(MOIS='sept.','09',IF(MOIS='oct.','10',IF(MOIS='nov.','11','12'))))))))))) AS SITUATION, 

                       %ID_AGENCE_CTOS_CLIENT AS AGENCE,

                       AGE_RELATION,

                       DESCRIPTION_CATEGORIE_CLIENT,

                       %ID_CATEGORIE_CLIENT,

                       'TND' AS DEVISE,

                       'BENOUN' AS NATURE,

                       'PRODUITS_FINANCIER' AS TYPE_DESC,

                       'ASSSURANCE' AS SOUS_TYPE_DESC,

                       'PROD_FIN' AS TYPE,

                       'ASS' AS SOUS_TYPE,

                       PROD_BENOUN AS PROD_MOIS,

                       SEGMENT,

                       CONQUETE,

                       0 AS ATTRITION,   DESCRIPTION_MARCHE_CLIENT,

                       STATUT_BENOUN AS STATUT_PROD,

                       ANCIENNETE_RELATION,

                       NOMBRE_BENOUN AS STOCK_NOMBRE,

                       0 AS PRODUCTION_NOMBRE,

                       0 AS PRODUCTTION_VOLUME_TND,

                       0 AS PRODUCTTION_VOLUME_DEV,

                      0 AS STOCK_VOLUME_DEV,

                       0 AS STOCK_VOLUME_TND,

                        SOUS_MARCHE,

                       TYPE_SOUS_MARCHE,

                       SEGMENT_2015 as SEGMENT_N_1

                      

                   

                   

                  FROM

                   

                   

                  C:\pfe\DONNEES_RECAP\BENOUN.qvd

                  (qvd);

                  LOAD

                       rowno() as tassurance_id,

                       'INCENDIE' AS PRODUIT,

                       STAUT_CLIENT AS STATUT_CLIENT,

                       MARCHE,ANNEE & '-' & IF(MOIS ='janv.','01',IF(MOIS='févr.','02',IF(MOIS='mars','03',IF(MOIS='avr.','04',IF(MOIS='mai','05',IF(MOIS='juin','06',IF(MOIS='juil.','07',IF(MOIS='août','08',IF(MOIS='sept.','09',IF(MOIS='oct.','10',IF(MOIS='nov.','11','12'))))))))))) AS SITUATION, 

                       %ID_AGENCE_CTOS_CLIENT AS AGENCE,

                       AGE_RELATION,

                       DESCRIPTION_CATEGORIE_CLIENT,

                       %ID_CATEGORIE_CLIENT,

                       'TND' AS DEVISE,

                       'INCENDIE' AS NATURE,

                       'PRODUITS_FINANCIER' AS TYPE_DESC,

                       'ASSSURANCE' AS SOUS_TYPE_DESC,

                       'PROD_FIN' AS TYPE,

                       'ASS' AS SOUS_TYPE,

                       PROD_INCENDIE AS PROD_MOIS,

                       SEGMENT,

                       CONQUETE,

                       0 AS ATTRITION,   DESCRIPTION_MARCHE_CLIENT,

                       STATUT_INCENDIE AS STATUT_PROD,

                       ANCIENNETE_RELATION,

                       NOMBRE_INCENDIE AS STOCK_NOMBRE,

                       0 AS PRODUCTION_NOMBRE,

                       0 AS PRODUCTTION_VOLUME_TND,

                       0 AS PRODUCTTION_VOLUME_DEV,

                      0 AS STOCK_VOLUME_DEV,

                       0 AS STOCK_VOLUME_TND,

                        SOUS_MARCHE,

                       TYPE_SOUS_MARCHE,

                       SEGMENT_2015 as SEGMENT_N_1

                      

                  FROM

                  [C:\pfe\DONNEES_RECAP\INCENDIE.qvd]

                  (qvd);

                  • Re: Auto_increment column
                    souad ouertani

                    VINEETH

                     

                    UNQUALIFY *;

                    [PACKSF]:

                    LOAD 

                     

                     

                    'PACKS' AS PRODUIT,

                        ANNEE & '-' & IF(MOIS ='janv.','01',IF(MOIS='févr.','02',IF(MOIS='mars','03',IF(MOIS='avr.','04',IF(MOIS='mai','05',IF(MOIS='juin','06',IF(MOIS='juil.','07',IF(MOIS='août','08',IF(MOIS='sept.','09',IF(MOIS='oct.','10',IF(MOIS='nov.','11','12'))))))))))) AS SITUATIONPACK, 

                         STAUT_CLIENT AS STATUT_CLIENT,

                         MARCHE,

                         DESCRIPTION_MARCHE_CLIENT,

                          %ID_AGENCE_CTOS_PACK AS AGENCEPACK,

                         AGE_RELATION,

                         DESCRIPTION_CATEGORIE_CLIENT,

                         %ID_CATEGORIE_CLIENT,

                         %ID_TYPE_PACK AS SOUS_TYPE,

                         LIB_TYPE_PACK AS SOUS_TYPE_DESC,

                         SEGMENT, 'PACK' AS TYPE,

                         0 AS CONQUETE,

                         ANCIENNETE_RELATION,

                         NOMBRE_PACK AS STOCK_NOMBRE,

                         0 AS PRODUCTTION_VOLUME_TND,

                         0 AS PRODUCTTION_VOLUME_DEV,

                         0 AS STOCK_VOLUME_DEV,

                         0 AS STOCK_VOLUME_TND,

                         0 AS PRODUCTION_NOMBRE,

                         0 AS ATTRITION,

                         0 AS PROD_MOIS,

                         STATUT_PACK AS STATUT_PROD, 'PACK' AS NATURE,

                         'XXX' AS DEVISE,'PACK' AS TYPE_DESC,

                          SOUS_MARCHE,

                         TYPE_SOUS_MARCHE,

                         SEGMENT_2015 as SEGMENT_N_1

                    FROM

                    [C:\pfe\DONNEES_RECAP\PACKS.qvd]

                    (qvd);

                     

                     

                    [PACKS]:

                    LOAD *,

                               RowNo() as tPACK_id

                    Resident [PACKSF];

                     

                     

                    DROP Table PACKSF;