mysql Merging results from a stored procedure

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!!

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 ?

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

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.

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

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.

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' ");

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?

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' ");

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).

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!!!!

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);

Hi mate, you answered this one for me, about 1 hour ago, in a seperate  question

Thank you !