Preceding Load with Access DB Connection

[Anonymous]

Hello all.

I have been trying to get a preceding load to work with a connection to an Access DB. I have so far not been able to get this to work. What I am trying to do is create a reference field that defines the order in which months should appear in a graph. The fiscal year begins in October so in the graph I want October to appear first on the x-axis then November, December, etc..

Below is my current script. It does load the database data but doesn't seem to do anything with the LOAD statement.

ODBC CONNECT32 TO [MS Access Database;DBQ=J:\Fleet\Reports\ScoreCard\New_Scorecard\Scorecard.accdb];

SQL SELECT *

FROM tblScoreCardByWeek_Data_FiscalYears;

LOAD *,

sMonth,

     if(sMonth=10,1,

     if(sMonth=11,2,

     if(sMonth=12,3,

     if(sMonth=1,4,

     if(sMonth=2,5,

     if(sMonth=3,6,

     if(sMonth=4,7,

     if(sMonth=5,8,

     if(sMonth=6,9,

     if(sMonth=7,10,

     if(sMonth=8,11,

     if(sMonth=9,12

     )))))))))))) as FISCAL_Month_ID;

[sunny_talwar]

I think you got the order incorrect... It should be like this

LOAD ....

SELECT ....

FROM ....

So try this:

ODBC CONNECT32 TO [MS Access Database;DBQ=J:\Fleet\Reports\ScoreCard\New_Scorecard\Scorecard.accdb];

LOAD *,

     sMonth,

     if(sMonth=10,1,

     if(sMonth=11,2,

     if(sMonth=12,3,

     if(sMonth=1,4,

     if(sMonth=2,5,

     if(sMonth=3,6,

     if(sMonth=4,7,

     if(sMonth=5,8,

     if(sMonth=6,9,

     if(sMonth=7,10,

     if(sMonth=8,11,

     if(sMonth=9,12

     )))))))))))) as FISCAL_Month_ID;

SQL SELECT *

FROM tblScoreCardByWeek_Data_FiscalYears;

[Anonymous]

Hi Sunny.

I did try tinkering with the order of the statement but when I order it as you suggested I receive the following error.

ODBC read failed

SQL SELECT *

FROM tblScoreCardByWeek_Data_FiscalYears

[sunny_talwar]

Do you still get the same error with Resident Load

ODBC CONNECT32 TO [MS Access Database;DBQ=J:\Fleet\Reports\ScoreCard\New_Scorecard\Scorecard.accdb];

TempTable:

SQL SELECT *

FROM tblScoreCardByWeek_Data_FiscalYears;

Table:

LOAD *,

     sMonth,

     if(sMonth=10,1,

     if(sMonth=11,2,

     if(sMonth=12,3,

     if(sMonth=1,4,

     if(sMonth=2,5,

     if(sMonth=3,6,

     if(sMonth=4,7,

     if(sMonth=5,8,

     if(sMonth=6,9,

     if(sMonth=7,10,

     if(sMonth=8,11,

     if(sMonth=9,12

     )))))))))))) as FISCAL_Month_ID

Resident TempTable;

DROP Table TempTable;

[sasiparupudi1]

if you remove the preceeding load , does it read the data without any error?

[Anonymous]

I get a different error with Resident Load.

Field names must be unique within table

Table:

LOAD *,

     sMonth,

     if(sMonth=10,1,

     if(sMonth=11,2,

     if(sMonth=12,3,

     if(sMonth=1,4,

     if(sMonth=2,5,

     if(sMonth=3,6,

     if(sMonth=4,7,

     if(sMonth=5,8,

     if(sMonth=6,9,

     if(sMonth=7,10,

     if(sMonth=8,11,

     if(sMonth=9,12

     )))))))))))) as FISCAL_Month_ID

Resident TempTable

[sasiparupudi1]

LOAD *,

     sMonth,

     if(sMonth=10,1,

     if(sMonth=11,2,

     if(sMonth=12,3,

     if(sMonth=1,4,

     if(sMonth=2,5,

     if(sMonth=3,6,

     if(sMonth=4,7,

     if(sMonth=5,8,

     if(sMonth=6,9,

     if(sMonth=7,10,

     if(sMonth=8,11,

     if(sMonth=9,12

     )))))))))))) as FISCAL_Month_ID

Resident TempTable

[sunny_talwar · Accepted solution]

I think we were adding sMonth using * and again with sMonth.... Try this

Table:

LOAD *,

    sMonth,

    if(sMonth=10,1,

    if(sMonth=11,2,

    if(sMonth=12,3,

    if(sMonth=1,4,

    if(sMonth=2,5,

    if(sMonth=3,6,

    if(sMonth=4,7,

    if(sMonth=5,8,

    if(sMonth=6,9,

    if(sMonth=7,10,

    if(sMonth=8,11,

    if(sMonth=9,12

    )))))))))))) as FISCAL_Month_ID

Resident TempTable;

[vishsaggi]

Sunny we can use Pick(Match()) as well right? Extending to few replies may be try this?

SET vAccessDBPath = 'J:\Fleet\Reports\ScoreCard\New_Scorecard\Scorecard.accdb';

ODBC CONNECT32 TO [MS Access Database;DBQ=J:\Fleet\Reports\ScoreCard\New_Scorecard\Scorecard.accdb];

Table1:

SQL SELECT *

FROM $(vAccessDBPath)."tblScoreCardByWeek_Data_FiscalYears";

NoConcatenate

FINAL:

LOAD *,

    Pick(Match(sMonth, '10','11','12','1','2','3','4','5','6','7','8','9'), 1,2,3,4,5,6,7,8,9,10,11,12) AS FISCAL_Month_ID

Resident Table1;

DROP TABLE Table1;

[sunny_talwar]

I guess this is fine tuning, but the main question here was why did the preceding load error out... We can further fine tune your script to this

Pick(sMonth, 4,5,6,7,8,9,10,11,12,1,2,3) as FISCAL_Month_ID