Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: SQL Script failed with Parameters

  1. #1

    Default SQL Script failed with Parameters

    Hi,

    i need to delete my table, before i can insert data.

    So i write follow sql :
    delete from table
    where to_date(substr(fixed,4,2)||substr(fixed,7,2),'mmrr') >= to_date(?,'mmrr') and
    to to_date(substr(fixed,4,2)||substr(fixed,7,2),'mmrr') <= to_date(?,'mmrr');

    Variable Subistitution is on

    Parameters:
    var1
    var2


    I get an Error like this :
    An error occurred, processing will be stopped:
    Couldn't execute SQL:
    ORA-01008: not all variables bound


    What is wrong ??

  2. #2
    Join Date
    May 2006
    Posts
    4,882

    Default

    Wrong usage... the ?'s are bound by the fields in the row... you have less fields in the row than you have ?'s

    Variable substitution is done using ${...}, but they are used for variables, not for row input.

    Regards,
    Sven

  3. #3

    Default

    Ok i try to understand...

    Can you give me a simple example to solve my problem ?

  4. #4

    Default

    In short: Variable Substitution should not be on in your case because you are not using variables. (Variables in PDI are not the fields from your stream but are independent from it).

    Also your statement seems wrong in one place:
    delete from table
    where to_date(substr(fixed,4,2)||substr(fixed,7,2),'mmrr ') >= to_date(?,'mmrr') and
    to to_date(substr(fixed,4,2)||substr(fixed,7,2),'mmrr ') <= to_date(?,'mmrr');


    Cheers,

    Axel

  5. #5

    Default

    Ok i give you the background about my problem.

    With Excel Input Step i read a Excel File. In this Filename i get 2 information that i must use in a later step (SQL Script). I read this information with an Java Script Step :

    Filename =
    getShortFilename(Filepath.getString());
    start = substr(Filename,9,4);
    final =
    substr(Filename,14,4);

    This variables start and final i will use in the SQL Script Step:

    delete from table
    where to_date(substr(fixed,4,2)||substr(fixed,7,2),'mmrr ') >= to_date('${start}','mmrr') and to_date(substr(fixed,4,2)||substr(fixed,7,2),'mmrr ') <= to_date('${final}','mmrr');

    The SQL Syntax is correct. It's although works but nothing happend in the database. The rows are not deleted.

    How can i use parameters in the sql syntax ?


  6. #6
    Join Date
    May 2006
    Posts
    4,882

    Default

    also discussed before... you can not use variables in the same transformation as where you set them (in the general case).

    Regards,
    Sven

  7. #7

    Default

    That's right. So you have two options:

    1) write the information into columns of your stream

    2) create a Job which first gets that information, puts it into variables and then starts your transformation.

    All the best,

    Axel

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.