Confirm

Expand all | Collapse all

Data Source Query - How to generate report with Feature showing Features which don't have a linked document (i.e. no picture)

Jump to Best Answer
  • 1.  Data Source Query - How to generate report with Feature showing Features which don't have a linked document (i.e. no picture)

    Posted 08-20-2020 05:30

    Hi,

    I need help regarding a query on how to generate a report which will show all features but then exclude features which have a linked document or a feature_document. I basically am looking to generate a report which shows what features/assets we still need to take pictures of.

    So far I have: 

    SELECT DISTINCT
    feature_document.site_code,
    feature_document.plot_number,
    feature.feature_deadflag,
    feature_type.feature_type_code,
    feature_type.feature_type_name,
    feature_document.document_path
    FROM
    feature,
    feature_document,
    feature_type
    WHERE
    feature_type.feature_type_code = feature.feature_type_code AND
    feature.site_code = feature_document.site_code AND
    feature.plot_number = feature_document.plot_number and
    feature_document.document_path not in AND
    NOT EXISTS (SELECT feature_document.site_code, feature_document.plot_number, feature.feature_deadflag, feature_type.feature_type_code, feature_type.feature_type_name,
    feature_document.document_path FROM feature, feature_document, feature_type where feature_document.document_path NOT IN)

    However, the not exists doesn't work and I have no idea ATM using SQL on how to make it work.

    Any help would be appreciated. TY



    ------------------------------
    Louis Johnston
    Knowledge Community Shared Account
    ------------------------------


  • 2.  RE: Data Source Query - How to generate report with Feature showing Features which don't have a linked document (i.e. no picture)
    Best Answer

    Posted 08-20-2020 06:56
    Edited by Steve Bish 08-20-2020 07:47
    Hi Louis,

    Try the following example:

    SELECT feature.site_code, feature.plot_number, feature.feature_id, feature.feature_type_code FROM feature WHERE feature.feature_deadflag = 'N' AND NOT EXISTS ( SELECT * FROM feature_document WHERE feature_document.site_code = feature.site_code AND feature_document.plot_number = feature.plot_number);​

    This will show all Live Features that do not have any documents linked to them.

    I hope this helps.

    Steve


    ------------------------------
    Steve Bish
    Senior Software Support Analyst
    Confirm
    ------------------------------



  • 3.  RE: Data Source Query - How to generate report with Feature showing Features which don't have a linked document (i.e. no picture)

    Posted 08-20-2020 07:49

    This works perfectly and is extremely simple.

    Thank you for your help.



    ------------------------------
    Louis Johnston
    Knowledge Community Shared Account
    ------------------------------