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…
    Parents
    • Kevin van Haaren
      +1 Offline Kevin van Haaren Wed, Aug 28 2019 10:15 AM

      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 better off doing as below, it's considerable shorter, ~400 characters, so you should be able to add in your other fields too.

      SELECT INITIAL_SURNAME
      FROM SIGNERS
      WHERE INITIALS LIKE CASE
      		WHEN '$EDIT#F6_DESIGNED$' <> '' THEN '$EDIT#F6_DESIGNED$'
      		WHEN '$EDIT#E6_DESIGNED$' <> '' THEN '$EDIT#E6_DESIGNED$'
      		WHEN '$EDIT#D6_DESIGNED$' <> '' THEN '$EDIT#D6_DESIGNED$'
      		WHEN '$EDIT#C6_DESIGNED$' <> '' THEN '$EDIT#C6_DESIGNED$'
      		WHEN '$EDIT#B6_DESIGNED$' <> '' THEN '$EDIT#B6_DESIGNED$'
      		ELSE '$EDIT#A6_DESIGNED$'
      	END;

      On your is null test: ProjectWise takes these queries, does text substitution on the stuff between $ and then submits the query to the database. If a field like F6_DESIGNED is empty, when ProjectWise does a text substitution to '$EDIT#F6_DESIGNED$' the result is an empty string (two ' with nothing between them). In SQL* the empty string ('') is not the same as null. So '' is null will be false.

      * Oracle actually has some exceptions to this if you google empty string null oracle you can read the gory details. I don't think they apply here because those exceptions are when you query a column directly which doesn't happen here.

      I notice in your queries you're doing ' ', two single quotes with a space between them. Not sure if that's a typo but that would also fail as an empty string '' is not the same as a string with a space ' '

       

      Answer Verified By: Philip Las Gourgues 

      • Cancel
      • Vote Up +1 Vote Down
      • Sign in to reply
      • Reject Answer
      • Cancel
    • Philip Las Gourgues
      0 Offline Philip Las Gourgues Wed, Aug 28 2019 5:22 PM in reply to Kevin van Haaren

      Hi Kevin,

      Thanks very much for your reply and suggestions. Unfortunately that won't work as I'm trying to cascade down the list to find the first populated value. So if F6 is empty then check if E6 is empty else return F6, if E6 is empty then check if D6 is empty else return D6 etc... The attributes A6 to F6 are filled out in order and I want to find the last one. I looked at compiling them into a list or string then grabbing the last one but as the length varies and anything other than straight string concatenations seemed to not be supported I ended up with the approach above.

      With regards to the single quotes strangely enough in the other example which is working it only works if I have the single quotes with a space between. Same as you've suggested I would have thought no space would be required but when that failed in testing I put a space in out of frustration and surprisingly it worked. I have no idea why....

      Cheers,

      Phil

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    • Kevin van Haaren
      0 Offline Kevin van Haaren Wed, Aug 28 2019 5:32 PM in reply to Philip Las Gourgues

      Yes, that's the way Oracle's CASE statement works, it uses a short-circuit evaluation method where it stops evaluating as soon as it finds a match.  In my CASE statement if F6 is empty it will proceed to E6, otherwise return F6. If E6 is empty it will proceed to D6, if it is not empty it will return E6's value.

      For it to work properly for your scenario just list the fields descending like I did and it should work.

      More on Oracle's CASE statement:

      docs.oracle.com/.../plsql-control-statements.html

       

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    • Philip Las Gourgues
      0 Offline Philip Las Gourgues Wed, Aug 28 2019 5:34 PM in reply to Kevin van Haaren

      Thanks very much Kevin. I had misunderstood that part of the CASE statement. I'll test your example shortly.

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    • Philip Las Gourgues
      0 Offline Philip Las Gourgues Wed, Aug 28 2019 10:12 PM in reply to Kevin van Haaren

      Hi Kevin,

      So I've tested your suggestion and it's working brilliantly with one minor bug/exception. In ProjectWise you definitely have to have the space in between the single quotes for it to evaluate. As suggested it always defaults to the ELSE value, if I add the space it works perfectly.

      It was bugging me so I tested with IS NOT NULL instead and that works perfectly too. Seems to be a less incorrect solution so I've gone with that.

      Thanks very much again. Your query is a huge improvement on the work around I had running just after i wrote the question.

      Regards,

      Philip Las Gourgues

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    • Kevin van Haaren
      0 Offline Kevin van Haaren Thu, Aug 29 2019 5:07 PM in reply to Philip Las Gourgues

      That's great. I'm as confused as you about the space in the quotes. I actually wrote it with IS NOT NULL at first then decided to rewrite it. doh.

       

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    Reply
    • Kevin van Haaren
      0 Offline Kevin van Haaren Thu, Aug 29 2019 5:07 PM in reply to Philip Las Gourgues

      That's great. I'm as confused as you about the space in the quotes. I actually wrote it with IS NOT NULL at first then decided to rewrite it. doh.

       

      • 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