Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Evaluate number of rows in a table step

  1. #1
    Join Date
    Sep 2017
    Posts
    27

    Default Evaluate number of rows in a table step

    I am using this step - "Evaluate number of rows in a table" in PDI 8.0 to determine whether or not data exists. I have written this SQl Script below, hoping that would help determine if rows are empty.

    SELECT COUNT (*)
    From TABLE
    GROUP BY SSN
    HAVING count(*) = 0

    If I want that SELECT statement to be true then I could select "Equal to" next to Success when rows do not exist? I tried that method, assuming it would send to next step when there are no existing rows (hop set to true) but
    it doesn't seem to work that way? I also thought that if this is "false", meaning rows do exist, it would send to different step (hop set to false)
    I don't understand what these conditions mean - "Different from", "Smalle than", "Smaller than or equal to" ?
    Can you elaborate?

    Thanks,
    Claudia

  2. #2
    Join Date
    Sep 2017
    Posts
    27

    Default

    Is there a practical approach to checking if data is empty on table rather than using Evaluate number of rows in table step? How about Filter step? What do programmers use ?

    Thanks?

  3. #3
    Join Date
    Apr 2008
    Posts
    4,689

    Default

    Start with just using the step as built, once you understand it, then start going into the custom code.

    You want a result from your SQL statement that comes back as a single value. The step does this by default by doing a "SELECT COUNT(*) FROM <SCHEMA>.<TABLE>"
    Success if this value is "greater than" (quotes because it's selectable) 0.

    What exactly do you define "data exists" to mean?
    Also, ignore "what programmers use" ... PDI is mostly a business-user friendly system. Think like a business process user, and things will actually become easier.

  4. #4

    Default

    Ok Gutlez,

    To add to your post, since the step already functions as select count(*) from table. Now how to group by on particular column with having clause. Can you please help us with an example?
    Regards,
    Dileep
    Mail ID

  5. #5
    Join Date
    Aug 2016
    Posts
    289

    Default

    What exactly are you trying to find out? "to determine whether or not data exists"?

    That's a select count(*) from table_name; Why you want grouping just to check if rows exists? If you want to know if data exists or not, it doesn't matter how any possibly existing data is grouped.

  6. #6
    Join Date
    May 2016
    Posts
    279

    Default

    You have a confusing SQL query:
    Code:
    SELECT COUNT(*)
    FROM TABLE
    GROUP BY SSN
    is the same as
    Code:
    SELECT COUNT(*) FROM TABLE
    , in some databases (I'm thinking Oracle) that statement won't work and would throw an error, others would shallow it, but I don't think they would give you the results you are expecting. The part of HAVING COUNT(*) = 0 doesn't make any sense, either you have a value of SSN or the SELECT COUNT(*) will return NULL, which is distinct from 0.
    Either you want:
    Code:
    SELECT COUNT(DISTINCT SSN) my_result
    FROM TABLE
    Or you want:
    Code:
    SELECT COUNT(*) my_result
    FROM (SELECT SSN, COUNT(*) num_ssn FROM TABLE GROUP BY SSN HAVING COUNT(*) > 1)
    And you want to evaluate my_result on any of this cases.
    OS: Ubuntu 16.04 64 bits
    Java: Openjdk 1.8.0_131
    Pentaho 6.1 CE

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.