Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: What's Wrong with my MSSQL Query?

  1. #1
    Join Date
    Sep 2005
    Posts
    1,403

    Default What's Wrong with my MSSQL Query?

    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.

  2. #2
    Join Date
    Sep 2005
    Posts
    1,403

    Default RE: What's Wrong with my MSSQL Query?

    The error says "Unable to get queryfields for SQL"

  3. #3
    Join Date
    Nov 1999
    Posts
    9,729

    Default RE: What's Wrong with my MSSQL Query?

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.