Column not found error while fetching data from MySQL

[kishorj1982]

Hi All,

Below is my incremental load script,

I am using date field for comparion.Although filed is present in DB it is throwing error as

AddressMainLoadDate column is missing.

set vLastReloadTime='2018-11-05 08:06:52 +00:00';

SET QVDFILE = $(QvdPath)DimCustomerAddress.qvd;

SET PKexp = CustomerId;

SET DailyQVD = $(DailyQvd)DimCustomerAddress&'_'&$(vDate).qvd;

SET QVD_HEADER = DimCustomerAddress;

SET TEMP_HEADER = DimCustomerAddress_TEMP;

//to_timestamp(LAST_UPDATE_DATE,'YYYY-MM-DD HH24:MI:SS')>=;

IF FileSize('$(QVDFILE)') > 0 THEN

$(QVD_HEADER):

SQL SELECT *

FROM CDPVault.BDV.DimCustomer

            where AddressMainLoadDate >= '$(vLastReloadTime)' ;

store  $(QVD_HEADER) INTO $(DailyQVD);

$(TEMP_HEADER):

LOAD DISTINCT *,

          $(PKexp) AS %PK

RESIDENT $(QVD_HEADER);

//STORE $(TEMP_HEADER) INTO $(TEMP_HEADER); THIS CODE ADDED WHEN NEED TO TEST THE

//DELTA RECORDS ONLY MEANS EXACTLY HOW MANY RECORDS ARE GETTING POPULARED

CONCATENATE('$(TEMP_HEADER)')

LOAD *

FROM $(QVDFILE) (Qvd) WHERE NOT EXISTS(%PK,CustomerId); ///////THIS PORTION NEED TO BE EDITED EVERY TIME

DROP FIELD %PK;

STORE $(TEMP_HEADER) INTO $(QVDFILE);

DROP TABLE $(TEMP_HEADER);

ELSE

$(QVD_HEADER):

     SQL SELECT *

FROM CDPVault.BDV.DimCustomer;

STORE $(QVD_HEADER) INTO $(QVDFILE);

     exit script;

Could you please suggest on this.

Thanks

[tamilarasu]

Hi Kishor,

Try loading below script and check whether the field is available or not. If it is available check the field name is matching properly (case sensitive).

TEST:

SQL SELECT *

FROM CDPVault.BDV.DimCustomer;

[petter]

Have you checked the MySQL database to see if the column really is there? AddressMainLoadDate

[kishorj1982]

Yes,

Column is already there and I have already created sample QVD for this.

Thanks

[kishorj1982]

My Original AddressMainLoadDate format is  2018-06-08 09:04:52 +00:00

May I know how should I format other dates in same format.

Thanks

[annapochyla]

Hi,

Try using date format without the last part with seconds fraction.

set vLastReloadTime='2018-11-05 08:06:52';

Anna

[kishorj1982]

Hi,

Thanks for the response,

May I know how should I show the column in 2018-06-08 09:04:52 +00:00 this format.

What changes do I need to do in Date(column,'YYYY-MM-DD hh:mm:dd') in this expression.

Because I am not getting how to move ahead.

Thanks