Below is the SQL which i used to run as a free hand sql and send the report to the end-user .But now he wants the universe for this.
There is a view with all the conditions below except the
d.dt_time_date_created = select max(g.dt_date_time_created…)to have access to invoice_dim and Sales_facts ,as they are on different database.
But My question is ,how to give this condition?
select a.tx_account_no,
a.tx_salesorder_no ,
to_char(c.dt_date_time_created, 'MM/DD/YYYY HH:MI:SS AM') as "RELEASED TO WHSE",
to_char(d.dt_date_time_created, 'MM/DD/YYYY HH:MI:SS AM') as "PICK COMPLETE" ,
round(((d.dt_date_time_created - c.dt_date_time_created) * 24 * 60), 2) as "PROCESSING TIME (MIN)", e.cd_carrier,
count(id_order_line),
id_order||id_order_suffix,
id_invoice,
dt_invoice
from orders a,
edi_document b,
detail_status c,
detail_status d,
invoice_dim@pdss01 e,
sales_facts@pdss01 f
where b.nm_order_id = a.nm_order_id
and c.tx_status_doc_id = b.tx_status_doc_id
and d.tx_status_doc_id = b.tx_status_doc_id
and a.tx_account_no like '54%'
and e.id_division like '54%'
and e.id_order like substr(a.tx_custpo_no, 1, 5 )
and e.cd_carrier = 'HS'
and b.tx_doc_type = 'CSW_3PL_I940'
and c.nm_application_id = 9
and c.nm_detail_status_id = 2002
and d.nm_application_id = 9
and d.nm_detail_status_id = 2012
and e.ky_invoice = f.ky_invoice
and e.dt_invoice = f.ky_time
and e.dt_invoice >= '21-jun-2004'
and c.dt_date_time_created >= '21-jun-2004'
and d.dt_date_time_created =
(select max(g.dt_date_time_created)
from orders e, edi_document f, detail_status g
where f.nm_order_id = e.nm_order_id
and g.tx_status_doc_id = f.tx_status_doc_id
and f.tx_doc_type = 'CSW_3PL_I940'
and g.nm_application_id = 9
and g.nm_detail_status_id = 2012
and e.nm_order_id = a.nm_order_id)
group by a.tx_account_no, c.dt_date_time_created, a.tx_salesorder_no,
d.dt_date_time_created,e.cd_carrier,id_order||id_order_suffix, id_invoice, dt_invoice
Usually one can solve this with a subquery in BO. Since the outcome of the part above will just be one value this can be an non-correlated subquery too.
Query panel allows you to build subqueries, but this is possibly a bit too much for an average user…
We have a few reports where we need the max date per month, for any/every month that has data, per customer per whatever.
To keep these queries fast we create a separate table/view in the DB with only the max dates for the specific query that we need, join it to the main table, and then force a join in the query by selecting an object from the new table, or a Condition object “WHERE 1=1”, with the max_date table selected as the table to work with.
d.dt_date_time_created =
(select max(g.dt_date_time_created)
from orders e, edi_document f, detail_status g
where f.nm_order_id = e.nm_order_id
and g.tx_status_doc_id = f.tx_status_doc_id
and f.tx_doc_type = 'CSW_3PL_I940'
and g.nm_application_id = 9
and g.nm_detail_status_id = 2012
and e.nm_order_id = a.nm_order_id)
One option: Create this as a predefined condition at the universe level.
With all due respect JP, I do believe BusObj (full client at least) can do this kind of sub-query. In the query panel, select the object, select equal to as the operator, and for the operand select Calculation. That will start a wizard that will create the sub-query. Ties Blom gave a bit of warning, that this calculation wizard does take an intermediate to advanced level of user, but it is quite powerful and can do the SELECT max(DATE) … functionality for sure.
Thanks for the info, Dwayne! It’s great to learn something new. (I edited my previous post so no one later would read wrong info; and also to correctly state what we are doing in our universes.)
your initial reply got me testing back in version 5 again …
None of my users feel comfortable with the calculation wizard, so I create aggregate objects in the universes on demand. Even then, subqueries takes a bit of insight into SQL to fully comprehend…
(BOB member since 2002-09-04)
(BOB member since 2002-10-22)
(BOB member since 2002-06-20)
(BOB member since 2002-09-19)
(I edited my previous post so no one later would read wrong info; and also to correctly state what we are doing in our universes.)
(BOB member since 2002-09-04)