Query Writing Differently (Relational Division)

How do you define “better”? Faster? Easier to read?

Anyway, what you’re attempting is relational division. Here’s one way to do this so you only need to access each table once, so should be faster and easier to read 🙂

It cross joins the tables, then groups by developer, project and skill. You want the rows from this where the developer has all the skills for the project. The expression:

sum(case when p.skill = d.skill then 1 else 0 end)

Finds you the rows where the project and developer skills match. If the sum of this (by project and dev) = the number of skills the project needs, you have a winner:

create table developers (name varchar2(30), skill varchar2(30)); create table projects (name varchar2(30), skill varchar2(30)); insert into developers values ('SMITH','ORACLE'); insert into developers values ('SMITH','JAVA'); insert into developers values ('ADAMS','JAVA'); insert into developers values ('ADAMS','C++'); insert into projects values ('MISSION MARS','JAVA'); insert into projects values ('MISSION MARS','C++'); insert into projects values ('MISSION IMPOSSIBLE','JAVA'); select pname, dname, sum(sm), min(ct) from ( select d.name dname, p.name pname, p.skill, count(*) ct, sum(case when p.skill = d.skill then 1 else 0 end) sm from developers d cross join projects p group by d.name, p.name, p.skill ) group by pname, dname having sum(sm) = min(ct); PNAME DNAME SUM(SM) MIN(CT) MISSION MARS ADAMS 2 2 

There are several other approaches you can take to implement relational division. Though arguably most are harder to comprehend.

Here’s an article giving a thorough discussion of them (they use SQL Server, but I guess we can forgive them for that ;):

https://www.simple-talk.com/sql/learn-sql-server/high-performance-relational-division-in-sql-server/

Note the first few examples won’t work in your case, because you have multiple projects. Go to the “Relational Division with Multiple Divisors” section for solutions that match your problem.

Related:

MQ Error retry is not working if sql-execute is successful while logging error

Hello All,

I have created a MPG which reads from a MQ A and puts message to MQ B. I have enabled Unit of Work for MQ Queue Manager and trying to create a scenario where retry happens couple of times and 3rd time when it fails, message and details will be inserted into DB and if DB call fails, message will be put into backout queue.

Below is the algorithm :

dp:url-open (MQ)

check url-open status

if error

choose
when retry counter = 2
{
sql-execute
if error
dp:reject – 2 attempts failed & DB insetion failed
}
otherwise
{“retry”}

However, even if sql insertion is not successful after MQ failure, message is not going to the backout queue. Below is the code snippet in XSLT

Backout Count***Error connecting to MQ and SQL insertion failed

Can anybody tell me why messages are not going to backout queue even if it goes to error rule? Thank you.

Related:

nco_p_ncpmonitor probe fail to send events to objectserver

Even though the probe status is ‘RUNNING’ , it is not sending alerts to objectserver. The following error is present into probe log file:

ERROR=Type mismatch for column on line 1 of statement ‘insert into alerts.status values (‘ITNM42->ItnmHealthChk2ITNM42′,0,’ITNM42′,”,’ITNM’,’ncp_virtualdomain’,’ITNM Status’,’ITNM42->ItnmHealthChkITNM42′,1,’Precision health check success event for domain ITNM42 from primary domain: ITNM42′,0,1491476667,14…’, at or near …

Related:

Transaction Handling from IBM BPM

Scenario:
I have a SQL Server database which is not distributed. Data source is configured in the Websphere Application Server. All my tables are defined in the same schema. Business requirement is to insert/update all the related tables in a single transaction. In case any table insert/update fail, then not to commit any insert/update operation. I am trying to use OOTB component of IBM BPM.

Solution Tried:
I have prepared a service, which will take list of SQLStatements. Then it will iterate all the statements and prepare a block of executable sql query and return that query in a SQLStatement object.

Sample of executable block of the service:
Begin Begin Try
sql query1;
sql query2;
……
sql queryN;
Commit;
End Try
Begin Catch
Rollback;
RAISERROR(Error Message,16,1,)
End Catch
End;

Problem:
1) I am able to see an error message “There is no executable sql statement for commit” while using OOTB SQL Execute Statement service.
2) SQL statements are automatically committed in Database.

Question:
I have used similar concept for Oracle in IBM BPM 7.5 and it worked perfectly fine (block syntax was different for Oracle). Not sure why it is not working in IBM BPM 8.5.7 for SQL Server. Please help me to understand what I need to perform to work it appropriately without using IID/java component.

Related:

500 Error: Failed to establish a backside connection while inserting data from Java REST service into DashDB for Analytics

Trying to POST JSON data (around 3000 records) through Java Rest service into DashDB for Analytics. Around 2700 records are inserted into DashDB, but response is “500 Error: Failed to establish a backside connection ” and all records are not inserted.

Wanted to know if there is limit in number of records that we can insert or there is any limit in timeout. If so is timeout configurable?

Related: