mysql Merging results from a stored procedure


Hi guys,

How do i merge results that are returned from a stored procedure which contains 7 diffferent result sets.



DELIMITER $$
DROP PROCEDURE IF EXISTS `COMBINED` $$
CREATE PROCEDURE `COMBINED`(IN whst TEXT)
BEGIN
      CALL `1_cashier`(whst);
      CALL `2_register`(whst);
      CALL `3_income`(whst);
      CALL `4_Total_Money`(whst);
      CALL `5_dept`(whst);
      CALL `6_customer`(whst);
      CALL `7_totals`(whst);

END $$
DELIMITER;

LVL 8
kingjelyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

flytox06Commented:
1) do you really need to merge those results ? can't you simply use them at client side ? (usually merging is used for sorting or grouping)
2) you can imagine make the procedure fill a temprary table with data, and then make the main procedure, select those data from the temporary table
3) UNION the results from the selects, but it can't be done outside many stored procedures but inside the same.

could you develop a bit concerning what you are doing and which kind of results you need in output, your current approach is probably not the best one.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
theGhost_k8Database ConsultantCommented:
As said union is not possible with SP, you can do that with Functions.

Select function1() union
Select function2()....

Now if you convert  these SPs to functions and just make them return query string, you can UNION here in SP and execute.

this approach depends on what you're trying to achieve!! Instead of making 7 diff SPs you can do that in single SP as well!!
0
kingjelyAuthor Commented:
Hi kate,

These are 6 seperate reports
`1_cashier` analysis
`2_register` analysis
`3_income` analysis
`4_Total_Money` analysis
`5_deptartment analysis`
`6_customer` analysis`    and
7_totals

whst = for store(s) and between date range, and a station number(whatever station number(s) in the store)


But most people run this report as
COMBINED ANALYSIS (1 - 6) they want to see the lot,

Rather then run each individual report.

but both combined, and each individual must be available.

So i guess i have got the each individual bit done, (thanks flytox)
Now I want to RUN the COMBINED ANALYSIS.

It would be good if i could merge all 7 into one long string..

or your saying now i have each individual, just change SP for COMBINED to a stored function and combine all 7 as 1, with union ?

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

kingjelyAuthor Commented:

Flytox, your a star,
1) I kind of need to merge the data as where the data needs to be cant read in 6 different calls(easily)
2) Fill temp table , would this just fill the columns? ( could i call this > export to excel to see the results, - this gives me hope that VB could read the whole string and place the fields on a form.
 
Union would be the answer to the question  flytox do you think ?

DELIMITER $$
DROP PROCEDURE IF EXISTS `COMBINED` $$
CREATE PROCEDURE `COMBINED`(IN whst TEXT)
BEGIN
      CALL `1_cashier`(whst) union
      CALL `2_register`(whst) union
      CALL `3_income`(whst) union
      CALL `4_Total_Money`(whst) union
      CALL `5_dept`(whst) union
      CALL `6_customer`(whst) union
      CALL `7_totals`(whst) union

END $$
DELIMITER;


something like that would

export to excel the same way it would export to an app...

Thanks for putting up with me =D

JB
0
flytox06Commented:
1) if you code VB, have a read to http://support.microsoft.com/?scid=kb%3Ben-us%3B182290&x=7&y=13
2) temporary table you fill with rows returned as output of each stored procedures, somehting like "INSERT INTO temp_table <your_big_select_here>" for every SP (not exactly, it's just the concept)

for output to excel, calling COMBINED as follows can give a nice result :

mysql -BNe "call COMBINED(whst)" > yourcsvfile.csv

that will output every results merged.
0
kingjelyAuthor Commented:

Hi flytox

Have got the guys to look at the VB code link you gave me.

I'm doing the SQL stuff, others are doing the VB code

I would like to get this file to Excel,

I have tried, your code but can't get it to work.
I don't exactly understand.

I copied and pasted this in the browser,
mysql -BNe "call COMBINED(whst)" > yourcsvfile.csv

or is this ment for the command line somehere

0
flytox06Commented:
this is command line statement.

It simply executes the combined stored procedure in command line and output result into a file. The option -BNe I gave you remove table formating, columns and several other outputs.

Have a look at your client help under windows, but I guess it will look the same.
0
kingjelyAuthor Commented:

Hi, i am not on the server, im on a remote machine.

I have tried something like this

mysql -h hostname -u myuser --password mypassword mydatabase
-BNe "call COMBINED(whst)" >combined.csv

Now I get the .csv to my c:\ and i can go in to the file but it has just dumped what looks like the HELP index into the CSV file

Can you see what i have done wrong with my command line entry?

Also, do i maybe have to run the combined query with the (whst) parameter, then save it?
How will calling COMBINED(whst) know what the actul (whst) is?

eg my whst is
CALL `COMBINED`("`storenumber` = 2 AND `transdate` between '2010-02-01' AND '2010-02-01' ");
0
flytox06Commented:
try that :

mysql -h hostname -u myuser -p -BNe "call COMBINED(\"storenumber = 2 AND transdate between '2010-02-01' AND '2010-02-01'\")" mydatabase

it will ask for your password.

If everything is fine, append "> conbined.csv" to output in the file
0
kingjelyAuthor Commented:
HI i got up to the point to addin the password,
But then got an error

access denied for my user,

where as, before i was not getting access denied, using same username and password?

Any clues?
0
kingjelyAuthor Commented:


also FOR KATE

Hi Kate,

This is my first SP it's a cashier analysis.
how do i make this a function, not a stored procedure, so i can try what you said above?

If you can show me this one, i can try to do it with all the others then use union?


DELIMITER $$
DROP PROCEDURE IF EXISTS `1_cashier` $$
CREATE PROCEDURE `1_cashier`(IN whst TEXT)


BEGIN
       SET @comb_1 = CONCAT("
        SELECT *,
         Round((profit / VALUE) * 100,2) AS margin
        FROM   (SELECT   `opername`                                                                  AS name,
                   Sum((`quantity` * `unitsell`) + ((`quantity` * `linediscount`) + `bulkdiscount`)) AS gross,
                   (`linediscount` * `quantity`) + (`bulkdiscount`)                                  AS discount,
                   Sum(`quantity` * `unitsell`)                                                      AS nett,
                   Sum(`quantity` * `gstretcontent`)                                                 AS gst,
                   Sum(`quantity` * `unitcostex`)                                                    AS cost,
                   Round(Sum((`unitsell` * `quantity`) - `bulkdiscount`) / 1.1 - Sum(`unitcostex`),
                         2) AS profit,
                   Round(Sum((`unitsell` * `quantity`) - `bulkdiscount`) / 1.1,
                         2)  AS VALUE,

            (SELECT Sum((`quantity` * `unitsell`) + (`quantity` * `linediscount`))
                    FROM history
                    WHERE",whst," AND `subtranstype` < 4 ) AS totalgross,

            (SELECT Sum((`linediscount` * `quantity`) + (`quantity` * `bulkdiscount`))
                    FROM history
                    WHERE",whst,"AND `subtranstype` < 4 ) AS totaldiscount,

            (SELECT Sum((`quantity` * `unitsell`))
                    FROM history
                    WHERE",whst,"AND `subtranstype` < 4 ) AS totalnett,

            (SELECT Sum((`quantity` * `gstretcontent`))
                    FROM history
                    WHERE",whst,"AND `subtranstype` < 4 ) AS totalGST,

            (SELECT Sum((`quantity` * `unitcostex`))
                    FROM history
                    WHERE",whst,"AND `subtranstype` < 4 ) AS totalcost
          FROM history
          WHERE", whst, " AND `subtranstype` < 4
          GROUP BY operid) AS margintable");
           PREPARE C_A_1 FROM @comb_1;
           EXECUTE C_A_1;
           DEALLOCATE PREPARE C_A_1;

END $$
DELIMITER;

CALL `1_cashier`(" `storenumber` = 2 AND `transdate` BETWEEN '2010-02-01' AND '2010-02-01' ");
0
flytox06Commented:
the user you're using must have rights.

Ensure "SHOW GRANTS myuser@host;" is returning correct values (where host is the machine you're trying to connect from).
0
kingjelyAuthor Commented:
Thanks GUys,

Flytox i have a new question on this topic,
instead of 1 parameter how do i call 4 per procedure

Ill ask now

Thanks!!!!
0
flytox06Commented:
in CREATE PROCEDURE statement, append parameters in paranthesis with simple comma, and when calling procedure, do the same. something like :

CREATE PROCEDURE test (param1 int, param2 int, param3 int) ....

call test (0, 2, 9);
0
kingjelyAuthor Commented:
Hi mate, you answered this one for me, about 1 hour ago, in a seperate  question

Thank you !
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.