I have records in a table that have a format like the following
Id / Code / Amount
ex.
1 / PDM / 50.00
1 / BIN / 75.00
1 / REN / 30.00
The records have the same id but different codes - the codes will never be
anything different than what is listed - so I could add a where clause for
each code.
I would like to select the three records and combine them into one. So that
it looks like the following:
ID / PDMAMT / BINAMT / RENAMT
1 / 50.00 / 75.00 / 30.00
I have looked at subqueries and the exists operator but I can't seem to find
what I am looking for.
Thanks for the help
http://www.aspfaq.com/2462
http://www.aspfaq.com/
(Reverse address to reply.)
"Heather" <Heather@.discussions.microsoft.com> wrote in message
news:EA6225CF-BF9C-4DB5-AB3C-1D31104E12E7@.microsoft.com...
> I have records in a table that have a format like the following
> Id / Code / Amount
> ex.
> 1 / PDM / 50.00
> 1 / BIN / 75.00
> 1 / REN / 30.00
> The records have the same id but different codes - the codes will never be
> anything different than what is listed - so I could add a where clause for
> each code.
> I would like to select the three records and combine them into one. So
that
> it looks like the following:
> ID / PDMAMT / BINAMT / RENAMT
> 1 / 50.00 / 75.00 / 30.00
> I have looked at subqueries and the exists operator but I can't seem to
find
> what I am looking for.
> Thanks for the help
|||Heather,
This is called a cross-tab (a.k.a. pivot), and in your case you could do it
this way:
SELECT id,
SUM(CASE WHEN Code = 'PDM' THEN Amount ELSE 0 END) AS PDMAMT,
SUM(CASE WHEN Code = 'BIN' THEN Amount ELSE 0 END) AS BINAMT,
SUM(CASE WHEN Code = 'REN' THEN Amount ELSE 0 END) AS RENAMT
FROM YourTable
GROUP BY id
"Heather" <Heather@.discussions.microsoft.com> wrote in message
news:EA6225CF-BF9C-4DB5-AB3C-1D31104E12E7@.microsoft.com...
> I have records in a table that have a format like the following
> Id / Code / Amount
> ex.
> 1 / PDM / 50.00
> 1 / BIN / 75.00
> 1 / REN / 30.00
> The records have the same id but different codes - the codes will never be
> anything different than what is listed - so I could add a where clause for
> each code.
> I would like to select the three records and combine them into one. So
that
> it looks like the following:
> ID / PDMAMT / BINAMT / RENAMT
> 1 / 50.00 / 75.00 / 30.00
> I have looked at subqueries and the exists operator but I can't seem to
find
> what I am looking for.
> Thanks for the help
Showing posts with label code. Show all posts
Showing posts with label code. Show all posts
Wednesday, March 21, 2012
Many Detail Record to One
I have records in a table that have a format like the following
Id / Code / Amount
ex.
1 / PDM / 50.00
1 / BIN / 75.00
1 / REN / 30.00
The records have the same id but different codes - the codes will never be
anything different than what is listed - so I could add a where clause for
each code.
I would like to select the three records and combine them into one. So that
it looks like the following:
ID / PDMAMT / BINAMT / RENAMT
1 / 50.00 / 75.00 / 30.00
I have looked at subqueries and the exists operator but I can't seem to find
what I am looking for.
Thanks for the helphttp://www.aspfaq.com/2462
http://www.aspfaq.com/
(Reverse address to reply.)
"Heather" <Heather@.discussions.microsoft.com> wrote in message
news:EA6225CF-BF9C-4DB5-AB3C-1D31104E12E7@.microsoft.com...
> I have records in a table that have a format like the following
> Id / Code / Amount
> ex.
> 1 / PDM / 50.00
> 1 / BIN / 75.00
> 1 / REN / 30.00
> The records have the same id but different codes - the codes will never be
> anything different than what is listed - so I could add a where clause for
> each code.
> I would like to select the three records and combine them into one. So
that
> it looks like the following:
> ID / PDMAMT / BINAMT / RENAMT
> 1 / 50.00 / 75.00 / 30.00
> I have looked at subqueries and the exists operator but I can't seem to
find
> what I am looking for.
> Thanks for the help|||Heather,
This is called a cross-tab (a.k.a. pivot), and in your case you could do it
this way:
SELECT id,
SUM(CASE WHEN Code = 'PDM' THEN Amount ELSE 0 END) AS PDMAMT,
SUM(CASE WHEN Code = 'BIN' THEN Amount ELSE 0 END) AS BINAMT,
SUM(CASE WHEN Code = 'REN' THEN Amount ELSE 0 END) AS RENAMT
FROM YourTable
GROUP BY id
"Heather" <Heather@.discussions.microsoft.com> wrote in message
news:EA6225CF-BF9C-4DB5-AB3C-1D31104E12E7@.microsoft.com...
> I have records in a table that have a format like the following
> Id / Code / Amount
> ex.
> 1 / PDM / 50.00
> 1 / BIN / 75.00
> 1 / REN / 30.00
> The records have the same id but different codes - the codes will never be
> anything different than what is listed - so I could add a where clause for
> each code.
> I would like to select the three records and combine them into one. So
that
> it looks like the following:
> ID / PDMAMT / BINAMT / RENAMT
> 1 / 50.00 / 75.00 / 30.00
> I have looked at subqueries and the exists operator but I can't seem to
find
> what I am looking for.
> Thanks for the help
Id / Code / Amount
ex.
1 / PDM / 50.00
1 / BIN / 75.00
1 / REN / 30.00
The records have the same id but different codes - the codes will never be
anything different than what is listed - so I could add a where clause for
each code.
I would like to select the three records and combine them into one. So that
it looks like the following:
ID / PDMAMT / BINAMT / RENAMT
1 / 50.00 / 75.00 / 30.00
I have looked at subqueries and the exists operator but I can't seem to find
what I am looking for.
Thanks for the helphttp://www.aspfaq.com/2462
http://www.aspfaq.com/
(Reverse address to reply.)
"Heather" <Heather@.discussions.microsoft.com> wrote in message
news:EA6225CF-BF9C-4DB5-AB3C-1D31104E12E7@.microsoft.com...
> I have records in a table that have a format like the following
> Id / Code / Amount
> ex.
> 1 / PDM / 50.00
> 1 / BIN / 75.00
> 1 / REN / 30.00
> The records have the same id but different codes - the codes will never be
> anything different than what is listed - so I could add a where clause for
> each code.
> I would like to select the three records and combine them into one. So
that
> it looks like the following:
> ID / PDMAMT / BINAMT / RENAMT
> 1 / 50.00 / 75.00 / 30.00
> I have looked at subqueries and the exists operator but I can't seem to
find
> what I am looking for.
> Thanks for the help|||Heather,
This is called a cross-tab (a.k.a. pivot), and in your case you could do it
this way:
SELECT id,
SUM(CASE WHEN Code = 'PDM' THEN Amount ELSE 0 END) AS PDMAMT,
SUM(CASE WHEN Code = 'BIN' THEN Amount ELSE 0 END) AS BINAMT,
SUM(CASE WHEN Code = 'REN' THEN Amount ELSE 0 END) AS RENAMT
FROM YourTable
GROUP BY id
"Heather" <Heather@.discussions.microsoft.com> wrote in message
news:EA6225CF-BF9C-4DB5-AB3C-1D31104E12E7@.microsoft.com...
> I have records in a table that have a format like the following
> Id / Code / Amount
> ex.
> 1 / PDM / 50.00
> 1 / BIN / 75.00
> 1 / REN / 30.00
> The records have the same id but different codes - the codes will never be
> anything different than what is listed - so I could add a where clause for
> each code.
> I would like to select the three records and combine them into one. So
that
> it looks like the following:
> ID / PDMAMT / BINAMT / RENAMT
> 1 / 50.00 / 75.00 / 30.00
> I have looked at subqueries and the exists operator but I can't seem to
find
> what I am looking for.
> Thanks for the help
Many codes, how many tables ?
Lots of codes
This must be a fairy generic problem, so there must
be solutions for this 'problem'.
The problem :
Many different code tables.
For example :
Gendercode: M,F(Male, Female)
or Departmentcode
Codes like M, F (Male, Female) are likely to be solved in the
application. (But suppose the gender code is being extended
to M, F, U, O (Male, Female, Unknown, Other) as it is in the
RIM-model (Reference Information Model, a model used
in healthcare).
Some codes cary more information than other codes.
(Short description, long description, mnemonic, language etc.)
Some codes are almost similar but not the same :
The financial departmentcode and the organisational departmentcode.
What to do ?
1. Have one table for all codes.
2. Have several tables, one for each 'group of types' of code.
3. Have a table for each codetype.
4. Have a generic solution. (Four or six tables implementing a generic model
solution.)*
(In our legacy database, it was to expensive to have a table for each
type of code, so there we had a code table, which could contain all
codes for all sorts of applications. But this was not an ideal solution.)
Second question, What to do with language ?
Gendercode : M, W, N, A
(mannlich, weiblich, nicht, ander)
or
Gendercode : J, M, O, A. (Here the M stands for Female).
Did any of you run into this problem, what 'solution' did you choose ?
(And what are the arguments for / against the solutions ?)
Thanks for your time,
ben brugman
*) The generic solution can be used to store any type of
relational data in four or six tables. Mostly it is used
to store meta data, or data which can not be defined
exactly at the time of implementation.
The generic solution is very flexible databasewise, but
needs a lot of coding to make it usefull. And it needs
coding to implement the constraints. So it is less flexible
and less efficient for the application.One solution that we have used in our application is to define the concept
of "code types". This table tracks the various types of codes that the
application is expected to work with. There would be another table called
"Codes" which has the "Code Type" and a set of fields that are common to all
codes. You could track different languages in the same table also. Multiple
tables is also a solution that can work, but your application code needs to
refer to different tables. Since code tables are near static, you can
benefit by having clustered indexes on the "Code Type" and also pinning the
table if required.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"ben brugman" <ben@.niethier.nl> wrote in message
news:%23QMB0iv7DHA.2088@.TK2MSFTNGP10.phx.gbl...
> Lots of codes
> This must be a fairy generic problem, so there must
> be solutions for this 'problem'.
> The problem :
> Many different code tables.
> For example :
> Gendercode: M,F(Male, Female)
> or Departmentcode
> Codes like M, F (Male, Female) are likely to be solved in the
> application. (But suppose the gender code is being extended
> to M, F, U, O (Male, Female, Unknown, Other) as it is in the
> RIM-model (Reference Information Model, a model used
> in healthcare).
> Some codes cary more information than other codes.
> (Short description, long description, mnemonic, language etc.)
> Some codes are almost similar but not the same :
> The financial departmentcode and the organisational departmentcode.
> What to do ?
> 1. Have one table for all codes.
> 2. Have several tables, one for each 'group of types' of code.
> 3. Have a table for each codetype.
> 4. Have a generic solution. (Four or six tables implementing a generic
model
> solution.)*
> (In our legacy database, it was to expensive to have a table for each
> type of code, so there we had a code table, which could contain all
> codes for all sorts of applications. But this was not an ideal solution.)
> Second question, What to do with language ?
> Gendercode : M, W, N, A
> (mannlich, weiblich, nicht, ander)
> or
> Gendercode : J, M, O, A. (Here the M stands for Female).
> Did any of you run into this problem, what 'solution' did you choose ?
> (And what are the arguments for / against the solutions ?)
> Thanks for your time,
> ben brugman
> *) The generic solution can be used to store any type of
> relational data in four or six tables. Mostly it is used
> to store meta data, or data which can not be defined
> exactly at the time of implementation.
> The generic solution is very flexible databasewise, but
> needs a lot of coding to make it usefull. And it needs
> coding to implement the constraints. So it is less flexible
> and less efficient for the application.
>|||Unknown gender?
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||Yes the RIM-model does account for unknown gender.
(Even more strange is the 'Other' gender.)
In this world it is fairly common that the gender of
a registered person is not known.
And there are occurences whereby the gender of
a person is not clear. (For example XXY types,
persons with two X type chromosomes one Y type
chromosome. Most often catogorised as Female,
but some are catogorised as Male).
But that said, in our legacy system we did not
cater for anything else then male or female.
But in our 'new' database Unknown and Other
are 'possible' genders. But as far as I know there
are no registrations within this catogory (yet).
ben brugman
"James Goodman" <j a m e s@.norton-associates.co.u k> wrote in message
news:c085bb$ske$1@.sparta.btinternet.com...
> Unknown gender?
>
> --
> Cheers,
> James Goodman MCSE, MCDBA
> http://www.angelfire.com/sports/f1pictures
>
This must be a fairy generic problem, so there must
be solutions for this 'problem'.
The problem :
Many different code tables.
For example :
Gendercode: M,F(Male, Female)
or Departmentcode
Codes like M, F (Male, Female) are likely to be solved in the
application. (But suppose the gender code is being extended
to M, F, U, O (Male, Female, Unknown, Other) as it is in the
RIM-model (Reference Information Model, a model used
in healthcare).
Some codes cary more information than other codes.
(Short description, long description, mnemonic, language etc.)
Some codes are almost similar but not the same :
The financial departmentcode and the organisational departmentcode.
What to do ?
1. Have one table for all codes.
2. Have several tables, one for each 'group of types' of code.
3. Have a table for each codetype.
4. Have a generic solution. (Four or six tables implementing a generic model
solution.)*
(In our legacy database, it was to expensive to have a table for each
type of code, so there we had a code table, which could contain all
codes for all sorts of applications. But this was not an ideal solution.)
Second question, What to do with language ?
Gendercode : M, W, N, A
(mannlich, weiblich, nicht, ander)
or
Gendercode : J, M, O, A. (Here the M stands for Female).
Did any of you run into this problem, what 'solution' did you choose ?
(And what are the arguments for / against the solutions ?)
Thanks for your time,
ben brugman
*) The generic solution can be used to store any type of
relational data in four or six tables. Mostly it is used
to store meta data, or data which can not be defined
exactly at the time of implementation.
The generic solution is very flexible databasewise, but
needs a lot of coding to make it usefull. And it needs
coding to implement the constraints. So it is less flexible
and less efficient for the application.One solution that we have used in our application is to define the concept
of "code types". This table tracks the various types of codes that the
application is expected to work with. There would be another table called
"Codes" which has the "Code Type" and a set of fields that are common to all
codes. You could track different languages in the same table also. Multiple
tables is also a solution that can work, but your application code needs to
refer to different tables. Since code tables are near static, you can
benefit by having clustered indexes on the "Code Type" and also pinning the
table if required.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"ben brugman" <ben@.niethier.nl> wrote in message
news:%23QMB0iv7DHA.2088@.TK2MSFTNGP10.phx.gbl...
> Lots of codes
> This must be a fairy generic problem, so there must
> be solutions for this 'problem'.
> The problem :
> Many different code tables.
> For example :
> Gendercode: M,F(Male, Female)
> or Departmentcode
> Codes like M, F (Male, Female) are likely to be solved in the
> application. (But suppose the gender code is being extended
> to M, F, U, O (Male, Female, Unknown, Other) as it is in the
> RIM-model (Reference Information Model, a model used
> in healthcare).
> Some codes cary more information than other codes.
> (Short description, long description, mnemonic, language etc.)
> Some codes are almost similar but not the same :
> The financial departmentcode and the organisational departmentcode.
> What to do ?
> 1. Have one table for all codes.
> 2. Have several tables, one for each 'group of types' of code.
> 3. Have a table for each codetype.
> 4. Have a generic solution. (Four or six tables implementing a generic
model
> solution.)*
> (In our legacy database, it was to expensive to have a table for each
> type of code, so there we had a code table, which could contain all
> codes for all sorts of applications. But this was not an ideal solution.)
> Second question, What to do with language ?
> Gendercode : M, W, N, A
> (mannlich, weiblich, nicht, ander)
> or
> Gendercode : J, M, O, A. (Here the M stands for Female).
> Did any of you run into this problem, what 'solution' did you choose ?
> (And what are the arguments for / against the solutions ?)
> Thanks for your time,
> ben brugman
> *) The generic solution can be used to store any type of
> relational data in four or six tables. Mostly it is used
> to store meta data, or data which can not be defined
> exactly at the time of implementation.
> The generic solution is very flexible databasewise, but
> needs a lot of coding to make it usefull. And it needs
> coding to implement the constraints. So it is less flexible
> and less efficient for the application.
>|||Unknown gender?
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||Yes the RIM-model does account for unknown gender.
(Even more strange is the 'Other' gender.)
In this world it is fairly common that the gender of
a registered person is not known.
And there are occurences whereby the gender of
a person is not clear. (For example XXY types,
persons with two X type chromosomes one Y type
chromosome. Most often catogorised as Female,
but some are catogorised as Male).
But that said, in our legacy system we did not
cater for anything else then male or female.
But in our 'new' database Unknown and Other
are 'possible' genders. But as far as I know there
are no registrations within this catogory (yet).
ben brugman
"James Goodman" <j a m e s@.norton-associates.co.u k> wrote in message
news:c085bb$ske$1@.sparta.btinternet.com...
> Unknown gender?
>
> --
> Cheers,
> James Goodman MCSE, MCDBA
> http://www.angelfire.com/sports/f1pictures
>
Many codes, how many tables ?
Lots of codes
This must be a fairy generic problem, so there must
be solutions for this 'problem'.
The problem :
Many different code tables.
For example :
Gendercode: M,F(Male, Female)
or Departmentcode
Codes like M, F (Male, Female) are likely to be solved in the
application. (But suppose the gender code is being extended
to M, F, U, O (Male, Female, Unknown, Other) as it is in the
RIM-model (Reference Information Model, a model used
in healthcare).
Some codes cary more information than other codes.
(Short description, long description, mnemonic, language etc.)
Some codes are almost similar but not the same :
The financial departmentcode and the organisational departmentcode.
What to do ?
1. Have one table for all codes.
2. Have several tables, one for each 'group of types' of code.
3. Have a table for each codetype.
4. Have a generic solution. (Four or six tables implementing a generic model
solution.)*
(In our legacy database, it was to expensive to have a table for each
type of code, so there we had a code table, which could contain all
codes for all sorts of applications. But this was not an ideal solution.)
Second question, What to do with language ?
Gendercode : M, W, N, A
(mannlich, weiblich, nicht, ander)
or
Gendercode : J, M, O, A. (Here the M stands for Female).
Did any of you run into this problem, what 'solution' did you choose ?
(And what are the arguments for / against the solutions ?)
Thanks for your time,
ben brugman
*) The generic solution can be used to store any type of
relational data in four or six tables. Mostly it is used
to store meta data, or data which can not be defined
exactly at the time of implementation.
The generic solution is very flexible databasewise, but
needs a lot of coding to make it usefull. And it needs
coding to implement the constraints. So it is less flexible
and less efficient for the application.One solution that we have used in our application is to define the concept
of "code types". This table tracks the various types of codes that the
application is expected to work with. There would be another table called
"Codes" which has the "Code Type" and a set of fields that are common to all
codes. You could track different languages in the same table also. Multiple
tables is also a solution that can work, but your application code needs to
refer to different tables. Since code tables are near static, you can
benefit by having clustered indexes on the "Code Type" and also pinning the
table if required.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"ben brugman" <ben@.niethier.nl> wrote in message
news:%23QMB0iv7DHA.2088@.TK2MSFTNGP10.phx.gbl...
> Lots of codes
> This must be a fairy generic problem, so there must
> be solutions for this 'problem'.
> The problem :
> Many different code tables.
> For example :
> Gendercode: M,F(Male, Female)
> or Departmentcode
> Codes like M, F (Male, Female) are likely to be solved in the
> application. (But suppose the gender code is being extended
> to M, F, U, O (Male, Female, Unknown, Other) as it is in the
> RIM-model (Reference Information Model, a model used
> in healthcare).
> Some codes cary more information than other codes.
> (Short description, long description, mnemonic, language etc.)
> Some codes are almost similar but not the same :
> The financial departmentcode and the organisational departmentcode.
> What to do ?
> 1. Have one table for all codes.
> 2. Have several tables, one for each 'group of types' of code.
> 3. Have a table for each codetype.
> 4. Have a generic solution. (Four or six tables implementing a generic
model
> solution.)*
> (In our legacy database, it was to expensive to have a table for each
> type of code, so there we had a code table, which could contain all
> codes for all sorts of applications. But this was not an ideal solution.)
> Second question, What to do with language ?
> Gendercode : M, W, N, A
> (mannlich, weiblich, nicht, ander)
> or
> Gendercode : J, M, O, A. (Here the M stands for Female).
> Did any of you run into this problem, what 'solution' did you choose ?
> (And what are the arguments for / against the solutions ?)
> Thanks for your time,
> ben brugman
> *) The generic solution can be used to store any type of
> relational data in four or six tables. Mostly it is used
> to store meta data, or data which can not be defined
> exactly at the time of implementation.
> The generic solution is very flexible databasewise, but
> needs a lot of coding to make it usefull. And it needs
> coding to implement the constraints. So it is less flexible
> and less efficient for the application.
>|||Unknown gender? :)
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||Yes the RIM-model does account for unknown gender.
(Even more strange is the 'Other' gender.)
In this world it is fairly common that the gender of
a registered person is not known.
And there are occurences whereby the gender of
a person is not clear. (For example XXY types,
persons with two X type chromosomes one Y type
chromosome. Most often catogorised as Female,
but some are catogorised as Male).
But that said, in our legacy system we did not
cater for anything else then male or female.
But in our 'new' database Unknown and Other
are 'possible' genders. But as far as I know there
are no registrations within this catogory (yet).
ben brugman
"James Goodman" <j a m e s@.norton-associates.co.u k> wrote in message
news:c085bb$ske$1@.sparta.btinternet.com...
> Unknown gender? :)
>
> --
> Cheers,
> James Goodman MCSE, MCDBA
> http://www.angelfire.com/sports/f1pictures
>
This must be a fairy generic problem, so there must
be solutions for this 'problem'.
The problem :
Many different code tables.
For example :
Gendercode: M,F(Male, Female)
or Departmentcode
Codes like M, F (Male, Female) are likely to be solved in the
application. (But suppose the gender code is being extended
to M, F, U, O (Male, Female, Unknown, Other) as it is in the
RIM-model (Reference Information Model, a model used
in healthcare).
Some codes cary more information than other codes.
(Short description, long description, mnemonic, language etc.)
Some codes are almost similar but not the same :
The financial departmentcode and the organisational departmentcode.
What to do ?
1. Have one table for all codes.
2. Have several tables, one for each 'group of types' of code.
3. Have a table for each codetype.
4. Have a generic solution. (Four or six tables implementing a generic model
solution.)*
(In our legacy database, it was to expensive to have a table for each
type of code, so there we had a code table, which could contain all
codes for all sorts of applications. But this was not an ideal solution.)
Second question, What to do with language ?
Gendercode : M, W, N, A
(mannlich, weiblich, nicht, ander)
or
Gendercode : J, M, O, A. (Here the M stands for Female).
Did any of you run into this problem, what 'solution' did you choose ?
(And what are the arguments for / against the solutions ?)
Thanks for your time,
ben brugman
*) The generic solution can be used to store any type of
relational data in four or six tables. Mostly it is used
to store meta data, or data which can not be defined
exactly at the time of implementation.
The generic solution is very flexible databasewise, but
needs a lot of coding to make it usefull. And it needs
coding to implement the constraints. So it is less flexible
and less efficient for the application.One solution that we have used in our application is to define the concept
of "code types". This table tracks the various types of codes that the
application is expected to work with. There would be another table called
"Codes" which has the "Code Type" and a set of fields that are common to all
codes. You could track different languages in the same table also. Multiple
tables is also a solution that can work, but your application code needs to
refer to different tables. Since code tables are near static, you can
benefit by having clustered indexes on the "Code Type" and also pinning the
table if required.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"ben brugman" <ben@.niethier.nl> wrote in message
news:%23QMB0iv7DHA.2088@.TK2MSFTNGP10.phx.gbl...
> Lots of codes
> This must be a fairy generic problem, so there must
> be solutions for this 'problem'.
> The problem :
> Many different code tables.
> For example :
> Gendercode: M,F(Male, Female)
> or Departmentcode
> Codes like M, F (Male, Female) are likely to be solved in the
> application. (But suppose the gender code is being extended
> to M, F, U, O (Male, Female, Unknown, Other) as it is in the
> RIM-model (Reference Information Model, a model used
> in healthcare).
> Some codes cary more information than other codes.
> (Short description, long description, mnemonic, language etc.)
> Some codes are almost similar but not the same :
> The financial departmentcode and the organisational departmentcode.
> What to do ?
> 1. Have one table for all codes.
> 2. Have several tables, one for each 'group of types' of code.
> 3. Have a table for each codetype.
> 4. Have a generic solution. (Four or six tables implementing a generic
model
> solution.)*
> (In our legacy database, it was to expensive to have a table for each
> type of code, so there we had a code table, which could contain all
> codes for all sorts of applications. But this was not an ideal solution.)
> Second question, What to do with language ?
> Gendercode : M, W, N, A
> (mannlich, weiblich, nicht, ander)
> or
> Gendercode : J, M, O, A. (Here the M stands for Female).
> Did any of you run into this problem, what 'solution' did you choose ?
> (And what are the arguments for / against the solutions ?)
> Thanks for your time,
> ben brugman
> *) The generic solution can be used to store any type of
> relational data in four or six tables. Mostly it is used
> to store meta data, or data which can not be defined
> exactly at the time of implementation.
> The generic solution is very flexible databasewise, but
> needs a lot of coding to make it usefull. And it needs
> coding to implement the constraints. So it is less flexible
> and less efficient for the application.
>|||Unknown gender? :)
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||Yes the RIM-model does account for unknown gender.
(Even more strange is the 'Other' gender.)
In this world it is fairly common that the gender of
a registered person is not known.
And there are occurences whereby the gender of
a person is not clear. (For example XXY types,
persons with two X type chromosomes one Y type
chromosome. Most often catogorised as Female,
but some are catogorised as Male).
But that said, in our legacy system we did not
cater for anything else then male or female.
But in our 'new' database Unknown and Other
are 'possible' genders. But as far as I know there
are no registrations within this catogory (yet).
ben brugman
"James Goodman" <j a m e s@.norton-associates.co.u k> wrote in message
news:c085bb$ske$1@.sparta.btinternet.com...
> Unknown gender? :)
>
> --
> Cheers,
> James Goodman MCSE, MCDBA
> http://www.angelfire.com/sports/f1pictures
>
Monday, March 12, 2012
Manual creation of DSNs
I'm trying to adapt the code in the article "How to Create ODBC DSN on
multiple SQL server Machines"
(http://www.databasejournal.com/feat...cle.php/2238221) to
create a script that will automate the creation of SQL Server DSNs.
The article's code writes the DSN to the appropriate location in the
registry--HKLM\SOFTWARE\ODBC\ODBC.INI\<DSN Name>. However, the code doesn't
seem to address how to write the SQL UserID and Password. It appears that
the SQL driver doesn't store that information in the registry.
Any suggestions on how to proceed? Thanks!"Stan" <NoSpam@.I> wrote in message
news:#qSSJ77zDHA.1740@.TK2MSFTNGP09.phx.gbl...
doesn't
This article was designed to use Trusted Connections -- reference the
TrustedConnection = "yes" entry.
Usually it's not a good idea to embed the user name and password with the
DSN entry. Consider using DSN-less entries if you are writing a VB
application to access SQL Server. Here is a starting point for the
connection strings: http://www.able-consulting.com/ADO_Conn.htm
Steve
multiple SQL server Machines"
(http://www.databasejournal.com/feat...cle.php/2238221) to
create a script that will automate the creation of SQL Server DSNs.
The article's code writes the DSN to the appropriate location in the
registry--HKLM\SOFTWARE\ODBC\ODBC.INI\<DSN Name>. However, the code doesn't
seem to address how to write the SQL UserID and Password. It appears that
the SQL driver doesn't store that information in the registry.
Any suggestions on how to proceed? Thanks!"Stan" <NoSpam@.I> wrote in message
news:#qSSJ77zDHA.1740@.TK2MSFTNGP09.phx.gbl...
quote:
> I'm trying to adapt the code in the article "How to Create ODBC DSN on
> multiple SQL server Machines"
> (http://www.databasejournal.com/feat...cle.php/2238221) to
> create a script that will automate the creation of SQL Server DSNs.
> The article's code writes the DSN to the appropriate location in the
> registry--HKLM\SOFTWARE\ODBC\ODBC.INI\<DSN Name>. However, the code
doesn't
quote:
> seem to address how to write the SQL UserID and Password. It appears that
> the SQL driver doesn't store that information in the registry.
> Any suggestions on how to proceed? Thanks!
This article was designed to use Trusted Connections -- reference the
TrustedConnection = "yes" entry.
Usually it's not a good idea to embed the user name and password with the
DSN entry. Consider using DSN-less entries if you are writing a VB
application to access SQL Server. Here is a starting point for the
connection strings: http://www.able-consulting.com/ADO_Conn.htm
Steve
Manipulating a text box in custom code.
Hi there,
A simple question I hope. I have got a textbox on a report and I'm trying to populate it by calling a custom assembly. I know I can reference it directly in the textbox (this works) but I am trying to do this from the code block. The following code didn't work:
Protected Overrides Sub OnInit()
ReportItems!textbox2.Value = POCCustomAssembly.CustAssembly.Hello()
End Sub
The TextBox is called textbox2 and the custom assembly simply returns a string.
I get an error message "The is an error on line 1 of custom code: [BC30469] Reference to a non-shared member requires an object reference".
What am I doing wrong?
Hi,
I know you submitted this months ago...but I was wondering if you found the solution to your problem? I essentially am trying to do the exact same thing and can't figure out the error. I would appreciate any help.
|||The error message you're getting suggests that the code wouldn't even work in the textbox value property, although the message could be wrong and misleading.
Is the method you have in your assembly "Shared" (VB) or "static" (c#)?
If not, try to make it shared/static. If that's not possible to do,
you can add objects to the report in Report > Report Properties > References > Classes
add the class name and your desired instance name for the object. Then you can use this instance name in your custom code.
|||Thank you for the responose. Here is my code:
I basically wrote an assembly to calculate percentages and handle situations to divide by zero.
namespace Calculations
{
public class calcPercentage
{
public decimal Percentage(decimal decValueOne, decimal decValueTwo)
{
decimal decPercentage = 0;
if (decValueOne == 0 || decValueTwo == 0)
{
decPercentage = 0;
}
else
{
decPercentage = (decValueOne - decValueTwo) / decValueOne;
}
return decPercentage;
}
}
}
Then in my textbox I put:
=Calculations.CalcPercentage.Percentage(SUM(Fields!PreviousYTDExpenseAmount.Value, "Template_OutputData_Sales"), SUM(Fields!PreviousYTDExpenseAmount.Value, "Template_OutputData_CGS"))
I referenced the assembly and added it to C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies and I still get that error message so I do not know what the problem is.
Then after carefully reading your response, I realized I didn't write "static" in my method even though that's what I meant it to be. Now the program builds successfully but when I run it and try to produce the PDF file it catches on error when it tries to render it as a PDF. Now to figure out that error...which I will save for tomorrow....I must have screwed something up in the process.
Thanks so much for you help! If you have any suggestions about my next error...i'm all ears!
|||Also, when I removed the assembly from my report and reran it they worked fine...so if you have any suggestions let me know.|||
I cannot get the report to render to design mode while using a custom assembly. As soon as I remove the assembly, it works just fine. can anyone suggest what might be the problem? All my code is listed above and what steps I have already taken.
Thanks!
|||Any custom assemblies used for SSRS must be declared SHARED for VB or STATIC for C#.
So for instance:
vb sample:
Public Class Class1
Public Shared Function CallMeIshmal() as string
Return "Call Me Ishaml"
End Function
Any other functions or subs must also be declared as Shared or Static.
You also need to either add the compiled dll to the GAC or copy it to the reportserver and reportbuilder directories.
You may also need to modify the reportserver.config file for permissions.
this link:
http://support.microsoft.com/default.aspx/pwebcst
has a list of webcasts, the first one is about using custom assemblies in SSRS>
Daryl
Manipulating a text box in custom code.
Hi there,
A simple question I hope. I have got a textbox on a report and I'm trying to populate it by calling a custom assembly. I know I can reference it directly in the textbox (this works) but I am trying to do this from the code block. The following code didn't work:
Protected Overrides Sub OnInit()
ReportItems!textbox2.Value = POCCustomAssembly.CustAssembly.Hello()
End Sub
The TextBox is called textbox2 and the custom assembly simply returns a string.
I get an error message "The is an error on line 1 of custom code: [BC30469] Reference to a non-shared member requires an object reference".
What am I doing wrong?
Hi,
I know you submitted this months ago...but I was wondering if you found the solution to your problem? I essentially am trying to do the exact same thing and can't figure out the error. I would appreciate any help.
|||The error message you're getting suggests that the code wouldn't even work in the textbox value property, although the message could be wrong and misleading.
Is the method you have in your assembly "Shared" (VB) or "static" (c#)?
If not, try to make it shared/static. If that's not possible to do,
you can add objects to the report in Report > Report Properties > References > Classes
add the class name and your desired instance name for the object. Then you can use this instance name in your custom code.
|||Thank you for the responose. Here is my code:
I basically wrote an assembly to calculate percentages and handle situations to divide by zero.
namespace Calculations
{
public class calcPercentage
{
public decimal Percentage(decimal decValueOne, decimal decValueTwo)
{
decimal decPercentage = 0;
if (decValueOne == 0 || decValueTwo == 0)
{
decPercentage = 0;
}
else
{
decPercentage = (decValueOne - decValueTwo) / decValueOne;
}
return decPercentage;
}
}
}
Then in my textbox I put:
=Calculations.CalcPercentage.Percentage(SUM(Fields!PreviousYTDExpenseAmount.Value, "Template_OutputData_Sales"), SUM(Fields!PreviousYTDExpenseAmount.Value, "Template_OutputData_CGS"))
I referenced the assembly and added it to C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies and I still get that error message so I do not know what the problem is.
Then after carefully reading your response, I realized I didn't write "static" in my method even though that's what I meant it to be. Now the program builds successfully but when I run it and try to produce the PDF file it catches on error when it tries to render it as a PDF. Now to figure out that error...which I will save for tomorrow....I must have screwed something up in the process.
Thanks so much for you help! If you have any suggestions about my next error...i'm all ears!
|||Also, when I removed the assembly from my report and reran it they worked fine...so if you have any suggestions let me know.|||
I cannot get the report to render to design mode while using a custom assembly. As soon as I remove the assembly, it works just fine. can anyone suggest what might be the problem? All my code is listed above and what steps I have already taken.
Thanks!
|||Any custom assemblies used for SSRS must be declared SHARED for VB or STATIC for C#.
So for instance:
vb sample:
Public Class Class1
Public Shared Function CallMeIshmal() as string
Return "Call Me Ishaml"
End Function
Any other functions or subs must also be declared as Shared or Static.
You also need to either add the compiled dll to the GAC or copy it to the reportserver and reportbuilder directories.
You may also need to modify the reportserver.config file for permissions.
this link:
http://support.microsoft.com/default.aspx/pwebcst
has a list of webcasts, the first one is about using custom assemblies in SSRS>
Daryl
manipulate field value from select statement
Hi all,
any assistance will be much appreciated on this one .... a bit clueless at the mo!
I've been trying to execute the code below in which part of my select statement is a calculated value i.e. Right([ED],2) & "/" & SUBSTRING([ED],5,2) & "/" & Left([ED],4) AS ENDDATE
code:
SELECT vw_contract_dates.[ContractNo], vw_contract_dates.[Title], vw_contract_dates.[CC], vw_contract_dates.[Sponsor],
Right([SD],2) & "/" & SUBSTRING([SD],5,2) & "/" & Left([SD],4) AS STARTDATE,
Right([ED],2) & "/" & SUBSTRING([ED],5,2) & "/" & Left([ED],4) AS ENDDATE, vw_contract_dates.[CEILING], [CEILING]-[SPEND] AS Remain,
vw_contract_spend.[SPEND], CASE WHEN [CEILING]-[SPEND]<0 THEN 1 ELSE [SPEND]/[CEILING] END AS [% Spend],
DATEDIFF(DAY,GETDATE(), ENDDATE) AS [Days Remain]
FROM vw_contract_spend INNER JOIN vw_contract_dates ON vw_contract_spend.[CONTRACTCODE] = vw_contract_dates.[ContractNo]
however this error message keeps coming up at runtime:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ENDDATE'.
My guess is it's happening when I try to get the date difference (DATEDIFF)....
help!!
Try this..
SELECT vw_contract_dates.[ContractNo], vw_contract_dates.[Title], vw_contract_dates.[CC], vw_contract_dates.[Sponsor],
Right([SD],2) + '/' + SUBSTRING([SD],5,2) + '/' + Left([SD],4) AS STARTDATE,
Right([ED],2) + '/' + SUBSTRING([ED],5,2) + '/' + Left([ED],4) AS ENDDATE,
vw_contract_dates.[CEILING], [CEILING]-[SPEND] AS Remain,
vw_contract_spend.[SPEND], CASE WHEN [CEILING]-[SPEND]<0 THEN 1 ELSE [SPEND]/[CEILING] END AS [% Spend],
DATEDIFF(DAY,GETDATE(), ENDDATE) AS [Days Remain]
FROM vw_contract_spend INNER JOIN vw_contract_dates ON vw_contract_spend.[CONTRACTCODE] = vw_contract_dates.[ContractNo]
Sh... should have seen that one.
Cheers mate .. however I'm still having an error from that code:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'vw_contract_spend'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'vw_contract_dates'.
Is there some sort of restriction on selecting from a view in sql server?
|||You're right, the problem is in the DATEDIFF statement.Bolugbe wrote:
My guess is it's happening when I try to get the date difference (DATEDIFF)....
You cannot use just assigned aliases in calculations, so you should either copy/paste the formula for getting ENDDATE into DATEDIFF function or use nested select statements|||
Try this one..
SELECT vw_contract_dates.[ContractNo], vw_contract_dates.[Title], vw_contract_dates.[CC], vw_contract_dates.[Sponsor],
Right([SD],2) + '/' + SUBSTRING([SD],5,2) + '/' + Left([SD],4) AS STARTDATE,
Right([ED],2) + '/' + SUBSTRING([ED],5,2) + '/' + Left([ED],4) AS ENDDATE,
vw_contract_dates.[CEILING], [CEILING]-[SPEND] AS Remain,
vw_contract_spend.[SPEND], CASE WHEN [CEILING]-[SPEND]<0 THEN 1 ELSE [SPEND]/[CEILING] END AS [% Spend],
DATEDIFF(DAY,GETDATE(), Convert(datetime,Right([ED],2) + '/' + SUBSTRING([ED],5,2) + '/' + Left([ED],4))) AS [Days Remain]
FROM vw_contract_spend INNER JOIN vw_contract_dates ON vw_contract_spend.[CONTRACTCODE] = vw_contract_dates.[ContractNo]
Saturday, February 25, 2012
Managing Database Mail using Microsoft.SqlServer.Management.Smo.Mail managed classes
I'm trying (or, more precisely, failing) to configure Database Mail from managed code using the classes in the Microsoft.SqlServer.Management.Smo.Mail namespace. I can easily retrieve the SqlMail object and create a new MailAccount instance but I can't figure out how to set the name of the mail server. Once the MailAccount is created, the MailAccount.MailServers property returns a single MailServer instance and this is always configured as the local server. In my case the SMTP server used to send email is not on the local server but is located elsewhere in the network. I figure I need to change the name of the MailServer instance, but whenever I try this I always get a FailedOperationException.
I've noticed that the dbo.sysmail_add_account_sp stored procedure allows you to specify the mailserver name when creating an account but the managed MailAccount class doesn't seem to provide this option. Can anyone tell me how to do the set the mail server name with the MailAccount class? If this isn't possible then it seems to me that the SMO features for managing database mail are basically useless.
David
Ok, I'm officially stupid
After spending the best part of an afternoon reading the documentation I finally figured out the answer. When a new MailAccount instance is created a default MailServer instance is created and added to the MailServers collection of the MailAccount instance. The MailServer class has a Rename method which allows you to change the name of the MailServer to the name of the SMTP server you want to use, but it's not actually possible to set this directly when you create the MailAccount. Quite why you can't construct an instance of the MailServer class and add it to the MailAccount.MailServers collection is beyond me?
I have to say that the SMO documentation in Sql Server 2005 Books Online / MSDN Library really sucks. As far as examples of configuring Database Mail goes, there's a single VB sample that illustrates creating a basic MailAccount. Good old Google isn't much help either as there appears to be very little documentation out there other than what's in MSDN Library (or if there is, my afternoon of Googling didn't find it!)
Anyhow, I finally got it working so all I need to do know is figure out how to programmatically enable Database Mail on the Sql Server instance that our product is being installed on. By default Database Mail is not enabled and has to turned on using the Sql Server Surface Area Configuration Tool or the wizard in Sql Server Mangement Studio. That's not a painless installation experience for my users so I want something a bit more automatic. It must be possible because Team Foundation Server seems to automatically enable and configure Database Mail when you install it, I just haven't figured out how yet. I'll keep looking.
|||It appears that you might want to execute the following code:
sp_configure 'Database Mail XPs', 1
go
reconfigure
go
You can do this within SMO using
objDB.ExecuteNonQuery(strSQL)where objDB is a defined database object on your server. (See my blog entry at http://sqljunkies.com/WebLog/marathonsqlguy/archive/2006/05/17/21039.aspx for details on mixing SQL and SMO in an application.)
Subscribe to:
Posts (Atom)