Also, some people do not have middle names, how would I write the query to
pull this out? Thanks again.
"Mike Collins" wrote:
> In the following query, I will be making 6 joins for each ID in the Proble
ms
> table to get a person's full name from our personnel table. Is there anoth
er
> way to do this than with the query I have below?
> Select TPRTitle, p1.FirstName + ' ' + p1.LastName As Originator,
> p2.FirstName + ' ' + p2.MiddleName + ' ' + p2.LastName As Screener
> From TPRs t
> Join common..personnel p1 On p1.PersonnelID = t.OriginatorID
> Join common..personnel p2 On p2.PersonnelID = t.ScreenerID
> ...
> CREATE TABLE [Personnel] (
> [PersonnelID] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT
> [DF_Personnel_PersonnelID] DEFAULT (newid()),
> [OrganizationID] [uniqueidentifier] NOT NULL ,
> [Title] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [FirstName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [MiddleName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LastName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Service] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [OfficeSymbol] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [EmailAddress] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LastModified] [datetime] NOT NULL ,
> CONSTRAINT [PK_Personnel] PRIMARY KEY CLUSTERED
> (
> [PersonnelID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> CREATE TABLE [Problems] (
> [ProblemID] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT
> [DF_TPRs_TPRID] DEFAULT (newid()),
> [OriginatorID] [uniqueidentifier] NOT NULL ,
> [ClassifiedByID] [uniqueidentifier] NOT NULL ,
> [ScreenerID] [uniqueidentifier] NOT NULL ,
> [SubjectMatterExpertID] [uniqueidentifier] NOT NULL ,
> [TestDirectorID] [uniqueidentifier] NOT NULL ,
> [TestEventID] [uniqueidentifier] NOT NULL ,
> [Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Location] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Function] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [OS] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ProblemSource] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SequenceOfEvents] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ProblemDescription] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WorkAround] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> CONSTRAINT [PK_tblTPRs] PRIMARY KEY CLUSTERED
> (
> [ProblemID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY] ,
> CONSTRAINT [FK_TPRs_TestEvents] FOREIGN KEY
> (
> [TestEventID]
> ) REFERENCES [TestEvents] (
> [TestEventID]
> )
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:5C6CE19B-3FCE-422E-B546-3A1FDC7DD28B@.microsoft.com...
> Also, some people do not have middle names, how would I write the query to
> pull this out? Thanks again.
>
> "Mike Collins" wrote:
>
From where I sit, you will need to do the joins. A couple of suggestions
however.
1. Use ISNULL for the MiddleName column. If it is empty, you will not get
a row back because by default NULL Concatenation returns NULL. So
something like: ISNULL(p1.FirstName, '') + ' ' + ISNULL(p1.MiddleName,
'')...
2. Since I don't see any FK constraints in the Problem table that will
guarantee that Originators, Screeners and so forth exist in the Personnel
table, you may want to use LEFT JOINS to ensure that you get rows back.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks...that helps a lot. I forgot about using the IsNull function. It
greatly simplifies the query. One question I have since you mentioned foreig
n
keys. The personnel table is located in another database. Is there a way to
create a foreign key that will span databases? Thanks for your help.
"Rick Sawtell" wrote:
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:5C6CE19B-3FCE-422E-B546-3A1FDC7DD28B@.microsoft.com...
>
> From where I sit, you will need to do the joins. A couple of suggestions
> however.
> 1. Use ISNULL for the MiddleName column. If it is empty, you will not ge
t
> a row back because by default NULL Concatenation returns NULL. So
> something like: ISNULL(p1.FirstName, '') + ' ' + ISNULL(p1.MiddleName,
> '')...
> 2. Since I don't see any FK constraints in the Problem table that will
> guarantee that Originators, Screeners and so forth exist in the Personnel
> table, you may want to use LEFT JOINS to ensure that you get rows back.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:CBAC9424-506B-40A3-85DD-8945A4AC61B5@.microsoft.com...
> Thanks...that helps a lot. I forgot about using the IsNull function. It
> greatly simplifies the query. One question I have since you mentioned
> foreign
> keys. The personnel table is located in another database. Is there a way
> to
> create a foreign key that will span databases? Thanks for your help.
>
You can't do it across DB's AFAIK. You can use sprocs and/or triggers for
this however, but it may slow your system down. Then again, you will
guarantee integrity that way. It's up to you.
Rick
Showing posts with label following. Show all posts
Showing posts with label following. Show all posts
Sunday, March 25, 2012
Monday, March 19, 2012
Adding Sequence number in SQL
Being a newbie to SQL programming, was hoping someone would be able to
guide me in the right direction.
I have a table that has the following data
Center Emp_ID
11 112
11 2254
11 346
12 456
12 138
13 8761
Etc, etc
I want to add a Sequence number which resets to 1 by center. So I want
it to look like this
List_ID Center Emp_ID
1 11 112
2 11 2254
3 11 346
1 12 456
2 12 138
1 13 8761
I've done the following:
SELECT TOP 100 PERCENT FIRST_NAME, LAST_NAME,
(SELECT COUNT(*)
FROM dbo.Planning_Heads e2
WHERE e2.ACCT_CD <=
dbo.Planning_Heads.ACCT_CD) AS List_ID, ACCT_CD
FROM dbo.Planning_Heads
ORDER BY ACCT_CD
but it's no "restarting" the List_Id or incrementing it properly
Sorry for the long post, but thought it would be best to give as much
info as I couldWhich criteria can we use to tell sql server that Emp_ID = 346 goes after
Emp_ID = 2254, other than analyzing row by row?.
In db [northwind], the orders for each customer are stored in table [orders]
and the column [orderid] is an identity one that we can use to sort them
chronologically by customer.
use northwind
go
select
count(*) as rank,
a.orderid,
a.employeeid
from
dbo.orders as a
inner join
dbo.orders as b
on a.employeeid = b.employeeid
and a.orderid >= b.orderid
group by
a.employeeid,
a.orderid
order by
a.employeeid,
rank
go
How to dynamically number rows in a SELECT Statement
http://support.microsoft.com/defaul...kb;en-us;186133
AMB
"kimmal" wrote:
> Being a newbie to SQL programming, was hoping someone would be able to
> guide me in the right direction.
> I have a table that has the following data
> Center Emp_ID
> 11 112
> 11 2254
> 11 346
> 12 456
> 12 138
> 13 8761
> Etc, etc
> I want to add a Sequence number which resets to 1 by center. So I want
> it to look like this
> List_ID Center Emp_ID
> 1 11 112
> 2 11 2254
> 3 11 346
> 1 12 456
> 2 12 138
> 1 13 8761
> I've done the following:
> SELECT TOP 100 PERCENT FIRST_NAME, LAST_NAME,
> (SELECT COUNT(*)
> FROM dbo.Planning_Heads e2
> WHERE e2.ACCT_CD <=
> dbo.Planning_Heads.ACCT_CD) AS List_ID, ACCT_CD
> FROM dbo.Planning_Heads
> ORDER BY ACCT_CD
> but it's no "restarting" the List_Id or incrementing it properly
> Sorry for the long post, but thought it would be best to give as much
> info as I could
>
guide me in the right direction.
I have a table that has the following data
Center Emp_ID
11 112
11 2254
11 346
12 456
12 138
13 8761
Etc, etc
I want to add a Sequence number which resets to 1 by center. So I want
it to look like this
List_ID Center Emp_ID
1 11 112
2 11 2254
3 11 346
1 12 456
2 12 138
1 13 8761
I've done the following:
SELECT TOP 100 PERCENT FIRST_NAME, LAST_NAME,
(SELECT COUNT(*)
FROM dbo.Planning_Heads e2
WHERE e2.ACCT_CD <=
dbo.Planning_Heads.ACCT_CD) AS List_ID, ACCT_CD
FROM dbo.Planning_Heads
ORDER BY ACCT_CD
but it's no "restarting" the List_Id or incrementing it properly
Sorry for the long post, but thought it would be best to give as much
info as I couldWhich criteria can we use to tell sql server that Emp_ID = 346 goes after
Emp_ID = 2254, other than analyzing row by row?.
In db [northwind], the orders for each customer are stored in table [orders]
and the column [orderid] is an identity one that we can use to sort them
chronologically by customer.
use northwind
go
select
count(*) as rank,
a.orderid,
a.employeeid
from
dbo.orders as a
inner join
dbo.orders as b
on a.employeeid = b.employeeid
and a.orderid >= b.orderid
group by
a.employeeid,
a.orderid
order by
a.employeeid,
rank
go
How to dynamically number rows in a SELECT Statement
http://support.microsoft.com/defaul...kb;en-us;186133
AMB
"kimmal" wrote:
> Being a newbie to SQL programming, was hoping someone would be able to
> guide me in the right direction.
> I have a table that has the following data
> Center Emp_ID
> 11 112
> 11 2254
> 11 346
> 12 456
> 12 138
> 13 8761
> Etc, etc
> I want to add a Sequence number which resets to 1 by center. So I want
> it to look like this
> List_ID Center Emp_ID
> 1 11 112
> 2 11 2254
> 3 11 346
> 1 12 456
> 2 12 138
> 1 13 8761
> I've done the following:
> SELECT TOP 100 PERCENT FIRST_NAME, LAST_NAME,
> (SELECT COUNT(*)
> FROM dbo.Planning_Heads e2
> WHERE e2.ACCT_CD <=
> dbo.Planning_Heads.ACCT_CD) AS List_ID, ACCT_CD
> FROM dbo.Planning_Heads
> ORDER BY ACCT_CD
> but it's no "restarting" the List_Id or incrementing it properly
> Sorry for the long post, but thought it would be best to give as much
> info as I could
>
Adding Rows Together in SQL (SQL Server 2000)
Dear friends,
We are trying to achieve the following. Please advice
select a.Firstname,a.Lastname, b.Dev_address,b.dev_description
from tbl1 a, tbl2 b
where b.data_id = a.id
and b.data_id=314
group by a.lastname,a.firstname,b.dev_address,b.dev_description
aaaa bbb 4234234 Pager
aaaa bbb 2342342 Work
aaaa bbb 23434 Home
aaaa bbb 23434 Cell Phone
kkk ccc 234234 Home
kkk ccc 23434 Cell
We want the result should be as follows:
aaaa bbb 4234234 Pager 2342342 Work 23434 Hom
e 23434 Cell Phone
kkk ccc 234234 Home 23434 Cell
Any ideas would be apprecaited.. we are using SQL Server 2000
ThanksHOW TO: Rotate a Table in SQL Server
http://support.microsoft.com/defaul...574&Product=sql
AMB
"Info Request" wrote:
> Dear friends,
> We are trying to achieve the following. Please advice
>
> select a.Firstname,a.Lastname, b.Dev_address,b.dev_description
> from tbl1 a, tbl2 b
> where b.data_id = a.id
> and b.data_id=314
> group by a.lastname,a.firstname,b.dev_address,b.dev_description
>
> aaaa bbb 4234234 Pager
> aaaa bbb 2342342 Work
> aaaa bbb 23434 Home
> aaaa bbb 23434 Cell Phone
> kkk ccc 234234 Home
> kkk ccc 23434 Cell
> We want the result should be as follows:
> aaaa bbb 4234234 Pager 2342342 Work 23434 Hom
e 23434 Cell Phone
> kkk ccc 234234 Home 23434 Cell
> Any ideas would be apprecaited.. we are using SQL Server 2000
> Thanks
>|||Thanks a lot. This works great.|||Rotation works perfect as long as we have one work phone number, if we
have more than one work phone number then it breaks with "Subquery
returned more than 1 value" error message.
Any ideas?
We are trying to achieve the following. Please advice
select a.Firstname,a.Lastname, b.Dev_address,b.dev_description
from tbl1 a, tbl2 b
where b.data_id = a.id
and b.data_id=314
group by a.lastname,a.firstname,b.dev_address,b.dev_description
aaaa bbb 4234234 Pager
aaaa bbb 2342342 Work
aaaa bbb 23434 Home
aaaa bbb 23434 Cell Phone
kkk ccc 234234 Home
kkk ccc 23434 Cell
We want the result should be as follows:
aaaa bbb 4234234 Pager 2342342 Work 23434 Hom
e 23434 Cell Phone
kkk ccc 234234 Home 23434 Cell
Any ideas would be apprecaited.. we are using SQL Server 2000
ThanksHOW TO: Rotate a Table in SQL Server
http://support.microsoft.com/defaul...574&Product=sql
AMB
"Info Request" wrote:
> Dear friends,
> We are trying to achieve the following. Please advice
>
> select a.Firstname,a.Lastname, b.Dev_address,b.dev_description
> from tbl1 a, tbl2 b
> where b.data_id = a.id
> and b.data_id=314
> group by a.lastname,a.firstname,b.dev_address,b.dev_description
>
> aaaa bbb 4234234 Pager
> aaaa bbb 2342342 Work
> aaaa bbb 23434 Home
> aaaa bbb 23434 Cell Phone
> kkk ccc 234234 Home
> kkk ccc 23434 Cell
> We want the result should be as follows:
> aaaa bbb 4234234 Pager 2342342 Work 23434 Hom
e 23434 Cell Phone
> kkk ccc 234234 Home 23434 Cell
> Any ideas would be apprecaited.. we are using SQL Server 2000
> Thanks
>|||Thanks a lot. This works great.|||Rotation works perfect as long as we have one work phone number, if we
have more than one work phone number then it breaks with "Subquery
returned more than 1 value" error message.
Any ideas?
Sunday, March 11, 2012
Adding Rows Together in SQL (SQL Server 2000)
Dear friends,
We are trying to achieve the following. Please advice
select a.Firstname,a.Lastname, b.Dev_address,b.dev_description
from tbl1 a, tbl2 b
where b.data_id = a.id
and b.data_id=314
group by a.lastname,a.firstname,b.dev_address,b.dev_descrip tion
aaaabbb4234234Pager
aaaabbb2342342Work
aaaabbb23434Home
aaaabbb23434Cell Phone
kkkccc234234Home
kkkccc23434Cell
We want the result should be as follows:
aaaa bbb4234234Pager2342342Work23434Home23434Cell Phone
kkk ccc234234Home23434Cell
Any ideas would be apprecaited.. we are using SQL Server 2000
Thanks
HOW TO: Rotate a Table in SQL Server
http://support.microsoft.com/default...74&Product=sql
AMB
"Info Request" wrote:
> Dear friends,
> We are trying to achieve the following. Please advice
>
> select a.Firstname,a.Lastname, b.Dev_address,b.dev_description
> from tbl1 a, tbl2 b
> where b.data_id = a.id
> and b.data_id=314
> group by a.lastname,a.firstname,b.dev_address,b.dev_descrip tion
>
> aaaabbb4234234Pager
> aaaabbb2342342Work
> aaaabbb23434Home
> aaaabbb23434Cell Phone
> kkkccc234234Home
> kkkccc23434Cell
> We want the result should be as follows:
> aaaa bbb4234234Pager2342342Work23434Home23434Cell Phone
> kkk ccc234234Home23434Cell
> Any ideas would be apprecaited.. we are using SQL Server 2000
> Thanks
>
|||Thanks a lot. This works great.
|||Rotation works perfect as long as we have one work phone number, if we
have more than one work phone number then it breaks with "Subquery
returned more than 1 value" error message.
Any ideas?
We are trying to achieve the following. Please advice
select a.Firstname,a.Lastname, b.Dev_address,b.dev_description
from tbl1 a, tbl2 b
where b.data_id = a.id
and b.data_id=314
group by a.lastname,a.firstname,b.dev_address,b.dev_descrip tion
aaaabbb4234234Pager
aaaabbb2342342Work
aaaabbb23434Home
aaaabbb23434Cell Phone
kkkccc234234Home
kkkccc23434Cell
We want the result should be as follows:
aaaa bbb4234234Pager2342342Work23434Home23434Cell Phone
kkk ccc234234Home23434Cell
Any ideas would be apprecaited.. we are using SQL Server 2000
Thanks
HOW TO: Rotate a Table in SQL Server
http://support.microsoft.com/default...74&Product=sql
AMB
"Info Request" wrote:
> Dear friends,
> We are trying to achieve the following. Please advice
>
> select a.Firstname,a.Lastname, b.Dev_address,b.dev_description
> from tbl1 a, tbl2 b
> where b.data_id = a.id
> and b.data_id=314
> group by a.lastname,a.firstname,b.dev_address,b.dev_descrip tion
>
> aaaabbb4234234Pager
> aaaabbb2342342Work
> aaaabbb23434Home
> aaaabbb23434Cell Phone
> kkkccc234234Home
> kkkccc23434Cell
> We want the result should be as follows:
> aaaa bbb4234234Pager2342342Work23434Home23434Cell Phone
> kkk ccc234234Home23434Cell
> Any ideas would be apprecaited.. we are using SQL Server 2000
> Thanks
>
|||Thanks a lot. This works great.
|||Rotation works perfect as long as we have one work phone number, if we
have more than one work phone number then it breaks with "Subquery
returned more than 1 value" error message.
Any ideas?
Adding Report Filter on Float field
I am adding a filter on a float datatype field in my dataset.
Example:
=Fields!SharePrice.Value > 50
When running the report, I get the following error:
"...the processing of filter for the data set 'dataset' cannot be performed.
The comparision failed. Please check the data type returned by the data
expression."
I've tried casting the field as decimal with mixed results. Does anyone
know why this error occurs?You'd have to explicitly cast the return value using CSng() function.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
news:uZ9L9oWWEHA.3716@.TK2MSFTNGP11.phx.gbl...
> I am adding a filter on a float datatype field in my dataset.
> Example:
> =Fields!SharePrice.Value > 50
> When running the report, I get the following error:
> "...the processing of filter for the data set 'dataset' cannot be
performed.
> The comparision failed. Please check the data type returned by the data
> expression."
> I've tried casting the field as decimal with mixed results. Does anyone
> know why this error occurs?
>|||Try this:
Filter expression: =CDbl(Fields!SharePrice.Value)
Operator: >
Filter value: =CDbl(50)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
news:uZ9L9oWWEHA.3716@.TK2MSFTNGP11.phx.gbl...
> I am adding a filter on a float datatype field in my dataset.
> Example:
> =Fields!SharePrice.Value > 50
> When running the report, I get the following error:
> "...the processing of filter for the data set 'dataset' cannot be
performed.
> The comparision failed. Please check the data type returned by the data
> expression."
> I've tried casting the field as decimal with mixed results. Does anyone
> know why this error occurs?
>|||Thanks. That did the trick.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:eNogP1WWEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Try this:
> Filter expression: =CDbl(Fields!SharePrice.Value)
> Operator: >
> Filter value: =CDbl(50)
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
> news:uZ9L9oWWEHA.3716@.TK2MSFTNGP11.phx.gbl...
> > I am adding a filter on a float datatype field in my dataset.
> >
> > Example:
> >
> > =Fields!SharePrice.Value > 50
> >
> > When running the report, I get the following error:
> >
> > "...the processing of filter for the data set 'dataset' cannot be
> performed.
> > The comparision failed. Please check the data type returned by the data
> > expression."
> >
> > I've tried casting the field as decimal with mixed results. Does anyone
> > know why this error occurs?
> >
> >
>
Example:
=Fields!SharePrice.Value > 50
When running the report, I get the following error:
"...the processing of filter for the data set 'dataset' cannot be performed.
The comparision failed. Please check the data type returned by the data
expression."
I've tried casting the field as decimal with mixed results. Does anyone
know why this error occurs?You'd have to explicitly cast the return value using CSng() function.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
news:uZ9L9oWWEHA.3716@.TK2MSFTNGP11.phx.gbl...
> I am adding a filter on a float datatype field in my dataset.
> Example:
> =Fields!SharePrice.Value > 50
> When running the report, I get the following error:
> "...the processing of filter for the data set 'dataset' cannot be
performed.
> The comparision failed. Please check the data type returned by the data
> expression."
> I've tried casting the field as decimal with mixed results. Does anyone
> know why this error occurs?
>|||Try this:
Filter expression: =CDbl(Fields!SharePrice.Value)
Operator: >
Filter value: =CDbl(50)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
news:uZ9L9oWWEHA.3716@.TK2MSFTNGP11.phx.gbl...
> I am adding a filter on a float datatype field in my dataset.
> Example:
> =Fields!SharePrice.Value > 50
> When running the report, I get the following error:
> "...the processing of filter for the data set 'dataset' cannot be
performed.
> The comparision failed. Please check the data type returned by the data
> expression."
> I've tried casting the field as decimal with mixed results. Does anyone
> know why this error occurs?
>|||Thanks. That did the trick.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:eNogP1WWEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Try this:
> Filter expression: =CDbl(Fields!SharePrice.Value)
> Operator: >
> Filter value: =CDbl(50)
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
> news:uZ9L9oWWEHA.3716@.TK2MSFTNGP11.phx.gbl...
> > I am adding a filter on a float datatype field in my dataset.
> >
> > Example:
> >
> > =Fields!SharePrice.Value > 50
> >
> > When running the report, I get the following error:
> >
> > "...the processing of filter for the data set 'dataset' cannot be
> performed.
> > The comparision failed. Please check the data type returned by the data
> > expression."
> >
> > I've tried casting the field as decimal with mixed results. Does anyone
> > know why this error occurs?
> >
> >
>
Thursday, March 8, 2012
Adding New Measure to OLAP Cube
To add a record count measure to the olap cube. Create the cube as
usual then run the following VB code with command line parameters
Step1: Build your cube as usual
Step2: Convert the blow vb code to exe prog
Step3: Run the exe with the <Ananlysis server name> <cube name>
parameters (e.g) OLAPcount.exe <Analysis Server> <Cube Name>
Public Sub main()
Dim dsoServer As New DSO.Server
Dim dsoDB As DSO.MDStore
Dim dsoCube As DSO.MDStore
Dim dsoMea As DSO.Measure
Dim dsoAssFactCube As DSO.Cube
Dim dsoPortAnalyzerCube As DSO.Cube
'for storing initial command line arguments as entered by user
Dim strArgs() As String
'for storing the parsed command line arguments
Dim ParsedArgs As String
'for storing the final array of command line arguments
Dim finalArgs() As String
'Splitting the command line arguments based on a space
strArgs = Split(Command$, " ")
'Parsing the command line arguments to generate the parsed string
For i = 0 To UBound(strArgs)
If Len(Trim(strArgs(i))) > 0 Then
ParsedArgs = ParsedArgs & Trim(strArgs(i)) & " "
End If
Next
'Splitting the parsed string into final array of arguments
finalArgs = Split(ParsedArgs, " ")
'Check for correct number of arguments
If UBound(finalArgs) < 2 Then
MsgBox ("Wrong Syntax..." & "or wrong number of
arguments....Correcet Syntax : OLAPcount.exe <Analysis Server> <Cube
Name> (e.g)OLAPcount.exe livdwqprj03 AIGTMSReport1")
Else
'connect to the server (Analysis Server name)
dsoServer.Connect (finalArgs(0))
'Examine whether all necessary components are present (Cube
name)
If dsoServer.MDStores.Find(finalArgs(1)) = False Then
GoTo err_no_database
End If
'Connect with the data base (CUBE) (Cube name)
Set dsoDB = dsoServer.MDStores(finalArgs(1))
If dsoDB.DataSources.Count = 0 Then
GoTo err_no_datasource
ElseIf dsoDB.Dimensions.Count = 0 Then
GoTo err_no_dimensions
ElseIf dsoDB.MDStores.Find("MSP_ASSN_FACT") = False Then
GoTo err_no_fact_cube
ElseIf dsoDB.MDStores.Find("MSP_PORTFOLIO_ANALYZER") = False
Then
GoTo err_no_analyzer
End If
'Set the cube table to use
Set dsoAssFactCube = dsoDB.MDStores("MSP_ASSN_FACT")
Set dsoPortAnalyzerCube =
dsoDB.MDStores("MSP_PORTFOLIO_ANALYZER")
'Specify the name of the new measure
Set dsoMea = dsoAssFactCube.Measures.AddNew("Total
Assignments")
'Specify the source column based on which the operation need to
be performed
'dsoMea.SourceColumn =
"""MSP_CUBE_ASSN_FACT"".""ENT_ASSIGNMENT_CODE6"""
dsoMea.SourceColumn = """MSP_CUBE_ASSN_FACT"".""PROJ_UID"""
'The datatype for the column
dsoMea.SourceColumnType = ADODB.DataTypeEnum.adDecimal
'The method for the column aggSum or aggCount aggregates the
column by summation or counts.
dsoMea.AggregateFunction = aggCount
'update the cube
dsoAssFactCube.Update
dsoAssFactCube.Process
'dsoAnalyzerCube represents a virtual Cube. the measure of a
virtual Cubes has
'the characteristics of the measure of the material cubes
Set dsoMea = dsoPortAnalyzerCube.Measures.AddNew("Total
Assignments")
'The column is indicated in "more normal" form, since the
measure belongs to the virtual Cube!
'dsoMea.SourceColumn = "MSP_ASSN_FACT.FIXED COST"
dsoMea.SourceColumn = "MSP_ASSN_FACT.Total Assignments"
dsoPortAnalyzerCube.Update
dsoPortAnalyzerCube.Process
dsoDB.Process
leave_now:
UserOLAPUpdate = 0
' Exit Function
err_no_database:
l_errnum = 1
s_errdesc = "Datenbank konnte nicht gefunden werden!"
UserOLAPUpdate = vbObjectError + 1
' Exit Function
err_no_datasource:
l_errnum = 1
s_errdesc = "Datenquelle konnte nicht gefunden werden!"
UserOLAPUpdate = vbObjectError + 2
' Exit Function
err_no_dimensions:
l_errnum = 1
s_errdesc = "Dimensionen konnten nicht gefunden werden!"
UserOLAPUpdate = vbObjectError + 3
' Exit Function
err_no_fact_cube:
l_errnum = 1
s_errdesc = "Cube MSP_ASSN_FACT konnte nicht gefunden werden!"
UserOLAPUpdate = vbObjectError + 4
' Exit Function
err_no_analyzer:
l_errnum = 1
s_errdesc = "Cube MSP_PORTFOLIO_ANALYZER konnte nicht gefunden
werden! "
UserOLAPUpdate = vbObjectError + 5
' Exit Function
error_handler:
l_errnum = Err.Number
s_errdesc = Err.Description
UserOLAPUpdate = 1 ' although it could be any non-zero value
' to indicate an error
End If
End SubPerhaps you should head for the ng
http://www.microsoft.com/communitie...sqlserver.olap
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"wilsonjust@.gmail.com" wrote:
> To add a record count measure to the olap cube. Create the cube as
> usual then run the following VB code with command line parameters
> Step1: Build your cube as usual
> Step2: Convert the blow vb code to exe prog
> Step3: Run the exe with the <Ananlysis server name> <cube name>
> parameters (e.g) OLAPcount.exe <Analysis Server> <Cube Name>
> Public Sub main()
> Dim dsoServer As New DSO.Server
> Dim dsoDB As DSO.MDStore
> Dim dsoCube As DSO.MDStore
> Dim dsoMea As DSO.Measure
> Dim dsoAssFactCube As DSO.Cube
> Dim dsoPortAnalyzerCube As DSO.Cube
> 'for storing initial command line arguments as entered by user
> Dim strArgs() As String
> 'for storing the parsed command line arguments
> Dim ParsedArgs As String
> 'for storing the final array of command line arguments
> Dim finalArgs() As String
> 'Splitting the command line arguments based on a space
> strArgs = Split(Command$, " ")
> 'Parsing the command line arguments to generate the parsed string
> For i = 0 To UBound(strArgs)
> If Len(Trim(strArgs(i))) > 0 Then
> ParsedArgs = ParsedArgs & Trim(strArgs(i)) & " "
> End If
> Next
> 'Splitting the parsed string into final array of arguments
> finalArgs = Split(ParsedArgs, " ")
> 'Check for correct number of arguments
> If UBound(finalArgs) < 2 Then
> MsgBox ("Wrong Syntax..." & "or wrong number of
> arguments....Correcet Syntax : OLAPcount.exe <Analysis Server> <Cube
> Name> (e.g)OLAPcount.exe livdwqprj03 AIGTMSReport1")
> Else
> 'connect to the server (Analysis Server name)
> dsoServer.Connect (finalArgs(0))
> 'Examine whether all necessary components are present (Cube
> name)
> If dsoServer.MDStores.Find(finalArgs(1)) = False Then
> GoTo err_no_database
> End If
> 'Connect with the data base (CUBE) (Cube name)
> Set dsoDB = dsoServer.MDStores(finalArgs(1))
> If dsoDB.DataSources.Count = 0 Then
> GoTo err_no_datasource
> ElseIf dsoDB.Dimensions.Count = 0 Then
> GoTo err_no_dimensions
> ElseIf dsoDB.MDStores.Find("MSP_ASSN_FACT") = False Then
> GoTo err_no_fact_cube
> ElseIf dsoDB.MDStores.Find("MSP_PORTFOLIO_ANALYZER") = False
> Then
> GoTo err_no_analyzer
> End If
> 'Set the cube table to use
> Set dsoAssFactCube = dsoDB.MDStores("MSP_ASSN_FACT")
> Set dsoPortAnalyzerCube =
> dsoDB.MDStores("MSP_PORTFOLIO_ANALYZER")
> 'Specify the name of the new measure
> Set dsoMea = dsoAssFactCube.Measures.AddNew("Total
> Assignments")
> 'Specify the source column based on which the operation need to
> be performed
> 'dsoMea.SourceColumn =
> """MSP_CUBE_ASSN_FACT"".""ENT_ASSIGNMENT_CODE6"""
> dsoMea.SourceColumn = """MSP_CUBE_ASSN_FACT"".""PROJ_UID"""
> 'The datatype for the column
> dsoMea.SourceColumnType = ADODB.DataTypeEnum.adDecimal
> 'The method for the column aggSum or aggCount aggregates the
> column by summation or counts.
> dsoMea.AggregateFunction = aggCount
> 'update the cube
> dsoAssFactCube.Update
> dsoAssFactCube.Process
> 'dsoAnalyzerCube represents a virtual Cube. the measure of a
> virtual Cubes has
> 'the characteristics of the measure of the material cubes
> Set dsoMea = dsoPortAnalyzerCube.Measures.AddNew("Total
> Assignments")
> 'The column is indicated in "more normal" form, since the
> measure belongs to the virtual Cube!
> 'dsoMea.SourceColumn = "MSP_ASSN_FACT.FIXED COST"
> dsoMea.SourceColumn = "MSP_ASSN_FACT.Total Assignments"
> dsoPortAnalyzerCube.Update
> dsoPortAnalyzerCube.Process
> dsoDB.Process
> leave_now:
> UserOLAPUpdate = 0
> ' Exit Function
> err_no_database:
> l_errnum = 1
> s_errdesc = "Datenbank konnte nicht gefunden werden!"
> UserOLAPUpdate = vbObjectError + 1
> ' Exit Function
> err_no_datasource:
> l_errnum = 1
> s_errdesc = "Datenquelle konnte nicht gefunden werden!"
> UserOLAPUpdate = vbObjectError + 2
> ' Exit Function
> err_no_dimensions:
> l_errnum = 1
> s_errdesc = "Dimensionen konnten nicht gefunden werden!"
> UserOLAPUpdate = vbObjectError + 3
> ' Exit Function
> err_no_fact_cube:
> l_errnum = 1
> s_errdesc = "Cube MSP_ASSN_FACT konnte nicht gefunden werden!"
> UserOLAPUpdate = vbObjectError + 4
> ' Exit Function
> err_no_analyzer:
> l_errnum = 1
> s_errdesc = "Cube MSP_PORTFOLIO_ANALYZER konnte nicht gefunden
> werden! "
> UserOLAPUpdate = vbObjectError + 5
> ' Exit Function
> error_handler:
> l_errnum = Err.Number
> s_errdesc = Err.Description
> UserOLAPUpdate = 1 ' although it could be any non-zero value
> ' to indicate an error
> End If
> End Sub
>
usual then run the following VB code with command line parameters
Step1: Build your cube as usual
Step2: Convert the blow vb code to exe prog
Step3: Run the exe with the <Ananlysis server name> <cube name>
parameters (e.g) OLAPcount.exe <Analysis Server> <Cube Name>
Public Sub main()
Dim dsoServer As New DSO.Server
Dim dsoDB As DSO.MDStore
Dim dsoCube As DSO.MDStore
Dim dsoMea As DSO.Measure
Dim dsoAssFactCube As DSO.Cube
Dim dsoPortAnalyzerCube As DSO.Cube
'for storing initial command line arguments as entered by user
Dim strArgs() As String
'for storing the parsed command line arguments
Dim ParsedArgs As String
'for storing the final array of command line arguments
Dim finalArgs() As String
'Splitting the command line arguments based on a space
strArgs = Split(Command$, " ")
'Parsing the command line arguments to generate the parsed string
For i = 0 To UBound(strArgs)
If Len(Trim(strArgs(i))) > 0 Then
ParsedArgs = ParsedArgs & Trim(strArgs(i)) & " "
End If
Next
'Splitting the parsed string into final array of arguments
finalArgs = Split(ParsedArgs, " ")
'Check for correct number of arguments
If UBound(finalArgs) < 2 Then
MsgBox ("Wrong Syntax..." & "or wrong number of
arguments....Correcet Syntax : OLAPcount.exe <Analysis Server> <Cube
Name> (e.g)OLAPcount.exe livdwqprj03 AIGTMSReport1")
Else
'connect to the server (Analysis Server name)
dsoServer.Connect (finalArgs(0))
'Examine whether all necessary components are present (Cube
name)
If dsoServer.MDStores.Find(finalArgs(1)) = False Then
GoTo err_no_database
End If
'Connect with the data base (CUBE) (Cube name)
Set dsoDB = dsoServer.MDStores(finalArgs(1))
If dsoDB.DataSources.Count = 0 Then
GoTo err_no_datasource
ElseIf dsoDB.Dimensions.Count = 0 Then
GoTo err_no_dimensions
ElseIf dsoDB.MDStores.Find("MSP_ASSN_FACT") = False Then
GoTo err_no_fact_cube
ElseIf dsoDB.MDStores.Find("MSP_PORTFOLIO_ANALYZER") = False
Then
GoTo err_no_analyzer
End If
'Set the cube table to use
Set dsoAssFactCube = dsoDB.MDStores("MSP_ASSN_FACT")
Set dsoPortAnalyzerCube =
dsoDB.MDStores("MSP_PORTFOLIO_ANALYZER")
'Specify the name of the new measure
Set dsoMea = dsoAssFactCube.Measures.AddNew("Total
Assignments")
'Specify the source column based on which the operation need to
be performed
'dsoMea.SourceColumn =
"""MSP_CUBE_ASSN_FACT"".""ENT_ASSIGNMENT_CODE6"""
dsoMea.SourceColumn = """MSP_CUBE_ASSN_FACT"".""PROJ_UID"""
'The datatype for the column
dsoMea.SourceColumnType = ADODB.DataTypeEnum.adDecimal
'The method for the column aggSum or aggCount aggregates the
column by summation or counts.
dsoMea.AggregateFunction = aggCount
'update the cube
dsoAssFactCube.Update
dsoAssFactCube.Process
'dsoAnalyzerCube represents a virtual Cube. the measure of a
virtual Cubes has
'the characteristics of the measure of the material cubes
Set dsoMea = dsoPortAnalyzerCube.Measures.AddNew("Total
Assignments")
'The column is indicated in "more normal" form, since the
measure belongs to the virtual Cube!
'dsoMea.SourceColumn = "MSP_ASSN_FACT.FIXED COST"
dsoMea.SourceColumn = "MSP_ASSN_FACT.Total Assignments"
dsoPortAnalyzerCube.Update
dsoPortAnalyzerCube.Process
dsoDB.Process
leave_now:
UserOLAPUpdate = 0
' Exit Function
err_no_database:
l_errnum = 1
s_errdesc = "Datenbank konnte nicht gefunden werden!"
UserOLAPUpdate = vbObjectError + 1
' Exit Function
err_no_datasource:
l_errnum = 1
s_errdesc = "Datenquelle konnte nicht gefunden werden!"
UserOLAPUpdate = vbObjectError + 2
' Exit Function
err_no_dimensions:
l_errnum = 1
s_errdesc = "Dimensionen konnten nicht gefunden werden!"
UserOLAPUpdate = vbObjectError + 3
' Exit Function
err_no_fact_cube:
l_errnum = 1
s_errdesc = "Cube MSP_ASSN_FACT konnte nicht gefunden werden!"
UserOLAPUpdate = vbObjectError + 4
' Exit Function
err_no_analyzer:
l_errnum = 1
s_errdesc = "Cube MSP_PORTFOLIO_ANALYZER konnte nicht gefunden
werden! "
UserOLAPUpdate = vbObjectError + 5
' Exit Function
error_handler:
l_errnum = Err.Number
s_errdesc = Err.Description
UserOLAPUpdate = 1 ' although it could be any non-zero value
' to indicate an error
End If
End SubPerhaps you should head for the ng
http://www.microsoft.com/communitie...sqlserver.olap
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"wilsonjust@.gmail.com" wrote:
> To add a record count measure to the olap cube. Create the cube as
> usual then run the following VB code with command line parameters
> Step1: Build your cube as usual
> Step2: Convert the blow vb code to exe prog
> Step3: Run the exe with the <Ananlysis server name> <cube name>
> parameters (e.g) OLAPcount.exe <Analysis Server> <Cube Name>
> Public Sub main()
> Dim dsoServer As New DSO.Server
> Dim dsoDB As DSO.MDStore
> Dim dsoCube As DSO.MDStore
> Dim dsoMea As DSO.Measure
> Dim dsoAssFactCube As DSO.Cube
> Dim dsoPortAnalyzerCube As DSO.Cube
> 'for storing initial command line arguments as entered by user
> Dim strArgs() As String
> 'for storing the parsed command line arguments
> Dim ParsedArgs As String
> 'for storing the final array of command line arguments
> Dim finalArgs() As String
> 'Splitting the command line arguments based on a space
> strArgs = Split(Command$, " ")
> 'Parsing the command line arguments to generate the parsed string
> For i = 0 To UBound(strArgs)
> If Len(Trim(strArgs(i))) > 0 Then
> ParsedArgs = ParsedArgs & Trim(strArgs(i)) & " "
> End If
> Next
> 'Splitting the parsed string into final array of arguments
> finalArgs = Split(ParsedArgs, " ")
> 'Check for correct number of arguments
> If UBound(finalArgs) < 2 Then
> MsgBox ("Wrong Syntax..." & "or wrong number of
> arguments....Correcet Syntax : OLAPcount.exe <Analysis Server> <Cube
> Name> (e.g)OLAPcount.exe livdwqprj03 AIGTMSReport1")
> Else
> 'connect to the server (Analysis Server name)
> dsoServer.Connect (finalArgs(0))
> 'Examine whether all necessary components are present (Cube
> name)
> If dsoServer.MDStores.Find(finalArgs(1)) = False Then
> GoTo err_no_database
> End If
> 'Connect with the data base (CUBE) (Cube name)
> Set dsoDB = dsoServer.MDStores(finalArgs(1))
> If dsoDB.DataSources.Count = 0 Then
> GoTo err_no_datasource
> ElseIf dsoDB.Dimensions.Count = 0 Then
> GoTo err_no_dimensions
> ElseIf dsoDB.MDStores.Find("MSP_ASSN_FACT") = False Then
> GoTo err_no_fact_cube
> ElseIf dsoDB.MDStores.Find("MSP_PORTFOLIO_ANALYZER") = False
> Then
> GoTo err_no_analyzer
> End If
> 'Set the cube table to use
> Set dsoAssFactCube = dsoDB.MDStores("MSP_ASSN_FACT")
> Set dsoPortAnalyzerCube =
> dsoDB.MDStores("MSP_PORTFOLIO_ANALYZER")
> 'Specify the name of the new measure
> Set dsoMea = dsoAssFactCube.Measures.AddNew("Total
> Assignments")
> 'Specify the source column based on which the operation need to
> be performed
> 'dsoMea.SourceColumn =
> """MSP_CUBE_ASSN_FACT"".""ENT_ASSIGNMENT_CODE6"""
> dsoMea.SourceColumn = """MSP_CUBE_ASSN_FACT"".""PROJ_UID"""
> 'The datatype for the column
> dsoMea.SourceColumnType = ADODB.DataTypeEnum.adDecimal
> 'The method for the column aggSum or aggCount aggregates the
> column by summation or counts.
> dsoMea.AggregateFunction = aggCount
> 'update the cube
> dsoAssFactCube.Update
> dsoAssFactCube.Process
> 'dsoAnalyzerCube represents a virtual Cube. the measure of a
> virtual Cubes has
> 'the characteristics of the measure of the material cubes
> Set dsoMea = dsoPortAnalyzerCube.Measures.AddNew("Total
> Assignments")
> 'The column is indicated in "more normal" form, since the
> measure belongs to the virtual Cube!
> 'dsoMea.SourceColumn = "MSP_ASSN_FACT.FIXED COST"
> dsoMea.SourceColumn = "MSP_ASSN_FACT.Total Assignments"
> dsoPortAnalyzerCube.Update
> dsoPortAnalyzerCube.Process
> dsoDB.Process
> leave_now:
> UserOLAPUpdate = 0
> ' Exit Function
> err_no_database:
> l_errnum = 1
> s_errdesc = "Datenbank konnte nicht gefunden werden!"
> UserOLAPUpdate = vbObjectError + 1
> ' Exit Function
> err_no_datasource:
> l_errnum = 1
> s_errdesc = "Datenquelle konnte nicht gefunden werden!"
> UserOLAPUpdate = vbObjectError + 2
> ' Exit Function
> err_no_dimensions:
> l_errnum = 1
> s_errdesc = "Dimensionen konnten nicht gefunden werden!"
> UserOLAPUpdate = vbObjectError + 3
> ' Exit Function
> err_no_fact_cube:
> l_errnum = 1
> s_errdesc = "Cube MSP_ASSN_FACT konnte nicht gefunden werden!"
> UserOLAPUpdate = vbObjectError + 4
> ' Exit Function
> err_no_analyzer:
> l_errnum = 1
> s_errdesc = "Cube MSP_PORTFOLIO_ANALYZER konnte nicht gefunden
> werden! "
> UserOLAPUpdate = vbObjectError + 5
> ' Exit Function
> error_handler:
> l_errnum = Err.Number
> s_errdesc = Err.Description
> UserOLAPUpdate = 1 ' although it could be any non-zero value
> ' to indicate an error
> End If
> End Sub
>
Tuesday, March 6, 2012
Adding new field in a table which being used for replication
Hi dear friends,
I'm trying to add a new field in a table and in my desired
position (OrdinalPosition), but I can not and I get the
following error message. As this table is involved in
replication I get this error message:
('tblTransPayments' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]
Cannot drop the table 'dbo.tblTransPayments' because it is
being used for replication.)
It should be noticed that I have lots of data in this
table and I'm not going to drop the table.
I can easily add my new field at the end of the column's
list but not as 21th column which I want to.
Thanks in advance.Hi -
Please check SQL BOL for "sp_repladdcolumn" and "sp_repldropcolumn".
Thanks
-Surajit
"Mattew" <anonymous@.discussions.microsoft.com> wrote in message
news:19d4101c44d7b$6507c260$a101280a@.phx.gbl...
> Hi dear friends,
> I'm trying to add a new field in a table and in my desired
> position (OrdinalPosition), but I can not and I get the
> following error message. As this table is involved in
> replication I get this error message:
> ('tblTransPayments' table
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]
> Cannot drop the table 'dbo.tblTransPayments' because it is
> being used for replication.)
> It should be noticed that I have lots of data in this
> table and I'm not going to drop the table.
> I can easily add my new field at the end of the column's
> list but not as 21th column which I want to.
> Thanks in advance.|||Surajit,
this won't give the ability to specify the position, and Matthew doesn't
want the column created at the end.
Regards,
Paul Ibison
I'm trying to add a new field in a table and in my desired
position (OrdinalPosition), but I can not and I get the
following error message. As this table is involved in
replication I get this error message:
('tblTransPayments' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]
Cannot drop the table 'dbo.tblTransPayments' because it is
being used for replication.)
It should be noticed that I have lots of data in this
table and I'm not going to drop the table.
I can easily add my new field at the end of the column's
list but not as 21th column which I want to.
Thanks in advance.Hi -
Please check SQL BOL for "sp_repladdcolumn" and "sp_repldropcolumn".
Thanks
-Surajit
"Mattew" <anonymous@.discussions.microsoft.com> wrote in message
news:19d4101c44d7b$6507c260$a101280a@.phx.gbl...
> Hi dear friends,
> I'm trying to add a new field in a table and in my desired
> position (OrdinalPosition), but I can not and I get the
> following error message. As this table is involved in
> replication I get this error message:
> ('tblTransPayments' table
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]
> Cannot drop the table 'dbo.tblTransPayments' because it is
> being used for replication.)
> It should be noticed that I have lots of data in this
> table and I'm not going to drop the table.
> I can easily add my new field at the end of the column's
> list but not as 21th column which I want to.
> Thanks in advance.|||Surajit,
this won't give the ability to specify the position, and Matthew doesn't
want the column created at the end.
Regards,
Paul Ibison
Adding mutiple columns
HI,
I have two tables A and B with following Data
Actual output is 252000.
I have two tables A and B with following Data
Table A
ID(PK) | V | W | X | Final
-
1 | 4 | 5 | 6 | output
Table B
ID | Par_ID (FK) | Cost_Per_Annum
1 | 1 | 12000
2 | 1 | 24000
3 | 1 | 14000
Output should be calculated using below the formula .
output = Column V * 1st record of par_id=1 + Column w * 2nd record of par_id=1 + Column X * 3rd record of par_id=1
output = 4* 12000 + 5* 24000 + 6 * 14000
How can i do like this i have tried several ways and failed. Please suggest me.
hi Rajesh,
Try this out
Code Snippet
declare @.TableA table(ID int,V int,W int, X int )
declare @.TableB table(ID int,Par_ID int,Cost_Per_Annum int)
insert into @.TableA select 1,4,5,6
insert into @.TableB select 1,1,12000
insert into @.TableB select 2,1,24000
insert into @.TableB select 3,1,14000
select a.*,
a.v * (
select top 1 v.Cost_Per_Annum from
( select top 1 Cost_Per_Annum,ID from @.TableB where Par_ID=a.ID
) v order by id desc )+
a.W *(
select top 1 v.Cost_Per_Annum from
( select top 2 Cost_Per_Annum,ID from @.TableB where Par_ID=a.ID
) v order by id desc)+
a.X *(
select top 1 v.Cost_Per_Annum from
( select top 3 Cost_Per_Annum,ID from @.TableB where Par_ID=a.ID
) v order by id desc ) Final
from @.TableA a
This should get you started.
Code Snippet
declare @.t1 table (i int, v int, w int, x int)
insert @.t1 select 1,4,5,6
declare @.t2 table (iii int, i int, c int)
insert @.t2 select 1,1,12000
union all select 2,1,24000
union all select 3,1,14000
select a.i,sum(vv*c) as [output]
from(
select * ,case ii when 'v' then 1 when 'w' then 2 when 'x' then 3 end as iii
from (select i, v,w,x from @.t1) as p
unpivot
(vv for ii in(v,w,x)) as unpvt
)a
join @.t2 b on a.i=b.i and a.iii=b.iii
group by a.i
Actual output is 252000.
I think problem in 16th line. Can you please check.
thanks|||Please copy the query block as it is and check once more , iam getting the output 252000 with the same query.|||i am getting 202000. Please refer following link for screen shot .
http://picavo.com//images/371729err.JPG
Raj
|||It is really strange, still working for me. Which version of sql server r u using?|||I am using sqlserver 2000.
|||I had checked it on MSDE with SP3 and Sql server Express edition, working on both.|||can i report this problem to Microsoft bug team?
|||
Try:
Code Snippet
createtable dbo.t1 (
ID int,
V int,
W int,
X int
)
go
insertinto dbo.t1 values(1, 4, 5, 6)
insertinto dbo.t1 values(2, 7, 8, 9)
go
createtable dbo.t2 (
ID int,
Par_ID int,
Cost_Per_Annum int
)
go
insertinto dbo.t2 values(1, 1, 12000)
insertinto dbo.t2 values(2, 1, 24000)
insertinto dbo.t2 values(3, 1, 14000)
insertinto dbo.t2 values(4, 2, 10000)
insertinto dbo.t2 values(5, 2, 20000)
insertinto dbo.t2 values(6, 2, 30000)
go
select
x.ID,
(V * Cost_Per_Annum_V)+(W * Cost_Per_Annum_W)+(X * Cost_Per_Annum_X)as [output]
from
dbo.t1 as x
innerjoin
(
select
c.Par_ID,
max(casewhen d.rn = 1 then c.Cost_Per_Annum end)as Cost_Per_Annum_V,
max(casewhen d.rn = 2 then c.Cost_Per_Annum end)as Cost_Per_Annum_W,
max(casewhen d.rn = 3 then c.Cost_Per_Annum end)as Cost_Per_Annum_X
from
dbo.t2 as c
innerjoin
(
select
a.Par_ID,
a.ID,
count(*)as rn
from
dbo.t2 as a
innerjoin
dbo.t2 as b
on a.Par_ID = b.Par_ID
and a.ID >= b.ID
groupby
a.Par_ID,
a.ID
)as d
on c.Par_ID = d.Par_ID
and c.ID = d.ID
groupby
c.Par_ID
)as y
on x.ID = y.Par_ID
go
droptable dbo.t1, dbo.t2
go
AMB
Saturday, February 25, 2012
Adding Leading Zeros to a char value - TSQL Question
Hi all:
I have save stored procedure which does an insert and update. Before the insert or update statements, I have the following SQL to adding leading zeroes to in my input params:
DECLARE @.SchoolCode_Modified char(6)
SET @.SchoolCode_Modified = (SELECT Right(Replicate('0',6) + '123',6))
SELECT @.SchoolCode_Modified AS Col1
The problem is that this works on by itself on SSMS, but when incorporated in the SPROC, it doesnt add the leading zeros. The datatype for both of the colum is char(6) as well. If I run the above query in SQL Server Management Studio, it displays Col1 with 000123 in it, which is what I want, from the sproc, but that doesnt happen. Why? Can someone please help me? The following is how it is implemented in the SPROC:
CREATE PROCEDURE [LAF].[uspSaveLoanApplication]
...more params here
@.SchoolCode char(6),
@.BranchCode char(2),
@.PK int OUTPUT,
@.PreviousLoanApplicationStatus char(3) OUTPUT
AS
SET NOCOUNT OFF
SET @.PreviousLoanApplicationStatus = 0
IF EXISTS(SELECT [LoanApplicationStatusCode] FROM [LAF].[LoanApplication] WHERE [LoanApplicationID] = @.LoanApplicationID)
BEGIN
SET @.PreviousLoanApplicationStatus = (SELECT [LoanApplicationStatusCode] FROM [LAF].[LoanApplication] WHERE [LoanApplicationID] = @.LoanApplicationID)
SELECT @.PreviousLoanApplicationStatus PreviousLoanApplicationStatus
END
-- Add Leading Zeroes to School Code and Branch Code to conform with CLIPS Formatting
DECLARE @.SchoolCode_Modified char(6)
DECLARE @.BranchCode_Modified char(2)
SET @.SchoolCode_Modified = (SELECT Right(Replicate('0',6) + @.SchoolCode,6))
SET @.BranchCode_Modified = (SELECT Right(Replicate('0',2) + @.BranchCode,2))
IF EXISTS(SELECT [LoanApplicationID] FROM [LAF].[LoanApplication] WHERE [LoanApplicationID] = @.LoanApplicationID)
BEGIN
UPDATE [LAF].[LoanApplication] SET
....more here
[SchoolCode] = @.SchoolCode_Modified,
[BranchCode] = @.BranchCode_Modified,
...more here
WHERE
[LoanApplicationID] = @.LoanApplicationID
AND [UpdatedOn] = @.LastUpdated
SELECT @.LoanApplicationID PK
SET @.PK = @.LoanApplicationID
END
ELSE
BEGIN
INSERT INTO [LAF].[LoanApplication] (
.....more here
[SchoolCode],
[BranchCode],
.....more here
) VALUES (
....more here
@.SchoolCode_Modified,
@.BranchCode_Modified,
....more here
)
SET @.PK = SCOPE_IDENTITY()
SELECT @.PK PK
END
With a quick scan, your code appears correct.
How is it that you are finding that the values of @.SchoolCode_Modified and @.BranchCode_Modified do NOT have the leading zeros?
|||It appears that your problem is that
@.SchoolCode is declared as char(6).
So it is NOT '123', but ' 123', i.e., 3 spaces followed by '123'.
It appears you must declare @.SchoolCode as varchar(6) if you want to get those leading zeroes in there.
Dan
|||DanR1 wrote:
It appears that your problem is that
@.SchoolCode is declared as char(6).
So it is NOT '123', but ' 123', i.e., 3 spaces followed by '123'.
It appears you must declare @.SchoolCode as varchar(6) if you want to get those leading zeroes in there.
Dan
Good catch...
Actually, I don't think that it would be ' 123', but instead 'should' be '123 '. In either case though, adding preceding characters and then taking the rightmost 6 characters would not effect any change in the value.
|||Arnie,
Yes, you are right about the trailing blanks, instead of leading blanks as I suggested. (I checked on it after I made my post, and figured it wasn't worth the trouble to make the edit.)
So it seems like "ltrim(rtrim(@.SchoolCode))" is what should be preceded by "000000" before taking the 6 rightmost characters.
Dan
|||DECLARE @.SchoolCode_Modified char(6)
SET @.SchoolCode_Modified = '123'
SET @.SchoolCode_Modified = REPLICATE('0',6 -LEN(@.SchoolCode_Modified))+ @.SchoolCode_Modified
SELECT @.SchoolCode_Modified AS Col1
Adding Leading Zeros
I need to make sure that numbers returned form a SELECT have at least one digit before the decimal point. Right now I have the following SQL statement in a DTS
SELECT PIN, ROUND (SUM(AREA/43560), 2) AS Expr1
FROM table GROUP BY PIN ORDER BY PIN
This produces lines like this ...
1-0005 -01-001,6250.410000
1-0008 -01-001,940.810000
1-0010 -01-001,9.230000
1-0010 -01-001A,.730000
1-0010 -01-002,73.520000
1-0010 -01-003,.680000
I need the output to look like this (check lines 4 and 6) ...
1-0005 -01-001,6250.410000
1-0008 -01-001,940.810000
1-0010 -01-001,9.230000
1-0010 -01-001A,0.730000
1-0010 -01-002,73.520000
1-0010 -01-003,0.680000
Any ideas?
DavidHello,
when you use Oracle you can convert the sum to a char with a special format mask
SELECT PIN, TO_CHAR(ROUND (SUM(AREA/43560), 2), '0.99' AS Expr1
Hope that helps ?
Regards
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com|||Oops. Forgot to mention that I'm running SQLServer 7. Haven't found a comparable function yet.
David|||Hello again,
that is important :)
SELECT PIN, CAST(ROUND (SUM(AREA/43560), 2) AS MONEY) AS Expr1
I am using the enterprise manager and it look ok ...
Hope this help ?
Regards
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com|||When I tried
CAST(ROUND (SUM(AREA/43560), 2) AS MONEY)
I still got the following results
6250.4100
940.8100
9.2300
.7300
73.5200
.6800
:confused:
However, taking your lead, I tried
CAST(ROUND (SUM(AREA/43560), 2) AS CHAR)
and got
6250.410000
940.810000
9.230000
0.730000
73.520000
0.680000
:)
Works for me! Thanks!
David
SELECT PIN, ROUND (SUM(AREA/43560), 2) AS Expr1
FROM table GROUP BY PIN ORDER BY PIN
This produces lines like this ...
1-0005 -01-001,6250.410000
1-0008 -01-001,940.810000
1-0010 -01-001,9.230000
1-0010 -01-001A,.730000
1-0010 -01-002,73.520000
1-0010 -01-003,.680000
I need the output to look like this (check lines 4 and 6) ...
1-0005 -01-001,6250.410000
1-0008 -01-001,940.810000
1-0010 -01-001,9.230000
1-0010 -01-001A,0.730000
1-0010 -01-002,73.520000
1-0010 -01-003,0.680000
Any ideas?
DavidHello,
when you use Oracle you can convert the sum to a char with a special format mask
SELECT PIN, TO_CHAR(ROUND (SUM(AREA/43560), 2), '0.99' AS Expr1
Hope that helps ?
Regards
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com|||Oops. Forgot to mention that I'm running SQLServer 7. Haven't found a comparable function yet.
David|||Hello again,
that is important :)
SELECT PIN, CAST(ROUND (SUM(AREA/43560), 2) AS MONEY) AS Expr1
I am using the enterprise manager and it look ok ...
Hope this help ?
Regards
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com|||When I tried
CAST(ROUND (SUM(AREA/43560), 2) AS MONEY)
I still got the following results
6250.4100
940.8100
9.2300
.7300
73.5200
.6800
:confused:
However, taking your lead, I tried
CAST(ROUND (SUM(AREA/43560), 2) AS CHAR)
and got
6250.410000
940.810000
9.230000
0.730000
73.520000
0.680000
:)
Works for me! Thanks!
David
Friday, February 24, 2012
Adding file information to SQL database on file upload
Hi there :)
I'm in the final stage of my asp.net project and one of the last things I need to do is add the following file information to my SQL server 2000 database when a file is uploaded:
First of all I have a resource table to which I need to add:
- filename
- file_path
- file_size
(the resource_id has a auto increment value)
so that should hopefully be straight forward when the file is uploaded. The next step is to reference the new resource_id in my module_resource table. My module resource table consists of:
- resource_id (foreign key)
- module_id (foreign key)
So, adding the module_id is easy enough as I can just get the value using Request.QueryString["module_id"]. The bit that I am unsure about is how to insert the new resource_id from the resource table into the module_resource table on file upload. How is this done? Using one table would solve the issue but I want one resource to be available to all modules - many to many relationship.
Any ideas?
Many thanks :)Since Resource_ID is an Identity column, you should be able to perform a SCOPE_IDENTITY() after the INSERT into the Resource table to pick up the value.
For example:
DECLARE @.resourceID bigintINSERT INTO
Resource
(
filename,
file_path,
file_size
)
VALUES
(
@.filename,
@.file_path,
@.file_size
)
SELECT @.resource_ID = SCOPE_IDENTITY()INSERT INTO
Module_Resource
(
module_ID,
resource_ID
)
VALUES
(
@.module_ID,
@.resource_ID
)
Terri
Sunday, February 19, 2012
Adding dates giving blank or incorrect date
My report has a finishdate column which is grouped into week intervals, Im trying to run the following formula to get the Saturday(end of the week) based on the finishdate e.g if the finish date is 4/7/2006 which is a Tuesday, I want to find out what the end off week date is - which would be 8/7/2006 (Saturday) and place this into another column.
NumberVar whatday;
whatday = dayofweek({finishdate});
If whatday = 2 then //monday
{finishdate} + 5;
Else whatday = 3 then
{finishdate} + 4;
Else whatday = 4 then
{finishdate} + 3;
Else whatday = 5 then
{finishdate} + 2;
Else whatday = 6 then
{finishdate} + 1;
This is not returning any errors but also it is giving me just a blank output. Also when i run the lines
{finishdate} + 4;
on its own, the dates are adding up correctly
OR
dayofweek({finishdate}) onit owns it returns the correct day off week for the finishdate
when i put variables
NumberVar whatday;
whatday = dayofweek({finishdate});
If whatday = 2 then //monday
{finishdate} + 5;
i get a day returned off 1/1/-4713 or an incorrect dayofweek
I have no idea why this is returned.
I am using Crystal Report 7 and the dateadd() is not available, I dont know why when run with conditions etc such as if and variable declarations, it doesnt return the right date, but when run on its own it works.
Can someone help,
thanksThe common mistake people do with Crystal, is when they forget there are two different syntaxes: Crystal and VB. In your case, I think the problem is in assignment operator. Try whatday := dayofweek({finishdate}); instead of whatday = dayofweek({finishdate}); The latter would return the boolean value as a result of comparision, not assignment.
NumberVar whatday;
whatday = dayofweek({finishdate});
If whatday = 2 then //monday
{finishdate} + 5;
Else whatday = 3 then
{finishdate} + 4;
Else whatday = 4 then
{finishdate} + 3;
Else whatday = 5 then
{finishdate} + 2;
Else whatday = 6 then
{finishdate} + 1;
This is not returning any errors but also it is giving me just a blank output. Also when i run the lines
{finishdate} + 4;
on its own, the dates are adding up correctly
OR
dayofweek({finishdate}) onit owns it returns the correct day off week for the finishdate
when i put variables
NumberVar whatday;
whatday = dayofweek({finishdate});
If whatday = 2 then //monday
{finishdate} + 5;
i get a day returned off 1/1/-4713 or an incorrect dayofweek
I have no idea why this is returned.
I am using Crystal Report 7 and the dateadd() is not available, I dont know why when run with conditions etc such as if and variable declarations, it doesnt return the right date, but when run on its own it works.
Can someone help,
thanksThe common mistake people do with Crystal, is when they forget there are two different syntaxes: Crystal and VB. In your case, I think the problem is in assignment operator. Try whatday := dayofweek({finishdate}); instead of whatday = dayofweek({finishdate}); The latter would return the boolean value as a result of comparision, not assignment.
Thursday, February 16, 2012
Adding data to database
would like to enquiry about the following codes to adding data to the database, it does not work.
Dim sqlConn As New SqlClient.SqlConnection
Dim sqlCmd1 As New SqlClient.SqlCommand
Dim sqlCmd2 As New SqlClient.SqlCommand
Dim insertCmd As New SqlClient.SqlCommand
Dim sdrData As SqlClient.SqlDataReader
sqlConn.ConnectionString = "server=localhost;database=Tsign;uid=sa;pwd=;"
sqlConn.Open()
sqlCmd1.CommandText = "select * from Customer where userId='" + userId.Text + "'"
sqlCmd1.Connection = sqlConn
sqlCmd2.CommandText = "select * from Customer where email='" + email.Text + "'"
sqlCmd2.Connection = sqlConn
sdrData = sqlCmd1.ExecuteReader()
If sdrData.Read() Then
userNameTaken.Visible = True
Else
sdrData = sqlCmd2.ExecuteReader()
If sdrData.Read() Then
userEmailTaken.Visible = True
Else
insertCmd.CommandText = "INSERT INTO Customer(name, address, gender, password, occupation, income, m_status, email, hPhone, oPhone, dob, tPhone, userId) VALUES('" + userId.Text + "', '" + password.Text + "', '" + email.Text + "', '" + nameCust.Text + "', '" + dob.Text + "', '" + gender.SelectedValue + "', '" + address.Text + "', '" + occupation.Text + "', '" + income.SelectedValue + "', '" + m_status.SelectedValue + "', " + hPhone.Text + ", " + oPhone.Text + ", " + tPhone.Text + ")"
insertCmd.Connection = sqlConn
sdrData = insertCmd.ExecuteNonQuery()
Dim name As String = sdrData("name")
Session("userid") = name
Response.Redirect("welcome.aspx")
End If
End If
sqlConn.Close()1. do you get any errors?
2. do you know that the 'insert' condition is being fired?|||One thought (the best one can do without any information aboutexactly what the problem is) is that you should surround [password] and [name] in square brackets, as they are reserved words.
Also, are hPhone and oPhone strings? If so, the insert statement needs single quotes around those values.|||douglas, that's more perceptive than me, and I wish I'd spotted it. then again, it's friday. have one on me.|||when i tried to browse the page in Visual studio, it popups a window with a warning, but i can preview the page anyway. and when i fill in the details and click submit, all the page does is refresh itself.
Monday, February 13, 2012
Adding columns to a Matrix report that don't belong to the matrix columns groups
Can we do this?
Adding more columns in a matrix report that don’t
belong to the columns drilldown dimensions…
That is, for example, having the following report:
Product Family
Product
Country City Number of units sold
Then I
would add some ratios, that is, Units Sold/Months (sold per month) and other that
is the average for Product Family (Units Sold/Number of Product Family), for putting an example… some
columns should be precalculated prior to the report so do not get into it, the
real problem I don’t see how to solve is adding one or two columns for showing
these calculated column that doesn’t depend on the column groups but they do
for the rows groups…
Any guidance
on that?
The only
way I am seeing by now is to set it as two different reports, and that is not
what my client wants…
Many
thanks,
Jose
The way of doing this, exposed on some articles through the web is to put a higher level group that is unique and there insert the corresponding columns..
Well if anybody knows of a nicer way to do this, please say so...|||
Hi
Saw this and i dont know if this is what your looking for
but it seemes like you may be able to implement this.
Quote Robert Bruckner MSFT :
" You can use the InScope function to distinguish subtotal cells from other cells. Please check the MSDN documentation about the InScope function:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp
With InScope you can determine the current scope of a matrix cell (e.g. in subtotal or not). You would use an IIF-expression to set the cell expression based on the InScope return values. Note: a matrix cell is "in scope" of column and row groupings, so you need at least two InScope function calls in the case where you have one dynamic row and one dynamic column grouping. E.g.
=iif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", "In Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of ColumnGroup1", "In Subtotal of entire matrix"))
Replace "In Cell" with =Sum(Fields!Amount.Value)
Replace "In Subtotal..." with =Avg(Fields!Amount.Value)
However, note that since the subtotal cells share the same cell definition as the group instance cells, adding two "subtotals" (one for "Total", the other for "AVG") at the same level is not supported. One way of solving this is to add a rectangle into the matrix cell and use two textboxes to show the total and the average. Then use conditional visibility on the average textbox to only have it visible for subtotals."
Sorry Robert for quoting you, I couldn't get the thread linked...
G
|||thanks!!Sorry I already tried to use InScope... I also tried to set up a higher grouping but that only does more confusion and servers for only one more value, when I need three or four... not quite desirable...
Thanks so much for your answer!
Jose
Adding Column To A Table At a Specific Position
Dear All
Is there a way in T-Sql where I can add a column in a specific order to an
existing table. For instance, if I have the following table
1. EmpID
2.Fname
3. Mname
4. Lname
5. Add1
6.Add2
I want to insert a Column PHNo before column 5. Is there a possibility in
T-SQL for this.
Hope to hear from you soon.
TIA.
Saleem
Muhammad Saleem Usmani
9-Sassi Town House
Abdullah Haroon Road
Karachi
PH: 92-21-521-2042-3
FAX: 92-21-521-2046
CELL: 0300-822-0105
support-karachi@.autosoftdynamics.com
usmani@.autosoftdynamics.com
I choose Polesoft Lockspam to fight spam, and you?
http://www.polesoft.com/refer.htmlOn Thu, 4 Nov 2004 15:25:12 +0500, Muhammad Saleem Usmani wrote:
>I want to insert a Column PHNo before column 5. Is there a possibility in
>T-SQL for this.
Hi Muhammad,
No, the only way you can achieve this is by dropping and recreating the
table.
Why would you want this? A table in a relational database is UNordered by
definition; you should assign no meaning to the cardinal positions of
columns, nor should you use code that relies on column order.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi
No. The only way would be to re-create the table with the new structure and
copy the data over.
If you need to have a specific order for a select, you should specifcy the
columns in the select statement.
Regards
Mike
"Muhammad Saleem Usmani" wrote:
> Dear All
> Is there a way in T-Sql where I can add a column in a specific order to an
> existing table. For instance, if I have the following table
> 1. EmpID
> 2.Fname
> 3. Mname
> 4. Lname
> 5. Add1
> 6.Add2
> I want to insert a Column PHNo before column 5. Is there a possibility in
> T-SQL for this.
> Hope to hear from you soon.
> TIA.
> Saleem
>
> --
> Muhammad Saleem Usmani
> 9-Sassi Town House
> Abdullah Haroon Road
> Karachi
> PH: 92-21-521-2042-3
> FAX: 92-21-521-2046
> CELL: 0300-822-0105
> support-karachi@.autosoftdynamics.com
> usmani@.autosoftdynamics.com
>
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
>
>|||I agree with the others... When I first started doing SQL I was all into the
physical ordering of the columns, because my background was COBOL, and it
was important there...But trying to keep up with this will be a nightmare...
It would be better to begin to enforce with the programmers, never to select
* , always use column names. Always use the column list in an insert
statement ie
insert into mytab ( col1, colu2) values ( 1,2)
this frees you up from the logical ordering of columns...
Good luck to you!
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Muhammad Saleem Usmani" <support-karachi@.autosoftdynamics.com> wrote in
message news:%23VUd$glwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> Dear All
> Is there a way in T-Sql where I can add a column in a specific order to an
> existing table. For instance, if I have the following table
> 1. EmpID
> 2.Fname
> 3. Mname
> 4. Lname
> 5. Add1
> 6.Add2
> I want to insert a Column PHNo before column 5. Is there a possibility in
> T-SQL for this.
> Hope to hear from you soon.
> TIA.
> Saleem
>
> --
> Muhammad Saleem Usmani
> 9-Sassi Town House
> Abdullah Haroon Road
> Karachi
> PH: 92-21-521-2042-3
> FAX: 92-21-521-2046
> CELL: 0300-822-0105
> support-karachi@.autosoftdynamics.com
> usmani@.autosoftdynamics.com
>
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
>|||No. Just in EM.
"Muhammad Saleem Usmani" wrote:
> Dear All
> Is there a way in T-Sql where I can add a column in a specific order to an
> existing table. For instance, if I have the following table
> 1. EmpID
> 2.Fname
> 3. Mname
> 4. Lname
> 5. Add1
> 6.Add2
> I want to insert a Column PHNo before column 5. Is there a possibility in
> T-SQL for this.
> Hope to hear from you soon.
> TIA.
> Saleem
>
> --
> Muhammad Saleem Usmani
> 9-Sassi Town House
> Abdullah Haroon Road
> Karachi
> PH: 92-21-521-2042-3
> FAX: 92-21-521-2046
> CELL: 0300-822-0105
> support-karachi@.autosoftdynamics.com
> usmani@.autosoftdynamics.com
>
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
>
>
Is there a way in T-Sql where I can add a column in a specific order to an
existing table. For instance, if I have the following table
1. EmpID
2.Fname
3. Mname
4. Lname
5. Add1
6.Add2
I want to insert a Column PHNo before column 5. Is there a possibility in
T-SQL for this.
Hope to hear from you soon.
TIA.
Saleem
Muhammad Saleem Usmani
9-Sassi Town House
Abdullah Haroon Road
Karachi
PH: 92-21-521-2042-3
FAX: 92-21-521-2046
CELL: 0300-822-0105
support-karachi@.autosoftdynamics.com
usmani@.autosoftdynamics.com
I choose Polesoft Lockspam to fight spam, and you?
http://www.polesoft.com/refer.htmlOn Thu, 4 Nov 2004 15:25:12 +0500, Muhammad Saleem Usmani wrote:
>I want to insert a Column PHNo before column 5. Is there a possibility in
>T-SQL for this.
Hi Muhammad,
No, the only way you can achieve this is by dropping and recreating the
table.
Why would you want this? A table in a relational database is UNordered by
definition; you should assign no meaning to the cardinal positions of
columns, nor should you use code that relies on column order.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi
No. The only way would be to re-create the table with the new structure and
copy the data over.
If you need to have a specific order for a select, you should specifcy the
columns in the select statement.
Regards
Mike
"Muhammad Saleem Usmani" wrote:
> Dear All
> Is there a way in T-Sql where I can add a column in a specific order to an
> existing table. For instance, if I have the following table
> 1. EmpID
> 2.Fname
> 3. Mname
> 4. Lname
> 5. Add1
> 6.Add2
> I want to insert a Column PHNo before column 5. Is there a possibility in
> T-SQL for this.
> Hope to hear from you soon.
> TIA.
> Saleem
>
> --
> Muhammad Saleem Usmani
> 9-Sassi Town House
> Abdullah Haroon Road
> Karachi
> PH: 92-21-521-2042-3
> FAX: 92-21-521-2046
> CELL: 0300-822-0105
> support-karachi@.autosoftdynamics.com
> usmani@.autosoftdynamics.com
>
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
>
>|||I agree with the others... When I first started doing SQL I was all into the
physical ordering of the columns, because my background was COBOL, and it
was important there...But trying to keep up with this will be a nightmare...
It would be better to begin to enforce with the programmers, never to select
* , always use column names. Always use the column list in an insert
statement ie
insert into mytab ( col1, colu2) values ( 1,2)
this frees you up from the logical ordering of columns...
Good luck to you!
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Muhammad Saleem Usmani" <support-karachi@.autosoftdynamics.com> wrote in
message news:%23VUd$glwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> Dear All
> Is there a way in T-Sql where I can add a column in a specific order to an
> existing table. For instance, if I have the following table
> 1. EmpID
> 2.Fname
> 3. Mname
> 4. Lname
> 5. Add1
> 6.Add2
> I want to insert a Column PHNo before column 5. Is there a possibility in
> T-SQL for this.
> Hope to hear from you soon.
> TIA.
> Saleem
>
> --
> Muhammad Saleem Usmani
> 9-Sassi Town House
> Abdullah Haroon Road
> Karachi
> PH: 92-21-521-2042-3
> FAX: 92-21-521-2046
> CELL: 0300-822-0105
> support-karachi@.autosoftdynamics.com
> usmani@.autosoftdynamics.com
>
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
>|||No. Just in EM.
"Muhammad Saleem Usmani" wrote:
> Dear All
> Is there a way in T-Sql where I can add a column in a specific order to an
> existing table. For instance, if I have the following table
> 1. EmpID
> 2.Fname
> 3. Mname
> 4. Lname
> 5. Add1
> 6.Add2
> I want to insert a Column PHNo before column 5. Is there a possibility in
> T-SQL for this.
> Hope to hear from you soon.
> TIA.
> Saleem
>
> --
> Muhammad Saleem Usmani
> 9-Sassi Town House
> Abdullah Haroon Road
> Karachi
> PH: 92-21-521-2042-3
> FAX: 92-21-521-2046
> CELL: 0300-822-0105
> support-karachi@.autosoftdynamics.com
> usmani@.autosoftdynamics.com
>
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
>
>
Adding Column To A Table At a Specific Position
Dear All
Is there a way in T-Sql where I can add a column in a specific order to an
existing table. For instance, if I have the following table
1. EmpID
2.Fname
3. Mname
4. Lname
5. Add1
6.Add2
I want to insert a Column PHNo before column 5. Is there a possibility in
T-SQL for this.
Hope to hear from you soon.
TIA.
Saleem
Muhammad Saleem Usmani
9-Sassi Town House
Abdullah Haroon Road
Karachi
PH: 92-21-521-2042-3
FAX: 92-21-521-2046
CELL: 0300-822-0105
support-karachi@.autosoftdynamics.com
usmani@.autosoftdynamics.com
I choose Polesoft Lockspam to fight spam, and you?
http://www.polesoft.com/refer.html
On Thu, 4 Nov 2004 15:25:12 +0500, Muhammad Saleem Usmani wrote:
>I want to insert a Column PHNo before column 5. Is there a possibility in
>T-SQL for this.
Hi Muhammad,
No, the only way you can achieve this is by dropping and recreating the
table.
Why would you want this? A table in a relational database is UNordered by
definition; you should assign no meaning to the cardinal positions of
columns, nor should you use code that relies on column order.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi
No. The only way would be to re-create the table with the new structure and
copy the data over.
If you need to have a specific order for a select, you should specifcy the
columns in the select statement.
Regards
Mike
"Muhammad Saleem Usmani" wrote:
> Dear All
> Is there a way in T-Sql where I can add a column in a specific order to an
> existing table. For instance, if I have the following table
> 1. EmpID
> 2.Fname
> 3. Mname
> 4. Lname
> 5. Add1
> 6.Add2
> I want to insert a Column PHNo before column 5. Is there a possibility in
> T-SQL for this.
> Hope to hear from you soon.
> TIA.
> Saleem
>
> --
> Muhammad Saleem Usmani
> 9-Sassi Town House
> Abdullah Haroon Road
> Karachi
> PH: 92-21-521-2042-3
> FAX: 92-21-521-2046
> CELL: 0300-822-0105
> support-karachi@.autosoftdynamics.com
> usmani@.autosoftdynamics.com
>
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
>
>
|||I agree with the others... When I first started doing SQL I was all into the
physical ordering of the columns, because my background was COBOL, and it
was important there...But trying to keep up with this will be a nightmare...
It would be better to begin to enforce with the programmers, never to select
* , always use column names. Always use the column list in an insert
statement ie
insert into mytab ( col1, colu2) values ( 1,2)
this frees you up from the logical ordering of columns...
Good luck to you!
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Muhammad Saleem Usmani" <support-karachi@.autosoftdynamics.com> wrote in
message news:%23VUd$glwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> Dear All
> Is there a way in T-Sql where I can add a column in a specific order to an
> existing table. For instance, if I have the following table
> 1. EmpID
> 2.Fname
> 3. Mname
> 4. Lname
> 5. Add1
> 6.Add2
> I want to insert a Column PHNo before column 5. Is there a possibility in
> T-SQL for this.
> Hope to hear from you soon.
> TIA.
> Saleem
>
> --
> Muhammad Saleem Usmani
> 9-Sassi Town House
> Abdullah Haroon Road
> Karachi
> PH: 92-21-521-2042-3
> FAX: 92-21-521-2046
> CELL: 0300-822-0105
> support-karachi@.autosoftdynamics.com
> usmani@.autosoftdynamics.com
>
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
>
|||No. Just in EM.
"Muhammad Saleem Usmani" wrote:
> Dear All
> Is there a way in T-Sql where I can add a column in a specific order to an
> existing table. For instance, if I have the following table
> 1. EmpID
> 2.Fname
> 3. Mname
> 4. Lname
> 5. Add1
> 6.Add2
> I want to insert a Column PHNo before column 5. Is there a possibility in
> T-SQL for this.
> Hope to hear from you soon.
> TIA.
> Saleem
>
> --
> Muhammad Saleem Usmani
> 9-Sassi Town House
> Abdullah Haroon Road
> Karachi
> PH: 92-21-521-2042-3
> FAX: 92-21-521-2046
> CELL: 0300-822-0105
> support-karachi@.autosoftdynamics.com
> usmani@.autosoftdynamics.com
>
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
>
>
Is there a way in T-Sql where I can add a column in a specific order to an
existing table. For instance, if I have the following table
1. EmpID
2.Fname
3. Mname
4. Lname
5. Add1
6.Add2
I want to insert a Column PHNo before column 5. Is there a possibility in
T-SQL for this.
Hope to hear from you soon.
TIA.
Saleem
Muhammad Saleem Usmani
9-Sassi Town House
Abdullah Haroon Road
Karachi
PH: 92-21-521-2042-3
FAX: 92-21-521-2046
CELL: 0300-822-0105
support-karachi@.autosoftdynamics.com
usmani@.autosoftdynamics.com
I choose Polesoft Lockspam to fight spam, and you?
http://www.polesoft.com/refer.html
On Thu, 4 Nov 2004 15:25:12 +0500, Muhammad Saleem Usmani wrote:
>I want to insert a Column PHNo before column 5. Is there a possibility in
>T-SQL for this.
Hi Muhammad,
No, the only way you can achieve this is by dropping and recreating the
table.
Why would you want this? A table in a relational database is UNordered by
definition; you should assign no meaning to the cardinal positions of
columns, nor should you use code that relies on column order.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi
No. The only way would be to re-create the table with the new structure and
copy the data over.
If you need to have a specific order for a select, you should specifcy the
columns in the select statement.
Regards
Mike
"Muhammad Saleem Usmani" wrote:
> Dear All
> Is there a way in T-Sql where I can add a column in a specific order to an
> existing table. For instance, if I have the following table
> 1. EmpID
> 2.Fname
> 3. Mname
> 4. Lname
> 5. Add1
> 6.Add2
> I want to insert a Column PHNo before column 5. Is there a possibility in
> T-SQL for this.
> Hope to hear from you soon.
> TIA.
> Saleem
>
> --
> Muhammad Saleem Usmani
> 9-Sassi Town House
> Abdullah Haroon Road
> Karachi
> PH: 92-21-521-2042-3
> FAX: 92-21-521-2046
> CELL: 0300-822-0105
> support-karachi@.autosoftdynamics.com
> usmani@.autosoftdynamics.com
>
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
>
>
|||I agree with the others... When I first started doing SQL I was all into the
physical ordering of the columns, because my background was COBOL, and it
was important there...But trying to keep up with this will be a nightmare...
It would be better to begin to enforce with the programmers, never to select
* , always use column names. Always use the column list in an insert
statement ie
insert into mytab ( col1, colu2) values ( 1,2)
this frees you up from the logical ordering of columns...
Good luck to you!
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Muhammad Saleem Usmani" <support-karachi@.autosoftdynamics.com> wrote in
message news:%23VUd$glwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> Dear All
> Is there a way in T-Sql where I can add a column in a specific order to an
> existing table. For instance, if I have the following table
> 1. EmpID
> 2.Fname
> 3. Mname
> 4. Lname
> 5. Add1
> 6.Add2
> I want to insert a Column PHNo before column 5. Is there a possibility in
> T-SQL for this.
> Hope to hear from you soon.
> TIA.
> Saleem
>
> --
> Muhammad Saleem Usmani
> 9-Sassi Town House
> Abdullah Haroon Road
> Karachi
> PH: 92-21-521-2042-3
> FAX: 92-21-521-2046
> CELL: 0300-822-0105
> support-karachi@.autosoftdynamics.com
> usmani@.autosoftdynamics.com
>
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
>
|||No. Just in EM.
"Muhammad Saleem Usmani" wrote:
> Dear All
> Is there a way in T-Sql where I can add a column in a specific order to an
> existing table. For instance, if I have the following table
> 1. EmpID
> 2.Fname
> 3. Mname
> 4. Lname
> 5. Add1
> 6.Add2
> I want to insert a Column PHNo before column 5. Is there a possibility in
> T-SQL for this.
> Hope to hear from you soon.
> TIA.
> Saleem
>
> --
> Muhammad Saleem Usmani
> 9-Sassi Town House
> Abdullah Haroon Road
> Karachi
> PH: 92-21-521-2042-3
> FAX: 92-21-521-2046
> CELL: 0300-822-0105
> support-karachi@.autosoftdynamics.com
> usmani@.autosoftdynamics.com
>
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
>
>
Adding Column To A Table At a Specific Position
Dear All
Is there a way in T-Sql where I can add a column in a specific order to an
existing table. For instance, if I have the following table
1. EmpID
2.Fname
3. Mname
4. Lname
5. Add1
6.Add2
I want to insert a Column PHNo before column 5. Is there a possibility in
T-SQL for this.
Hope to hear from you soon.
TIA.
Saleem
--
Muhammad Saleem Usmani
9-Sassi Town House
Abdullah Haroon Road
Karachi
PH: 92-21-521-2042-3
FAX: 92-21-521-2046
CELL: 0300-822-0105
support-karachi@.autosoftdynamics.com
usmani@.autosoftdynamics.com
I choose Polesoft Lockspam to fight spam, and you?
http://www.polesoft.com/refer.htmlOn Thu, 4 Nov 2004 15:25:12 +0500, Muhammad Saleem Usmani wrote:
>I want to insert a Column PHNo before column 5. Is there a possibility in
>T-SQL for this.
Hi Muhammad,
No, the only way you can achieve this is by dropping and recreating the
table.
Why would you want this? A table in a relational database is UNordered by
definition; you should assign no meaning to the cardinal positions of
columns, nor should you use code that relies on column order.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi
No. The only way would be to re-create the table with the new structure and
copy the data over.
If you need to have a specific order for a select, you should specifcy the
columns in the select statement.
Regards
Mike
"Muhammad Saleem Usmani" wrote:
> Dear All
> Is there a way in T-Sql where I can add a column in a specific order to an
> existing table. For instance, if I have the following table
> 1. EmpID
> 2.Fname
> 3. Mname
> 4. Lname
> 5. Add1
> 6.Add2
> I want to insert a Column PHNo before column 5. Is there a possibility in
> T-SQL for this.
> Hope to hear from you soon.
> TIA.
> Saleem
>
> --
> Muhammad Saleem Usmani
> 9-Sassi Town House
> Abdullah Haroon Road
> Karachi
> PH: 92-21-521-2042-3
> FAX: 92-21-521-2046
> CELL: 0300-822-0105
> support-karachi@.autosoftdynamics.com
> usmani@.autosoftdynamics.com
>
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
>
>|||I agree with the others... When I first started doing SQL I was all into the
physical ordering of the columns, because my background was COBOL, and it
was important there...But trying to keep up with this will be a nightmare...
It would be better to begin to enforce with the programmers, never to select
* , always use column names. Always use the column list in an insert
statement ie
insert into mytab ( col1, colu2) values ( 1,2)
this frees you up from the logical ordering of columns...
Good luck to you!
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Muhammad Saleem Usmani" <support-karachi@.autosoftdynamics.com> wrote in
message news:%23VUd$glwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> Dear All
> Is there a way in T-Sql where I can add a column in a specific order to an
> existing table. For instance, if I have the following table
> 1. EmpID
> 2.Fname
> 3. Mname
> 4. Lname
> 5. Add1
> 6.Add2
> I want to insert a Column PHNo before column 5. Is there a possibility in
> T-SQL for this.
> Hope to hear from you soon.
> TIA.
> Saleem
>
> --
> Muhammad Saleem Usmani
> 9-Sassi Town House
> Abdullah Haroon Road
> Karachi
> PH: 92-21-521-2042-3
> FAX: 92-21-521-2046
> CELL: 0300-822-0105
> support-karachi@.autosoftdynamics.com
> usmani@.autosoftdynamics.com
>
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
>|||No. Just in EM.
"Muhammad Saleem Usmani" wrote:
> Dear All
> Is there a way in T-Sql where I can add a column in a specific order to an
> existing table. For instance, if I have the following table
> 1. EmpID
> 2.Fname
> 3. Mname
> 4. Lname
> 5. Add1
> 6.Add2
> I want to insert a Column PHNo before column 5. Is there a possibility in
> T-SQL for this.
> Hope to hear from you soon.
> TIA.
> Saleem
>
> --
> Muhammad Saleem Usmani
> 9-Sassi Town House
> Abdullah Haroon Road
> Karachi
> PH: 92-21-521-2042-3
> FAX: 92-21-521-2046
> CELL: 0300-822-0105
> support-karachi@.autosoftdynamics.com
> usmani@.autosoftdynamics.com
>
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
>
>
Is there a way in T-Sql where I can add a column in a specific order to an
existing table. For instance, if I have the following table
1. EmpID
2.Fname
3. Mname
4. Lname
5. Add1
6.Add2
I want to insert a Column PHNo before column 5. Is there a possibility in
T-SQL for this.
Hope to hear from you soon.
TIA.
Saleem
--
Muhammad Saleem Usmani
9-Sassi Town House
Abdullah Haroon Road
Karachi
PH: 92-21-521-2042-3
FAX: 92-21-521-2046
CELL: 0300-822-0105
support-karachi@.autosoftdynamics.com
usmani@.autosoftdynamics.com
I choose Polesoft Lockspam to fight spam, and you?
http://www.polesoft.com/refer.htmlOn Thu, 4 Nov 2004 15:25:12 +0500, Muhammad Saleem Usmani wrote:
>I want to insert a Column PHNo before column 5. Is there a possibility in
>T-SQL for this.
Hi Muhammad,
No, the only way you can achieve this is by dropping and recreating the
table.
Why would you want this? A table in a relational database is UNordered by
definition; you should assign no meaning to the cardinal positions of
columns, nor should you use code that relies on column order.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi
No. The only way would be to re-create the table with the new structure and
copy the data over.
If you need to have a specific order for a select, you should specifcy the
columns in the select statement.
Regards
Mike
"Muhammad Saleem Usmani" wrote:
> Dear All
> Is there a way in T-Sql where I can add a column in a specific order to an
> existing table. For instance, if I have the following table
> 1. EmpID
> 2.Fname
> 3. Mname
> 4. Lname
> 5. Add1
> 6.Add2
> I want to insert a Column PHNo before column 5. Is there a possibility in
> T-SQL for this.
> Hope to hear from you soon.
> TIA.
> Saleem
>
> --
> Muhammad Saleem Usmani
> 9-Sassi Town House
> Abdullah Haroon Road
> Karachi
> PH: 92-21-521-2042-3
> FAX: 92-21-521-2046
> CELL: 0300-822-0105
> support-karachi@.autosoftdynamics.com
> usmani@.autosoftdynamics.com
>
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
>
>|||I agree with the others... When I first started doing SQL I was all into the
physical ordering of the columns, because my background was COBOL, and it
was important there...But trying to keep up with this will be a nightmare...
It would be better to begin to enforce with the programmers, never to select
* , always use column names. Always use the column list in an insert
statement ie
insert into mytab ( col1, colu2) values ( 1,2)
this frees you up from the logical ordering of columns...
Good luck to you!
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Muhammad Saleem Usmani" <support-karachi@.autosoftdynamics.com> wrote in
message news:%23VUd$glwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> Dear All
> Is there a way in T-Sql where I can add a column in a specific order to an
> existing table. For instance, if I have the following table
> 1. EmpID
> 2.Fname
> 3. Mname
> 4. Lname
> 5. Add1
> 6.Add2
> I want to insert a Column PHNo before column 5. Is there a possibility in
> T-SQL for this.
> Hope to hear from you soon.
> TIA.
> Saleem
>
> --
> Muhammad Saleem Usmani
> 9-Sassi Town House
> Abdullah Haroon Road
> Karachi
> PH: 92-21-521-2042-3
> FAX: 92-21-521-2046
> CELL: 0300-822-0105
> support-karachi@.autosoftdynamics.com
> usmani@.autosoftdynamics.com
>
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
>|||No. Just in EM.
"Muhammad Saleem Usmani" wrote:
> Dear All
> Is there a way in T-Sql where I can add a column in a specific order to an
> existing table. For instance, if I have the following table
> 1. EmpID
> 2.Fname
> 3. Mname
> 4. Lname
> 5. Add1
> 6.Add2
> I want to insert a Column PHNo before column 5. Is there a possibility in
> T-SQL for this.
> Hope to hear from you soon.
> TIA.
> Saleem
>
> --
> Muhammad Saleem Usmani
> 9-Sassi Town House
> Abdullah Haroon Road
> Karachi
> PH: 92-21-521-2042-3
> FAX: 92-21-521-2046
> CELL: 0300-822-0105
> support-karachi@.autosoftdynamics.com
> usmani@.autosoftdynamics.com
>
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
>
>
Sunday, February 12, 2012
Adding attributes to the root element.
Hi,
I am using the following caluse to generate xml ... For XML Path
('Vendor'), root ('vendors'), elements xsinil.
Which is good and working ... the result some what
<Vendors xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Vendors ID='... > ...
..
</Vendors>
I would like to add more attributes to the root element 'Vendors'
1. xsd file --> xsi:noNamespaceSchemaLocation="VendorDetails.xsd"
2. Last query execution time -->
xml_generation_timestamp="2007-07-25 13:41:00.370" by using getdate()
3. custom attributes like Region="California"
Query looks like:
SELECT TOP 10 PERCENT
pv.VendorID AS '@.ID',
pv.AccountNumber AS '@.AccountNumber',
pv.Name,
pv.ActiveFlag AS 'Details/@.ActiveFlag',
pv.CreditRating AS 'Details/CreditRating',
pv.PreferredVendorStatus AS 'Details/PreferredVendorStatus'
FROM
PurchasingVendor pv
FOR XML PATH('Vendor'), root('Vendors'), elements xsinil
Please help ...
Thanks in advanceVankayala wrote:
> I am using the following caluse to generate xml ... For XML Path
> ('Vendor'), root ('vendors'), elements xsinil.
> Which is good and working ... the result some what
> <Vendors xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
> <Vendors ID='... > ...
> ...
> </Vendors>
> I would like to add more attributes to the root element 'Vendors'
> 1. xsd file --> xsi:noNamespaceSchemaLocation="VendorDetails.xsd"
> 2. Last query execution time -->
> xml_generation_timestamp="2007-07-25 13:41:00.370" by using getdate()
> 3. custom attributes like Region="California"
>
> Query looks like:
> SELECT TOP 10 PERCENT
> pv.VendorID AS '@.ID',
> pv.AccountNumber AS '@.AccountNumber',
> pv.Name,
> pv.ActiveFlag AS 'Details/@.ActiveFlag',
> pv.CreditRating AS 'Details/CreditRating',
> pv.PreferredVendorStatus AS 'Details/PreferredVendorStatus'
> FROM
> PurchasingVendor pv
> FOR XML PATH('Vendor'), root('Vendors'), elements xsinil
One way to achieve that is by selecting the first query result into a
variable of type xml, then you can use the modify method to manipulate
the variable as needed, then you can select the variable:
DECLARE @.x xml;
SET @.x = (SELECT TOP 10 PERCENT
pv.VendorID AS '@.ID',
pv.AccountNumber AS '@.AccountNumber',
pv.Name,
pv.ActiveFlag AS 'Details/@.ActiveFlag',
pv.CreditRating AS 'Details/CreditRating',
pv.PreferredVendorStatus AS 'Details/PreferredVendorStatus'
FROM
PurchasingVendor pv
FOR XML PATH('Vendor'), root('Vendors'), elements xsinil, TYPE);
SET @.x.modify('
declare namespace xsi="http://www.w3.org/2001/XMLSchema-instance";
insert attribute xsi:noNamespaceSchemaLocation {"VendorDetails.xsd"}
into (/*)[1]
');
-- add further attributes if needed
SELECT @.x;
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
I am using the following caluse to generate xml ... For XML Path
('Vendor'), root ('vendors'), elements xsinil.
Which is good and working ... the result some what
<Vendors xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Vendors ID='... > ...
..
</Vendors>
I would like to add more attributes to the root element 'Vendors'
1. xsd file --> xsi:noNamespaceSchemaLocation="VendorDetails.xsd"
2. Last query execution time -->
xml_generation_timestamp="2007-07-25 13:41:00.370" by using getdate()
3. custom attributes like Region="California"
Query looks like:
SELECT TOP 10 PERCENT
pv.VendorID AS '@.ID',
pv.AccountNumber AS '@.AccountNumber',
pv.Name,
pv.ActiveFlag AS 'Details/@.ActiveFlag',
pv.CreditRating AS 'Details/CreditRating',
pv.PreferredVendorStatus AS 'Details/PreferredVendorStatus'
FROM
PurchasingVendor pv
FOR XML PATH('Vendor'), root('Vendors'), elements xsinil
Please help ...
Thanks in advanceVankayala wrote:
> I am using the following caluse to generate xml ... For XML Path
> ('Vendor'), root ('vendors'), elements xsinil.
> Which is good and working ... the result some what
> <Vendors xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
> <Vendors ID='... > ...
> ...
> </Vendors>
> I would like to add more attributes to the root element 'Vendors'
> 1. xsd file --> xsi:noNamespaceSchemaLocation="VendorDetails.xsd"
> 2. Last query execution time -->
> xml_generation_timestamp="2007-07-25 13:41:00.370" by using getdate()
> 3. custom attributes like Region="California"
>
> Query looks like:
> SELECT TOP 10 PERCENT
> pv.VendorID AS '@.ID',
> pv.AccountNumber AS '@.AccountNumber',
> pv.Name,
> pv.ActiveFlag AS 'Details/@.ActiveFlag',
> pv.CreditRating AS 'Details/CreditRating',
> pv.PreferredVendorStatus AS 'Details/PreferredVendorStatus'
> FROM
> PurchasingVendor pv
> FOR XML PATH('Vendor'), root('Vendors'), elements xsinil
One way to achieve that is by selecting the first query result into a
variable of type xml, then you can use the modify method to manipulate
the variable as needed, then you can select the variable:
DECLARE @.x xml;
SET @.x = (SELECT TOP 10 PERCENT
pv.VendorID AS '@.ID',
pv.AccountNumber AS '@.AccountNumber',
pv.Name,
pv.ActiveFlag AS 'Details/@.ActiveFlag',
pv.CreditRating AS 'Details/CreditRating',
pv.PreferredVendorStatus AS 'Details/PreferredVendorStatus'
FROM
PurchasingVendor pv
FOR XML PATH('Vendor'), root('Vendors'), elements xsinil, TYPE);
SET @.x.modify('
declare namespace xsi="http://www.w3.org/2001/XMLSchema-instance";
insert attribute xsi:noNamespaceSchemaLocation {"VendorDetails.xsd"}
into (/*)[1]
');
-- add further attributes if needed
SELECT @.x;
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
Adding attributes to the root element.
Hi,
I am using the following caluse to generate xml ... For XML Path
('Vendor'), root ('vendors'), elements xsinil.
Which is good and working ... the result some what
<Vendors xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Vendors ID='... > ...
...
</Vendors>
I would like to add more attributes to the root element 'Vendors'
1. xsd file --> xsi:noNamespaceSchemaLocation="VendorDetails.xsd"
2. Last query execution time -->
xml_generation_timestamp="2007-07-25 13:41:00.370" by using getdate()
3. custom attributes like Region="California"
Query looks like:
SELECT TOP 10 PERCENT
pv.VendorID AS '@.ID',
pv.AccountNumber AS '@.AccountNumber',
pv.Name,
pv.ActiveFlag AS 'Details/@.ActiveFlag',
pv.CreditRating AS 'Details/CreditRating',
pv.PreferredVendorStatus AS 'Details/PreferredVendorStatus'
FROM
PurchasingVendor pv
FOR XML PATH('Vendor'), root('Vendors'), elements xsinil
Please help ...
Thanks in advance
Vankayala wrote:
> I am using the following caluse to generate xml ... For XML Path
> ('Vendor'), root ('vendors'), elements xsinil.
> Which is good and working ... the result some what
> <Vendors xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
> <Vendors ID='... > ...
> ...
> </Vendors>
> I would like to add more attributes to the root element 'Vendors'
> 1. xsd file --> xsi:noNamespaceSchemaLocation="VendorDetails.xsd"
> 2. Last query execution time -->
> xml_generation_timestamp="2007-07-25 13:41:00.370" by using getdate()
> 3. custom attributes like Region="California"
>
> Query looks like:
> SELECT TOP 10 PERCENT
> pv.VendorID AS '@.ID',
> pv.AccountNumber AS '@.AccountNumber',
> pv.Name,
> pv.ActiveFlag AS 'Details/@.ActiveFlag',
> pv.CreditRating AS 'Details/CreditRating',
> pv.PreferredVendorStatus AS 'Details/PreferredVendorStatus'
> FROM
> PurchasingVendor pv
> FOR XML PATH('Vendor'), root('Vendors'), elements xsinil
One way to achieve that is by selecting the first query result into a
variable of type xml, then you can use the modify method to manipulate
the variable as needed, then you can select the variable:
DECLARE @.x xml;
SET @.x = (SELECT TOP 10 PERCENT
pv.VendorID AS '@.ID',
pv.AccountNumber AS '@.AccountNumber',
pv.Name,
pv.ActiveFlag AS 'Details/@.ActiveFlag',
pv.CreditRating AS 'Details/CreditRating',
pv.PreferredVendorStatus AS 'Details/PreferredVendorStatus'
FROM
PurchasingVendor pv
FOR XML PATH('Vendor'), root('Vendors'), elements xsinil, TYPE);
SET @.x.modify('
declare namespace xsi="http://www.w3.org/2001/XMLSchema-instance";
insert attribute xsi:noNamespaceSchemaLocation {"VendorDetails.xsd"}
into (/*)[1]
');
-- add further attributes if needed
SELECT @.x;
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
I am using the following caluse to generate xml ... For XML Path
('Vendor'), root ('vendors'), elements xsinil.
Which is good and working ... the result some what
<Vendors xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Vendors ID='... > ...
...
</Vendors>
I would like to add more attributes to the root element 'Vendors'
1. xsd file --> xsi:noNamespaceSchemaLocation="VendorDetails.xsd"
2. Last query execution time -->
xml_generation_timestamp="2007-07-25 13:41:00.370" by using getdate()
3. custom attributes like Region="California"
Query looks like:
SELECT TOP 10 PERCENT
pv.VendorID AS '@.ID',
pv.AccountNumber AS '@.AccountNumber',
pv.Name,
pv.ActiveFlag AS 'Details/@.ActiveFlag',
pv.CreditRating AS 'Details/CreditRating',
pv.PreferredVendorStatus AS 'Details/PreferredVendorStatus'
FROM
PurchasingVendor pv
FOR XML PATH('Vendor'), root('Vendors'), elements xsinil
Please help ...
Thanks in advance
Vankayala wrote:
> I am using the following caluse to generate xml ... For XML Path
> ('Vendor'), root ('vendors'), elements xsinil.
> Which is good and working ... the result some what
> <Vendors xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
> <Vendors ID='... > ...
> ...
> </Vendors>
> I would like to add more attributes to the root element 'Vendors'
> 1. xsd file --> xsi:noNamespaceSchemaLocation="VendorDetails.xsd"
> 2. Last query execution time -->
> xml_generation_timestamp="2007-07-25 13:41:00.370" by using getdate()
> 3. custom attributes like Region="California"
>
> Query looks like:
> SELECT TOP 10 PERCENT
> pv.VendorID AS '@.ID',
> pv.AccountNumber AS '@.AccountNumber',
> pv.Name,
> pv.ActiveFlag AS 'Details/@.ActiveFlag',
> pv.CreditRating AS 'Details/CreditRating',
> pv.PreferredVendorStatus AS 'Details/PreferredVendorStatus'
> FROM
> PurchasingVendor pv
> FOR XML PATH('Vendor'), root('Vendors'), elements xsinil
One way to achieve that is by selecting the first query result into a
variable of type xml, then you can use the modify method to manipulate
the variable as needed, then you can select the variable:
DECLARE @.x xml;
SET @.x = (SELECT TOP 10 PERCENT
pv.VendorID AS '@.ID',
pv.AccountNumber AS '@.AccountNumber',
pv.Name,
pv.ActiveFlag AS 'Details/@.ActiveFlag',
pv.CreditRating AS 'Details/CreditRating',
pv.PreferredVendorStatus AS 'Details/PreferredVendorStatus'
FROM
PurchasingVendor pv
FOR XML PATH('Vendor'), root('Vendors'), elements xsinil, TYPE);
SET @.x.modify('
declare namespace xsi="http://www.w3.org/2001/XMLSchema-instance";
insert attribute xsi:noNamespaceSchemaLocation {"VendorDetails.xsd"}
into (/*)[1]
');
-- add further attributes if needed
SELECT @.x;
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
Adding another step to the following
I have the following statement :
CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+
CONVERT (VARCHAR(4), SUBSTRING(TerminatingIDDDCityCode, PATINDEX('%[^0]%',
TerminatingIDDDCityCode), LEN(TerminatingIDDDCityCode)))))
This runs perfectly.
However I just found out that the field actually contains two different
"types" of data. Example of the two data entries in the field are:
00000011 or
00000-12
What I need to add to the above statement is that if the data in the field
contain the '-', I need for it to be changed to a zero (0) and keep it so
that the results would look like
11 or
012
Thanks in advance... Will post sample data and table if neededOn Wed, 26 Jan 2005 08:07:04 -0800, scuba79 wrote:
(snip)
>What I need to add to the above statement is that if the data in the field
>contain the '-', I need for it to be changed to a zero (0) and keep it so
>that the results would look like
(snip)
Hi scuba79,
You can change any '-' to '0' with
REPLACE (terminatingcountrycode, '-', '0')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Try,
select
case when patindex('%[^0-9]%', colA) > 0 then '0' + right(colA,
patindex('%[^0-9]%', reverse(colA)) - 1)
else cast(cast(colA as int) as varchar)
end
from
(
select '00000011'
union all
select '00000-12'
) as t(colA)
go
AMB
"scuba79" wrote:
> I have the following statement :
> CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
> PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+
> CONVERT (VARCHAR(4), SUBSTRING(TerminatingIDDDCityCode, PATINDEX('%[^0]%',
> TerminatingIDDDCityCode), LEN(TerminatingIDDDCityCode)))))
> This runs perfectly.
> However I just found out that the field actually contains two different
> "types" of data. Example of the two data entries in the field are:
> 00000011 or
> 00000-12
> What I need to add to the above statement is that if the data in the field
> contain the '-', I need for it to be changed to a zero (0) and keep it so
> that the results would look like
> 11 or
> 012
> Thanks in advance... Will post sample data and table if needed
>|||Or,
select
case when charindex('-', colA) > 0 then '0' + right(colA, charindex('-',
reverse(colA)) - 1)
else cast(cast(colA as int) as varchar)
end
from
(
select '00000011'
union all
select '00000-12'
) as t(colA)
go
AMB
"Alejandro Mesa" wrote:
> Try,
> select
> case when patindex('%[^0-9]%', colA) > 0 then '0' + right(colA,
> patindex('%[^0-9]%', reverse(colA)) - 1)
> else cast(cast(colA as int) as varchar)
> end
> from
> (
> select '00000011'
> union all
> select '00000-12'
> ) as t(colA)
> go
>
> AMB
>
> "scuba79" wrote:
>|||Or,
select
replace(coalesce(nullif(right(colA, charindex('-', reverse(colA))), ''),
cast(cast(colA as int) as varchar)), '-', '0')
from
(
select '00000011'
union all
select '00000-12'
) as t(colA)
go
AMB
"Alejandro Mesa" wrote:
> Or,
> select
> case when charindex('-', colA) > 0 then '0' + right(colA, charindex('-',
> reverse(colA)) - 1)
> else cast(cast(colA as int) as varchar)
> end
> from
> (
> select '00000011'
> union all
> select '00000-12'
> ) as t(colA)
> go
>
> AMB
> "Alejandro Mesa" wrote:
>|||Alejandro,
Your statement works great when I run it as a seperate statement and not
combined with the rest of the statement that I provided. I mistated my
question... The statement shows that there are two different fields being
changed. However, it's the field "terminatingidddcitycode" that has the two
different data entries that I need to find the solution for while still bein
g
able to use
"CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+"
part, since that field will never contain any "-". Unless I totaling losing
it and not understanding your code
Thanks
scuba79
"Alejandro Mesa" wrote:
> Or,
> select
> replace(coalesce(nullif(right(colA, charindex('-', reverse(colA))), ''),
> cast(cast(colA as int) as varchar)), '-', '0')
> from
> (
> select '00000011'
> union all
> select '00000-12'
> ) as t(colA)
> go
>
> AMB
>
> "Alejandro Mesa" wrote:
>|||Can you provide some DDL (for those columns), sample data and expected resul
t?
AMB
"scuba79" wrote:
> Alejandro,
> Your statement works great when I run it as a seperate statement and not
> combined with the rest of the statement that I provided. I mistated my
> question... The statement shows that there are two different fields being
> changed. However, it's the field "terminatingidddcitycode" that has the t
wo
> different data entries that I need to find the solution for while still be
ing
> able to use
> "CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
> PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+
"
> part, since that field will never contain any "-". Unless I totaling losi
ng
> it and not understanding your code
> Thanks
> scuba79
> "Alejandro Mesa" wrote:
>|||On Wed, 26 Jan 2005 08:57:01 -0800, scuba79 wrote:
>Your statement works great when I run it as a seperate statement and not
>combined with the rest of the statement that I provided. I mistated my
>question... The statement shows that there are two different fields being
>changed. However, it's the field "terminatingidddcitycode" that has the tw
o
>different data entries that I need to find the solution for while still bei
ng
>able to use
>"CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
>PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+"
>part, since that field will never contain any "-". Unless I totaling losin
g
>it and not understanding your code
Hi scuba79,
If you take your original query and change each terminatingidddcitycode to
REPLACE (terminatingidddcitycode, '-','0'), it should work.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
By using the replace statement as you stated. When the entire statement
runs won't it strip that zero (0) '
Scuba79
"Hugo Kornelis" wrote:
> On Wed, 26 Jan 2005 08:57:01 -0800, scuba79 wrote:
>
> Hi scuba79,
> If you take your original query and change each terminatingidddcitycode to
> REPLACE (terminatingidddcitycode, '-','0'), it should work.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||Alejandro using the following:
CREATE TABLE [Table2] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[TerminatingCountryCode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OriginatingCountryCode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[TerminatingIDDDCityCode] [varchar] (8) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[OriginatingIDDDCityCode] [varchar] (8) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO Table2 (TerminatingCountryCode, OriginatingCountryCode,
TerminatingIDDDCityCode, OriginatingIDDDCityCode)
VALUES( '0052', '0000', '00000002','00000000')
INSERT INTO Table2 (TerminatingCountryCode, OriginatingCountryCode,
TerminatingIDDDCityCode, OriginatingIDDDCityCode)
VALUES( '0052', '0000', '00000-68','00000000')
INSERT INTO Table2 (TerminatingCountryCode, OriginatingCountryCode,
TerminatingIDDDCityCode, OriginatingIDDDCityCode)
VALUES( '007', '0000', '00000003','00000000')
INSERT INTO Table2 (TerminatingCountryCode, OriginatingCountryCode,
TerminatingIDDDCityCode, OriginatingIDDDCityCode)
VALUES( '0079', '0000', '00000-79','00000000')
The results that I looking for are when you combine fields
TerminatingCountryCode and TerminatingIDDDCityCode should be
522
52068
73
79079
Hope this helps and thank you for the assistance
Scuba79
"Alejandro Mesa" wrote:
> Can you provide some DDL (for those columns), sample data and expected res
ult?
>
> AMB
> "scuba79" wrote:
>
CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+
CONVERT (VARCHAR(4), SUBSTRING(TerminatingIDDDCityCode, PATINDEX('%[^0]%',
TerminatingIDDDCityCode), LEN(TerminatingIDDDCityCode)))))
This runs perfectly.
However I just found out that the field actually contains two different
"types" of data. Example of the two data entries in the field are:
00000011 or
00000-12
What I need to add to the above statement is that if the data in the field
contain the '-', I need for it to be changed to a zero (0) and keep it so
that the results would look like
11 or
012
Thanks in advance... Will post sample data and table if neededOn Wed, 26 Jan 2005 08:07:04 -0800, scuba79 wrote:
(snip)
>What I need to add to the above statement is that if the data in the field
>contain the '-', I need for it to be changed to a zero (0) and keep it so
>that the results would look like
(snip)
Hi scuba79,
You can change any '-' to '0' with
REPLACE (terminatingcountrycode, '-', '0')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Try,
select
case when patindex('%[^0-9]%', colA) > 0 then '0' + right(colA,
patindex('%[^0-9]%', reverse(colA)) - 1)
else cast(cast(colA as int) as varchar)
end
from
(
select '00000011'
union all
select '00000-12'
) as t(colA)
go
AMB
"scuba79" wrote:
> I have the following statement :
> CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
> PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+
> CONVERT (VARCHAR(4), SUBSTRING(TerminatingIDDDCityCode, PATINDEX('%[^0]%',
> TerminatingIDDDCityCode), LEN(TerminatingIDDDCityCode)))))
> This runs perfectly.
> However I just found out that the field actually contains two different
> "types" of data. Example of the two data entries in the field are:
> 00000011 or
> 00000-12
> What I need to add to the above statement is that if the data in the field
> contain the '-', I need for it to be changed to a zero (0) and keep it so
> that the results would look like
> 11 or
> 012
> Thanks in advance... Will post sample data and table if needed
>|||Or,
select
case when charindex('-', colA) > 0 then '0' + right(colA, charindex('-',
reverse(colA)) - 1)
else cast(cast(colA as int) as varchar)
end
from
(
select '00000011'
union all
select '00000-12'
) as t(colA)
go
AMB
"Alejandro Mesa" wrote:
> Try,
> select
> case when patindex('%[^0-9]%', colA) > 0 then '0' + right(colA,
> patindex('%[^0-9]%', reverse(colA)) - 1)
> else cast(cast(colA as int) as varchar)
> end
> from
> (
> select '00000011'
> union all
> select '00000-12'
> ) as t(colA)
> go
>
> AMB
>
> "scuba79" wrote:
>|||Or,
select
replace(coalesce(nullif(right(colA, charindex('-', reverse(colA))), ''),
cast(cast(colA as int) as varchar)), '-', '0')
from
(
select '00000011'
union all
select '00000-12'
) as t(colA)
go
AMB
"Alejandro Mesa" wrote:
> Or,
> select
> case when charindex('-', colA) > 0 then '0' + right(colA, charindex('-',
> reverse(colA)) - 1)
> else cast(cast(colA as int) as varchar)
> end
> from
> (
> select '00000011'
> union all
> select '00000-12'
> ) as t(colA)
> go
>
> AMB
> "Alejandro Mesa" wrote:
>|||Alejandro,
Your statement works great when I run it as a seperate statement and not
combined with the rest of the statement that I provided. I mistated my
question... The statement shows that there are two different fields being
changed. However, it's the field "terminatingidddcitycode" that has the two
different data entries that I need to find the solution for while still bein
g
able to use
"CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+"
part, since that field will never contain any "-". Unless I totaling losing
it and not understanding your code
Thanks
scuba79
"Alejandro Mesa" wrote:
> Or,
> select
> replace(coalesce(nullif(right(colA, charindex('-', reverse(colA))), ''),
> cast(cast(colA as int) as varchar)), '-', '0')
> from
> (
> select '00000011'
> union all
> select '00000-12'
> ) as t(colA)
> go
>
> AMB
>
> "Alejandro Mesa" wrote:
>|||Can you provide some DDL (for those columns), sample data and expected resul
t?
AMB
"scuba79" wrote:
> Alejandro,
> Your statement works great when I run it as a seperate statement and not
> combined with the rest of the statement that I provided. I mistated my
> question... The statement shows that there are two different fields being
> changed. However, it's the field "terminatingidddcitycode" that has the t
wo
> different data entries that I need to find the solution for while still be
ing
> able to use
> "CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
> PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+
"
> part, since that field will never contain any "-". Unless I totaling losi
ng
> it and not understanding your code
> Thanks
> scuba79
> "Alejandro Mesa" wrote:
>|||On Wed, 26 Jan 2005 08:57:01 -0800, scuba79 wrote:
>Your statement works great when I run it as a seperate statement and not
>combined with the rest of the statement that I provided. I mistated my
>question... The statement shows that there are two different fields being
>changed. However, it's the field "terminatingidddcitycode" that has the tw
o
>different data entries that I need to find the solution for while still bei
ng
>able to use
>"CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
>PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+"
>part, since that field will never contain any "-". Unless I totaling losin
g
>it and not understanding your code
Hi scuba79,
If you take your original query and change each terminatingidddcitycode to
REPLACE (terminatingidddcitycode, '-','0'), it should work.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
By using the replace statement as you stated. When the entire statement
runs won't it strip that zero (0) '
Scuba79
"Hugo Kornelis" wrote:
> On Wed, 26 Jan 2005 08:57:01 -0800, scuba79 wrote:
>
> Hi scuba79,
> If you take your original query and change each terminatingidddcitycode to
> REPLACE (terminatingidddcitycode, '-','0'), it should work.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||Alejandro using the following:
CREATE TABLE [Table2] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[TerminatingCountryCode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OriginatingCountryCode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[TerminatingIDDDCityCode] [varchar] (8) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[OriginatingIDDDCityCode] [varchar] (8) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO Table2 (TerminatingCountryCode, OriginatingCountryCode,
TerminatingIDDDCityCode, OriginatingIDDDCityCode)
VALUES( '0052', '0000', '00000002','00000000')
INSERT INTO Table2 (TerminatingCountryCode, OriginatingCountryCode,
TerminatingIDDDCityCode, OriginatingIDDDCityCode)
VALUES( '0052', '0000', '00000-68','00000000')
INSERT INTO Table2 (TerminatingCountryCode, OriginatingCountryCode,
TerminatingIDDDCityCode, OriginatingIDDDCityCode)
VALUES( '007', '0000', '00000003','00000000')
INSERT INTO Table2 (TerminatingCountryCode, OriginatingCountryCode,
TerminatingIDDDCityCode, OriginatingIDDDCityCode)
VALUES( '0079', '0000', '00000-79','00000000')
The results that I looking for are when you combine fields
TerminatingCountryCode and TerminatingIDDDCityCode should be
522
52068
73
79079
Hope this helps and thank you for the assistance
Scuba79
"Alejandro Mesa" wrote:
> Can you provide some DDL (for those columns), sample data and expected res
ult?
>
> AMB
> "scuba79" wrote:
>
Subscribe to:
Posts (Atom)