Solved

Calling one stored procedure from another

Posted on 2013-12-19
6
580 Views
Last Modified: 2013-12-19
I am trying to call one stored procedure withing another - using an if statement.
I am getting an error so I believe that I have something out of sequence

CREATE PROCEDURE reportFreeCoolingTrackerCalls (
        IN fromDate varchar (50),
        IN toDate varchar (50),
        IN timeZone varchar (50))

    BEGIN
            DECLARE startDate varchar (50);
            DECLARE endDate varchar (50);
            DECLARE mylogID Int;

             SET startDate = FROM_UNIXTIME(fromDate/1000);
             SET endDate = FROM_UNIXTIME(toDate/1000);
            
     IF (l1.activityId = t2.activityId) 
      THEN CALL reportFreeCoolingTrackerError (
        fromDate,
        toDate,
        timeZone );
    ELSEIF  (l1.activityId != t2.activityId)
       THEN CALL reportFreeCoolingTracker (
        fromDate,
        toDate,
        timeZone );
            END IF;

         
  SELECT l1.activityId,t2.activityId
  FROM logs l 
      INNER JOIN groups g ON g.groupId = l.groupId
      LEFT JOIN groups g1 ON g.parentId = g1.groupId
      LEFT JOIN groups g2 ON g1.parentId = g2.groupId
      LEFT JOIN groups g3 ON g2.parentId = g3.groupId                
      INNER JOIN activities a ON l.logId = a.logId 
      INNER JOIN log1644 l1 ON a.activityId = l1.activityId 
      INNER JOIN log1644 t2 ON t2.recordId = l1.recordid + 1
      INNER JOIN items i ON l.logId = i.logId AND i.name LIKE '%KW%'
      INNER JOIN users u ON l1.userId = u.userId AND i.name LIKE '%KW%'
     WHERE i.itemID = "31985"  AND  l1.activityId = 1257
      AND l1.started
        BETWEEN startDate
            AND endDate 
     ORDER BY l1.recordId,l1.started;
  
    END //

DELIMITER ;

Open in new window


ERROR

Unknown table 'l1' in field list
0
Comment
Question by:portlight
  • 4
  • 2
6 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 39730106
Those aliases, l1 and l2, exist only in the context of the SELECT statement that creates them.
0
 

Author Comment

by:portlight
ID: 39730111
Okay Is there a way to fix this?
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 39730124
This is untested, but should get you a step closer.

CREATE PROCEDURE reportFreeCoolingTrackerCalls (
        IN fromDate varchar (50),
        IN toDate varchar (50),
        IN timeZone varchar (50))

    BEGIN
            DECLARE startDate varchar (50);
            DECLARE endDate varchar (50);
            DECLARE mylogID Int;
            DECLARE @ActivityID1 int;
            DECLARE @ActivityID2 int;

             SET startDate = FROM_UNIXTIME(fromDate/1000);
             SET endDate = FROM_UNIXTIME(toDate/1000);
            

SELECT @ActivityID1 = l1.activityId, @ActivityID2 = t2.activityId
  FROM logs l 
      INNER JOIN groups g ON g.groupId = l.groupId
      LEFT JOIN groups g1 ON g.parentId = g1.groupId
      LEFT JOIN groups g2 ON g1.parentId = g2.groupId
      LEFT JOIN groups g3 ON g2.parentId = g3.groupId                
      INNER JOIN activities a ON l.logId = a.logId 
      INNER JOIN log1644 l1 ON a.activityId = l1.activityId 
      INNER JOIN log1644 t2 ON t2.recordId = l1.recordid + 1
      INNER JOIN items i ON l.logId = i.logId AND i.name LIKE '%KW%'
      INNER JOIN users u ON l1.userId = u.userId AND i.name LIKE '%KW%'
     WHERE i.itemID = "31985"  AND  l1.activityId = 1257
      AND l1.started
        BETWEEN startDate
            AND endDate 
     ORDER BY l1.recordId,l1.started;

     IF (@ActivityID1 = @ActivityID2) 
      THEN CALL reportFreeCoolingTrackerError (
        fromDate,
        toDate,
        timeZone );
    ELSEIF  (@ActivityID1 != @ActivityID2)
       THEN CALL reportFreeCoolingTracker (
        fromDate,
        toDate,
        timeZone );
            END IF;

         
  Select @ActivityID1, @ActivityID2;
  
    END //

DELIMITER ;

Open in new window

0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 39730126
Basically, I'm selecting the values you need, sticking them into variables, then calling the stored procedure conditionally, and finally returning those values to the user that you were originally returning.
0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 500 total points
ID: 39730135
Looks like I should have used := for the assignments.
http://dev.mysql.com/doc/refman/5.5/en/user-variables.html

CREATE PROCEDURE reportFreeCoolingTrackerCalls (
        IN fromDate varchar (50),
        IN toDate varchar (50),
        IN timeZone varchar (50))

    BEGIN
            DECLARE startDate varchar (50);
            DECLARE endDate varchar (50);
            DECLARE mylogID Int;
            DECLARE @ActivityID1 int;
            DECLARE @ActivityID2 int;

             SET startDate = FROM_UNIXTIME(fromDate/1000);
             SET endDate = FROM_UNIXTIME(toDate/1000);
            

SELECT @ActivityID1 := l1.activityId, @ActivityID2 := t2.activityId
  FROM logs l 
      INNER JOIN groups g ON g.groupId = l.groupId
      LEFT JOIN groups g1 ON g.parentId = g1.groupId
      LEFT JOIN groups g2 ON g1.parentId = g2.groupId
      LEFT JOIN groups g3 ON g2.parentId = g3.groupId                
      INNER JOIN activities a ON l.logId = a.logId 
      INNER JOIN log1644 l1 ON a.activityId = l1.activityId 
      INNER JOIN log1644 t2 ON t2.recordId = l1.recordid + 1
      INNER JOIN items i ON l.logId = i.logId AND i.name LIKE '%KW%'
      INNER JOIN users u ON l1.userId = u.userId AND i.name LIKE '%KW%'
     WHERE i.itemID = "31985"  AND  l1.activityId = 1257
      AND l1.started
        BETWEEN startDate
            AND endDate 
     ORDER BY l1.recordId,l1.started;

     IF (@ActivityID1 = @ActivityID2) 
      THEN CALL reportFreeCoolingTrackerError (
        fromDate,
        toDate,
        timeZone );
    ELSEIF  (@ActivityID1 != @ActivityID2)
       THEN CALL reportFreeCoolingTracker (
        fromDate,
        toDate,
        timeZone );
            END IF;

         
  Select @ActivityID1, @ActivityID2;
  
    END //

DELIMITER ;
                                            

Open in new window

0
 

Author Comment

by:portlight
ID: 39730391
When I execute this it actually pulls 3 distinct result sets.
I only need it to pull the result set for the query actually executed
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Question about consuming GB from Comcast 5 53
MS SQL Server - Looking to filter rows based on column value 3 36
MySQL Error Code 2 19
Question about DB Schema 27 51
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now