?
Solved

Merge

Posted on 2006-07-05
3
Medium Priority
?
404 Views
Last Modified: 2013-12-26
I am trying to merge 2 parts.
when I use it just as SQL script it works, but I recently made it through the PowerBuilder Window.
Could someone tell me what am I doing wrong?
Sometime it works, sometimes doesn't.
May be I miss something.
Thanks for your help in advance.

//With an embedded SQL

//cb_merge.Clicked()
//===================

Long ls_oid_to_delete, ls_oid_to_keep, to_item_oid, from_item_oid
String  temp_keep, temp_delete, from_sn, to_sn
int inv_status_oid, to_aircraft_oid, to_city_oid, to_status_oid
Datetime inv_move_date
//Get oid's from SingleLineEdit control

//temp_keep = sle_1.text
//temp_delete   = sle_2.text

ls_oid_to_keep = long(sle_1.text)
ls_oid_to_delete = long(sle_2.text)

to_city_oid = dw_1.getItemNumber(1, 'city_oid')
to_aircraft_oid = dw_2.getItemNumber(1, 'aircraft_oid')
to_status_oid = dw_3.getItemNumber(1, 'status_oid')
// insert oids as necessary -
// system will merge the two records into the "oid_to_keep"
// and purge the "oid_to_delete"

Select Serial_Number, item_oid into :from_sn, :from_item_oid from rotable_inventory, item_Master where rotable_inventory.item_oid = item_master.oid and rotable_inventory.oid = :ls_oid_to_delete;

Select Serial_Number, item_oid into :to_sn, :to_item_oid from rotable_inventory, item_master where rotable_inventory.item_oid = item_master.oid and rotable_inventory.oid = :ls_oid_to_keep;

Select Max(inv_move_date) into :inv_move_date from inv_move_log where inv_oid = :ls_oid_to_keep;

update inv_move_log
      set inv_oid = :ls_oid_to_keep,
            upd_timestamp = getdate()
      where inv_oid = :ls_oid_to_delete;

update work_order_data
      set inv_oid = :ls_oid_to_keep,
            upd_timestamp = getdate()
      where inv_oid = :ls_oid_to_delete;

update wo_inv_action
      set inv_oid = :ls_oid_to_keep,
            upd_timestamp = getdate()
      where inv_oid = :ls_oid_to_delete;

update mc_rotable
      set inv_oid = :ls_oid_to_keep,
            upd_timestamp = getdate()
      where inv_oid = :ls_oid_to_delete;

update purchase_order_data
      set inv_oid = :ls_oid_to_keep,
            upd_timestamp = getdate()
      where inv_oid = :ls_oid_to_delete;

update rotable_inventory
      set status_oid = 2428,
      upd_timestamp = getdate()
      where oid = :ls_oid_to_delete;

delete from rotable_inventory where oid = :ls_oid_to_delete;

Insert into Inv_Move_log (inv_oid, to_inv_status_oid, to_on_aircraft_oid,to_city_oid,  create_Date, inv_move_date, From_serial_no, To_serial_no, from_item_oid, to_item_oid, Updated_by_sym, upd_timestamp)
                                     Values(:ls_oid_to_keep, :to_status_oid, :to_aircraft_oid, :to_city_oid, getutcdate(), :inv_move_date, :from_sn, :to_sn, :from_item_oid, :to_item_oid, :g_login_id, getutcdate());
MessageBox("Merge","Merge is Complete. Database has been updated.")
Close(w_inventory_merge)
0
Comment
Question by:Namik48075
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 

Author Comment

by:Namik48075
ID: 17042742
I have changed it to be done with Stored procedure.
Now its giving me an error message

//With an embedded SQL

//cb_merge.Clicked()
//===================
Long ls_oid_to_delete, ls_oid_to_keep, to_item_oid, from_item_oid
int  to_aircraft_oid, to_city_oid, to_status_oid


//temp_keep = sle_1.text
//temp_delete   = sle_2.text

ls_oid_to_keep = long(sle_1.text)
ls_oid_to_delete = long(sle_2.text)

to_city_oid = dw_1.getItemNumber(1, 'city_oid')
to_aircraft_oid = dw_2.getItemNumber(1, 'aircraft_oid')
to_status_oid = dw_3.getItemNumber(1, 'status_oid')

//IF SQLCA.SQLCode <> 0 THEN
//   MessageBox ( "Error", "DECLARE failed" )
//   RETURN
//END IF;
DECLARE test_inventory_merge procedure for sp_inventory_merge(:ls_oid_to_keep, :ls_oid_to_delete, :to_aircraft_oid, :to_status_oid, :to_city_oid) using SQLCA;

EXECUTE test_inventory_merge;
//IF  ( SQLCA.SQLCode <> 0   ) &
//and ( SQLCA.SQLCode <> 100 ) THEN
//   MessageBox ( "Error", "EXECUTE failed" )
//   RETURN
//END IF

MessageBox("Merge","Merge is Complete. Database has been updated.")


Compile Error:

Database C0038: Line 1: Incorrect syntax near '0';

Please help.

Thanks  a lot.
0
 
LVL 14

Accepted Solution

by:
sandeep_patel earned 255 total points
ID: 17079107
Hi,

I think it looks like database related problem....

1) check which driver you are using for connectivity.
2) is your stored procedure compiled and valid ?

in your code first comment stored procedure declaration and execution part and try to compile. If it compiles then it's sure that problem is in declaration and calling stored procedure..

Regards,
Sandeep
0
 
LVL 1

Expert Comment

by:sandhiyaa
ID: 17191601
Did you executed the procedure seperately at the backend?
Regards,
Sandhiya
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Programmer's Notepad is, one of the best free text editing tools available, simply because the developers appear to have second-guessed every weird problem or issue a programmer is likely to run into. One of these problems is selecting and deleti…
Update (December 2011): Since this article was published, the things have changed for good for Android native developers. The Sequoyah Project (http://www.eclipse.org/sequoyah/) automates most of the tasks discussed in this article. You can even fin…
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.
Suggested Courses
Course of the Month14 days, 4 hours left to enroll

800 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