Today Date Table Extraction from ODBC Connector

[bhavvibudagam]

Hi Experts,

Can any one please help me on below requirement.

In My Remedy Database daily one Job will run with today date on Job/Table name. By using ODBC Driver extracting the jobs.

The Code is like below. Yesterday I have extracted the A180217AJOB manually by selecting the table from the Data base and stored in to QVD. Today on 180217 there is another job added like A180218P_AJOB. Like this daily one new job will be added with corresponding date and that latest date job has to be stored in the QVD.

Instead of going to the database daily and selecting todays table manually is there any way to load the today's date job here

FROM "USER"."A180217_AJOB"; using for loop in the extraction layer.

The Date which is present in this job contains other dates also along with 180217 date in the current job.

LIB CONNECT TO 'Remedy-ODBC64 (John)';

LOAD

    Client,

    Date,

    START TIME,

    END TIME;

[A180209PR1_AJOB]:

SELECT  Client,

    Date,

    START TIME,

    END TIME

FROM "USER"."A180217_AJOB";

Thanks in advance.

[mdmukramali]

Hi,

Can you try something like this?

LET vToday=Date(Today(),'DDMMYY');

LET vSource = 'A'&$(vToday)&'PR1_AJOB';

LIB CONNECT TO 'Remedy-ODBC64 (John)';

LOAD

    Client,

    Date,

    START TIME,

    END TIME;

[A180209PR1_AJOB]:

SELECT  Client,

    Date,

    START TIME,

    END TIME

FROM "USER".$(vSource );

// FROM "USER"."$(vSource )";

[kaushiknsolanki]

Hi,

Try this, to load only latest date table.

Let vTableName = 'A'&Date(today(),'DDMMYY')&'PR1_AJOB';

LIB CONNECT TO 'Remedy-ODBC64 (John)';

LOAD

    Client,

    Date,

    START TIME,

    END TIME;

$(vTableName😞

SELECT  Client,

    Date,

    START TIME,

    END TIME

FROM "USER".$(vTableName);

Regards,

Kaushik Solanki

[bhavvibudagam]

Hi Koushik,

Thanks for your reply. I need one more small help related to this.

While generating new job on daily after 6 months almost 180 qvds will be generated.

Could you please help me on how to store only last 8 days jobs from today job tables in separate QVDs in the Extraction script.

Suppose When I have extracted the job on today 18/02/18  from data base I have to extract only from today to last 8 days jobs from data base to QVDs i.e.,

Total 9 QVDs need to generate in the folder every time from extraction script. Please help me on this.

1. A180210PR1_AJOB

2. A180211PR1_AJOB

3. A180212PR1_AJOB

4. A180213PR1_AJOB

5. A180214PR1_AJOB

6. A180215PR1_AJOB

7. A180216PR1_AJOB

8. A180217PR1_AJOB

9. A180218PR1_AJOB

Thanks in advance.

[kaushiknsolanki · Accepted solution]

Try this.

For i=0 to 8

Let vTableName = 'A'&Date(today()-$(i),'DDMMYY')&'PR1_AJOB';

LIB CONNECT TO 'Remedy-ODBC64 (John)';

LOAD

    Client,

    Date,

    START TIME,

    END TIME;

Data:

SELECT  Client,

    Date,

    START TIME,

    END TIME

FROM "USER".$(vTableName);

Next

Store Data in data.qvd;

Drop table Data;

Regards,

Kaushik Solanki

[bhavvibudagam]

Hi Kaushik,

Thanks a lot for your help. Its working