PDA

View Full Version : What's Wrong with my MSSQL Query?



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 = &#39;PS&#39;
AND A.COST IN (&#39;1300000000&#39;, &#39;1210000000&#39;, &#39;1510000000&#39;, &#39;1810000000&#39;, &#39;3020000000&#39;,
&#39;1220000000&#39;, &#39;1530000000&#39;, &#39;1820000000&#39;, &#39;1840000000&#39;, &#39;1310000000&#39;,
&#39;1520000000&#39;, &#39;1540000000&#39;, &#39;1830000000&#39;, &#39;3021000000&#39;)
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.

kettle_anonymous
03-26-2006, 05:41 PM
The error says "Unable to get queryfields for SQL"

MattCasters
03-26-2006, 10:14 PM
I guess the declare is too much for the JDBC driver.

Please re-write this with the @d1 and @d2 fields as ?
Then send about around 33 time d1/d2 Date values to the step.
There is an example in the manual, page 37

HTH,

Matt