IN DOCUMENT clause and a set function appear in the same SELECT."

HI all,
I want to sum the r_link_cnt from dm_document(all) in document_id ('r_object_id) descend.

select sum (r_link_cnt) from dm_document(all) in Document id('r_object_id') descend where r_link_cnt>=1;

getting error like Error occured during query execution :[DM_QUERY_E_COMPOUND_FUNC]error: "IN DOCUMENT clause and a set function appear in the same SELECT."

Is there any better way to get the sum of r_link_cnt.

Comments

  • If you remove the sum(), does your query work? If it still doesn't work, what are you trying to do in English? You are using IN DOCUMENT function that has to do with virtual documents - not sure if you are aware of this or not.

  • Hello ,

    Yes If i remove sum(), query is working fine. I am trying to count all the virtual documents with all descends including direct and indirect components.

  • If you see query, I am getting below result, my requirement is to get the sum of r_link_cnt as an end result. I.e 59 is the total sum.

    select r_object_id,r_link_cnt from dm_document(all) in Document id(‘09001faa800b70e3’) descend where r_link_cnt>=1

    r_object_id r_link_cnt

    09001faa800be0e4 4

    09001faa800b70e3 9

    09001faa800b70e4 17

    09001faa800b70e6 12

    09001faa800b70e8 4

    09001faa800b70ea 3

    09001faa800b70ec 2

    09001faa800b70ee 3

    09001faa800b70f0 2

    09001faa800b70f2 3

  • I'm not sure how summing up all r_link_cnt will be of much use. Remember that r_link_cnt represents the number of VD descendents + 1 (e.g. if you have 2 descendents it will be 3) and it will be 0 for leaf elements. With the where clause r_link_count >=1 you excluded all leaf elements.

    Did you try it with a subselect: e.g. use your query as the inner query (dropping where clause) and the do an outer select where you filter the rows with r_link_cnt = 0 if that's what you want.

  • I tried like below
    Dql--Enter Query:

    select sum (r_link_cnt) from dm_document(all) where r_object_id in
    (select r_object_id from dm_document(all) in Document id('')descend
    );
    Execute Show the SQL
    Error occured during query execution :[DM_QUERY_E_COMPOUND_SUB]error: "IN DOCUMENT clause appears within a subquery."

    but still getting the same error.? any help will be appreciated

  • You might just need to export the results into Excel and then using SUM()