by kions » Mon May 07, 2007 2:10 pm
i created a view using several unions and 2 base tables but i cannot create a primary key/row identifier on any of the columns/attributes..including the columns which were primary keys in the base tables...i tried the coalese function as well as 'distinct' key word and 'is not null' ,but nothing works...my code looks like this>>>
select distinct BatchNote_Types.BNT_Code,BatchNote_Types.BNT_Description, BNT_Fields.Field_No,BNT_Fields.Field_Name,BNT_Fields.Grouping_Function,
BNT_Fields.Field_Formula,BNT_Fields.Entry_Id,BNT_Fields.Item_id,
BNT_Fields.Entry_type,BNT_Fields.Other as other1,coalesce(BatchNote_Types.BNT_Code,BNT_Fields.Field_Name) from
BatchNote_Types,BNT_Fields where
BatchNote_Types.BNT_Code = BNT_Fields.BNT_Code and BatchNote_Types.BNT_Code is not null and BNT_Fields.Field_No is not null union
select BatchNote_Types.BNT_Code,BatchNote_Types.BNT_Description,
-99999999,'Total Amount',0,'Amount','*','*','*','*','*' from
BatchNote_Types
union
select BatchNote_Types.BNT_Code,BatchNote_Types.BNT_Description,
-99999998,'No of Entries',1,'N/A','*','*','*','*','*' from
BatchNote_Types
<<<<<
where bnt_code and fied_no are primary keys on the bnt_fields table and bnt_code is the primary key of the batch_notetypes table.