Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Below said script does not load any records. I have Oracle as the sourcing database.
LET vFromDate = DayStart(Today());
BATCHLOG:
LOAD PK, Date1, Field2, Summation;
SQL
SELECT PK, Date1, Field2, SUM(Field2) AS Summation
FROM MY_TABLE
WHERE Date1 >= $(vFromDate)
GROUP BY PK, Date1, Field2;
Below is my setting:
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='DD-MMM-YYYY';
// SET TimestampFormat='DD-MMM-YYYY h:mm:ss[.fff] TT';
SET TimestampFormat='DD-MMM-YYYY h:mm:ss';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
Hi Usmansheriff,
check your database date format. Is it matching with your variable format.
Variable could have the values in 11/5/2012 12:00:00 AM format.
May be you need the truncate function for format compatibility.
hope that will help you
Regards,
Sushil
Hi,
your SQL is wrong. Try...
LET vFromDate = DayStart(Today());
BATCHLOG:
LOAD PK, Date1, Field2, Summation;
SQL
SELECT PK, Date1, Field2, SUM(Field2) AS Summation
FROM MY_TABLE
WHERE Date1 >= to_date('$(vFromDate)', 'DD-MMM-YYYY')
GROUP BY PK, Date1, Field2;
Best regards
I also tried below conditions. Its not working
Method 1 : Date1 >= To_Date('$(vFromDate)','DD-MMM-YYYY')
Method 2 : Date1 >= To_Date('$(vFromDate)','DD-MMM-YYYY h:mm:ss')
My source (Oracle) databse outputs SYSDATE as 05-Nov-2012 07:10:17
By the way, Date type of Date1 is "Date"
Hello,
it is a format issue. My fault. Oracle use MON as format code for e.g. 'Nov'
Date1 >= To_Date('$(vFromDate)','DD-MON-YYYY')
It is difficultly for me to reconstruct your problem, because I have other language codes.
On SQLPLUS you can use
SQL> select to_char(sysdate, 'DD-MON-YYYY') x from dual;
to try out the format codes