nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Adding records based on if a field does not match itself?

dlmallard

All, I need help.
 
We have a report that shows services provided for clients and the authorization (Auth) code for the services.
I'm trying to update the report to also show selected fields if the services were not provided under the authorization codes that exist for the client. Our goal is to determine where services are being provided that we aren't getting paid for (because they are not authorized). There is a challenge in that each client has his/her own Auth codes and there can be more than one for any given client.
 
How do I code to pull in all auth codes and then look through that list to check records NOT IN that list?
 
Thank you kindly for any help you can provide!
 
D,

Find more posts tagged with

Comments

wwilliams

Is this a SQL database? Are the valid codes in a separate table ...need more info.

dlmallard

<blockquote class="ipsBlockquote" data-author="wwilliams" data-cid="132272" data-time="1416945844">
<div>
Is this a SQL database? Are the valid codes in a separate table ...need more info.
</div>
</blockquote>
 
Thanks WW, I am using the BIRT with a DB2 database.
 
The valid codes are in the same table as some of the fields. I wand to check those valid codes against themselves .. so for instance if a given user has auth code '12345' , '23456', & '34567' assigned to them (which are valid), I need a way to check if they also were given service under any other auth code (or no code at all) those will be invalid . 
 
So, I need a way to run a second pass of the auth code field to check it against itself or somehow place all of the codes for a given user into an array so that, at each user, I suppose I could query the array to see if the user had service under any other auth code besides the valid ones. If that condition is found, I want to write additional fields outlining the invalid service event (those fields will be pulled from a different table.)
 
Thanks for your help, I'm VERY new to BIRT.

wwilliams

"The valid codes are in the same table as some of the fields. I wand to check those valid codes against themselves .. so for instance if a given user has auth code '12345' , '23456', & '34567' assigned to them (which are valid)"
 
If they exist in the same table how do you know if those codes are valid\assigned to them?
I would think, one table would hold the service records and another for the client with auth codes or something along that line.
I am sorry it still doesn't make sense, but sometimes I am a bit dense.

dlmallard

WW, Thanks again,
 
Sorry I wasn't clear. The valid Auth codes are on one table. We know these are valid by simply being on the table. The auth codes for each service event are on a separate table with all of the service event info. I want to run a report which shows all of the valid service codes THEN show a list of the service records that have an event service code that does not match any of the valid service codes for a given client.
 
So, I'm thinking I have to run a query to obtain all of the valid auth info for a given client, then run a second query to check all of the service events for that client that have an auth code that is different than the array of valid auth codes. I just don't know how to do that in BIRT (how to add selected info into an array..AND how to run a second SQL query)
 
I thank you again kindly for your help!!!

wwilliams

OK, so we have a Service table, which should include a client column as an authorization column.
The second table (Client) will have a client column and the associated authorization codes in another column e.g. "authcodes".
So the relationship between the two tables will be the client column and then the authcodes?
So the result set you want will be those where the service record authcode is null or does not match those authcodes in the second table (Client).
 
this sound correct?

dlmallard

WW,
 
The relationship between all tables is a client number.
 
One table ... "Master" has all info about the client (name, gender, address..etc... along with their client number)
Another table..."AuthTBL" has all of the valid auth codes (some other info) and the associated client number.
A Third table "Services" has all of the service event info including an auth code recorded for that specific event which includes the client number the service was for.
 
The 1ST result set should include information regarding the client and their VALID AuthTBL auth codes.
The 2ND result set should include those service records where the Services authcode is null or does not match the VALID AuthTBL authcodes for that client.
 
Thanks!

wwilliams

Try something like this
 
SELECT Service.id, Service.authcode, authcode.authcode,Master.client 
FROM (Service LEFT JOIN authcode ON (Service.client = authcode.client) AND (Service.authcode = authcode.authcode)) 
LEFT JOIN Master on Master.client = Service.client 
WHERE ((authcode.authcode) Is Null);

dlmallard

Thanks for your help. I found a way to create the report and provided the needed data without going to extra step.