rows[0]["siteid"] rows[1]["siteid"] what does it mean

the below query is taken from maximo out of box rptdesign file "" rfq_vendor_comparision.rptdesign ""

sqlText = " select rfqvendor.vendor, coalesce(quotationline.unitcost,0) unitcost, coalesce(quotationline.orderqty,0) orderqty, "
+ " quotationline.orderunit, coalesce(quotationline.linecost,0) linecost, rfqvendor.currencycode from rfq "
+ " inner join rfqline on rfq.rfqnum = rfqline.rfqnum and rfq.siteid = rfqline.siteid "
+ " inner join rfqvendor on rfq.rfqnum = rfqvendor.rfqnum and rfq.siteid = rfqvendor.siteid "
+ " inner join quotationline on rfq.rfqnum = quotationline.rfqnum and rfq.siteid = quotationline.siteid "
+ " and rfqline.rfqlinenum = quotationline.rfqlinenum and rfqvendor.vendor = quotationline.vendor "
+ " where rfq.rfqnum = '" + rows[1]["rfqnum"] + "' "
+ " and quotationline.itemnum = '" + rows[2]["itemnum"] + "'"
;

what does row[1]["rfqnum"] and rows[2]["itemnum"] mean??

Find more posts tagged with

Comments

mwilliams

'rows' can be called from your dataSet script to access row data. So, most likely, they're just accessing fields called rfqnum and itemnum. If you attach the report, I can probably be more specific.

srikar

<blockquote class='ipsBlockquote' data-author="'srikar'" data-cid="110360" data-time="1349877466" data-date="10 October 2012 - 06:57 AM">
the below query is taken from maximo out of box rptdesign file "" rfq_vendor_comparision.rptdesign "" 
 
Main query 
 
sqlText = " select rfq.rfqnum, rfq.description, request.displayname as rfq_requestedby, " 
+ " purchase.displayname as rfq_purchaseagent, rfq.siteid, rfq.status, rfq.orgid from rfq " 
+ " inner join person request on rfq.requestedby = request.personid " 
+ " inner join person purchase on rfq.purchaseagent = purchase.personid " 
+ " where " + params["where"] 
+ " order by rfq.rfqnum" 
; 
 
Subdataset 
 
sqlText = " select rfqvendor.vendor, coalesce(sum(quotationline.linecost),0) total, rfqvendor.currencycode " 
+ " from rfq inner join rfqline on rfq.rfqnum = rfqline.rfqnum and rfq.siteid = rfqline.siteid " 
+ " inner join rfqvendor on rfq.rfqnum = rfqvendor.rfqnum and rfq.siteid = rfqvendor.siteid " 
+ " inner join quotationline on rfq.rfqnum = quotationline.rfqnum and rfq.siteid = quotationline.siteid " 
+ " and rfqline.rfqlinenum = quotationline.rfqlinenum and rfqvendor.vendor = quotationline.vendor " 
+ " where rfq.rfqnum = '" + rows[0]["rfqnum"] + "' " 
+ " group by rfqvendor.vendor, rfqvendor.currencycode order by rfqvendor.vendor " 
; 
 
Subdataset1 
sqlText = " select distinct rfqline.commoditygroup, rfq.rfqnum " 
+ " from rfq inner join rfqline on rfq.rfqnum = rfqline.rfqnum and rfq.siteid = rfqline.siteid " 
+ " inner join rfqvendor on rfq.rfqnum = rfqvendor.rfqnum and rfq.siteid = rfqvendor.siteid " 
+ " inner join quotationline on rfq.rfqnum = quotationline.rfqnum and rfq.siteid = quotationline.siteid " 
+ " and rfqline.rfqlinenum = quotationline.rfqlinenum and rfqvendor.vendor = quotationline.vendor " 
+ " where rfq.rfqnum = '" + rows[0]["rfqnum"] + "' " 
+ " order by rfqline.commoditygroup " 
; 
 
 
Subdataset2 
 
sqlText = " select rfqvendor.vendor, coalesce(sum(quotationline.linecost),0) linecost, rfqline.commoditygroup " 
+ " from rfq inner join rfqline on rfq.rfqnum = rfqline.rfqnum and rfq.siteid = rfqline.siteid " 
+ " inner join rfqvendor on rfq.rfqnum = rfqvendor.rfqnum and rfq.siteid = rfqvendor.siteid " 
+ " inner join quotationline on rfq.rfqnum = quotationline.rfqnum and rfq.siteid = quotationline.siteid " 
+ " and rfqline.rfqlinenum = quotationline.rfqlinenum and rfqvendor.vendor = quotationline.vendor " 
+ " where rfq.rfqnum = '" + rows[1]["rfqnum"] + "'" 
; 
 
 
Subdataset3 
 
sqlText = "select rfqline.itemnum, rfqline.description, coalesce(rfqline.orderqty,0) orderqty, rfqline.orderunit, " 
+ " rfqline.commoditygroup from rfqline " 
+ " where rfqline.rfqnum = '" + rows[1]["rfqnum"] + "'" 
+ " and rfqline.siteid = '" + rows[0]["siteid"] + "'"; 
if (rows[1]["commoditygroup"] == null) { 
sqlText += " and rfqline.commoditygroup is null"; 
} else{ 
sqlText += " and rfqline.commoditygroup = '" + rows[1]["commoditygroup"] + "'"; 
} 
 
subdataset4 
 
sqlText = " select rfqvendor.vendor, coalesce(quotationline.unitcost,0) unitcost, coalesce(quotationline.orderqty,0) orderqty, " 
+ " quotationline.orderunit, coalesce(quotationline.linecost,0) linecost, rfqvendor.currencycode from rfq " 
+ " inner join rfqline on rfq.rfqnum = rfqline.rfqnum and rfq.siteid = rfqline.siteid " 
+ " inner join rfqvendor on rfq.rfqnum = rfqvendor.rfqnum and rfq.siteid = rfqvendor.siteid " 
+ " inner join quotationline on rfq.rfqnum = quotationline.rfqnum and rfq.siteid = quotationline.siteid " 
+ " and rfqline.rfqlinenum = quotationline.rfqlinenum and rfqvendor.vendor = quotationline.vendor " 
+ " where rfq.rfqnum = '" + rows[1]["rfqnum"] + "' " 
+ " and quotationline.itemnum = '" + rows[2]["itemnum"] + "'" 
; 
 
 
@mwilliums:: Due to security constraints i can not upload the rptfile..but please go thru the above sqlText and explain me...what does row[1]["rfqnum"] and rows[2]["itemnum"] mean?? </blockquote>

srikar

@mwilliums:: Due to security constraints i can not upload the rptfile..but please go thru the above sqlText and explain me...what does row[1]["rfqnum"] and rows[2]["itemnum"] mean??

mwilliams

Without seeing the setup, I can't be exact, but it appears that row[1]["rfqnum"] is in a dataSet that is bound to a table that is embedded at least 3 tables deep. It would appear it's pulling rfqnum from the second most outer table (the most outer would be [0]) and it's pulling itemnum from the 3rd most outer table, which would make it at least the 4th deepest element.

itemnum only shows in subdataSet3, so the table for subdataSet4 is embedded in subdataSet3's table which is embedded in subdataSet1's table, which is embedded in the main query's table. This is just my best guess, from looking at the queries and not seeing the layout, but it appears subdataSet4 pulls those field values from subdataSet1 and subdataSet3.

Hope this helps. What's your BIRT version? Maybe I can give you an example that might help show what's happening, using the sample database, if you need.

cjm13905

We are BIRT 3.7.1if you have multiple datasets maindataset, dataseta, datasetband your sql code references rows[0] how can you tell what dataset it is referring to ?Where is it defined that 0 refers to data set x ?

wwilliams

Each one of those datasets are "nested" e.g basically subreports you have the maindataset and each table down the hierarchy is dependent on the previous one. subData and 1,2,3,4 as Michael pointed out. If it still confusing add logging then maybe you can see visually what is happening it it will become clear.