January 30, 2008 at 10:54 am
I created a new view on a lab/test system with SSE 2005 (SP2) and can run a Crystal report against this data on this system without an error. However after creating the view in a test db on the live system with full MSSQL 2005 (SP2) I get the error multi-part identifier "ps_tkt_hist_tax.tkt_no" could not be bound. I then created the view on another lab/test system once again SSE 2005 (SP2) and the report runs without error there. All the searches I've run deal with MSSQL 2000. I get the same results whether the script used to create the view uses aliases or table names. Any help would be appreciated. Below is the script used to create the view.
CREATE view [dbo].[UVI_ACC_TAX_REPORT] as
selecth.str_id,
h.sta_id,
h.tkt_no,
h.tkt_dat,
h.post_dat,
h.sal_sub_tot,
h.sal_tot,
h.sal_tax_amt,
t.auth_cod,
t.rul_cod,
t.txbl_lin_amt,
t.txbl_misc_chg_amt_1,
t.txbl_misc_chg_amt_2,
t.txbl_misc_chg_amt_3,
t.txbl_misc_chg_amt_4,
t.txbl_misc_chg_amt_5,
t.txbl_gfc_amt,
t.txbl_tax_amt,
t.tax_amt,
t.norm_tax_amt,
t.txbl_svc_amt,
a.nam,
r.descr,
g.doc_bal
from ps_tkt_hist_tax t left outer join ps_tkt_hist h on t.tkt_no = h.tkt_no
left outer join sy_tax_auth a on t.auth_cod = a.auth_cod
left outer join sy_tax_auth_rul r on t.rul_cod = r.rul_cod
left outer join vi_apply_to_grps g on t.tkt_no = g.doc_no
where g.doc_bal is null or g.doc_bal = '0'
group by h.str_id,
h.sta_id,
h.tkt_no,
h.tkt_dat,
h.post_dat,
h.sal_sub_tot,
h.sal_tot,
h.sal_tax_amt,
t.auth_cod,
t.rul_cod,
t.txbl_lin_amt,
t.txbl_misc_chg_amt_1,
t.txbl_misc_chg_amt_2,
t.txbl_misc_chg_amt_3,
t.txbl_misc_chg_amt_4,
t.txbl_misc_chg_amt_5,
t.txbl_gfc_amt,
t.txbl_tax_amt,
t.tax_amt,
t.norm_tax_amt,
t.txbl_svc_amt,
a.nam,
r.descr,
g.doc_bal
February 1, 2008 at 12:09 pm
Curtis,
This is a shot in the dark.
1. Can your run the select in query analyzer in both environments no problem? If the answer is yes, then I wonder if this is on the Crystal reports side.
We have Cognos so they are not the same yet there are features in our data catalogs that disallow outer joins.
Does the Crystal data catalogs or what ever they are called have these types of options?
February 1, 2008 at 12:30 pm
J
Thanks for the response.
- yes I can run the select in both verions of SQL
- yes Crystal does allows all types of joins
Any other ideas? All the searches for this probelm point to a bug in SQL 2000 not 2005 when using order by or gropu by clauses. My next thought is to put this view into a SProc and let the Crystal report call the SProc.
Curtis
February 1, 2008 at 12:36 pm
The other thing you could try is making sure the domain of the tables is included in the query. Not sure if that'll matter in your case, but I've seen Crystal do weird things on that before.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 1, 2008 at 12:48 pm
Curtis,
You have four left outer joins. So all rows on the left table are brought back regardless if they match the right table
ps_tkt_hist_tax is left joining ps_tkt_hist on tkt_no field
ps_tkt_hist_tax is left joining sy_tax_auth on auth_cod
ps_tkt_hist_tax is left joining sy_tax_auth_rul on rul_cod
ps_tkt_hist_tax is left joining vi_apply_to_grps doc_no via tkt_no
Two things pop in my mind just to confirm the query...
Do a simple LEFT OUTER JOIN on ps_tkt_hist_tax and vi_apply_to_grps on tkt_no = doc_no
See if this gets you the desired results. I have a funny feeling your multi- error may be caused by the fourth outer join.
The second thing is trying adding a HAVING clause after the WHERE clause
WHERE t.tkt_no = g.doc_no
HAVING g.doc_bal = '0'
Third thing (I know I said just two) gradually add the joins. Start with two, then add the third, then add the fourth, I had a query similar to this...
Again I am just giving options to troubleshoot...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply