Incorrect columns returned by view

[John]

When I create a view consisting of an inner join between a view and a
table the columns refernced in the the view are returned incorrectly,
example

select id.itemcode, id.description, iv.linevalue, iv.vatvalue from
dbo.tbl_itemdetails id inner join dbo.vw_invoices iv where
dbo.tbl_itemdetails.itemcode = dbo.vw_invoices.itemcode

The actual value that is returned from the view is the column
immediately to the left of the linevalue column, ie stockroom

[David Portas]

The code you have posted isn't correct syntax, you've missed the ON clause.

One possibility for you: Could it be that you have some control characters
such as carriage return/line feed in one of the columns you are returning?
Special characters sometimes cause formatting problems when displaying data
in Query Analyzer.

If you need more help please post some code we can run that will reproduce
the problem: DDL (CREATE TABLE statement(s) for the table(s)), sample data
(INSERT statements) and the actual definition of the view.

--
David Portas
SQL Server MVP
--

[John Dennison]

Yeah it was just a quick rehash of the view, limiting the information
for demo purposes the actual view(vw_test) is below:

CREATE VIEW dbo.vw_test
AS
SELECT dbo.vw_Invoices_I.ID_Company_Number,
dbo.vw_Invoices_I.ID_Item_Code, dbo.vw_Invoices_I.I_Order_Number,
dbo.vw_Invoices_I.I_Order_Line_Number,
dbo.vw_Invoices_I.I_Invoice_Number, dbo.vw_Invoices_I.I_Customer_Number,
dbo.vw_Invoices_I.I_Delivery_Address_Code,
dbo.vw_Invoices_I.I_Line_Value *
dbo.tbl_Lagged_Sales.Percentage_Of_Sales AS Expr1,
dbo.vw_Invoices_I.I_VAT_Value,
dbo.vw_Invoices_I.I_Discount_Value, dbo.vw_Invoices_I.I_Standard_Cost,
dbo.vw_Invoices_I.I_Line_Cost_Value
FROM dbo.tbl_Lagged_Sales INNER JOIN
dbo.vw_Invoices_I ON
dbo.tbl_Lagged_Sales.Supplier_Code = dbo.vw_Invoices_I.ID_Company AND
dbo.tbl_Lagged_Sales.Lag_Product_Group =
dbo.vw_Invoices_I.ID_Product_GroupHi
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tbl_Invoices_I_DB_TEST]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tbl_Invoices_I_DB_TEST]
GO

CREATE TABLE [dbo].[tbl_Invoices_I_DB_TEST] (
[I_Company_Number] [char] (2) COLLATE Latin1_General_CI_AS NOT NULL ,
[I_Order_Number] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
[I_Order_Line_Number] [smallint] NOT NULL ,
[I_Invoice_Number] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
[I_Disp_Seq_No] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[I_Item_Code] [char] (15) COLLATE Latin1_General_CI_AS NULL ,
[I_Pack_Item_Code] [char] (15) COLLATE Latin1_General_CI_AS NULL ,
[I_Stockroom] [char] (2) COLLATE Latin1_General_CI_AS NULL ,
[I_Line_Value] [numeric](17, 2) NOT NULL ,
[I_VAT_Value] [numeric](17, 2) NULL ,
[I_Discount_Value] [numeric](17, 2) NULL ,
[I_Standard_Cost] [numeric](17, 2) NULL ,
[I_Line_Quantity] [numeric](13, 3) NOT NULL ,
[I_Week_Number] [int] NULL ,
[I_Period_Number] [int] NULL ,
[I_Transaction_Type] [smallint] NULL ,
[I_Transaction_Date] [int] NULL ,
[I_Customer_Number] [char] (8) COLLATE Latin1_General_CI_AS NULL ,
[I_Delivery_Address_Code] [char] (3) COLLATE Latin1_General_CI_AS NULL
,
[I_Line_Cost_Value] [numeric](17, 2) NOT NULL ,
[I_Status] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
[I_Parent_Line_Number] [smallint] NULL ,
[I_Reason_Code] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
[I_Print_Flag] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
[I_Sales_Analysis_Update_Flag] [char] (1) COLLATE Latin1_General_CI_AS
NULL ,
[I_Item_Type] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
[I_Sales_Type] [int] NULL ,
[I_Month_Number] [tinyint] NOT NULL ,
[I_Year_Number] [smallint] NOT NULL ,
[I_Transaction_Date_PC] [datetime] NOT NULL ,
[I_Gross_Margin] [numeric](17, 2) NULL ,
[I_Date_Downloaded] [datetime] NULL ,
[Flagged_for_Exception] [bit] NULL
) ON [PRIMARY]
GO
CREATE VIEW dbo.vw_Invoices_I
AS
SELECT dbo.vw_Item_Details_ID.*, dbo.tbl_Invoices_I_DB_TEST.*,
dbo.vw_Customer_Details.*
FROM dbo.tbl_Invoices_I_DB_TEST INNER JOIN
dbo.vw_Customer_Details ON
dbo.tbl_Invoices_I_DB_TEST.I_Company_Number =
dbo.vw_Customer_Details.CD_Company_Number AND
dbo.tbl_Invoices_I_DB_TEST.I_Customer_Number =
dbo.vw_Customer_Details.CD_Customer_Number AND
dbo.tbl_Invoices_I_DB_TEST.I_Delivery_Address_Code
= dbo.vw_Customer_Details.CD_Dseq INNER JOIN
dbo.vw_Item_Details_ID ON
dbo.tbl_Invoices_I_DB_TEST.I_Company_Number =
dbo.vw_Item_Details_ID.ID_Company_Number AND
dbo.tbl_Invoices_I_DB_TEST.I_Item_Code =
dbo.vw_Item_Details_ID.ID_Item_Code

CREATE VIEW dbo.vw_Item_Details_ID
AS
SELECT dbo.tbl_Item_Details_ID.ID_Company_Number,
dbo.tbl_Item_Details_ID.ID_Item_Code,
dbo.tbl_Item_Details_ID.ID_Description,
dbo.tbl_Item_Details_ID.ID_STD_Cost,
dbo.tbl_Item_Details_ID.ID_Product_Type,
dbo.tbl_ILU_Z_PTYP.Z_Product_Type_Description,
dbo.tbl_Item_Details_ID.ID_Company,
dbo.tbl_ILU_A_COMP.A_Company_Description,
dbo.tbl_Item_Details_ID.ID_Brand_Type,
dbo.tbl_ILU_B_BTYP.B_Brand_Type_Description,
dbo.tbl_Item_Details_ID.ID_Brand,
dbo.tbl_ILU_C_BRND.C_Brand_Description,
dbo.tbl_Item_Details_ID.ID_Range,
dbo.tbl_ILU_D_RANG.D_Range_Description, dbo.tbl_Item_Details_ID.ID_Item,
dbo.tbl_ILU_E_ITEM.E_Item_Description,
dbo.tbl_Item_Details_ID.ID_Function_Description,
dbo.tbl_ILU_F_DESC.F_Description_Description,
dbo.tbl_Item_Details_ID.ID_Det_Fuel,
dbo.tbl_ILU_G_DET_FUEL.G_Det_Fuel_Description,
dbo.tbl_Item_Details_ID.ID_Colour,
dbo.tbl_ILU_H_COLR.H_Colour_Description,
dbo.tbl_Item_Details_ID.ID_TypeCat,
dbo.tbl_ILU_I_TYPECAT.I_TypeCat_Description,
dbo.tbl_Item_Details_ID.ID_DetFunc,
dbo.tbl_ILU_J_DETFUNC.J_DETFUNC_Description,
dbo.tbl_Item_Details_ID.ID_Function,
dbo.tbl_ILU_K_FCTN.K_Function_Description,
dbo.tbl_Item_Details_ID.ID_Owner,
dbo.tbl_ILU_L_OWNR.L_Owner_Description,
dbo.tbl_Item_Details_ID.ID_Application,
dbo.tbl_ILU_M_APPL.M_Application_Description,
dbo.tbl_Item_Details_ID.ID_Planning_Group,
dbo.tbl_ILU_O_PLANG.O_Planning_Group_Description,
dbo.tbl_Item_Details_ID.ID_Planning_Sub_Group,

dbo.tbl_ILU_P_PLNSG.P_Planning_Sub_Group_Descripti on,
dbo.tbl_Item_Details_ID.ID_Product_Group,
dbo.tbl_ILU_O_GROP.O_PoductGroup_Description,
dbo.tbl_Item_Details_ID.ID_Top_Fuel,
dbo.tbl_ILU_N_TFUL.N_TopFuel_Description,
dbo.tbl_Item_Details_ID.ID_Date_Last_Manufactured,
dbo.tbl_Item_Details_ID.ID_Model, dbo.tbl_ILU_P_MODL.P_Model_Description
FROM dbo.tbl_Item_Details_ID INNER JOIN
dbo.tbl_ILU_P_MODL ON
dbo.tbl_Item_Details_ID.ID_Model = dbo.tbl_ILU_P_MODL.P_MODL_ID LEFT
OUTER JOIN
dbo.tbl_ILU_A_COMP ON
dbo.tbl_Item_Details_ID.ID_Company = dbo.tbl_ILU_A_COMP.A_COMP_ID LEFT
OUTER JOIN
dbo.tbl_ILU_I_TYPECAT ON
dbo.tbl_Item_Details_ID.ID_TypeCat = dbo.tbl_ILU_I_TYPECAT.I_TYPECAT_ID
LEFT OUTER JOIN
dbo.tbl_ILU_E_ITEM ON
dbo.tbl_Item_Details_ID.ID_Item = dbo.tbl_ILU_E_ITEM.E_ITEM_ID LEFT
OUTER JOIN
dbo.tbl_ILU_G_DET_FUEL ON
dbo.tbl_Item_Details_ID.ID_Det_Fuel =
dbo.tbl_ILU_G_DET_FUEL.G_DET_FUEL_ID LEFT OUTER JOIN
dbo.tbl_ILU_J_DETFUNC ON
dbo.tbl_Item_Details_ID.ID_DetFunc = dbo.tbl_ILU_J_DETFUNC.J_DETFUNC_ID
LEFT OUTER JOIN
dbo.tbl_ILU_N_TFUL ON
dbo.tbl_Item_Details_ID.ID_Top_Fuel = dbo.tbl_ILU_N_TFUL.N_TFUL_ID LEFT
OUTER JOIN
dbo.tbl_ILU_O_GROP ON
dbo.tbl_Item_Details_ID.ID_Product_Group = dbo.tbl_ILU_O_GROP.O_GROP_ID
LEFT OUTER JOIN
dbo.tbl_ILU_C_BRND ON
dbo.tbl_Item_Details_ID.ID_Brand = dbo.tbl_ILU_C_BRND.C_BRND_ID LEFT
OUTER JOIN
dbo.tbl_ILU_K_FCTN ON
dbo.tbl_Item_Details_ID.ID_Function = dbo.tbl_ILU_K_FCTN.K_FCTN_ID LEFT
OUTER JOIN
dbo.tbl_ILU_M_APPL ON
dbo.tbl_Item_Details_ID.ID_Application = dbo.tbl_ILU_M_APPL.M_APPL_ID
LEFT OUTER JOIN
dbo.tbl_ILU_P_PLNSG ON
dbo.tbl_Item_Details_ID.ID_Planning_Sub_Group =
dbo.tbl_ILU_P_PLNSG.P_PLNSG_ID LEFT OUTER JOIN
dbo.tbl_ILU_O_PLANG ON
dbo.tbl_Item_Details_ID.ID_Planning_Group =
dbo.tbl_ILU_O_PLANG.O_PLANG_ID LEFT OUTER JOIN
dbo.tbl_ILU_L_OWNR ON
dbo.tbl_Item_Details_ID.ID_Owner = dbo.tbl_ILU_L_OWNR.L_OWNR_ID LEFT
OUTER JOIN
dbo.tbl_ILU_H_COLR ON
dbo.tbl_Item_Details_ID.ID_Colour = dbo.tbl_ILU_H_COLR.H_COLR_ID LEFT
OUTER JOIN
dbo.tbl_ILU_F_DESC ON
dbo.tbl_Item_Details_ID.ID_Function_Description =
dbo.tbl_ILU_F_DESC.F_DESC_ID LEFT OUTER JOIN
dbo.tbl_ILU_D_RANG ON
dbo.tbl_Item_Details_ID.ID_Range = dbo.tbl_ILU_D_RANG.D_RANG_ID LEFT
OUTER JOIN
dbo.tbl_ILU_B_BTYP ON
dbo.tbl_Item_Details_ID.ID_Brand_Type = dbo.tbl_ILU_B_BTYP.B_BTYP_ID
LEFT OUTER JOIN
dbo.tbl_ILU_Z_PTYP ON
dbo.tbl_Item_Details_ID.ID_Product_Type = dbo.tbl_ILU_Z_PTYP.Z_PTYP_ID

CREATE VIEW dbo.vw_Customer_Details
AS
SELECT dbo.tbl_Customer_Details_CD.CD_Company_Number,
dbo.tbl_Customer_Details_CD.CD_Customer_Number,
dbo.tbl_Customer_Details_CD.CD_Dseq,
dbo.tbl_Customer_Details_CD.CD_Customer_Name,
dbo.tbl_Customer_Details_CD.CD_Customer_Address_1,
dbo.tbl_Customer_Details_CD.CD_Customer_Address_2,
dbo.tbl_Customer_Details_CD.CD_Customer_Address_3,
dbo.tbl_Customer_Details_CD.CD_Customer_Address_4,
dbo.tbl_Customer_Details_CD.CD_Customer_Address_5,
dbo.tbl_Customer_Details_CD.CD_Post_Code_1,
dbo.tbl_Customer_Details_CD.CD_Post_Code_2,
dbo.tbl_Customer_Details_CD.CD_Customer_Group_1,
dbo.tbl_Customer_Details_CD.CD_Customer_Group_2,
dbo.tbl_Customer_Details_CD.CD_Customer_Group_3,
dbo.tbl_Customer_Details_CD.CD_Customer_Group_4,
dbo.tbl_Customer_Details_CD.CD_Region,
dbo.tbl_Customer_Details_CD.CD_Credit_Limit,
dbo.tbl_Customer_Details_CD.CD_Customer_Contact,
dbo.tbl_Customer_Details_CD.CD_Phone_Number,
dbo.tbl_Customer_Details_CD.CD_Date_Account_Opened ,

dbo.tbl_Customer_Details_CD.CD_Bank_Account_Number ,
dbo.tbl_Customer_Details_CD.CD_Bank_Account_Name,
dbo.tbl_Customer_Details_CD.CD_Bank_Address_1,
dbo.tbl_Customer_Details_CD.CD_Bank_Address_2,
dbo.tbl_Customer_Details_CD.CD_Credit_Controller,
dbo.tbl_Credit_Controller.CC_Description,
dbo.tbl_Customer_Details_CD.CD_Customer_Group,

dbo.tbl_CLU_Z_CGT.Z_Customer_Group_Type_Descriptio n,
dbo.tbl_Customer_Details_CD.CD_Customer_Parent,

dbo.tbl_CLU_Y_CPT.Y_Customer_Parent_Type_Descripti on,
dbo.tbl_Customer_Details_CD.CD_Sales_Region,
dbo.tbl_CLU_A_SRGN.A_Sales_Region_Description,
dbo.tbl_Customer_Details_CD.CD_Business_Type,
dbo.tbl_CLU_B_BTYP.B_Business_Type_Description,
dbo.tbl_Customer_Details_CD.CD_Distribution_Channe l,

dbo.tbl_CLU_C_DCNL.C_Distribution_Channel_Descript ion,
dbo.tbl_Customer_Details_CD.CD_Distribution_Cluste r,

dbo.tbl_CLU_D_DCSR.D_Distribution_Cluster_Descript ion,
dbo.tbl_Customer_Details_CD.CD_Delivery_Type,
dbo.tbl_CLU_E_DTYP.E_Delivery_Type_Description,
dbo.tbl_Customer_Details_CD.CD_Marketing_Director,

dbo.tbl_CLU_F_MDR.F_Marketing_Director_Resposibili ty_Description,
dbo.tbl_Customer_Details_CD.CD_Sales_Director,

dbo.tbl_CLU_G_SDR.G_Sales_Director_Responsibility_ Description,
dbo.tbl_Customer_Details_CD.CD_Account_Manager,

dbo.tbl_CLU_H_AMR.H_Account_Manager_Responsibility _Description,
dbo.tbl_Customer_Details_CD.CD_Date_Account_Opened _PC,

dbo.tbl_Customer_Details_CD.CD_Consolidated_Custom er,
dbo.tbl_CLU_I_CNSL.I_Consolidated_Customer_Descrip tion
FROM dbo.tbl_Customer_Details_CD LEFT OUTER JOIN
dbo.tbl_CLU_I_CNSL ON
dbo.tbl_Customer_Details_CD.CD_Consolidated_Custom er =
dbo.tbl_CLU_I_CNSL.I_CNSL_ID LEFT OUTER JOIN
dbo.tbl_Credit_Controller ON
dbo.tbl_Customer_Details_CD.CD_Credit_Controller =
dbo.tbl_Credit_Controller.CC_ID LEFT OUTER JOIN
dbo.tbl_CLU_H_AMR ON
dbo.tbl_Customer_Details_CD.CD_Account_Manager =
dbo.tbl_CLU_H_AMR.H_AMR_ID LEFT OUTER JOIN
dbo.tbl_CLU_G_SDR ON
dbo.tbl_Customer_Details_CD.CD_Sales_Director =
dbo.tbl_CLU_G_SDR.G_SDR_ID LEFT OUTER JOIN
dbo.tbl_CLU_F_MDR ON
dbo.tbl_Customer_Details_CD.CD_Marketing_Director =
dbo.tbl_CLU_F_MDR.F_MDR_ID LEFT OUTER JOIN
dbo.tbl_CLU_E_DTYP ON
dbo.tbl_Customer_Details_CD.CD_Delivery_Type =
dbo.tbl_CLU_E_DTYP.E_DTYP_ID LEFT OUTER JOIN
dbo.tbl_CLU_D_DCSR ON
dbo.tbl_Customer_Details_CD.CD_Distribution_Cluste r =
dbo.tbl_CLU_D_DCSR.D_DCSR_ID LEFT OUTER JOIN
dbo.tbl_CLU_C_DCNL ON
dbo.tbl_Customer_Details_CD.CD_Distribution_Channe l =
dbo.tbl_CLU_C_DCNL.C_DCNL_ID LEFT OUTER JOIN
dbo.tbl_CLU_B_BTYP ON
dbo.tbl_Customer_Details_CD.CD_Business_Type =
dbo.tbl_CLU_B_BTYP.B_BTYP_ID LEFT OUTER JOIN
dbo.tbl_CLU_A_SRGN ON
dbo.tbl_Customer_Details_CD.CD_Sales_Region =
dbo.tbl_CLU_A_SRGN.A_SRGN_ID LEFT OUTER JOIN
dbo.tbl_CLU_Y_CPT ON
dbo.tbl_Customer_Details_CD.CD_Customer_Parent =
dbo.tbl_CLU_Y_CPT.Y_CPT_ID LEFT OUTER JOIN
dbo.tbl_CLU_Z_CGT ON
dbo.tbl_Customer_Details_CD.CD_Customer_Group =
dbo.tbl_CLU_Z_CGT.Z_CGT_ID
The above should give you some idea of the data, I haven't include the
scripts for the base tables as this would take considerable time

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!