Bentley Communities
Bentley Communities
  • Site
  • User
  • Site
  • Search
  • User
ProjectWise
  • Product Communities
ProjectWise
ProjectWise Design Integration Forum Select statement subqueries in case Expression Help
    • Sign In

    • State Verified Answer
    • +1 person also asked this people also asked this
    • Replies 2 replies
    • Subscribers 60 subscribers
    • Views 1558 views
    • Users 0 members are here

    Select statement subqueries in case Expression Help

    Juan Lopez
    Offline Juan Lopez over 5 years ago

    Hi  All,

    I try to implement the select value list using this SQL expression:

    Select case

    When exist (Select distinct <Field01>, len (<Field01>) FROM <lk_Table>  ORDER BY len (<Field01>) , <Field01> )

    Else

    (Select distinct <Field01>, len (<Field01>), <FilterID>  FROM <lk_Table>   where <Filter_ID>  = '$EDIT#Filter_ID$' ORDER BY len (<Field01>) , <Field01> )

    End

    The outcome is empty but each subquery works fine. 

    Any thoughts on how to achieve this?

    • Sign in to reply
    • Cancel

    Top Replies

    • Juan Lopez
      Offline Juan Lopez Wed, Sep 20 2017 5:33 PM +2 verified
      Hi, All The issue is solved. SELECT DISTINCT <Field01>, len (<Field01>) FROM <lk_Table> WHERE <Filter_ID> LIKE (CASE WHEN EXIST(Select distinct <Field01>, len (<Field01>), <FilterID> FROM <lk_Table…
    Parents
    • Juan Lopez
      +1 Offline Juan Lopez Wed, Sep 20 2017 5:33 PM

      Hi, All

      The issue is solved.

      SELECT DISTINCT <Field01>, len (<Field01>)

      FROM <lk_Table> 

      WHERE <Filter_ID>  LIKE (CASE WHEN EXIST(Select distinct <Field01>, len (<Field01>), <FilterID>  

         FROM <lk_Table>   WHERE <Filter_ID>  = '$EDIT#Filter_ID$' ) THEN  '$EDIT#Filter_ID$'  ELSE  '%' END)

      ORDER BY len (<Field01>) , <Field01> 

      Answer Verified By: Vickie Ramsey 

      • Cancel
      • Vote Up +2 Vote Down
      • Sign in to reply
      • Verify Answer
      • Reject Answer
      • Cancel
    • Fay Ellard
      0 Offline Fay Ellard Thu, Mar 29 2018 4:57 PM in reply to Juan Lopez

      Juan,

      I have a similar problem to yours and can't see a solution can you see how to adjust to make it run, outcome is empty but each subquery works fine?

      SELECT CASE WHEN '$EDIT#FI_RIPPROJECT$' = 'YES' THEN SELECT pw_code, pw_description FROM lkp_fi_attributes WHERE pw_filter = 'RIP-Sec-Sub' ELSE SELECT pw_code, pw_description FROM lkp_fi_attributes WHERE pw_filter = 'Section - ' + '$EDIT#FI_ASSET_CODE$' ORDER BY pw_sortorder END

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    Reply
    • Fay Ellard
      0 Offline Fay Ellard Thu, Mar 29 2018 4:57 PM in reply to Juan Lopez

      Juan,

      I have a similar problem to yours and can't see a solution can you see how to adjust to make it run, outcome is empty but each subquery works fine?

      SELECT CASE WHEN '$EDIT#FI_RIPPROJECT$' = 'YES' THEN SELECT pw_code, pw_description FROM lkp_fi_attributes WHERE pw_filter = 'RIP-Sec-Sub' ELSE SELECT pw_code, pw_description FROM lkp_fi_attributes WHERE pw_filter = 'Section - ' + '$EDIT#FI_ASSET_CODE$' ORDER BY pw_sortorder END

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    Children
    No Data

    Communities
    • Home
    • Getting Started
    • Community Central
    • Products
    • Support
    • Secure File Upload
    • Feedback
    Support and Services
    • Home
    • Product Support
    • Downloads
    • Subscription Services Portal
    Training and Learning
    • Home
    • About Bentley Institute
    • My Learning History
    • Reference Books
    Social Media
    •    LinkedIn
    •    Facebook
    •    Twitter
    •    YouTube
    •    RSS Feed
    •    Email

    © 2023 Bentley Systems, Incorporated  |  Contact Us  |  Privacy |  Terms of Use  |  Cookies