kettle_anonymous

03-26-2006, 05:23 PM

DECLARE @D1 as DATETIME, @D2 as DATETIME

SET @D1 = '01/01/2006'

SET @D2 = '01/31/2006'

SELECT @D1 AS DATEFR, @D2 AS DATETO,

A.CCYAMT,

ADB = ( ABS(A.CCYAMT) *

CASE WHEN (A.VDATE <= @D1 AND A.MDATE > @D2) THEN (ABS(DATEDIFF(dd, @D2, @D1)))+1

WHEN (A.MDATE = @D2 AND A.VDATE < @D1) THEN ABS(DATEDIFF(dd, A.MDATE, @D1))

WHEN (A.VDATE = @D1 AND A.MDATE > @D2) THEN (ABS(DATEDIFF(dd, @D2, @D1)))+1

WHEN (A.VDATE = @D1 AND A.MDATE = @D2) THEN (ABS(DATEDIFF(dd, @D2, @D1)))

WHEN (A.VDATE >= @D1 AND A.MDATE <= @D2) THEN ABS(DATEDIFF(dd,A.MDATE,A.VDATE))

WHEN (A.MDATE > @D2 AND (A.VDATE > @D1 AND A.VDATE <= @D2)) THEN ABS(DATEDIFF(dd, @D2,A.VDATE)) + 1

WHEN (A.VDATE < @D1 AND (A.MDATE < @D2 AND A.MDATE > @D1)) THEN ABS(DATEDIFF(dd, A.MDATE,@D1))

ELSE 0 END ) / (ABS(DATEDIFF(dd, @D1, @D2))+1),

B.CNO, B.SN

FROM OPXDB522.dbo.DLDT A, OPXDB522.dbo.CUST B

WHERE A.MDATE > @D1

AND A.VERDATE <= @D2

AND A.VDATE <= @D2

AND (A.REVDATE > @D2 OR A.REVDATE IS NULL)

AND A.PRODTYPE = 'PS'

AND A.COST IN ('1300000000', '1210000000', '1510000000', '1810000000', '3020000000',

'1220000000', '1530000000', '1820000000', '1840000000', '1310000000',

'1520000000', '1540000000', '1830000000', '3021000000')

AND A.CNO = B.CNO

------------------------------------------

Hi, I have this mssql query. This query works fine with Kettle (data can be previewed) , however, when I perform the stream lookup, kettle says that it cannot retrieve the fields.

How do I go about this problem? I already had a successful lookup operation with other tables. I think the problem would be the declaration of the variables at the first 3 lines.

SET @D1 = '01/01/2006'

SET @D2 = '01/31/2006'

SELECT @D1 AS DATEFR, @D2 AS DATETO,

A.CCYAMT,

ADB = ( ABS(A.CCYAMT) *

CASE WHEN (A.VDATE <= @D1 AND A.MDATE > @D2) THEN (ABS(DATEDIFF(dd, @D2, @D1)))+1

WHEN (A.MDATE = @D2 AND A.VDATE < @D1) THEN ABS(DATEDIFF(dd, A.MDATE, @D1))

WHEN (A.VDATE = @D1 AND A.MDATE > @D2) THEN (ABS(DATEDIFF(dd, @D2, @D1)))+1

WHEN (A.VDATE = @D1 AND A.MDATE = @D2) THEN (ABS(DATEDIFF(dd, @D2, @D1)))

WHEN (A.VDATE >= @D1 AND A.MDATE <= @D2) THEN ABS(DATEDIFF(dd,A.MDATE,A.VDATE))

WHEN (A.MDATE > @D2 AND (A.VDATE > @D1 AND A.VDATE <= @D2)) THEN ABS(DATEDIFF(dd, @D2,A.VDATE)) + 1

WHEN (A.VDATE < @D1 AND (A.MDATE < @D2 AND A.MDATE > @D1)) THEN ABS(DATEDIFF(dd, A.MDATE,@D1))

ELSE 0 END ) / (ABS(DATEDIFF(dd, @D1, @D2))+1),

B.CNO, B.SN

FROM OPXDB522.dbo.DLDT A, OPXDB522.dbo.CUST B

WHERE A.MDATE > @D1

AND A.VERDATE <= @D2

AND A.VDATE <= @D2

AND (A.REVDATE > @D2 OR A.REVDATE IS NULL)

AND A.PRODTYPE = 'PS'

AND A.COST IN ('1300000000', '1210000000', '1510000000', '1810000000', '3020000000',

'1220000000', '1530000000', '1820000000', '1840000000', '1310000000',

'1520000000', '1540000000', '1830000000', '3021000000')

AND A.CNO = B.CNO

------------------------------------------

Hi, I have this mssql query. This query works fine with Kettle (data can be previewed) , however, when I perform the stream lookup, kettle says that it cannot retrieve the fields.

How do I go about this problem? I already had a successful lookup operation with other tables. I think the problem would be the declaration of the variables at the first 3 lines.