5 Replies Latest reply: May 19, 2016 11:36 PM by Francois Tremblay RSS

    Invalid Path

    Francois Tremblay

      Hello everyone & Thanks for your contribution!

       

      I have to built a Calendar with tables from MS SQL. I'm administrator of the database.

       

      I tried Do While Loop, Generate etc, but unfortunately I always get an error 'Invalid Path', at the CalTemp2:

       

      I need a table that will start will:

       

      Fiscal Year   - Period

      2015                          1

      2015                          2

      2015                          3

      2015                          4 

      ......                              ....

      2016                           1    etc

       

      Depending of the company it could have 12 or 13 Periods / Year, so that's why I can't simply insert the periods.

       

       

      In comment you will see all my attemps,

       

      Please help!! Thanks!

       

       

      LIB CONNECT TO 'SQLEXPRESS';

       

       

      GL01:

      LOAD OPTIONID,

          PHONENBR,

          FAX,

          COMPANYID,

          CONTACT,

          CLOSESEG,

          ACCTSEG,

          ABRKDFLT,

          ABRKDELM,

          SWBTCHEDIT,

          SWPROVPST,

          SWPRTPBTCH,

          CODELOCK1,

          CODELOCK2,

          CODELOCK3,

          CODELOCK4,

          CODELOCK5,

          LOCKFILL,

          SWQTY,

          QTYDEC,

          YRSHIST,

          YRACCTDEL,

          NEXTBTCHNO,

          PSTSQ,

          PROVPSTSQ,

          PJRNLPRGTO,

          BTCHPSTTO,

          JRNLPRGTO,

          FLOOR(YRCLSLST) as FSCYEAR,

          YRLSTACTL,

          PRDNOPSTPR,

          YRNOPSTPR,

          REACCT,

          SWMC,

          DFLRATETYP,

          SWACCTGRP,

          SWPRVYRPST,

          YRSTRANDTL,

          HSTCLRACCT,

          RPACCT,

          SRCETYPE,

          SWUSESEC,

          SWDEFACCSS;

      SQL SELECT OPTIONID,

          PHONENBR,

          FAX,

          COMPANYID,

          CONTACT,

          CLOSESEG,

          ACCTSEG,

          ABRKDFLT,

          ABRKDELM,

          SWBTCHEDIT,

          SWPROVPST,

          SWPRTPBTCH,

          CODELOCK1,

          CODELOCK2,

          CODELOCK3,

          CODELOCK4,

          CODELOCK5,

          LOCKFILL,

          SWQTY,

          QTYDEC,

          YRSHIST,

          YRACCTDEL,

          NEXTBTCHNO,

          PSTSQ,

          PROVPSTSQ,

          PJRNLPRGTO,

          BTCHPSTTO,

          JRNLPRGTO,

          YRCLSLST,

          YRLSTACTL,

          PRDNOPSTPR,

          YRNOPSTPR,

          REACCT,

          SWMC,

          DFLRATETYP,

          SWACCTGRP,

          SWPRVYRPST,

          YRSTRANDTL,

          HSTCLRACCT,

          RPACCT,

          SRCETYPE,

          SWUSESEC,

          SWDEFACCSS

      FROM OCT53D.dbo.GL01;

       

       

      LIB CONNECT TO 'SQLEXPRESS';

       

       

      CSFSC:

      LOAD FSCYEAR,

          PERIODS,

          QTR4PERD,

          ACTIVE,

          BGNDATE1,

          BGNDATE2,

          BGNDATE3,

          BGNDATE4,

          BGNDATE5,

          BGNDATE6,

          BGNDATE7,

          BGNDATE8,

          BGNDATE9,

          BGNDATE10,

          BGNDATE11,

          BGNDATE12,

          BGNDATE13,

          ENDDATE1,

          ENDDATE2,

          ENDDATE3,

          ENDDATE4,

          ENDDATE5,

          ENDDATE6,

          ENDDATE7,

          ENDDATE8,

          ENDDATE9,

          ENDDATE10,

          ENDDATE11,

          ENDDATE12,

          ENDDATE13;

      SQL SELECT FSCYEAR,

          PERIODS,

          QTR4PERD,

          ACTIVE,

          BGNDATE1,

          BGNDATE2,

          BGNDATE3,

          BGNDATE4,

          BGNDATE5,

          BGNDATE6,

          BGNDATE7,

          BGNDATE8,

          BGNDATE9,

          BGNDATE10,

          BGNDATE11,

          BGNDATE12,

          BGNDATE13,

          ENDDATE1,

          ENDDATE2,

          ENDDATE3,

          ENDDATE4,

          ENDDATE5,

          ENDDATE6,

          ENDDATE7,

          ENDDATE8,

          ENDDATE9,

          ENDDATE10,

          ENDDATE11,

          ENDDATE12,

          ENDDATE13

      FROM OCT53D.dbo.CSFSC;

       

       

       

       

      LIB CONNECT TO 'SQLEXPRESS';

       

       

      MinMaxYear:

      Load

        MIN(FSCYEAR) AS MinYear,

        MAX(FSCYEAR) AS MaxYear

      RESIDENT CSFSC;

       

       

      // Create variables with Min and Max Year & Period Calendar

      LET vMinYear = NUM(PEEK('MinYear',0,'MinMaxYear'));

       

       

      LET vMaxYear = NUM(PEEK('MaxYear',0,'MinMaxYear'));

       

       

      LET vYear = $(vMaxYear);

       

       

      // Generate a single table with one field containing

      // all existing Fiscal Year between vMinYear & vMaxYear.

       

       

      CalTemp:

      LOAD

        ($(vMinYear) + ROWNO() -1) AS TempYear

      AutoGenerate

        $(vMaxYear) - $(vMinYear) +1;

       

       

      // _________________________________________________________________

       

       

       

       

       

       

      LIB CONNECT TO 'SQLEXPRESS';

       

       

      YearPeriods:

      Load

        FSCYEAR AS vFSCYEAR,

        PERIODS AS vPeriods

      RESIDENT CSFSC;

       

       

      // Create variables with Min and Max Year & Period Calendar

      LET vMinPeriods = 1;

      LET vFSCYEAR = NUM(PEEK('vFSCYEAR',0,'YearPeriods'));

      LET vPeriods = NUM(PEEK('vPeriods',0,'YearPeriods'));

       

       

      // LET vYear = $(vMaxYear);

       

       

      // Generate a single table with two fields containing

      // all existing Fiscal Year & Period between 12 & 13 periods.

       

       

      // LIB CONNECT TO 'SQLEXPRESS';

       

       

      CalTemp2:

        Load * from YearPeriod;

        $(TempYear) as TempYear,

          ($(vMinPeriods) + ROWNO() -1) AS TempPeriod

        AutoGenerate

          $(vPeriods) - $(vMinPeriods) +1;

       

       

      // Do While $(vMinYear) <= $(vMaxYear)

      // Load $(vMinYear) as vFSCYEAR,

      //   ($(vMinPeriods) + ROWNO() -1) AS TempPeriod

      // AutoGenerate

      //   $(vPeriods) - $(vMinPeriods) +1;

      // Let $(vMinYear) = $(vMinYear) +1;

      // Loop

        • Re: Invalid Path
          Gysbert Wassenaar

          CalTemp2:

            Load * from YearPeriod;

            $(TempYear) as TempYear,

              ($(vMinPeriods) + ROWNO() -1) AS TempPeriod

            AutoGenerate

              $(vPeriods) - $(vMinPeriods) +1;

           

           

          The first two lines create a table called CalTemp2

          The rest after the semicolon after YearPeriod is missing a LOAD keyword. Unless you're trying to do something I don't understand.

          • Re: Invalid Path
            Francois Tremblay

            Hello again,

             

            & Thanks for your contribution Gysbert & Swuehl!

             

            I've made a clean up and here's my new script.

             

            My goal is to get a table that has a first column with the Year (FSCYEAR), second column the Period and it should look like this:

             

            2015  1

            2015  2

            ...        ...

            2015  12

            2016    1

            2016    2

            ....        ....

             

            My section CalTemp2: works if I comment the line FSCYEAR, I get a row with all the periods from 1 to 12 but I'm not able to get the FSCYEAR in the first column. I get an error :

             

            The following error occurred:

            Field not found - <FSCYEAR>

            The error occurred here:

            CalTemp2: Load FSCYEAR, (1 + ROWNO() -1) AS vPeriods AutoGenerate 12 - 1 +1

             

            Would you use 'Do while Loop' instead? what would be your coding for it? I have many column to add after the period.

             

            Thanks a lot for your help Guys!

             

             

             

            My Code:

            ________________________________

            SET ThousandSep=',';

            SET DecimalSep='.';

            SET MoneyThousandSep=',';

            SET MoneyDecimalSep='.';

            SET MoneyFormat='$#,##0.00;($#,##0.00)';

            SET TimeFormat='h:mm:ss TT';

            SET DateFormat='M/D/YYYY';

            SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

            SET FirstWeekDay=6;

            SET BrokenWeeks=1;

            SET ReferenceDay=0;

            SET FirstMonthOfYear=1;

            SET CollationLocale='en-US';

            SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

            SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

            SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

            SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

             

             

            LIB CONNECT TO 'SQLEXPRESS';

             

             

             

             

            CSFSC:

            LOAD FSCYEAR,

                PERIODS,

            ....

            SQL SELECT FSCYEAR,

                PERIODS,

            ...

            FROM OCT53D.dbo.CSFSC;

             

            // This is a test

            YearPeriods:

            Load

              FSCYEAR AS vFSCYEAR,

              PERIODS AS vPeriods

            RESIDENT CSFSC;

             

            // I need to know if I have 12 or 13 periods per year

            MaxPeriods:

            Load

              MAX(PERIODS) AS MaxPeriods

            RESIDENT CSFSC;

             

            // Create variables with Min and Max Year & Period Calendar

            LET vMinPeriods = 1;

            LET vFSCYEAR = NUM(PEEK('FSCYEAR',0,'CSFSC'));

            LET vMaxPeriods = NUM(PEEK('MaxPeriods',0,'MaxPeriods'));

             

             

             

             

            // Generate a single table with two fields containing

            // all existing Fiscal Year & Periods from 1 to 12 (or 13) depending of the company.

             

            CalTemp2:

            Load

            // FSCYEAR,

                ($(vMinPeriods) + ROWNO() -1) AS vPeriods

            AutoGenerate

                 $(vMaxPeriods) - $(vMinPeriods) +1;

              • Re: Invalid Path
                Stefan Wühl

                In a load statement, you can only reference fields that are part of the input table.

                 

                When using an Autogenerate statement, there is no real input table, hence you can't reference field FSCYEAR from your resident table CSFCS without using inter record functions.

                 

                Maybe LOAD your FSCYEAR from your resident table and create the periods using a WHILE clause?

                 

                CalTemp2:

                Load Distinct

                     FSCYEAR,

                    ($(vMinPeriods) + iterno() -1) AS vPeriods

                RESIDENT CSFCS

                WHILE   ($(vMinPeriods) + iterno() -1) <= $(vMaxPeriods);

                  • Re: Invalid Path
                    Francois Tremblay

                    Thanks a million times Swuehl! (And Gysbert)

                     

                    I've been searching for two days, to get this simple task to work (I guess I have a long way to go ), but luckly I know that I can count on you guys.

                     

                    Thanks again and have a great weekend!