Select exceptions
Last Post 08 Dec 2012 12:51 PM by Jim_IT. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Brad
Advanced Member
Advanced Member
Posts:462
Avatar

--
04 Dec 2012 02:08 PM

I have a file with claim information. In my selection I'm gathering the claim, line, an amount, some eligibility codes, benifit code and an ID. If the amount on each line is .01 I do not want that claim. If one line is .01 I do want to include that line if other lines are greater than .01. I don't know if that makes sense.

Here is some mock data. 

1234		1		15.23	FH	XX	RSK		125698745.001
1234		2		52.30	FH		HOS		125698745.001
4321		1		2.36	FH	15	PID		147852366
56789		1		5.99	FH		RSK		256987444.002
56789		2		.01	15		RK		256987444.002
45896		1		.01	FH		BCR		154896547.002
741258		1		89.25	FH	XX	BGF		124569774.001

In this mock data I would want every row except the 6th row where there is only one line and it is .01. My statement works in eliminating that sixth row but it also eliminates the 5th row which I would want included.

Here's my SQL, pretty simple.

       Exec Sql
         Declare Claimcursor Cursor For
          Select Cmlcl#,
                 Cmlln#,
                 Cmlamt,
                 Cmlir1,
                 Cmlir2,
                 Cmlbcd,
                 Cmlpid
           From Claims
           Where Cmlcl# = :Peclaim
            And Cmlamt > .01
           Order By Cmlcl#, Cmlln#;
       Exec Sql
         Open ClaimCursor;

Craig R. Lockhart
Veteran Member
Veteran Member
Posts:1167
Avatar

--
04 Dec 2012 04:20 PM

Try a where exists subselect:

Exec Sql
  Declare Claimcursor Cursor For
   Select Cmlcl#,
          Cmlln#,
          Cmlamt,
          Cmlir1,
          Cmlir2,
          Cmlbcd,
          Cmlpid
    From Claims a
    Where Cmlcl# = :Peclaim
     And exists (select 1 from claims b
                 where b.cmlcl# = a.cmlcl#
                   and b.Cmlamt > .01)
    Order By Cmlcl#, Cmlln#;
Exec Sql
  Open ClaimCursor;


 

 

Jim_IT
Basic Member
Basic Member
Posts:217

--
04 Dec 2012 07:57 PM

This will work also, but much faster.

 

Create Table QTEMP.CLAIMS
(CMLCL#, CMLLN#, CMLAMT, CMLIR1, CMLIR2, CMLBCD, CMLPID)
As
(Values
(1234, 1, 15.23, 'FH', 'XX', 'RSK', 125698745.001),
(1234, 2, 52.30, 'FH', '', 'HOS', 125698745.001),
(4321, 1, 2.36, 'FH', '15', 'PID', 147852366),
(56789, 1, 5.99, 'FH', '', 'RSK', 256987444.002),
(56789, 2, .01, '15', '', 'RK', 256987444.002),
(45896, 1, .01, 'FH', '', 'BCR', 154896547.002),
(741258, 1, 89.25, 'FH', 'XX', 'BGF', 124569774.001))
With Data
With WANTED_CLAIMS As ( 
Select
Distinct
CMLCL# 
From
QTEMP.CLAIMS 
Where
CMLAMT > .01) 
Select
B.* 
From
WANTED_CLAIMS As A 
Left Join
QTEMP.CLAIMS As B 
On
A.CMLCL# = B.CMLCL#
Order By A.Cmlcl#, B.Cmlln# 

Returns the following data.

1234 1 15.23 FH XX RSK 125698745.001
1234 2 52.30 FH HOS 125698745.001
4321 1 2.36 FH 15 PID 147852366.000
56789  1 5.99 FH RSK 256987444.002
56789 2 0.01 15 RK 256987444.002
741258  1   89.25   FH   XX  BGF   124569774.001

Jim

lostromich
Veteran Member
Veteran Member
Posts:1310

--
07 Dec 2012 05:53 AM
Jim
could you please explain why your SQL example will work faster ?


Ideally , when all Indexes and statistics are intact,
Query engine should be smart enough to execute both statements with compartable speed.

Jim_IT
Basic Member
Basic Member
Posts:217

--
08 Dec 2012 12:51 PM

lostromich,

When I wrote the example, I wrote from personal experience, but since you asked I went ahead and checked what the optimizer said about both statements.

Working with the very small sample table I posted without any indexes, I ran each statement through Visual Explain. Each VE diagram looked very similar and without any indexes the results were within .001 ms, but you stated when all indexes and statistics are intact, so lets create an index over claim# and rerun VE.

create index claimsx on claims(CMLCL#)

Now then the difference becomes larger.  Both statements dropped in time overall, but the SQL using CTE was now estimated 3 times faster.  The VE diagrams are again basically the same with the main being difference in procesing time over intial table scan by the statement with the exists in.

I can only assume it's some sort of look ahead predictive processing occurring behind the scenes because of the join type.

Jim

You are not authorized to post a reply.

Acceptable Use Policy