Wsrtjtyk

i have the following stored procedure:

create temporary table logData (table_name varchar(50), column_name varchar(50), changed_date date, changed_by char(10), old_value varchar(50), new_value varchar(50))



insert into logData SELECT table_name, column_name, changed_date, changed_by, old_value, new_value FROM log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate



UNION



SELECT "reservation_flight", column_name, changed_date, changed_by, old_value, new_value FROM reservation_flight_log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate



UNION



SELECT "ticket", column_name, changed_date, changed_by, old_value, new_value FROM ticket_log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate



UNION



SELECT "passenger_record", column_name, changed_date, changed_by, old_value, new_value FROM passenger_record_log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate



select * from logData order by changed_date;

In the above sql select statement empId, startDate and endDate are the input parameters for this stored procedure. This is showing error please help? Thank You!

I believe you are missing two semicolons: one at the end of the first statement and another at the end of the before-last line. You may rewrite it like the following:

CREATE temporary TABLE logData (table_name   varchar(50), 

                                column_name  varchar(50), 

                                changed_date date, 

                                changed_by   char(10), 

                                old_value    varchar(50), 

                                new_value    varchar(50));



INSERT INTO logData 

   SELECT table_name, column_name, changed_date, changed_by, old_value, new_value 

   FROM log 

   WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate

UNION

   SELECT "reservation_flight", column_name, changed_date, changed_by, old_value, new_value 

   FROM reservation_flight_log 

   WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate

UNION

   SELECT "ticket", column_name, changed_date, changed_by, old_value, new_value 

   FROM ticket_log 

   WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate

UNION

   SELECT "passenger_record", column_name, changed_date, changed_by, old_value, new_value

   FROM passenger_record_log

   WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate;



SELECT * FROM logData ORDER BY changed_date;