SQL Server Performance Forum – Threads Archive

dbo question

Hello Everyone, I have the following code: selectCSG_Hist.OJB_JOBS.ir_tech_ojb as TECHN,
substring(CSG_Hist..OJB_JOBS.compl_cde_ojb, pstn.p, 3) as RESCODE,
Parser.OJB_JOBS.compl_dte_ojb as COMPLET,
Parser.ELP_Codes.CodeDes as [DESCRIPTION],
CSG_Hist.SBB_BASE.res_name_sbb as RES_NAME,
CSG_Hist.HSE_BASE.ADDR1_HSE as [ADDRESS],
CSG_Hist.OJB_JOBS.job_typ_ojb as JOB,
CSG_Hist.OJB_JOBS.job_class_ojb as TYP,
CSG_Hist.OCR_ORDER_COMP.order_no_ocr as NUMB,
CSG_Hist.OCR_ORDER_COMP.ls_chg_op_id_ocr as OPR,
Parser.ELP_Codes.CommissionAMT as NCommissionAMT,
COUNT(DISTINCT Parser.dbo.Parser_OCR.order_no_ocr) as QTYW
fromParser.Parser_OCR
Inner JoinParser.Parser_ojb on Parser.Parser_OCR.order_no_ocr = CSG_Hist.OJB_JOBS.order_no_ojb
Inner JOINParser.RGV_Codes on substring (Parser.Parser_OJB.compl_cde_ojb, pstn.p, 3) = CODE — IS THIS A VARIABLE/PARAMETER OR A COLUMN NAME?
Inner JOINcsg_hist.sbb_base on CSG_Hist.OCR_ORDER_COMP.hse_key_ocr = CSG_Hist.SBB_BASE.hse_key_sbb
Inner JOINcsg_hist.hse_base on CSG_Hist.OCR_ORDER_COMP.hse_key_ocr = Parser.Parser_OCR.hse_key_ocr
Inner JOINcsg_nrt.nrt_ojb_jobs ON csg_nrt.nrt_ojb_jobs.ir_tech_ojb = CSG_Hist.OJB_JOBS.IR_TECH_OJB
cross Join(
select 1 as p union all
select 4 union all
select 7 union all
select 10 union all
select 13 union all
select 16
) as pstn
WHERECSG_hist.OJB_JOBS.ir_tech_ojb between 950 and 999
and Parser.Parser_OJB.compl_dte_ojb BETWEEN ‘2007-01-09’ AND ‘2007-01-22’
and CSG_Hist.Parser_OCR.prin_ocr = 8600
GROUP BYCSG_Hist.OJB_JOBS.ir_tech_ojb,
substring(CSG_Hist.OJB_JOBS.compl_cde_ojb, pstn.p, 3),
Parser.Parser_OJB.compl_dte_ojb,
Parser.ELP_Codes.CodeDes,
CSG_Hist.SBB_BASE.res_name_sbb,
CSG_Hist.HSE_BASE.ADDR1_HSE,
Parser.Parser_OJB.job_typ_ojb,
Parser.Parser_OJB.job_class_ojb,
CSG_Hist.OCR_ORDER_COMP.order_no_ocr,
CSG_Hist.OCR_ORDER_COMP.ls_chg_op_id_ocr,
Parser.ELP_Codes.CommissionAMT
I now get the following error message: Msg 208, Level 16, State 1, Line 1
Invalid object name ‘Parser.Parser_OCR’. Any ideas on this? Any help is greatly appreciated. Thanks, Kurt

Lots of stuff not working out there. For instance, you have a table/view called CSG_hist.OJB_JOBS, which is used in the ON clause of several JOIN statements, but it isn’t actually mentioned as one of the tables/views which are being joined. Another thing – but this could be my ignorance of the new structure of SQL 2005 – is that you sometimes mention the owner, and sometimes use .. – and elsewhere you skip the owner completely. You need to be consistent! Use aliases – they’re so much easier than if you have to retype the full object reference. Anyway … rebuild the query from scratch, using aliases.

Hello Adriaan, I am learning this from scratch pretty much. Here is the code again: Parser.dbo.ELP_Codes.CodeDes as [DESCRIPTION],
CSG_Hist.dbo.SBB_BASE.RES_NAME_SBB as RES_NAME,
CSG_Hist.dbo.HSE_BASE.ADDR1_HSE as [ADDRESS],
CSG_Hist.db :confused:

JB_JOBS.JOB_TYP_OJB as JOB,
CSG_Hist.db :confused:

JB_JOBS.JOB_CLASS_OJB as TYP,
CSG_Hist.db :confused:

CR_ORDER_COMP.ORDER_NO_OCR as NUMB,
CSG_Hist.db :confused:

CR_ORDER_COMP.LS_CHG_OP_ID_OCR as OPR,
Parser.dbo.ELP_Codes.CommissionAMT as NCommissionAMT,
COUNT(DISTINCT Parser.dbo.Parser_OCR.ORDER_NO_OCR) as QTYW
fromParser_OCR
Inner JoinParser.dbo.Parser_OJB on Parser.dbo.Parser_OCR.ORDER_NO_OCR = CSG_Hist.db :confused:

JB_JOBS.ORDER_NO_OJB
Inner JOINParser.dbo.RGV_Codes on substring (Parser.dbo.Parser_OJB.COMPL_CDE_OJB, pstn.p, 3) = CODE — IS THIS A VARIABLE/PARAMETER OR A COLUMN NAME?
Inner JOINCSG_Hist.dbo.SBB_BASE on CSG_Hist.db :confused:

CR_ORDER_COMP.HSE_KEY_OCR = CSG_Hist.dbo.SBB_BASE.HSE_KEY_SBB
Inner JOINCSG_Hist.dbo.HSE_BASE on CSG_Hist.db :confused:

CR_ORDER_COMP.HSE_KEY_OCR = Parser.dbo.Parser_OCR.HSE_KEY_OCR
Inner JOINCSG_NRT.dbo.NRT_OJB_JOBS ON CSG_NRT.dbo.NRT_OJB_JOBS.IR_TECH_NOJB = CSG_Hist.db :confused:

JB_JOBS.IR_TECH_OJB
cross Join(
select 1 as p union all
select 4 union all
select 7 union all
select 10 union all
select 13 union all
select 16
) as pstn
WHERECSG_hist.db :confused:

JB_JOBS.IR_TECH_OJB between 950 and 999
and Parser.dbo.Parser_OJB.COMPL_DTE_OJB BETWEEN ‘2007-01-09’ AND ‘2007-01-22’
and CSG_Hist.dbo.Parser_OCR.prin_ocr = 8600
GROUP BYCSG_Hist.db :confused:

JB_JOBS.IR_TECH_OJB,
substring(CSG_Hist.db :confused:

JB_JOBS.COMPL_CDE_OJB, pstn.p, 3),
Parser.dbo.Parser_OJB.COMPL_DTE_OJB,
Parser.dbo.ELP_Codes.CodeDes,
CSG_Hist.dbo.SBB_BASE.RES_NAME_SBB,
CSG_Hist.dbo.HSE_BASE.ADDR1_HSE,
Parser.dbo.Parser_OJB.JOB_TYP_OJB,
Parser.dbo.Parser_OJB.JOB_CLASS_OJB,
CSG_Hist.db :confused:

CR_ORDER_COMP.ORDER_NO_OCR,
CSG_Hist.db :confused:

CR_ORDER_COMP.LS_CHG_OP_ID_OCR,
Parser.dbo.ELP_Codes.CommissionAMT
I have tried to make sure to include the .dbo Can someone please show me how to get this code working without a lot of rewrite? Thanks, Kurt

The basic idea is that you use the full reference only in the FROM clause, with an alias. You can then use the alias everywhere else in your query SELECT t1.col1
FROM dbo.MyTable t1
WHERE t1.colX = ‘Y’ etc. You only need to include the database reference in case your query is joining tables from different databases. In that case, you can still use a simple alias.

]]>