Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

SQL Select Statement


CREATE TABLE [dbo].[Category](
        [CatID] [varchar](5),
        [Cat1] [varchar](5) NULL,
        [Cat1a] [varchar](5) NULL,
        [Cat2] [varchar](5) NULL,
        [CatA1] [varchar](5) NULL,
        [Cat1b] [varchar](5) NULL,
        [Cat1c] [varchar](5) NULL,
        [Cat1d] [varchar](5) NULL,
        [Catd1] [varchar](5) NULL,
        [CatA] [varchar](5) NULL,
        [CatB] [varchar](5) NULL,
        [CatC] [varchar](5) NULL,
        [CatC1] [varchar](5) NULL,
        [CatC2] [varchar](5) NULL,
        [CatC3] [varchar](5) NULL,
        [CatType] [varchar](2),
        [Amount] [float] NULL
) ON [PRIMARY]

insert into dbo.Category
values('ID1','C1','C2','C3','C4','C5','C6','C7','C8','C9','C10','C11','C12' ,'C13','C14','A',4.55)
insert into dbo.Category
values('ID2','C1','C2','C3','C4','C5','C6','C7','C8','C9','C10','C11','C12' ,'C13','C14','B',2.70)
insert into dbo.Category
values('ID3','C1','C2','C3','C4','C5','C6','C7','C8','C9','C10','C11','C12' ,'C13','C14','C',5.32)
insert into dbo.Category
values('ID4','C1','C2','C3','C4','C5','C6','C7','C8','C9','C10','C11','C12' ,'C13','C14','D',12.95)
insert into dbo.Category
values('ID5','C1','C2','C3','C4','C5','C6','C7','C8','C9','C10','C11','C12' ,'C13','C14','E',6.28)
insert into dbo.Category
values('ID6','C1','C2','C3','C4','C5','C6','C7','C8','C9','C10','C11','C12' ,'C13','C14','F',3.99)
insert into dbo.Category
values('ID7','C1','C2','C3','C4','C5','C6','C7','C8','C9','C10','C11','C12' ,'C13','C14','G',5.99)

CREATE TABLE [dbo].[CatType](
        [CatID] [varchar](5) not null,
        [Type] [int] not NULL
) ON [PRIMARY]
insert into dbo.CatType values('ID1', 1)
insert into dbo.CatType values('ID1', 1)
insert into dbo.CatType values('ID1', 0)
insert into dbo.CatType values('ID1', 1)
insert into dbo.CatType values('ID1', 0)
insert into dbo.CatType values('ID1', 0)

I need to display all fields from the Category table based on the Type in
CatType table.
If CatType.Type=0 then display NULL in the following fields:
        Category.Amount
        Category.CatA1
        Category.Cat1C
        Category.Catd1
        Category.CatC
        Category.CatC3
And rest of the fields in Category should display whatever is in the field.
I can do a CASE in each field like the following but I am looking for a
better solution.
    case CatType.Type
       When 0 then NULL
       When 1 then Category.Cat1
    End as Cat1

>> I can do a CASE in each field like the following but I am looking for a
>> better solution.

Are you trying find better solutions with existing schema or would you be
more inclined to redesign your tables?

--
Anith

-----------------------------------------------Reply-----------------------------------------------

I am looking for a better solution with existing schema; redesigning tables
would not be an option.

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc