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
