【Question】

I have a Source table as below:

Name  Subject  Marks  Year

 

A  Science  88  2015

 

A  Social  75  2015

 

A  Social  75  2015

 

A  Maths  22  2015

 

B  Social  75  2015

 

B  Maths  50  2014

 

C  Science 88  2015

 

C  Social  75  2014

 

D  Science  88  2015

 

D  Social  75  2015

 

A  Social  75  2015

 

B  Maths  50  2014

 

I have a requirement as below: if any student satisfies both (as in below) requirements then he should be awarded as corresponding set name in table B:

Set1

 

Social  75  2015

 

Science  88  2015

 

Set2

 

Social  75  2015

 

Maths  50  2014

 

The expected output in table B is as below:

Name  Status

 

A  Set1

 

B  Set2

 

C  None

 

D  Set1

 

A correct solution:

SELECT NAME,

 

CASE WHEN MAX(SCIENCE) + MAX(SOCIAL) = 2 THEN 'Set1'

 

WHEN MAX(SOCIAL) + MAX(MATHS) =2 THEN 'Set2'

 

ELSE 'None'END AS Status

 

FROM (SELECT NAME,

 

CASE

 

WHEN SUBJECT = 'Science' AND MARKS = 88 AND YEAR = 2015

 

THEN 1 ELSE 0

 

END

 

AS SCIENCE,

 

CASE

 

WHEN SUBJECT = 'Social' AND MARKS = 75 AND YEAR = 2015

 

THEN  1 ELSE 0

 

END

 

AS SOCIAL,

 

CASE

 

WHEN SUBJECT = 'Maths' AND MARKS = 50 AND YEAR = 2014

 

THEN  1 ELSE 0

 

END AS MATHS

 

FROM A)x group by Name;

 

【Answer】

The first thing that came to my mind was that this is a set operation problem. But, without explicit set data type, SQL has to hardcode the computation, which makes it difficult to modify the program if the requirements are changed. In view of this, I want to offer an intuitive and easy to debug solution. I’ll retrieve the source data out from the database to be processed in SPL (Structured Process Language). Here’s the script:

 

	A
1	$select Name,Subject,Marks,Year   from tb
2	=A1.group(Name;~.([Subject,Marks,Year]):g)
3	=A2.new(Name,if(g.pos(Set1),"Set1",if(g.pos(Set2),"Set2","None")):Status)

A1: Retrieve data from the source table with a SQL-like statement.

A2: Group records by Name.

A3: Create a new table sequence made up of the desired fields. Set1 and Set2 are sequence type parameters. There values are [[“Social”,75,2015],[“Science”,88,2015]] and [[“Social”,75,2015],[“Maths”,50,2014]] respectively. pos() function finds if set B is a subset of set A.