Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
How to select a range of columns for an update? (Read 6929 times)
alupasco
YaBB Newbies
*


I Love SQL*XL

Posts: 3
How to select a range of columns for an update?
03.10.07 at 18:38:55
 
Hi,  
 
I am evaluating the SQL*XL.  
 
We need  
1) to download purchase orders info from Oracle tables to MS Excel.  
2) to update some attributes (descriptive fields)  
3) to upload updated attributes back to Oracle  
 
We must not update all columns.  
 
How to select a range of columns for an update?  
 
Best Regards,  
Andrei
Back to top
 
 
Email   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: How to select a range of columns for an update
Reply #1 - 04.10.07 at 15:37:00
 
All columns you retrieve will be checked whether an update is necessary. SQL*XL will only post those changes that you have ok-ed. If you don't want SQL*XL to check certain columns, don't retrieve them.  
 
You probably now do a select * from invoices retrieving all columns. You can also do a more restrictive select of the columns like select id, description1, description2, description3 from invoices to only update these columns. Again only updates are made to fields that you have changed and then only if you instruct SQL*XL explicitly to make the change.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
alupasco
YaBB Newbies
*


I Love SQL*XL

Posts: 3
Re: How to select a range of columns for an update
Reply #2 - 04.10.07 at 16:52:48
 
Dear Gerrit-Jan,
 
What to do if users need more columns for analysis?
 
For instance,  
 
We download PO (purchase orders)  info:
1) PO Number
2) PO Line Number
3) PO Distribution Number
4) Description
5) Quantity
6) Price
7) Need By Date
8) Promissed Date
 
Sometimes, PO is approved 10 days later of its creation.
Our buyers need all the columns above for analysis,  
but they should update the Promissed Date column (only).
For instance, they should add 10 days to the initial promissed date.
 
To tell more, the 7 columns mentioned above are from 3 different tablea  
(PO_HEADERA, PO_LINES, PO_DISTRIBUTIONS).
 
What would you recommend to do?
 
Best Regards,
Andrei
Back to top
 
 
Email   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: How to select a range of columns for an update
Reply #3 - 04.10.07 at 22:19:05
 
In this case I would go for the following solution:
 
Make a spreadsheet where  you reserve a few columns to dump the basic PO information. Retrieve the PO number and the columns you need to update in these columns.
 
Then in the columns next to this data use the SQLQueryValue formula to retrieve the additional info. For example if you would put the PO number in column A you could use the following array formula in e.g. D2:F2  =SQLQueryValue("select description, quantity, price from PO_lines where po_number = " & A2)
 
Now copy this formula into all the cells you need to view this information.  
You end up with having the queried columns you can update and linked to it by the po number the informative data you need to make your updates.
 
Array formulas are somewhat advanced. I suggest you use the SQLQueryValue function first to get 1 value. Then read the documentation of the SQLQueryValue function on how to use it as an array function. Basically the array function is one function (1 SQL query execution!) to populate multiple cells.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
alupasco
YaBB Newbies
*


I Love SQL*XL

Posts: 3
Re: How to select a range of columns for an update
Reply #4 - 08.10.07 at 11:02:43
 
Dear Gerrit-Jan,  

 
Thank you very much for the the SQLQueryValue function. It is working fine Smiley .
 
Could you please help us with parameters.
 
 
We should set the following parameters of download:
 
     Parameter 1 (mandatory) Buyer
     Paramter 2/3 (manadatory) Encumbrance Date From/To  
     Parameter 4 (optional) Vendor (LOV)
     Paramter 5/6 (optional) PO From/To  
 
I have defined the Encumbrance Date From/To parameters as Bind and Date.
It is working, but I have to re-define it as Date every time Sad I open the saved spreadsheet and refresh it.
How to save parameters (including data type)?
 
 
Another issue is the Vendor parameter
Vendor ID is stored in the PO Headers table.
If I include the PO Headers table in the select, I will not be able to update the PO Distributions undecided.
 
What would you recommend to do?  
 
Also, I need a new Activation code to continue the evaluation and prepare a demo spreadsheet for our Purchasing team.
 
Best Regards,  
Andrei
Back to top
 
 
Email   IP Logged
Pages: 1