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
    • Scott M. Turner
      Scott M. Turner Wed, Jul 22 2015 5:24 PM
      Hi Paul,
      This can be done, but it will take a bit of planning and strategy to make it work long-term. In saying this I am assuming you will have a single lookup table that is used across multiple projects for a single datasource.
      Here's what I've done: My lookup table has a generic structure that allows for a high degree of flexibility to suit my needs. My table columns include things like: Client, DataType, DataValue, Description, InUse, ProjectID, RelatesTo, Vault and more. What this does is allow me to enter data into the table that is consistent to a particular scenario. It also allows me to have a full set of lookup values, but toggle them on or off as needed (InUse).
      One thing to keep in mind is to stay away from a NULL value. SQL will not evaluate it (although you can test for it with IS NULL), your queries will not return the result you expect.
      Another thing you can do is to use IF EXISTS to test for expected values. If a value is found the query evaluates to TRUE otherwise FALSE.
      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Cancel
    Reply
    • Scott M. Turner
      Scott M. Turner Wed, Jul 22 2015 5:24 PM
      Hi Paul,
      This can be done, but it will take a bit of planning and strategy to make it work long-term. In saying this I am assuming you will have a single lookup table that is used across multiple projects for a single datasource.
      Here's what I've done: My lookup table has a generic structure that allows for a high degree of flexibility to suit my needs. My table columns include things like: Client, DataType, DataValue, Description, InUse, ProjectID, RelatesTo, Vault and more. What this does is allow me to enter data into the table that is consistent to a particular scenario. It also allows me to have a full set of lookup values, but toggle them on or off as needed (InUse).
      One thing to keep in mind is to stay away from a NULL value. SQL will not evaluate it (although you can test for it with IS NULL), your queries will not return the result you expect.
      Another thing you can do is to use IF EXISTS to test for expected values. If a value is found the query evaluates to TRUE otherwise FALSE.
      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Cancel
    Children
    • Paul Patterson
      Paul Patterson Thu, Jul 23 2015 11:23 AM in reply to Scott M. Turner
      Scott - I'm interested to see what you're doing here. Maybe we could connect offline?

      Thanks for the reply, greatly appreciated.
      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Cancel

    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