Help designing view

Hi folks,
I've got a view, illustrated at www.dbconsult.dk/ms/sqlquestion.jpg.
There must be a smarter way of doing it, but I can't figure it out:
I wish to get rid of the CASE's.
For each row in tblFejlantal I wish for each row in tblFejlKoder to be
listet as a column.
In the illustration the alias F10/F11/etc equals tblFejlKode.FejlKode,
but with a prefix of "F".
Thanks in advance for your efforts! ;-)
Regards /SnedkerIf you can't alter the design of this database and/or you can't or simply
don't want to do this kind of transformations on the client (I'm guessing
it's for presentation purposes) maybe you might find a few pointers in this
article by Itzik Ben-Gan:
http://www.windowsitpro.com/Article...15608.html?Ad=1
ML|||On Tue, 13 Sep 2005 04:19:03 -0700, "ML"
<ML@.discussions.microsoft.com> wrote:
If you have suggestions to the design, I'm all ears! Anything goes.
Regards /Snedker

>If you can't alter the design of this database and/or you can't or simply
>don't want to do this kind of transformations on the client (I'm guessing
>it's for presentation purposes) maybe you might find a few pointers in this
>article by Itzik Ben-Gan:
>http://www.windowsitpro.com/Article...15608.html?Ad=1
>
>ML|||Either post the requirements regarding this specific element of your system
or post DDL and some sample data.
In any case when designing a data-centric solution follow these basic
principles:
1) identifiy all entities, starting with the essential ones;
2) identify relationships between entities; and
3) identify attributes of all entities, with special attention to those
required by genuine business needs.
ML|||On Tue, 13 Sep 2005 05:17:03 -0700, "ML"
<ML@.discussions.microsoft.com> wrote:
Thanks for your response.
As for the database design, normalizing should be okay.
Let's say we have two tables, tblPrimary and tblForeign.
tblPrimary has three columns with one row:
PrimaryID ErrValue ForeignID
1 21.7 5
tblForeign has two columns with 5 rows
ForeignID ErrorCode
1 10
2 20
3 30
4 40
5 50
My view should look like:
PrimaryID Err10 Err20 Err30 Err40 Err50
1 0 0 0 0 21.7
If a row (6, 55) is added to tblForeign the result would be
PrimaryID Err10 Err20 Err30 Err40 Err50
Err55
1 0 0 0 0 21.7 0
I want each row in tblForeign represented in my view as a column.
/Snedker

>Either post the requirements regarding this specific element of your system
>or post DDL and some sample data.
>In any case when designing a data-centric solution follow these basic
>principles:
>1) identifiy all entities, starting with the essential ones;
>2) identify relationships between entities; and
>3) identify attributes of all entities, with special attention to those
>required by genuine business needs.
>
>ML|||Based on the sample you posted I strongly believe the solution described in
Itzik's article is the way to go for you.
Now, turning it into a view is a bit trickier. One way that I can think of
(and works) is a not-really-best-practice solution involving OPENQUERY.
E.g.:
create view dbo.CrossTabbedView
as
select <column list>
from openquery(<server_name>, 'exec <procedure name> <parameters>')
As I said it might work as expected, yet it's pretty resource-intensive. On
the other hand, if you expect it to be fast, it may not "work as expected".
:)
ML

HELP : empty string to Null conversion by SQL

Hi folks,

I've have about 100 tables, for some reasons, column values that are originally NULL was inserted as emtpy string. So, I am wondering if I can write JUST ONE SQL (hopefully don't have to specify the field names in the SQL as well) for each table so that all the empty strings will be converted back to NULL.

THANKS JOHNHi folks,

I've have about 100 tables, for some reasons, column values that are originally NULL was inserted as emtpy string. So, I am wondering if I can write JUST ONE SQL (hopefully don't have to specify the field names in the SQL as well) for each table so that all the empty strings will be converted back to NULL.

THANKS JOHN

Dunno how you're going to do it without naming column names.

UPDATE MyTable
SET Col1 = Null, Col2 = Null, Col3 = Null
WHERE [insert where clause here]

Note that this will set Col1, Col2 and Col3 to Null; not just where the are equal to an empty string, but where there are values as well.

You may consider the CASE statement to selectively change blank values to NULL.

Regards,

hmscott|||As an alternate consideration, you might try:

SELECT
'UPDATE [' + TABLE_NAME + '] SET [' + COLUMN_NAME + '] = NULL
WHERE [' + COLUMN_NAME + '] = '''
FROM INFORMATION_SCHEMA.columns
WHERE DATA_TYPE IN ('char','nchar','varchar','nvarchar')

This will give you a bunch of individualized UPDATE statements. You would then have to run each statement seperately.

Regards,

hmscott|||Ok, but please use:
set [YourColumn] = NULLIF([YourColumn], '')
...or you will wipe out all your data.

HELP - Selection of Development Software? Thoughts?

Folks, I have a quick question that I would very much appreciate some
feedback on. We are a not for profit charity organization that has decided
to develop a software in-house to manage our volunteers. We have SQL and
that makes the most sense from a database solution but we have some issues
surrounding the choice of the development language. Some have suggested
100% java while others say Visual Basic. The head of our team has suggested
we do it in Cold Fusion since this will be an internet based application and
I guess I would very much like some feedback on that choice. We have about 5
organizations that we will tie into this system with about 5000 users
logging in once per month.
Any suggestions or comments would be greatly appreciated.

Cheers
WadeFor Web Development, my choice is Codecharge... http://www.codecharge.com/

For desktop applications, I choose Clarion, http://www.softvelocity.com/

Both are very capable.

Tim Morrison

"Wade Eyre" <weyre@.cogeco.ca> wrote in message
news:9vnGb.34812$mV5.1257@.read1.cgocable.net...
> Folks, I have a quick question that I would very much appreciate some
> feedback on. We are a not for profit charity organization that has decided
> to develop a software in-house to manage our volunteers. We have SQL and
> that makes the most sense from a database solution but we have some issues
> surrounding the choice of the development language. Some have suggested
> 100% java while others say Visual Basic. The head of our team has
suggested
> we do it in Cold Fusion since this will be an internet based application
and
> I guess I would very much like some feedback on that choice. We have about
5
> organizations that we will tie into this system with about 5000 users
> logging in once per month.
> Any suggestions or comments would be greatly appreciated.
> Cheers
> Wade|||"Wade Eyre" <weyre@.cogeco.ca> wrote in message
news:9vnGb.34812$mV5.1257@.read1.cgocable.net...
> Folks, I have a quick question that I would very much appreciate some
> feedback on. We are a not for profit charity organization that has decided
> to develop a software in-house to manage our volunteers. We have SQL and
> that makes the most sense from a database solution but we have some issues
> surrounding the choice of the development language. Some have suggested
> 100% java while others say Visual Basic. The head of our team has
suggested
> we do it in Cold Fusion since this will be an internet based application
and
> I guess I would very much like some feedback on that choice. We have about
5
> organizations that we will tie into this system with about 5000 users
> logging in once per month.
> Any suggestions or comments would be greatly appreciated.

What are your developers skilled in?

Personally I'd say ASP. It's free and in my experience works better than
CF.

> Cheers
> Wade|||In article <9vnGb.34812$mV5.1257@.read1.cgocable.net>, weyre@.cogeco.ca
says...
> Folks, I have a quick question that I would very much appreciate some
> feedback on. We are a not for profit charity organization that has decided
> to develop a software in-house to manage our volunteers. We have SQL and

I hope you bought a CPU license for your SQL server as you can not use a
standard CAL for applications that provide data to internet based
applications. The only exception to this is a SBS 2003 install if I
remember correctly.

A CPU license retails for about $4,999.

> that makes the most sense from a database solution but we have some issues
> surrounding the choice of the development language. Some have suggested
> 100% java while others say Visual Basic. The head of our team has suggested
> we do it in Cold Fusion since this will be an internet based application and

Cold Fusion runs on top of IIS, Cold Fusion MX is another beast
entirely.

Since you didn't tell us what the site is for, no technical details, it
will be hard for anyone to tell you what is the best development
platform.

Here are some things to consider -

Have you considered an Off-The-Shelf product?

What languages do your current developers understand?

What is your timeframe for completion?

Did you build a list of requirements and template the user interfaces
and get signoff from the people that will be using the system on a daily
basis - you don't need to know what platform you will use to mock up
some templates to fit the requirements definitions.

What Server platforms are you currently running, supporting, have
experience with?

Are you hosting the application in your facility or external location?

If internal, did you purchase a firewall yet?

Answer all these and we can tell you what would be best.

Without the above, I can say the following:

If your people are MS types, go with .Net, it's as fast as other
platforms.

If you people are not MS types, and your servers are Linux based, go
with a java platform.

I've built large sites to manage people with my coding teams, we've done
every platform known and have no preference for any of them. It all
comes does to what the supporting organization is able to manage, what
the clients people know, and what the client wants to use.

..Net or Java, either one works great. Stay away from CF if you can.

--
--
spamfree999@.rrohio.com
(Remove 999 to reply to me)|||"Wade Eyre" <weyre@.cogeco.ca> wrote in message news:<9vnGb.34812$mV5.1257@.read1.cgocable.net>...
> Folks, I have a quick question that I would very much appreciate some
> feedback on. We are a not for profit charity organization that has decided
> to develop a software in-house to manage our volunteers. We have SQL and
> that makes the most sense from a database solution but we have some issues
> surrounding the choice of the development language. Some have suggested
> 100% java while others say Visual Basic. The head of our team has suggested
> we do it in Cold Fusion since this will be an internet based application and
> I guess I would very much like some feedback on that choice. We have about 5
> organizations that we will tie into this system with about 5000 users
> logging in once per month.
> Any suggestions or comments would be greatly appreciated.
> Cheers
> Wade

Hi Wade,
5000 users once a month is relatively speaking a small site. I second
Greg's opinion that your developer's skill set really should determine
what language to use. Java, asp, and cf are capable languages. It's
really up to the developer if he's going to make you a cadillac of a
site or an unmanageable mess. In my opinion web developement is still
more art than science. Because the developer needs to be
knowledgeable in sql, java/asp/cf, html, style sheets, javascript and
windows/unix.