• State Verified Answer
  • Replies 6 replies
  • Subscribers 61 subscribers
  • Views 1911 views
  • Users 0 members are here

Oracle query with IS NULL and CASE WHEN

Philip Las Gourgues
Offline

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

  • +1 Offline

    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 

Communities
Support and Services
Training and Learning
Social Media