Update where dataid in(...)

Guillaume_Bonnet

Hi,

I'm not an Oracle expert, and I have some trouble doing what I want.

I have a webreport listing forms (SQL table revision mechanism). I added a checkbox for each row, and user can check some of them, choose a form field and its new value in order to update it for all forms.

This html form calls the following livereport :

%1 = InsertString : the dataid list, something like (7868, 98876, 98756, 89768)

%2 = InsertString : the form field that has to be modified

%3 = String : the new value for that field.

SQL : update Z_DP_SPEC set %2=%3 where dataid in %1

(Z_DP_SPEC is the database table associated to the form template of all my forms)

This works, but I'd like this operation to update only the last version of each form. The report should be performing something like

"For each dataid in %1, update update Z_DP_SPEC set %2=%3 where dataid=dataid and versionnum = max(versionnum)"

Does anybody have an idea of how to do this ?

Thanks for your help,

Guillaume

Lindsay_Davies

Hi Guillaume,

 

You can’t do an update of rows based on pairs of values in one in-list.

So this sort of thing

       Update table set col2=x where column3 in ( DataID1+Version1, DataID2+Version2,…)

will NOT work.

 

You would need to know a unique key to the row to update and use that in an in-list that will be built by matching DTree.DataID and DTree.VersionNum.

A sub-select would be a good way to do it.

 

Assuming your custom table Z_DP_SPEC stores a row per dataid + version (matching the rows in DTree and DVersData)  then it will be possible to do this but your statement may need restructuring – it depends on your table design.

 

You would need a unique key value for the row in Z_DP_SPEC like we have in DVersData, that identifies a version of a dataid.

 

I can demonstrate with the single rows of DTree and multiple rows for versions in DVersData.

 

   update dversdata set col=”ABC” where versionid in

                  (select versionid from dversdata dv, dtree d where d.dataid=dv.docid and d.versionnum=dv.version

                          and d.dataid in ( <list of values from current selection> )

                   )

 

There are many ways to do it, this example seems OK to me – but is not necessarily the best.

Regards

Lindsay

 

OpenText UK

European Escalation Team

 

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 2012 July 12, Thu 19:05
To: eLink Recipient
Subject: Update where dataid in(...)

 

Update where dataid in(...)

Posted byguillaume.bonnet@logica.com (Bonnet, Guillaume) On 07-12-2012 13:58

 

Hi, I'm not an Oracle expert, and I have some trouble doing what I want. I have a webreport listing forms (SQL table revision mechanism). I added a checkbox for each row, and user can check some of them, choose a form field and its new value in order to update it for all forms. This html form calls the following livereport : %1 = InsertString : the dataid list, something like (7868, 98876, 98756, 89768) %2 = InsertString : the form field that has to be modified %3 = String : the new value for that field. SQL : update Z_DP_SPEC set %2=%3 where dataid in %1 (Z_DP_SPEC is the database table associated to the form template of all my forms) This works, but I'd like this operation to update only the last version of each form. The report should be performing something like "For each dataid in %1, update update Z_DP_SPEC set %2=%3 where dataid=dataid and versionnum = max(versionnum)"! Does anybody have an idea of how to do this ? Thanks for your help, Guillaume

---

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center