Issue with Cross join query on reporting services 2008

[stephane michaud]

Hello,
i have an issue with a cross join query.
Here is the query i run with winsql, and it's working:

select date_v.cal_dt ,count(location_v.cmc_str_nbr)
from date_v
cross join location_v
left join order_v
on date_v.cal_dt = order_v.cal_dt and order_v.cmc_chn_str_nbr = location_v.cmc_chn_str_nbr
where (date_v.cal_dt between now()-8 and now() -1)
and order_v.cmc_chn_str_nbr is null
and location_v.str_live_dt is not null and location_v.str_deinstall_dt is null
and location_v.str_typ_cd in (0) and location_v.str_rdir_flg = 'Y'
and location_v.cmc_chn_nbr in (1,2,22,24,65,86,87)
group by date_v.cal_dt
order by date_v.cal_dt


when i put it on reporting services, it converts me like below and it's not weorking

SELECT admin.date_v.cal_dt, admin.location_v.cmc_chn_str_nbr
FROM admin.location_v, { oj admin.date_v LEFT OUTER JOIN
admin.order_v ON admin.date_v.cal_dt = admin.order_v.cal_dt AND admin.order_v.cmc_chn_str_nbr = admin.location_v.cmc_chn_str_nbr }
WHERE (admin.date_v.cal_dt BETWEEN NOW() - 8 AND NOW() - 1) AND (admin.order_v.cmc_chn_str_nbr IS NULL) AND (admin.location_v.str_live_dt IS NOT NULL) AND
(admin.location_v.str_deinstall_dt IS NULL) AND (admin.location_v.str_typ_cd IN (0)) AND (admin.location_v.str_rdir_flg = 'Y') AND
(admin.location_v.cmc_chn_nbr IN (1, 2, 22, 24, 65, 86, 87))
ORDER BY admin.date_v.cal_dt

it give me this error:
ERROR [HY000] ERROR: Table name "pncfre1r.admin.location_v" not listed in the FROM-clause

Do you know how i could use the cross join query?
thanks a lot for your help

[Parker]

One way would be to create a stored procedure on the server using this
code, and then call the stored procedure from the report.

This is a good practice for a number of reasons, so it may be worth
your learning this technique.

If you don't have server access, you may be able to write the SQL as a
string and then run it with EXEC or sp_executesql - but I don't know
if either of those will run from the Reporting Services query editor.

The documentation (or Google) should give you the examples you need.

On Jul 1, 5:55 am, stephane michaud

Post by stephane michaud
Hello,
i have an issue with a cross join query.
select date_v.cal_dt ,count(location_v.cmc_str_nbr)
from date_v
cross join location_v
left join order_v
on date_v.cal_dt = order_v.cal_dt and order_v.cmc_chn_str_nbr = location_v.cmc_chn_str_nbr
where (date_v.cal_dt between now()-8 and now() -1)
and order_v.cmc_chn_str_nbr  is null
and location_v.str_live_dt is not null and location_v.str_deinstall_dt is null
and location_v.str_typ_cd in (0) and location_v.str_rdir_flg = 'Y'
and location_v.cmc_chn_nbr in (1,2,22,24,65,86,87)
group by date_v.cal_dt
order by date_v.cal_dt
when i put it on reporting services, it converts me like below and it's not weorking
SELECT        admin.date_v.cal_dt, admin.location_v.cmc_chn_str_nbr
FROM            admin.location_v, { oj admin.date_v LEFT OUTER JOIN
                         admin.order_v ON admin.date_v.cal_dt = admin.order_v.cal_dt AND admin.order_v.cmc_chn_str_nbr = admin.location_v.cmc_chn_str_nbr }
WHERE        (admin.date_v.cal_dt BETWEEN NOW() - 8 AND NOW() - 1) AND (admin.order_v.cmc_chn_str_nbr IS NULL) AND (admin.location_v.str_live_dt IS NOT NULL) AND
                         (admin.location_v.str_deinstall_dt IS NULL) AND (admin.location_v.str_typ_cd IN (0)) AND (admin.location_v.str_rdir_flg = 'Y') AND
                         (admin.location_v.cmc_chn_nbr IN (1, 2, 22, 24, 65, 86, 87))
ORDER BY admin.date_v.cal_dt
ERROR [HY000] ERROR:  Table name "pncfre1r.admin.location_v" not listed in the FROM-clause
Do you know how i could use the cross join query?
thanks a lot for your help

[stephane michaud]

Hello,

Thanks for your reply.
I don't have access, i use visual studio 2008 and then deploy report on reporting services server.

i can try stored procedure.

thanks

[stephane michaud]

Some news,
i have found how to do without a cross join
and that works fine
SELECT TO_CHAR(cal_dt,'YYYY-MM-DD') AS PREPROCESS_DT,CHN_NM, SUM(TOTAL_STR_RECEIVED) AS COUNT_STORE
FROM (SELECT TO_CHAR(FRDLOG.TRAN_LOG_T.RECV_TMS, 'YYYY-MM-DD') AS RECV_TMS,
RTLDIR.PARENT_CORP_T.PARENT_CORP_NM AS CHN_NM,
COUNT(FRDLOG.TRAN_LOG_T.STR_NBR) AS TOTAL_STR_RECEIVED
FROM RTLDIR.PARENT_CORP_T, "MASTER".CHN_SPEC_T, FRDLOG.TRAN_LOG_T
WHERE RTLDIR.PARENT_CORP_T.PARENT_CORP_KEY = "MASTER".CHN_SPEC_T.PARENT_CORP_KEY AND
"MASTER".CHN_SPEC_T.CUSTOM_GRP_CHN_NBR = FRDLOG.TRAN_LOG_T.CHN_NBR
AND (TO_CHAR(FRDLOG.TRAN_LOG_T.RECV_TMS, 'YYYY-MM-DD')

TO_CHAR(SYSDATE - 9, 'YYYY-MM-DD')) and FRDLOG.TRAN_LOG_T.CHN_NBR in (30,31,32,34,35,50)

GROUP BY FRDLOG.TRAN_LOG_T.RECV_TMS, RTLDIR.PARENT_CORP_T.PARENT_CORP_NM) DERIVEDTBL_1
right join master.date_t
on TO_CHAR(cal_dt,'YYYY-MM-DD') = RECV_TMS
where TO_CHAR(cal_dt,'YYYY-MM-DD')between TO_CHAR(SYSDATE-8 , 'YYYY-MM-DD') and TO_CHAR(SYSDATE,'YYYY-MM-DD')
GROUP BY cal_dt, CHN_NM
ORDER BY cal_dt,CHN_NM

[stephane michaud]

hello
i have found how to do without cross join. that works fine like that

SELECT TO_CHAR(cal_dt,'YYYY-MM-DD') AS PREPROCESS_DT,CHN_NM, SUM(TOTAL_STR_RECEIVED) AS COUNT_STORE
FROM (SELECT TO_CHAR(FRDLOG.TRAN_LOG_T.RECV_TMS, 'YYYY-MM-DD') AS RECV_TMS,
RTLDIR.PARENT_CORP_T.PARENT_CORP_NM AS CHN_NM,
COUNT(FRDLOG.TRAN_LOG_T.STR_NBR) AS TOTAL_STR_RECEIVED
FROM RTLDIR.PARENT_CORP_T, "MASTER".CHN_SPEC_T, FRDLOG.TRAN_LOG_T
WHERE RTLDIR.PARENT_CORP_T.PARENT_CORP_KEY = "MASTER".CHN_SPEC_T.PARENT_CORP_KEY AND
"MASTER".CHN_SPEC_T.CUSTOM_GRP_CHN_NBR = FRDLOG.TRAN_LOG_T.CHN_NBR
AND (TO_CHAR(FRDLOG.TRAN_LOG_T.RECV_TMS, 'YYYY-MM-DD')

TO_CHAR(SYSDATE - 9, 'YYYY-MM-DD')) and FRDLOG.TRAN_LOG_T.CHN_NBR in (30,31,32,34,35,50)

GROUP BY FRDLOG.TRAN_LOG_T.RECV_TMS, RTLDIR.PARENT_CORP_T.PARENT_CORP_NM) DERIVEDTBL_1
right join master.date_t
on TO_CHAR(cal_dt,'YYYY-MM-DD') = RECV_TMS
where TO_CHAR(cal_dt,'YYYY-MM-DD')between TO_CHAR(SYSDATE-8 , 'YYYY-MM-DD') and TO_CHAR(SYSDATE,'YYYY-MM-DD')
GROUP BY cal_dt, CHN_NM
ORDER BY cal_dt,CHN_NM