Hi experts,
I’m new in Data Service. I have to extract some order numbers that exist in Excel file and delete them using an sql query. I extracted these numbers from the Excel file and load them into a flat file unsing a query. but i don’t konw how to put these numbers into the sql query in order to delete them. Do you have an idea about this process? how to use the sql query and put into the condition of delete the numbers?
Thank you for you response
Sara_nh (BOB member since 2013-08-13)
Hi,
You can achieve that by using Script/Variables/loop
What I would do:
WF 1:
-> Exctract data from the Excel file and load it to a temporary table.
In this table, define a primary key (integer auto increment) and the order number.
WF2:
-> Script to initalise variables
-> Get the number of records loaded (count from your temp table)
-> LOOP (i is a count)
-----> script: get the value of the order you cant to delete (you’ll have to do a query like select order_number from temp_table where pk=i) and use a variable to store the value
-----> Script: Delete your data from your target table using the value you get in step above
-----> increment i
And something like this should do the trick.
It’s not an easy way but it works!
cedrickb 
(BOB member since 2005-08-19)
Hi cedrickb,
Firtst thank you for your reply.
Could you please tell me how to write this script? I’m new to Data Services and i don’t realy konw how to use a script.
is the whole script is wrriten in sql? or there is a langage of Data services?
How can i get the number of records loaded?
Thank you for you help. I appreciate it .
Sara_nh (BOB member since 2013-08-13)
the script is not written in SQL.
You can get the number of record by doing a query “select count from table” and use a variable to store the value.
When you create a DF or WF, you can use a script.
The langage used is specific to data services. You should have a look to the developper’s guide for that and check specific functions like SQL().
And check the use of the variables and parameters in the guide, you’ll find all information you need.
Regards
cedrickb (BOB member since 2005-08-19)
Do not load them into a flat file, load them into a database table first.
Then in a script use the sql function in Data Services after you have populated this “Excel” table to delete data from the “other” table. e.g.
sql('DATASTORE','DELETE FROM othertable WHERE othertable.ordernumber IN (SELECT ordernumber FROM exceltable)')
However I would typically insert these rows into an archive table somewhere, before they are deleted from the othertable just to be safe and timestamp when this happened. You can do this in a dataflow with a normal Query Transform, joining the othertable to the excel table.
Check out the Technical manual for more help on the sql function.
Darth Services 
(BOB member since 2007-11-20)
Hi,
Thank you both cedrickb and Darth Services for your responses. I will try your solutions and tell you the results.
Have a nice day
Thank you
Sara_nh (BOB member since 2013-08-13)
You could use the map operation to turn the rows from the spreadsheets into delete operations and then in the target table object specify that it needs to “use input primary key” so that it knows how to find the row to delete in the target table.
eganjp 
(BOB member since 2007-09-12)
Seconding Jim’s suggestion. Using script-heavy approaches isn’t leveraging BODS’ native abilities.
JeffPrenevost (BOB member since 2010-10-09)