Bentley Communities
Bentley Communities
  • Site
  • User
  • Site
  • Search
  • User
ProjectWise
  • Product Communities
ProjectWise
ProjectWise Design Integration Forum Lookup Tables: A slightly more complex SELECT statement?
    • Sign In

    • Replies 5 replies
    • Subscribers 62 subscribers
    • Views 3391 views
    • Users 0 members are here
    • lookup tables
    • ProjectWise

    Lookup Tables: A slightly more complex SELECT statement?

    Paul Patterson
    Paul Patterson over 8 years ago

    Morning Everyone,

    We introduced a lookup table some time ago to allow for drop down menu's in our interfaces, configured at the project level. So far, this has worked as expected. If you haven't looked into this, I highly suggest it.

    The basic select statement we have is the usual..

    SELECT value, value_description FROM LOOKUP_TABLE where ATTRIBUTE='AREA' AND PROJ_NO=$PROJECT#PROJECT_Project_Number$ ORDER BY SORT_ORDER

    So far so good.. the query above finds the records that are related to the project number the user is working in. check.

    What I'd like to do is have a query that will query the records as above, but if the results are null, query a second set of records where PROJ_NO = 000000. This would allow us to have a default set of values, where not overridden by values defined by the project.

    Any thoughts on how to achieve this?

    Paul Patterson

    • Sign in to reply
    • Cancel
    Parents
    • Paul Patterson
      Paul Patterson Thu, Jul 23 2015 11:54 AM

      For those interested, hopefully this will help someone else out there..

      I ended up with..

      SELECT value, value_description from lookup_table where attribute = 'DOC_ORIGINATING_COMPANY' and proj_no = decode((select count(*) from lookup_table where attribute = 'DOC_ORIGINATING_COMPANY' and proj_no =  $PROJECT#PROJECT_Project_Number$),0,'123456', $PROJECT#PROJECT_Project_Number$);

      (Apologies for the lack of formatting)

      The intent is to have a generic project, with a project property of project #123456, that contains records with our standard lookup values for our picklists.. the query above will determine if there are any records inside the active project, and if so, will display those values as oppose to our default values..

      Paul Patterson

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Cancel
    • Kevin van Haaren
      Offline Kevin van Haaren Tue, Jul 28 2015 5:37 PM in reply to Paul Patterson
      Are you using Oracle? From my reading on the web it looks like the decode() function you use is Oracle only. For anyone on SQL Server I think you can produce a similar result on SQL Server using CASE...WHEN statements, but i don't have time to dig into exactly how to write this issue.

       

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Cancel
    • Paul Patterson
      Paul Patterson Wed, Jul 29 2015 9:06 AM in reply to Kevin van Haaren
      Kevin,

      Good point! I should have mentioned that yes, indeed we are using Oracle!

      We'll be transitioning over to SQL in the mid-term.. I'll update this post with the new query when the time comes.

      Paul
      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Cancel
    Reply
    • Paul Patterson
      Paul Patterson Wed, Jul 29 2015 9:06 AM in reply to Kevin van Haaren
      Kevin,

      Good point! I should have mentioned that yes, indeed we are using Oracle!

      We'll be transitioning over to SQL in the mid-term.. I'll update this post with the new query when the time comes.

      Paul
      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • 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