Ben,
Bills post didn't have anything to do with Select actions vs had
written SQL in DirectDBMS actions
his complaint, ( a very valid one) seems to be that assigning the
[EMAIL PROTECTED] array to a method scoped variable, causes the zero
row to be lost... a serious problem if you write good object
oriented code, and don't want every database query to end up as a
global variable.
/John
On Tue, Apr 1, 2008 at 2:30 AM, Ben Johansen <[EMAIL PROTECTED]> wrote:
I think this has been discussed and hashed out several times on the
list.
the is due as you state at the end to directDBMS actions, as as Search
action will always return the zero row.
The main reason is the directDBMS action is not just for straight sql
with with nice know column names.
Not sure if this will ever be considered broken because of the coding
that would have to be done to determine what to put in the 0 row could
hinder performance.
I have found in directdbms action to put "as" designators on ambiguous
columns
for example:
SELECT orderitem.OrderItemId as orderitemid,
orderitem.OrderId as order_number,
pd_vendor_tracking.po_number as po_number,
orderitem.itemstatusid as order_item_status,
luorderstatus.status as order_item_status_name,
date(orderitem.cancel_date) as cancel_date,
coalesce(unix_timestamp(orderitem.cancel_date),-1) as
cancel_date_nbr,
coalesce(unix_timestamp(cancelled_order_items.process_date),-1) as
process_date_nbr,
left(bolinf_customer_list_int.campaign_id,2) as campaign_id,
orderhdr.account_number as account_number,
orderhdr.Name as customer_name,
case when isnull(pd_vendor_tracking.line_id) then
products.vendor
else
pd_vendor_tracking.vendor
end as vendor,
pd_vendor_tracking.status as vendor_status,
products.name as product_name,
products.sku as product_sku,
products.price as product_sell_price,
products.shipprice as product_ship_price,
products.cogs as product_cogs,
orderitem.replacesku as replacement_sku,
orderitem.replacecogs as replacement_cogs,
subcategories.name as subcategory_name,
date(pd_settlement.settlement_date) as settlement_date,
date(orderitem.vendor_po_date) as vendor_po_date,
date(orderitem.vendor_payment_date) as vendor_payment_date,
case when
(coalesce(unix_timestamp(orderitem.vendor_po_date),-1) < 1)
then 'BEFORE PO'
when pd_vendor_tracking.status = 'REJECTED' then 'EDI
PO/REJECTED'
when (length(coalesce(orderitem.tracking_num,'')) <
1) then 'AFTER
PO BEFORE ASN'
when
(coalesce(unix_timestamp(pd_settlement.settlement_date),-1) <
1) then 'AFTER ASN BEFORE SETTLEMENT'
else 'AFTER ASN/SETTLEMENT' end AS cancel_timing,
pd_vendor_tracking.status as edi_po_status,
date(orderitem.shipdate) as ship_date,
orderitem.tracking_num as tracking_number,
orderitem.cancel_reason as cancel_reason,
pd_cancellation_reason.description as
cancel_reason_description,
ordernotes.note as dashboard_comment,
cancelled_order_items.id,
cancelled_order_items.order_item_id,
after_settled_dispositions.after_settled_disposition AS
delivery_status,
cancelled_order_items.delivery_date,
cancelled_order_items.delivery_stat_check_date,
cancelled_order_items.base_prod_orderno,
cancelled_order_items.first_vendor_ra_req_date,
cancelled_order_items.last_vendor_ra_req_date,
cancelled_order_items.ra_req_count,
cancelled_order_items.vendor_ra_rcvd_date,
cancelled_order_items.vendor_ra_number,
cancelled_order_items.vendor_ra_exp_date,
cancelled_order_items.ra_labels_to_cust_date,
cancelled_order_items.ra_track_number,
cancelled_order_items.last_date_ra_track_checked,
cancelled_order_items.prod_back_at_vendor_date,
cancelled_order_items.first_vendor_cm_req_date,
cancelled_order_items.last_vendor_cm_req_date,
cancelled_order_items.vendor_cm_req_count,
cancelled_order_items.vendor_cm_date,
cancelled_order_items.vendor_cm_number,
cancelled_order_items.vendor_cm_amount,
cancelled_order_items.prod_retuned_to_pear_date,
cancelled_order_items.pear_inventory_number,
cancelled_order_items.system_rma_date,
cancelled_order_items.system_rma_line_id,
cancelled_order_items.system_rma_order_nbr,
cancelled_order_items.rma_tax,
cancelled_order_items.restocking_20per_fee,
cancelled_order_items.net_rma_amount,
cancelled_order_items.process_staff_member,
cancelled_order_items.rma_book_date,
cancelled_order_items.cust_keep_confirm_date,
cancelled_order_items.oper_approved_credit_date,
cancelled_order_items.finance_applied_date,
cancelled_order_items.ge_settlement_date,
case when cancelled_order_items.vendor_cm_missing_indicator =
0 then
'N'
when
cancelled_order_items.vendor_cm_missing_indicator = 1 then 'Y'
else 'N' end AS vendor_cm_missing_indicator,
temp_credit_dispositions.temp_credit_disposition AS
temp_credit_indicator,
cancelled_order_items.followup_notes,
cancelled_order_items.base_product_order_status,
cancelled_order_items.ra_label_expire_date,
cancelled_order_items.tn_status,
cancelled_order_items.process_date,
cancelled_order_items.last_updated,
orderitem.UnitPrice as unit_selling_price,
orderitem.shipPrice as unit_ship_price,
orderitem.tax_amount as unit_tax_amount,
cancelled_order_items.rma_product_price,
cancelled_order_items.rma_ship_price,
cancelled_order_items.shipping_status,
case when cancelled_order_items.urgent_approv_credit_ind = 0
then 'N'
when cancelled_order_items.urgent_approv_credit_ind =
1 then 'Y'
else 'N' end AS urgent_approv_credit_ind,
orderitem.parentid,
products.categoryCode,
pd_warranty_status.status as warranty_status
FROM orderitem
LEFT OUTER JOIN orderhdr ON orderitem.orderid =
orderhdr.orderid
LEFT OUTER JOIN bolinf_customer_list_int ON
orderhdr.certification_id
= bolinf_customer_list_int.certification_id
left join products on case when isnull(orderitem.replacesku)
then
orderitem.productsku when orderitem.replacesku = '' then
orderitem.productsku else orderitem.replacesku end = products.sku
LEFT OUTER JOIN pd_settlement ON orderitem.orderitemid =
pd_settlement.orderitemid
LEFT OUTER JOIN subcategories ON products.subcategorycode =
subcategories.code
left outer join luorderstatus on orderitem.itemstatusid =
luorderstatus.orderstatusid
left outer join pd_cancellation_reason on
orderitem.cancel_reason =
pd_cancellation_reason.cancel_reason_code
left outer join cancelled_order_items on
orderitem.orderitemid =
cancelled_order_items.order_item_id
left outer join pd_vendor_tracking on orderitem.orderitemid =
pd_vendor_tracking.orderitemid
left outer join ordernotes on orderitem.orderid =
ordernotes.orderid
and left(ordernotes.note,11) = '[Cancelled]'
left outer join after_settled_dispositions on
after_settled_dispositions.after_settled_id =
cancelled_order_items.delivery_status
left outer join temp_credit_dispositions on
temp_credit_dispositions.temp_credit_id =
cancelled_order_items.temp_credit_indicator
left outer join pd_warranty_status on orderitem.orderitemid =
pd_warranty_status.orderitemid
WHERE date(orderitem.cancel_date) >= date('2007-05-04')
group by orderitem.orderitemid order by orderitem.orderitemid
I have no problem getting row 0 to populate using this select in a
directDBMS.
Ben
On Apr 1, 2008, at 12:11 AM, William M Conlon wrote:
> I would really like to see Row 0 consistently implemented. I do all
> my Database actions in methods, so to return an array, I assign the
> resultset to a method scope variable. I've found that row 0 is NOT
> returned if I merely
>
> <@ASSIGN SCOPE="method" NAME="array" VALUE="@@request$resultset">
>
> Instead, I must explicitly assign row 0:
>
> <@ASSIGN SCOPE="method" NAME="array[0,*]" VALUE="@@request
> $resultset[0,*]">
> <@ASSIGN SCOPE="method" NAME="array[1,*]" VALUE="@@request
> $resultset[1,*]">
>
> Perhaps this will be fixed in v6 as as a natural consequence of
> allowing DBMS actions to be assigned to @@method$resultset.
>
> Bill
>
> William M. Conlon, P.E., Ph.D.
> To the Point
> 2330 Bryant Street
> Palo Alto, CA 94301
> vox: 650.327.2175 (direct)
> fax: 650.329.8335
> mobile: 650.906.9929
> e-mail: mailto:[EMAIL PROTECTED]
> web: http://www.tothept.com
>
>
________________________________________________________________________
> TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
>
________________________________________________________________________
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
--
/John
________________________________________________________________________
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
