How to avoid hard coding

[Anonymous]

Hi

what is the best way to get the future data into the SQL query without hard coding the date in the SQL query while loading the SQL query in data loader?

below is my SQL query in which i have hard coded my date directly in order to get the future data .Plzz suggest?

SELECT AL1.ACCT, AL1.REG_CAT_COARSE, AL1.FIRST_FUNDED_DT, SUM ( AL2.TXN_CNT ),

(case when (SUM ( AL2.TXN_CNT )) > 0  then 'Y' else 'N' end),AL2.PROCESS_DATE,

AL1.POE_CAT, (case when AL1.BUS_LINE = 'PAS' then 'PAS' when AL1.BUS_LINE = 'SMAF' then 'PAS'

when AL1.BUS_LINE = 'UMA' then 'PAS' when AL1.BUS_LINE = 'UMAT' then 'PAS' when AL1.BUS_LINE = 'BDI' then

'PAS' when AL1.BUS_LINE = 'SMEI' then 'PAS' when AL1.BUS_LINE = 'SMLC' then 'PAS' when AL1.BUS_LINE = 'DMA'

then 'PAS' when AL1.BUS_LINE = 'PWM' then 'PAS' when AL1.BUS_LINE = 'PWMT' then 'PAS' else 'Non PAS' end),

AL1.HH, AL1.BUS_LINE, AL1.CIT_STATE, AL1.CITY, AL1.ORIGIN, AL1.STATE, AL1.ZIP, AL1.RETIRE_INDIC, AL1.ACCT_TYPE,

AL1.ACCT_STATUS FROM PLVDBO.ACCOUNT AL1, PLVDBO.A_FIN_TXN_DTL AL2 WHERE ( AL1.ACCT = AL2.ACCT (+))  AND

((AL1.FIRST_FUNDED_DT BETWEEN TIMESTAMP '2015-01-01 00:00:00.000' AND TIMESTAMP '2050-12-31 00:00:00.000' AND

AL2.PROCESS_DATE(+) BETWEEN TIMESTAMP '2015-01-01 00:00:00.000' AND TIMESTAMP '2050-12-31 00:00:00.000' AND

AL2.COMM_TRADE_INDIC(+)='Y')) GROUP BY AL1.ACCT, AL1.REG_CAT_COARSE, AL1.FIRST_FUNDED_DT, AL1.POE_CAT,

(case when AL1.BUS_LINE = 'PAS' then 'PAS' when AL1.BUS_LINE = 'SMAF' then 'PAS' when AL1.BUS_LINE = 'UMA'

then 'PAS' when AL1.BUS_LINE = 'UMAT' then 'PAS' when AL1.BUS_LINE = 'BDI' then 'PAS' when AL1.BUS_LINE = 'SMEI'

then 'PAS' when AL1.BUS_LINE = 'SMLC' then 'PAS' when AL1.BUS_LINE = 'DMA' then 'PAS' when AL1.BUS_LINE = 'PWM'

then 'PAS' when AL1.BUS_LINE = 'PWMT' then 'PAS' else 'Non PAS' end), AL1.HH,

AL1.BUS_LINE, AL1.CIT_STATE, AL1.CITY, AL1.ORIGIN, AL1.STATE, AL1.ZIP, AL1.RETIRE_INDIC,

AL1.ACCT_TYPE, AL1.ACCT_STATUS,AL2.PROCESS_DATE;

[Aurelien_Martinez]

Hi,

You can create two variables like :

LET vDateStart = YearStart(Today()); //you can use addyears, addmonths...

LET vDateEnd  = YearEnd(Today()); //you can use addyears, addmonths...

And in your SQL

sql Select [...]

AL1.FIRST_FUNDED_DT BETWEEN TIMESTAMP '$(vDateStart)' AND TIMESTAMP '$(vDateEnd)'

;

Aurélien

[Anonymous]

I am getting this below error

[Aurelien_Martinez]

You have an issue with the date format,

You can try :

LET vDateStart = Date(YearStart(Today()), 'YYYY-MM-DD');

LET vDateEnd  = Date(YearEnd(Today(), 'YYYY-MM-DD');

or

to_date in your sql

Aurélien

[calexandru · Accepted solution]

Hi Surya,

This is most likely due to the fact that you need to provide a timestamp and the YearStart and YearEnd functions that Aurelien suggested as solutions return a date string.

You might want to wrap the functions as such:

LET vDateStart = Timestamp(YearStart(Today()), 'YYYY-MM-DD hh:mm:ss.fff')

LET vDateEnd  = Timestamp(YearEnd(Today()), 'YYYY-MM-DD hh:mm:ss.fff')

Cheers,

Alex

[Anonymous]

Hi Alex

Its working now..../can you explain me the working functionality of the above function

[calexandru]

Glad it works,

Sure thing, timestamp() deals with formatting an expression in either the system timestamp format or a user defined format.

It takes two arguments with the second being optional.

first parameter must be a valid interpretation of either a date or timestamp and the second is the format you want to format the timestamp in.

In your case you have the following:

Today() which returns the date for the current day

YearStart() which returns the first date of the year for the parameter you provided, in this case

and lastly timestamp() converts the date into a timestamp

The code looks like this:

Today() -> 17/11/2017

YearStart(17/11/2017) -> 01/01/2017

Timestamp(01/01/2017, 'YYYY-MM-DD hh:mm:ss.fff') -> 2017-01-01 00:00:00.000

Regards,

Alex

[Anonymous]

Thanks a lot Alex...Your explanation is crystal clear

[calexandru]

My pleasure Surya