# Using SAP HANA to count my Elephants, Cows and Goats

We were working on SAP HANA for some project work, when one of our colleagues suddenly jumped in with a simple puzzle. This was a basic kind of math puzzle, which all of us might have solved in our math classes. But then, a technical minds are about finding something new out of everything. So we tried to use SAP HANA to count my number of elephants, cows and goats.

Here is what we found:

# The Puzzle

You have \$100, with which you have to buy exact 100 toys.

There are three different choices for toys: Elephant Toy = \$10, Cow Toy = \$3, Goat Toy=\$0.5.

So what are the possible options for buying 100 toys with just \$100?

# The Challenge

You don’t have to use any programming language, but some database technology (and that too, only SAP HANA) to achieve the result. ðŸ™‚

# Reason (for such absurd challenge)

For such kinds of puzzle, anyone with technical background would prefer to use some programming language (C, C++, Java etc.), which provides support for loops and while statements. But then in past few years, with evolution of SAP HANA, we have learnt that providing a single platform for both application and database layers would work much faster than using two different technologies (a database technology to handle data, and some application platform running the business logic on top of database layer). If the whole business logic, the application code and the database could reside on a single huge chunk of RAM, having the fastest of the processors processing the entire business flow from top to bottom, that could create wonders out of such technology. And for doing this, we need to understand the ways in which we can harness this kind of technology to its full extent. This challenge may be one ay of doing that!!!

# How it progressed

To proceed with, we started with the most basic programming constructs, running loops and using conditional statements in them. This would be a simple stored procedure developed in SQLScript. With help from various sources (CREATE PROCEDURE, Working with stored procedures), discussions (link 1, link 2) and with feedback from experts (Maxime Tremblay-Savard, Ravindra Channe, Lars Breddemann and many others) , we came out with the first working construct to find the answer. Here it goes:

–creating the table for storing logs

CREATE TABLE PRACTICE_TEST.MY_LOG

(LOG_ENTRY varchar(100));

–procedure to make entries in log tables

CREATE PROCEDURE PRACTICE_TEST.PROC_MY_LOG

(IN v_in_msg    VARCHAR  (100)   )

LANGUAGE SQLSCRIPT

AS

BEGIN

INSERT INTO PRACTICE_TEST.MY_LOG ( LOG_ENTRY) VALUES (:v_in_msg);

END;

–dropping any other procedure with similar name (just in case)

DROP PROCEDURE PRACTICE_TEST.PROC_PUZZLE_OUTPUT;

–Creating the actual stored procedure to process the business logic

CREATE PROCEDURE PRACTICE_TEST.PROC_PUZZLE_OUTPUT

LANGUAGE SQLSCRIPT

AS

ELEPHANT_COUNT  integer;

COW_COUNT integer;

GOAT_COUNT integer;

ELEPHANT integer;

CNTR integer;

v_msg varchar(100);

Begin

ELEPHANT_COUNT := 1;

CNTR := 0;

v_msg := ”;

While ELEPHANT_COUNT < 10

DO

COW_COUNT := 1;

While COW_COUNT < 100

DO

GOAT_COUNT := 1;

while GOAT_COUNT < 100

Do

CNTR := CNTR + 1;

if MOD(:cntr, 1000) = 0 then

v_msg := ‘CNTR RUN COUNT = ‘||:CNTR;

call PRACTICE_TEST.PROC_MY_LOG(:v_msg);

end if;

if ((:ELEPHANT_COUNT + :COW_COUNT + :GOAT_COUNT) = 100) AND ((:ELEPHANT_COUNT * 10) + (:COW_COUNT * 3)  + (:GOAT_COUNT * 0.5) = 100)

THEN

v_msg := ‘CNTR RUN COUNT = ‘||:CNTR || ‘Goat Loop : Elephant count = ‘||:ELEPHANT_COUNT||’ Cow count = ‘||:COW_COUNT||’Goat count = ‘||:GOAT_COUNT;

call PRACTICE_TEST.PROC_MY_LOG(:v_msg);

end if;

GOAT_COUNT := :GOAT_COUNT + 1;

END While;

COW_COUNT := :COW_COUNT + 1;

End While;
ELEPHANT_COUNT := :ELEPHANT_COUNT + 1;

end while;

End;

–deleting the log entries for previous runs

truncate table PRACTICE_TEST.MY_LOG;

–running the procedure having business logic

call PRACTICE_TEST.PROC_PUZZLE_OUTPUT;

–Checking the results in the log

SELECT * from PRACTICE_TEST.MY_LOG ASC;

-X- X- X–X- X- X–X- X- X–X- X- X–X- X- X–X- X- X–X- X- X–X- X- X–X- X- X-

Yes, this worked well. We found the correct answer to the puzzle. 5 Elephants, 1 Cow and 94 Goats would fetch us exact 100 toys in exact \$100. But then with the discussions in the feedback, we realized that this was not the best method for carrying out this logic. As suggested by Lars, a better way would be to use the internal CE functions provided by SAP HANA.

CE (Calculation engine) Built-In functions are special function provided by SAP, that are optimized for performance with SAP HANA. If these functions are used instead of normal DB Constructs, the internal implementation would be in a much more optimized way, because their logic is directly implemented in the calculation engine. (For more details about CE Functions, see http://help.sap.com/hana/hana_dev_sqlscript_en.pdf )

We are still looking forward to create the same business logic using CE functions in SAP. If we could create the same business logic using appropriate CE Functions, and measure its performance against the traditional B Programming constructs, we could identify how much difference this creates.

Conclusion:

We still believe this article as an open article, and conclusion can be made only after the same business logic is implemented using the CE functions. So until our team is working with that, we invite suggestion/feedback/inputs from everyone about this idea.

### Assigned Tags

You must be Logged on to comment or reply to a post.

Hello Vishal,

Thanks for the article and solution to the puzzle.

Instead of the "Logic" in SQL Script, can it be done via SQL Select in a procedure? Like Shown in the post Analytical ABAP for HANA Applications: Characteristics, Unique Selling Points and first Programming Idioms

• You create a table with few dummy entries for Elephants, Goat, Cow with its price. Probably number of entries for each toy = \$100 / its price.
• Do a Select with Group by, Order By, JOIN, etc to get to the result

Thanks,
Naimesh Patel

Vishal Soni
Blog Post Author

Hey Naimesh,

Thanks for sharing your thoughts. Actually we had thought of doing the performance comparison for few more alternate versions of this scenario. One if them included the idea that you suggested, and yet another one was to create all the possible combinations of answers in form of huge tables, and then just simply running select query for matching conditions. But then we realized (from the various feedbacks) that the actual benefits of SAP HANA can be realized by using internal CE functions.

Although i still have all these plans in mind, and now they are also on paper (or on internet), in form of this comment. ðŸ™‚

I would be running with all these scenarios as and when time permits. But till then, i wanted to open this up for everyone, so that others can also contribute.

Once again thanks for your valuable suggestion.

Hello Vishal,

Thanks for the nice challenge and article.

Here my solution approach letting SQL doing all the math.

I create and fill a helper table with values from 1..100.

I join this table 3 times - and let the where clause select the appropriate combination.

DROP TABLE COACH.HELPER;

DROP PROCEDURE COACH.INIT_GAME;

CREATE COLUMN TABLE "COACH"."HELPER" ("ID" INTEGER CS_INT NOT NULL);

CREATE

PROCEDURE COACH.INIT_GAME() LANGUAGE SQLSCRIPT AS

v_count INT

;

BEGIN

FOR v_count IN 1 .. 100 DO

INSERT INTO COACH.HELPER VALUES(:v_count);
END FOR;

END;

CALL COACH.INIT_GAME();

SELECT

e.id elephants, c.id cows, g.id goats FROM COACH.HELPER e, COACH.HELPER c, COACH.HELPER g

WHERE e.id+c.id+g.id=100

AND 10*e.id+3*c.id+0.5*g.id=100;

Ciao and Thanks

Mo

Vishal Soni
Blog Post Author

Hey Mohammed,

Thanks. This was one of the approaches that we were discussing in the above comment thread.

This is really helpful. Thanks for putting this up. So what is your count for the animals? i guess if you would start your loop from 0 instead of 1, we might get more results (though such solutions are not the desired one !!)  ðŸ™‚

Hi Vishal,

it was the same number which you have written in the blog(5 Elephants, 1 Cow and 94 Goats ).

Yes, you are right if starting with 0 you get two results.

If you put some more upfront knowledge into the sql - you can further speed it up:

SELECT

e.id elephants, c.id cows, g.id goats FROM COACH.HELPER e, COACH.HELPER c, COACH.HELPER g

WHERE

e.id <10

AND c.id<34

AND e.id+c.id+g.id=100

AND 10*e.id+3*c.id+0.5*g.id=100;

But this would be some kind of cheating;-)

Ciao

Mo