I was given a report to use with our database, and I'm getting errors. The report query uses an OUTER APPLY, and this is the first I've worked with them.
Here's the query:
DECLARE @begin_process_date datetime, @end_process_date datetime
SELECT @begin_process_date = '9/1/2009', @end_process_date = '9/30/2009'
Select
Q.*,
lpc.counter
From
(
Select
t.location_id,
(count(td.tran_detail_id) * 1.1) as adjusted_sales,
case
when (count(td.tran_detail_id) * 1.1) <= 220 then 1
else CEILING((count(td.tran_detail_id) * 1.1)/200) end as label_count,
so.old_agent_id,
UPPER(so.sales_outlet_name) as sales_outlet_name,
UPPER(a.raw_address_1) as raw_address_1,
a.raw_address_2,
a.raw_city,
a.raw_state,
a.raw_zip,
'USA' as country,
'GROUND' as pfbc_service,
'PREPAID' as pfbc_option,
'PACKAGE' as pfbc_package,
'31' as pfbc_weight
From
ALSReplicator.TB_ORA_tran t
INNER JOIN ALSReplicator.TB_ORA_tran_detail td
ON t.tran_ref_id = td.tran_ref_id
INNER JOIN ALSReplicator.TB_ORA_vw_repl_location l
ON t.location_id = l.location_id,
ALSReplicator.address a,
ALSReplicator.address_usage_index aui,
ALSReplicator.sales_outlet so,
ALSReplicator.terminal tt
Where
t.tran_status = 'C' and
t.test_tran = 'N' and
td.tran_status in ('C','I') and
t.process_date >= @begin_process_date and
t.process_date < dateadd(dd, 1, @end_process_date)and
(td.priv_code between 101 and 134) and
Right( Convert( varchar, Convert( Decimal(10, 0 ), t.location_id ) ), 5 ) = tt.terminal_key and
a.address_key = aui.address_key and
aui.entity_type = 'SLSO' and
aui.entity_type = 'SLSO' and
aui.address_type = 'STRT' and
so.sales_outlet_key = aui.entity_key and
aui.address_key = a.address_key and
so.sales_outlet_key = tt.sales_outlet_key
-- and
-- t.location_id not in (@test_lid)
Group By t.location_id, so.old_agent_id, so.sales_outlet_name, a.raw_address_1,
a.raw_address_2, a.raw_city, a.raw_state, a.raw_zip) AS Q
OUTER APPLY udf_GetLabelPrintCounter(Q.location_id, Q.label_count) AS lpc ' Line 57 <-----
Where Q.location_id = lpc.LocID
I get the message:
Msg 102, Level 15, State 1, Line 57
Incorrect syntax near '.'.