Bentley Communities
Bentley Communities
  • Site
  • User
  • Site
  • Search
  • User
ProjectWise
  • Product Communities
ProjectWise
ProjectWise Design Integration Forum Oracle query with IS NULL and CASE WHEN
    • Sign In

    • State Verified Answer
    • Replies 6 replies
    • Subscribers 61 subscribers
    • Views 1809 views
    • Users 0 members are here
    • CONNECT Edition
    • ProjectWise
    • Project Wise
    • Configuration

    Oracle query with IS NULL and CASE WHEN

    Philip Las Gourgues
    Offline Philip Las Gourgues over 4 years ago

    Hi all,

    We're running ProjectWise 10.00.03.140 on an Oracle database and I'm having some issues with Oracle functions not working as expected.

    I'm attempting to write a query to parse a series of attributes and select the last non-null one to populate current drawing approvers and current revisions.

    When the main query failed I wrote the simplified test case below which also fails. I did manage to get it to work in another environment by replacing IS NULL with = ' ' but this seems to be dependent on the attribute being referenced as it doesn't work for the B6_DESIGNED case below.

    select case when '$EDIT#B6_DESIGNED$' is null then 'BLANK' else 'NOT BLANK' end from DUAL

    I've also had an issue with running into the 1024 character limit for queries. Is there any way around this without splitting part of the query into an interim attribute? I've considered stored procedures but I'm concerned this may not be allowed when we migrate to a Bentley cloud hosted solution in the future.

    The full query I'm attempting to run is shown below (I've had to truncate the last couple of revisions to get it under the 1024 character limit). A6_DESIGNED to F6_DESIGNED are VARCHAR attributes, SIGNERS is a lookup environment and INITIAL_SURNAME and INITIALS are both VARCHAR attributes in the SIGNERS environment.

    select case when '$EDIT#F6_DESIGNED$' = ' ' then (
         select case when '$EDIT#E6_DESIGNED$' = ' ' then (
              select case when '$EDIT#D6_DESIGNED$' = ' ' then (
                   select case when '$EDIT#C6_DESIGNED$' = ' ' then (
                        select case when '$EDIT#B6_DESIGNED$' = ' ' then (select INITIAL_SURNAME from SIGNERS where INITIALS like '$EDIT#A6_DESIGNED$')
                        else (select INITIAL_SURNAME from SIGNERS where INITIALS like '$EDIT#B6_DESIGNED$') end from DUAL)
                   else (select INITIAL_SURNAME from SIGNERS where INITIALS like '$EDIT#C6_DESIGNED$') end from DUAL)
              else (select INITIAL_SURNAME from SIGNERS where INITIALS like '$EDIT#D6_DESIGNED$') end from DUAL)
         else (select INITIAL_SURNAME from SIGNERS where INITIALS like '$EDIT#E6_DESIGNED$') end from DUAL)
    else (select INITIAL_SURNAME from SIGNERS where INITIALS like '$EDIT#F6_DESIGNED$') end from DUAL

    The below query works as required in a different environment. Obviously it's not referencing another environment via lookup so that may be the issue.

    select case when '$EDIT#REV10$' = ' ' then (
         select case when '$EDIT#REV9$' = ' ' then (
              select case when '$EDIT#REV8$' = ' ' then (
                   select case when '$EDIT#REV7$' = ' ' then (
                        select case when '$EDIT#REV6$' = ' ' then (
                             select case when '$EDIT#REV5$' = ' ' then (
                                  select case when '$EDIT#REV4$' = ' ' then (
                                       select case when '$EDIT#REV3$' = ' ' then (
                                            select case when '$EDIT#REV2$' = ' ' then (select '$EDIT#REV1$' from DUAL)
                                            else '$EDIT#REV2$' end from DUAL)
                                       else '$EDIT#REV3$' end from DUAL)
                                  else '$EDIT#REV4$' end from DUAL)
                             else '$EDIT#REV5$' end from DUAL)
                        else '$EDIT#REV6$' end from DUAL)
                   else '$EDIT#REV7$' end from DUAL)
              else '$EDIT#REV8$' end from DUAL)
         else '$EDIT#REV9$' end from DUAL)
    else '$EDIT#REV10$' end from DUAL

    Thanks very much.

    Regards,

    Philip Las Gourgues

    • Sign in to reply
    • Cancel

    Top Replies

    • Kevin van Haaren
      Offline Kevin van Haaren Wed, Aug 28 2019 10:15 AM +1 verified
      I'm not an Oracle person and don't have access to an Oracle database to test this out, but if you're doing what i think you are, and i understood the query stuff I looked up online correctly you may be…

    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