Prev: collect rows of damaged data
Next: Like search in SQL server 2008 x64 BUG?
From: Junior on 22 Jan 2010 05:23
I have a table with about 20 columns and I am trying to create a view of it
that includes
about 10 columns. The problem is that in the process, I am trying to
concatenate some
fields into one field and I am unable to get any thing working. Below is one
example of
my attempts to tackle this. Any ideas will be highly appreciated.

// The following should be considered as pseudo-SQL

CREATE function [dbo].[fn_myview] (@CardNo varchar(11)) returns Table
as
BEGIN

declare @FullName as nvarchar(128)

(SELECT FirstName, FatherName, GFatherName, FamilyName, Birthdate,
Birthplace, Photo
FROM dbo.ClientData
WHERE CardNo=(a)CardNo)

set @FullName=FirstName
if FatherName <> '' begin // This is allowed to be null
set @FullName += ' '+ FatherName
end

if GFatherName is not null begin // This is allowed to be null
set @FullName += ' '+ GFatherName
end

set @FullName += ' '+FamilyName

RETURN (@FullName, BirthPlace, Birthdate, Photo) // Never mind the syntax
here
END

From: Dan Guzman on 22 Jan 2010 07:36
You mention VIEW but it looks like you are trying to create a table-valued
function based on your pseudo-code. In that case, try:

CREATE FUNCTION [dbo].[fn_myview] (@CardNo varchar(11)) RETURNS TABLE
AS
RETURN(
SELECT
CardNo,
FirstName
+ CASE WHEN FatherName IS NOT NULL AND FatherName <> '' THEN ' ' +
FatherName ELSE '' END
+ CASE WHEN GFatherName IS NOT NULL AND GFatherName <> '' THEN ' ' +
GFatherName ELSE '' END
+ ' ' + FamilyName AS FullName,
BirthPlace,
Birthdate,
Photo
FROM dbo.ClientData
WHERE CardNo = @CardNo
);
GO

Usage:

SELECT
CardNo,
FullName,
BirthPlace,
BirthDate,
Photo
FROM [dbo].[fn_myview]('01234567890');

If you want a view rather than a TVF, try:

CREATE VIEW dbo.MyView
AS
SELECT
CardNo,
FirstName
+ CASE WHEN FatherName IS NOT NULL AND FatherName <> '' THEN ' ' +
FatherName ELSE '' END
+ CASE WHEN GFatherName IS NOT NULL AND GFatherName <> '' THEN ' ' +
GFatherName ELSE '' END
+ ' ' + FamilyName AS FullName,
BirthPlace,
Birthdate,
Photo
FROM dbo.ClientData;
GO

Usage::

SELECT
CardNo,
FullName,
BirthPlace,
BirthDate,
Photo
FROM dbo.MyView
WHERE CardNo = '01234567890';

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/


"Junior" <junior(a)gmail.com> wrote in message
news:ONEGnz0mKHA.3636(a)TK2MSFTNGP06.phx.gbl...
> I have a table with about 20 columns and I am trying to create a view of
> it that includes
> about 10 columns. The problem is that in the process, I am trying to
> concatenate some
> fields into one field and I am unable to get any thing working. Below is
> one example of
> my attempts to tackle this. Any ideas will be highly appreciated.
>
> // The following should be considered as pseudo-SQL
>
> CREATE function [dbo].[fn_myview] (@CardNo varchar(11)) returns Table
> as
> BEGIN
>
> declare @FullName as nvarchar(128)
>
> (SELECT FirstName, FatherName, GFatherName, FamilyName, Birthdate,
> Birthplace, Photo
> FROM dbo.ClientData
> WHERE CardNo=(a)CardNo)
>
> set @FullName=FirstName
> if FatherName <> '' begin // This is allowed to be null
> set @FullName += ' '+ FatherName
> end
>
> if GFatherName is not null begin // This is allowed to be null
> set @FullName += ' '+ GFatherName
> end
>
> set @FullName += ' '+FamilyName
>
> RETURN (@FullName, BirthPlace, Birthdate, Photo) // Never mind the syntax
> here
> END

From: Junior on 22 Jan 2010 08:09
Thanks a lot Dan. The view is what I wanted but the function is handy to.

"Dan Guzman" <guzmanda(a)nospam-online.sbcglobal.net> wrote in message
news:9D9AA45A-E67C-40D8-9F67-2C1644FE5E56(a)microsoft.com...
> You mention VIEW but it looks like you are trying to create a table-valued
> function based on your pseudo-code. In that case, try:
>
> CREATE FUNCTION [dbo].[fn_myview] (@CardNo varchar(11)) RETURNS TABLE
> AS
> RETURN(
> SELECT
> CardNo,
> FirstName
> + CASE WHEN FatherName IS NOT NULL AND FatherName <> '' THEN ' ' +
> FatherName ELSE '' END
> + CASE WHEN GFatherName IS NOT NULL AND GFatherName <> '' THEN ' ' +
> GFatherName ELSE '' END
> + ' ' + FamilyName AS FullName,
> BirthPlace,
> Birthdate,
> Photo
> FROM dbo.ClientData
> WHERE CardNo = @CardNo
> );
> GO
>
> Usage:
>
> SELECT
> CardNo,
> FullName,
> BirthPlace,
> BirthDate,
> Photo
> FROM [dbo].[fn_myview]('01234567890');
>
> If you want a view rather than a TVF, try:
>
> CREATE VIEW dbo.MyView
> AS
> SELECT
> CardNo,
> FirstName
> + CASE WHEN FatherName IS NOT NULL AND FatherName <> '' THEN ' ' +
> FatherName ELSE '' END
> + CASE WHEN GFatherName IS NOT NULL AND GFatherName <> '' THEN ' ' +
> GFatherName ELSE '' END
> + ' ' + FamilyName AS FullName,
> BirthPlace,
> Birthdate,
> Photo
> FROM dbo.ClientData;
> GO
>
> Usage::
>
> SELECT
> CardNo,
> FullName,
> BirthPlace,
> BirthDate,
> Photo
> FROM dbo.MyView
> WHERE CardNo = '01234567890';
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
>
>
> "Junior" <junior(a)gmail.com> wrote in message
> news:ONEGnz0mKHA.3636(a)TK2MSFTNGP06.phx.gbl...
>> I have a table with about 20 columns and I am trying to create a view of
>> it that includes
>> about 10 columns. The problem is that in the process, I am trying to
>> concatenate some
>> fields into one field and I am unable to get any thing working. Below is
>> one example of
>> my attempts to tackle this. Any ideas will be highly appreciated.
>>
>> // The following should be considered as pseudo-SQL
>>
>> CREATE function [dbo].[fn_myview] (@CardNo varchar(11)) returns Table
>> as
>> BEGIN
>>
>> declare @FullName as nvarchar(128)
>>
>> (SELECT FirstName, FatherName, GFatherName, FamilyName, Birthdate,
>> Birthplace, Photo
>> FROM dbo.ClientData
>> WHERE CardNo=(a)CardNo)
>>
>> set @FullName=FirstName
>> if FatherName <> '' begin // This is allowed to be null
>> set @FullName += ' '+ FatherName
>> end
>>
>> if GFatherName is not null begin // This is allowed to be null
>> set @FullName += ' '+ GFatherName
>> end
>>
>> set @FullName += ' '+FamilyName
>>
>> RETURN (@FullName, BirthPlace, Birthdate, Photo) // Never mind the syntax
>> here
>> END
>
From: Gert-Jan Strik on 22 Jan 2010 12:31
FYI, you can simplify the View definition to this:

CREATE VIEW dbo.MyView
AS
SELECT
CardNo,
FirstName
+ COALESCE(' ' + FatherName, '')
+ COALESCE(' ' + GFatherName, '')
+ ' ' + FamilyName AS FullName,
BirthPlace,
Birthdate,
Photo
FROM dbo.ClientData;

--
Gert-Jan



Junior wrote:
>
> Thanks a lot Dan. The view is what I wanted but the function is handy to.
>
> "Dan Guzman" <guzmanda(a)nospam-online.sbcglobal.net> wrote in message
> news:9D9AA45A-E67C-40D8-9F67-2C1644FE5E56(a)microsoft.com...
> > You mention VIEW but it looks like you are trying to create a table-valued
> > function based on your pseudo-code. In that case, try:
> >
> > CREATE FUNCTION [dbo].[fn_myview] (@CardNo varchar(11)) RETURNS TABLE
> > AS
> > RETURN(
> > SELECT
> > CardNo,
> > FirstName
> > + CASE WHEN FatherName IS NOT NULL AND FatherName <> '' THEN ' ' +
> > FatherName ELSE '' END
> > + CASE WHEN GFatherName IS NOT NULL AND GFatherName <> '' THEN ' ' +
> > GFatherName ELSE '' END
> > + ' ' + FamilyName AS FullName,
> > BirthPlace,
> > Birthdate,
> > Photo
> > FROM dbo.ClientData
> > WHERE CardNo = @CardNo
> > );
> > GO
> >
> > Usage:
> >
> > SELECT
> > CardNo,
> > FullName,
> > BirthPlace,
> > BirthDate,
> > Photo
> > FROM [dbo].[fn_myview]('01234567890');
> >
> > If you want a view rather than a TVF, try:
> >
> > CREATE VIEW dbo.MyView
> > AS
> > SELECT
> > CardNo,
> > FirstName
> > + CASE WHEN FatherName IS NOT NULL AND FatherName <> '' THEN ' ' +
> > FatherName ELSE '' END
> > + CASE WHEN GFatherName IS NOT NULL AND GFatherName <> '' THEN ' ' +
> > GFatherName ELSE '' END
> > + ' ' + FamilyName AS FullName,
> > BirthPlace,
> > Birthdate,
> > Photo
> > FROM dbo.ClientData;
> > GO
> >
> > Usage::
> >
> > SELECT
> > CardNo,
> > FullName,
> > BirthPlace,
> > BirthDate,
> > Photo
> > FROM dbo.MyView
> > WHERE CardNo = '01234567890';
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> > http://weblogs.sqlteam.com/dang/
> >
> >
> > "Junior" <junior(a)gmail.com> wrote in message
> > news:ONEGnz0mKHA.3636(a)TK2MSFTNGP06.phx.gbl...
> >> I have a table with about 20 columns and I am trying to create a view of
> >> it that includes
> >> about 10 columns. The problem is that in the process, I am trying to
> >> concatenate some
> >> fields into one field and I am unable to get any thing working. Below is
> >> one example of
> >> my attempts to tackle this. Any ideas will be highly appreciated.
> >>
> >> // The following should be considered as pseudo-SQL
> >>
> >> CREATE function [dbo].[fn_myview] (@CardNo varchar(11)) returns Table
> >> as
> >> BEGIN
> >>
> >> declare @FullName as nvarchar(128)
> >>
> >> (SELECT FirstName, FatherName, GFatherName, FamilyName, Birthdate,
> >> Birthplace, Photo
> >> FROM dbo.ClientData
> >> WHERE CardNo=(a)CardNo)
> >>
> >> set @FullName=FirstName
> >> if FatherName <> '' begin // This is allowed to be null
> >> set @FullName += ' '+ FatherName
> >> end
> >>
> >> if GFatherName is not null begin // This is allowed to be null
> >> set @FullName += ' '+ GFatherName
> >> end
> >>
> >> set @FullName += ' '+FamilyName
> >>
> >> RETURN (@FullName, BirthPlace, Birthdate, Photo) // Never mind the syntax
> >> here
> >> END
> >
 | 
Pages: 1
Prev: collect rows of damaged data
Next: Like search in SQL server 2008 x64 BUG?