2

I have following query to handle null value and flag those based on two table join
however c.pure_proc_flag is null then 0 else 1 does not provide the result I expect.

query as following

select 
b.procedure_Type as procedure_type,b.procedure_Episode_ID as episode_id,b.claim_claim_number as claim_number,1.0 as card_procedure_ind,
c.pure_proc_flag as pure_proc_flag_orig,
ifnull(c.pure_proc_flag,0) as pure_proc_flag2,
case when c.pure_proc_flag is null then 0 else 1 end as pure_proc_flag3
from
(select procedure_Type,procedure_Episode_ID,claim_Claim_Number FROM HEALTHPLAN_SANDBOX.JX_INS_CLAIMS_PROCEDURE
where procedure_Type='Cardiology - CABG' and procedure_Episode_ID='1029877775') b
left join
(select procedure_Type,procedure_Episode_ID,claim_Claim_Number,1 as pure_proc_flag FROM HEALTHPLAN_SANDBOX.JX_INS_CLAIMS_PROCEDURE_SOURCE
where procedure_Type='Cardiology - CABG' and procedure_Episode_ID='1029877775') c
on b.procedure_Type=c.procedure_Type and b.procedure_Episode_ID=c.procedure_Episode_ID and b.claim_Claim_Number=c.claim_Claim_Number

the query is pretty straightforward.
I use b table left join c table
whereever the raw in b table not find the match in c table, It return null value, rest should be 1 show as pure_proc_flag_orig column
based on the condition of pure_proc_flag in c table, I want to flag those into 0 or 1

For understanding. pure_proc_flag2 and pure_proc_flag3 logic are the same.

however the result is different. enter image description here

as you can see ifnull(c.pure_proc_flag,0) as pure_proc_flag2 work as expect, however, case when c.pure_proc_flag is null then 0 else 1 end as pure_proc_flag3 does not work. (it indicated no blank row in c table. however, as we can see in pure_proc_flag_orig column, c table have blank row due to left join.

do I miss any important concept in mysql?

system version: aurora_version 2.02.5
innodb_version 5.7.12

Improve this question
5
  • Images should not be used for textual data, such as results. Since SQL includes data definition, a minimal reproducible example for an SQL question should include DDL statements for sample tables and DML statements for sample data. Desired results don't need to be presented as sample code, as results are the output of code and not code themselves. Commented Apr 15, 2022 at 18:39
  • Could you provide some sample data and expect result? Commented Apr 18, 2022 at 14:17
  • Please provide EXPLAIN SELECT ... Commented Apr 18, 2022 at 21:21
  • Can you reproduce your issue with sample data in a fiddle: dbfiddle.uk/?rdbms=mysql_5.7? Commented Apr 19, 2022 at 8:28
  • When you have already made 1 as pure_proc_flag in c . This means that you will never get 0 or null in any case. Then why you have this statement case when c.pure_proc_flag is null then 0 else 1 end as pure_proc_flag3 , This will always be` 1`. Commented Apr 25, 2022 at 6:24

2 Answers 2

Reset to default
3
+25

Your query could actually be simplified without doubling the select queries. Both "b" and "c" queries were filtering on the same criteria of procedure type and episode. The only difference was the left-join to the "c" additionally included the CLAIM number which is where you would possibly find your null matches, such as when a given procedure has multiple claims against it.

Procedure_type  Procedure_Episode_id  Claim_Claim_Number
A               1                     X
A               1                     Y

The first entry same episode wont find the match against the second claim (and vice-versa) so those would show as null/0

Revised query without explicit select subqueries.

select
        cp.procedure_Type,
        cp.procedure_Episode_ID episode_id,
        cp.claim_claim_number claim_number,
        1.0 as card_procedure_ind,
        cp2.pure_proc_flag pure_proc_flag_orig,
        COALESCE( cp2.pure_proc_flag, 0 ) as pure_proc_flag2UsingCoalesce,
        ifnull( cp2.pure_proc_flag, 0 ) as pure_proc_flag2,
        case when cp2.pure_proc_flag is null 
            then 0 else 1 end as pure_proc_flag3
    from
        HEALTHPLAN_SANDBOX.JX_INS_CLAIMS_PROCEDURE cp
            LEFT JOIN HEALTHPLAN_SANDBOX.JX_INS_CLAIMS_PROCEDURE_SOURCE cp2
                on cp.procedure_Type = cp2.procedure_Type 
                and cp.procedure_Episode_ID = cp2.procedure_Episode_ID 
                and cp.claim_Claim_Number = cp2.claim_Claim_Number
    where
            cp.procedure_Type = 'Cardiology - CABG' 
        and cp.procedure_Episode_ID = '1029877775'

As to the issue, I think it might be a bug in the IFNULL() function. I changed (actually added) another copy but used COALESCE() instead as you can see in my example, and it properly shows the 0 or 1 respectively where the IFNULL() does not.

Now, to POSSIBLY explain the reasoning behind the failure is only speculation. From your original secondary query "c" alias for the 1 as Pure_Proc_Flag, that query is being processed in full before the JOIN is done. By having a forced value all the time, does the MySQL actually force this column as a non-null and thus is always (by its returned structure), will never be a null value? And that is why it is returning 1 all the time.

You may instead want to check for NULL of any of the columns you are attempting to JOIN on that SHOULD exist, but may not. Such as I added in my sample.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Hi, DRapp, you are right. when I add where c.pure_proc_flag =1 into the where query, it return all rows. so mysql do not think pure_proc_flag have any row is blank even through c.pure_proc_flag in select part have null (by the way, if null/coalesce both works but case when does not not). I guess it is a bug for mysql case when on my version.
@MichaelLi, glad the solution can get you moving forward now.
@MichaelLi, then you should be able to up/check / solution resolved so others know that it appears to be a bug in the MySQL version.
since this is still just speculation. I am still testing on different environment and see whether it is a bug or I still not really understand sql
0

Kidnly answer the below 3 questions for a better understanding:

  1. Try to use ifnull for pure_proc_flag3, and check if it will give the same result when using case function or not.

  2. Try to use length(pure_proc_flag3), to check what is the number of characters when the value is NULL.

  3. Try to run this replace(pure_proc_flag3, null, 'Ops'), and check if it will print to you Ops or not.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.