Calling Webservice: statement

Showing posts with label statement. Show all posts

Sunday, March 25, 2012

Can I do this in one SQL statement

Let's say that I have a database that has customer name, invoice date, and
amount in it. If an entry exists in this database, then the company owes us
money. I am trying to put together a report that, for each company, details
how much is owed that is 30 days out from a given date, how much is owed
that is 60 days out, 90 days out, and greater than that.
The first statement would look a lot like this:
SELECT name, sold, SUM(owed) AS AmountOwed
FROM InvOwed
WHERE (CONVERT(datetime, invdate) >= CONVERT(datetime, '4-mar-2004')) AND
(DATEADD(d, - 30, '4-mar-2004') <= CONVERT(datetime, invdate))
GROUP BY name, sold
ORDER BY name
The second statement looks like this:
SELECT name, sold, SUM(owed) AS AmountOwed
FROM InvOwed
WHERE (CONVERT(datetime, invdate) <= DATEADD(d, - 31, '4-mar-2004')) AND
(DATEADD(d, - 60, '4-mar-2004') <= CONVERT(datetime, invdate))
GROUP BY name, sold
ORDER BY name
And so on. But, then I have to cut-and-paste this into Excel, and massage
it a bit to get
Customer Amount30DaysDue Amount60DaysDue Amount90DaysDue
Is there any way to get all these in one SQL statement so I don't have to
massage the data?
Thank you.
JoshuaTry,
SELECT
[name],
sold,
SUM(case when invdate >= dateadd(day, -30, convert(char(8), getdate(),
112)) then owed end) AS Amount30DaysDue,
SUM(case when invdate >= dateadd(day, -60, convert(char(8), getdate(),
112)) and invdate < dateadd(day, -30, convert(char(8), getdate(), 112)) then
owed end) AS Amount60DaysDue,
SUM(case when invdate >= dateadd(day, -90, convert(char(8), getdate(),
112)) and invdate < dateadd(day, -60, convert(char(8), getdate(), 112)) then
owed end) AS Amount90DaysDue,
FROM
InvOwed
GROUP BY name, sold
ORDER BY name
go
AMB
"Joshua Campbell" wrote:

> Let's say that I have a database that has customer name, invoice date, and
> amount in it. If an entry exists in this database, then the company owes
us
> money. I am trying to put together a report that, for each company, detai
ls
> how much is owed that is 30 days out from a given date, how much is owed
> that is 60 days out, 90 days out, and greater than that.
> The first statement would look a lot like this:
> SELECT name, sold, SUM(owed) AS AmountOwed
> FROM InvOwed
> WHERE (CONVERT(datetime, invdate) >= CONVERT(datetime, '4-mar-2004')) AND
> (DATEADD(d, - 30, '4-mar-2004') <= CONVERT(datetime, invdate))
> GROUP BY name, sold
> ORDER BY name
> The second statement looks like this:
> SELECT name, sold, SUM(owed) AS AmountOwed
> FROM InvOwed
> WHERE (CONVERT(datetime, invdate) <= DATEADD(d, - 31, '4-mar-2004')) AND
> (DATEADD(d, - 60, '4-mar-2004') <= CONVERT(datetime, invdate))
> GROUP BY name, sold
> ORDER BY name
> And so on. But, then I have to cut-and-paste this into Excel, and massage
> it a bit to get
> Customer Amount30DaysDue Amount60DaysDue Amount90DaysDue
>
> Is there any way to get all these in one SQL statement so I don't have to
> massage the data?
> Thank you.
> Joshua
>
>
>|||Try:
select name,sold,sum(case when datediff(d,invdate,'20040304') between 1 and
30 then owed else 0 end) [30day],
sum(case when datediff(d,invdate,'20040304') between 31 and 60 then owed
else 0 end) [60day],
sum(case when datediff(d,invdate,'20040304') between 61 and 90 then owed
else 0 end) [90day]
from InvOwed
group by name,sold
-oj
"Joshua Campbell" <Joshua.Campbell@.nospam.nospam> wrote in message
news:%231oNyuUOFHA.3144@.tk2msftngp13.phx.gbl...
> Let's say that I have a database that has customer name, invoice date, and
> amount in it. If an entry exists in this database, then the company owes
> us
> money. I am trying to put together a report that, for each company,
> details
> how much is owed that is 30 days out from a given date, how much is owed
> that is 60 days out, 90 days out, and greater than that.
> The first statement would look a lot like this:
> SELECT name, sold, SUM(owed) AS AmountOwed
> FROM InvOwed
> WHERE (CONVERT(datetime, invdate) >= CONVERT(datetime, '4-mar-2004')) AND
> (DATEADD(d, - 30, '4-mar-2004') <= CONVERT(datetime, invdate))
> GROUP BY name, sold
> ORDER BY name
> The second statement looks like this:
> SELECT name, sold, SUM(owed) AS AmountOwed
> FROM InvOwed
> WHERE (CONVERT(datetime, invdate) <= DATEADD(d, - 31, '4-mar-2004')) AND
> (DATEADD(d, - 60, '4-mar-2004') <= CONVERT(datetime, invdate))
> GROUP BY name, sold
> ORDER BY name
> And so on. But, then I have to cut-and-paste this into Excel, and massage
> it a bit to get
> Customer Amount30DaysDue Amount60DaysDue Amount90DaysDue
>
> Is there any way to get all these in one SQL statement so I don't have to
> massage the data?
> Thank you.
> Joshua
>
>|||I didn't know you could use case like that. Excellent. Thank you very
much!
"oj" <nospam_ojngo@.home.com> wrote in message
news:eoXDR5UOFHA.2468@.tk2msftngp13.phx.gbl...
> Try:
> select name,sold,sum(case when datediff(d,invdate,'20040304') between 1
and
> 30 then owed else 0 end) [30day],
> sum(case when datediff(d,invdate,'20040304') between 31 and 60 then owed
> else 0 end) [60day],
> sum(case when datediff(d,invdate,'20040304') between 61 and 90 then owed
> else 0 end) [90day]
> from InvOwed
> group by name,sold
> --
> -oj
>
> "Joshua Campbell" <Joshua.Campbell@.nospam.nospam> wrote in message
> news:%231oNyuUOFHA.3144@.tk2msftngp13.phx.gbl...
and
owes
AND
massage
to
>

Thursday, March 22, 2012

Can I create a Top n statement within a stored procedure using a parameter?

In a 'Top n' type statement I wish to be able to insert the n value
from a parameter, within a stored precedure eg

Having declared @.pageSize as a parameter I want to run the following
type of query :

SELECT DISTINCT TOP @.pageSize routeID, routeName FROM
tblRoute_Header

When I attempt to do so I get an error mesage indicating incorrect
syntax. I do not get an error message if I specify 'n' directly as in
TOP 10

Am I missing something or is this not possible within a stored
procedure?

Best wishes, John MorganOn Mon, 12 Apr 2004 16:45:26 +0100, John Morgan wrote:

>
>In a 'Top n' type statement I wish to be able to insert the n value
>from a parameter, within a stored precedure eg
>Having declared @.pageSize as a parameter I want to run the following
>type of query :
>SELECT DISTINCT TOP @.pageSize routeID, routeName FROM
>tblRoute_Header
>When I attempt to do so I get an error mesage indicating incorrect
>syntax. I do not get an error message if I specify 'n' directly as in
>TOP 10
>Am I missing something or is this not possible within a stored
>procedure?
>Best wishes, John Morgan

The TOP clause will only take an integer value, not a variable.

There are two other ways to limit your output to @.pageSize rows:

1. Using proprietary syntax, not portable to other DBMS's

SET ROWCOUNT @.pageSize
SELECT DISTINCT routeID, routeName
FROM tblRoute_Header
WHERE ...
ORDER BY ...
SET ROWCOUNT 0

Note 1: Don't forget to SET ROWCOUNT 0 afterwards, or else all other
queries you execute will be limited to @.pageSize rows of output.
Note 2: Don't leave out the order by clause, or else your output will
be unpredictable. Result sets, like tables, are unordered by default.
If you get the first 10 from an unordered collection, there's no way
of predicting which 10 it will be, nor can anybody guarantee that
you'll get the same 10 if you get "the first 10" again.

2. Using ANSI-standard syntax:

SELECT DISTINCT routeID, routeName
FROM tblRoute_Header AS RH1
WHERE ...
AND (SELECT COUNT(*)
FROM tblRoute_Header AS RH2
WHERE RH2.routeID < RH1.routeID) < @.pageSize
ORDER BY routeID

Note 1: This is based on assumptions re your data structure. You need
to adapt it to your actual situation.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||"John Morgan" <jfm@.XXwoodlander.co.uk> wrote in message
news:ntdl70h6hmja4h5rshiiuob1hcg32pm12d@.4ax.com...
>
> In a 'Top n' type statement I wish to be able to insert the n value
> from a parameter, within a stored precedure eg
> Having declared @.pageSize as a parameter I want to run the following
> type of query :
> SELECT DISTINCT TOP @.pageSize routeID, routeName FROM
> tblRoute_Header
> When I attempt to do so I get an error mesage indicating incorrect
> syntax. I do not get an error message if I specify 'n' directly as in
> TOP 10
> Am I missing something or is this not possible within a stored
> procedure?
> Best wishes, John Morgan

TOP doesn't allow parameters, but SET ROWCOUNT does:

SET ROWCOUNT @.n

SELECT ...

SET ROWCOUNT 0

Don't forget to set ROWCOUNT back to zero immediately after your query, or
all the following statements will be affected too. Note that TOP without
ORDER BY, as in your example above, returns random rows - there is no
guarantee that you will get what you expect without the ORDER BY.

Simon|||"John Morgan" <jfm@.XXwoodlander.co.uk> wrote in message
news:ntdl70h6hmja4h5rshiiuob1hcg32pm12d@.4ax.com...
>
> In a 'Top n' type statement I wish to be able to insert the n value
> from a parameter, within a stored precedure eg
> Having declared @.pageSize as a parameter I want to run the following
> type of query :
> SELECT DISTINCT TOP @.pageSize routeID, routeName FROM
> tblRoute_Header
> When I attempt to do so I get an error mesage indicating incorrect
> syntax. I do not get an error message if I specify 'n' directly as in
> TOP 10
> Am I missing something or is this not possible within a stored
> procedure?
> Best wishes, John Morgan

TOP doesn't allow parameters, but SET ROWCOUNT does:

SET ROWCOUNT @.n

SELECT ...

SET ROWCOUNT 0

Simon|||Thank you Simon for your help - appreciated,

Best wishes, John Morgan

On Mon, 12 Apr 2004 22:06:52 +0200, "Simon Hayes" <sql@.hayes.ch>
wrote:

>"John Morgan" <jfm@.XXwoodlander.co.uk> wrote in message
>news:ntdl70h6hmja4h5rshiiuob1hcg32pm12d@.4ax.com...
>>
>>
>> In a 'Top n' type statement I wish to be able to insert the n value
>> from a parameter, within a stored precedure eg
>>
>> Having declared @.pageSize as a parameter I want to run the following
>> type of query :
>>
>> SELECT DISTINCT TOP @.pageSize routeID, routeName FROM
>> tblRoute_Header
>>
>> When I attempt to do so I get an error mesage indicating incorrect
>> syntax. I do not get an error message if I specify 'n' directly as in
>> TOP 10
>>
>> Am I missing something or is this not possible within a stored
>> procedure?
>>
>> Best wishes, John Morgan
>TOP doesn't allow parameters, but SET ROWCOUNT does:
>SET ROWCOUNT @.n
>SELECT ...
>SET ROWCOUNT 0
>Don't forget to set ROWCOUNT back to zero immediately after your query, or
>all the following statements will be affected too. Note that TOP without
>ORDER BY, as in your example above, returns random rows - there is no
>guarantee that you will get what you expect without the ORDER BY.
>Simon|||Thank you Simon for your help - appreciated,

Best wishes, John Morgan

On Mon, 12 Apr 2004 22:06:52 +0200, "Simon Hayes" <sql@.hayes.ch>
wrote:

Tuesday, March 20, 2012

Can I call stored procedure inside the case statement

Hi
Can I call stored procedure inside the case statement.small correction
Can I execute stored procedure inside the case statement
CASE t1.OPERATION WHEN 'I' THEN (EXEC [UPD_SEQ_GENERATOR_PROC_VEERU]
@.p_seq_name, @.p_next_value1 OUTPUT select @.p_next_value1 WHEN 'L' THEN EXEC
[UPD_SEQ_GENERATOR_PROC_VEERU] @.p_seq_name, @.p_next_value1 OUTPUT select
@.p_next_value1 END
I want to return the value from the stroed procedure based on the condition.
Regards
Veeru
"Veeru" wrote:

> Hi
> Can I call stored procedure inside the case statement.
>|||No. Why don't you use IF ?
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Veeru" <Veeru@.discussions.microsoft.com> wrote in message
news:4BCD8E67-FB16-46BF-9298-4242A70D05E2@.microsoft.com...
> small correction
> Can I execute stored procedure inside the case statement
>
> CASE t1.OPERATION WHEN 'I' THEN (EXEC [UPD_SEQ_GENERATOR_PROC_VEERU]
> @.p_seq_name, @.p_next_value1 OUTPUT select @.p_next_value1 WHEN 'L' THEN
> EXEC
> [UPD_SEQ_GENERATOR_PROC_VEERU] @.p_seq_name, @.p_next_value1 OUTPUT select
> @.p_next_value1 END
> I want to return the value from the stroed procedure based on the
> condition.
> Regards
> Veeru
>
>
> "Veeru" wrote:
>|||Hi veeru,
I have been following your posts for quite sometime. Can you give the
exact requirement, why you ned that sequence table and what are you trying t
o
achieve with an example and may be we can help you out.
-Omnibuzz
"Veeru" wrote:
> small correction
> Can I execute stored procedure inside the case statement
>
> CASE t1.OPERATION WHEN 'I' THEN (EXEC [UPD_SEQ_GENERATOR_PROC_VEERU]
> @.p_seq_name, @.p_next_value1 OUTPUT select @.p_next_value1 WHEN 'L' THEN EXE
C
> [UPD_SEQ_GENERATOR_PROC_VEERU] @.p_seq_name, @.p_next_value1 OUTPUT select
> @.p_next_value1 END
> I want to return the value from the stroed procedure based on the conditio
n.
> Regards
> Veeru
>
>
> "Veeru" wrote:
>|||Hi Omnibuzz,
I have the requirement to load the flat file data into tables. previously we
did this in Orale. there we created one control file to load the flat file
data into table. In the control file we did some validations like
DATA_STREAMER_ID "DATA_STREAMER_ID_SEQ.nextval",
KEY_2 CONSTANT 'PRES',
KEY_3 CONSTANT '-1',
KEY_4 "Data_Streamer_Id_Seq.Nextval",
OPERATION " DECODE(TRIM(:OPERATION),'L',1,'I',1,'U',
0)",
IDENTIFIER_15 "DECODE
(TRIM:OPERATION),'L',SEQ_IS_GLOBAL_IDENT
IFIER.nextval,'I',SEQ_IS_GLOBAL_IDEN
TIFIER.nextval)"
DATE_1 DATE "FXYYYY-MM-DD HH24:MI:SS" "DECODE(:OPERATION,'L',NVL
(:DATE_1,to_char(sysdate,'YYYY-MM-DD
HH24:MI:SS')),'I',NVL(:DATE_1,to_char(sy
sdate,'YYYY-MM-DD
HH24:MI:SS')),:DATE_1)", -- STATUS_CHANGE_DATE
I want to do the same thing now in SQL server. For that I have created
Format file to map the data file fields to table column.
we have CASE statement in SQL Server which works same as DECODE in Oracle.
I have posted some more info yesterday subject as "Can we update the table
in user defined function". Actually I have been waiting for your reply for
that. Can you please refer that also and suggest me the approach.
Regards
Veeru.
"Omnibuzz" wrote:
> Hi veeru,
> I have been following your posts for quite sometime. Can you give the
> exact requirement, why you ned that sequence table and what are you trying
to
> achieve with an example and may be we can help you out.
> -Omnibuzz
> "Veeru" wrote:
>|||Do you want the values to be autogenerated like this?
KEY_1 KEY_4
1 1
2 2
3 3
or like this
KEY_1 KEY_4
1 2
3 4
5 6|||I want values like
KEY_1 KEY_4
1 2
3 4
5 6
and
I want the below syntax in INSERT...OPENROWSER(BULK) in SQL Server
IDENTIFIER_15 "DECODE
(TRIM:OPERATION),'L',SEQ_IS_GLOBAL_IDENT
IFIER.nextval,'I',SEQ_IS_GLOBAL_IDEN
TIFIER.nextval)"
Regards
Veeru
"Omnibuzz" wrote:

> Do you want the values to be autogenerated like this?
> KEY_1 KEY_4
> 1 1
> 2 2
> 3 3
> or like this
> KEY_1 KEY_4
> 1 2
> 3 4
> 5 6
>|||then use a table variable. Insert into the table variable from the source.
And select from the table variable and insert into the destination.
The code is this...
--The table variable definition should be something like this.
declare @.tbl1 table (id1 int identity(1,2), source_col1,source_col2,...)
Insert into @.tbl1 (source_col1,source_col2,... ) select ... from source.
--Here id1 will be autogenerated into the table variable.
--use this for insert into destination
INSERT...OPENROWSER(BULK) select id1, source_col1...., case when blah..blah
then id1 + 1 else
... blah blah.. end
Sorry.. leaving for a party :)
Try to decipher this.. If not will help u (in detail :)
Bye.|||Thanks Omnibuzz. I will try. If any problem I will post my doubts
"Omnibuzz" wrote:

> then use a table variable. Insert into the table variable from the source.
> And select from the table variable and insert into the destination.
> The code is this...
> --The table variable definition should be something like this.
> declare @.tbl1 table (id1 int identity(1,2), source_col1,source_col2,...)
> Insert into @.tbl1 (source_col1,source_col2,... ) select ... from source.
> --Here id1 will be autogenerated into the table variable.
> --use this for insert into destination
> INSERT...OPENROWSER(BULK) select id1, source_col1...., case when blah..bl
ah
> then id1 + 1 else
> ... blah blah.. end
>
> Sorry.. leaving for a party :)
> Try to decipher this.. If not will help u (in detail :)
> Bye.
>|||Hi Omnibuzz,
I have multiple records in my flat file. I have to load all the records into
the database. Like this I have to load into so many tables and have one
interface fir each. the sequence number should be unique in the database not
in the table. So I have to generate sequence number for all the Interfaces
where evevr I need and should be unique. In Oracle we can create one sequenc
e
and generate the sequences. Like that I have to do. I think you can
understand what I need.
Can you suggest me.
Thanks in advance.
Regards
Veeru
"Omnibuzz" wrote:

> then use a table variable. Insert into the table variable from the source.
> And select from the table variable and insert into the destination.
> The code is this...
> --The table variable definition should be something like this.
> declare @.tbl1 table (id1 int identity(1,2), source_col1,source_col2,...)
> Insert into @.tbl1 (source_col1,source_col2,... ) select ... from source.
> --Here id1 will be autogenerated into the table variable.
> --use this for insert into destination
> INSERT...OPENROWSER(BULK) select id1, source_col1...., case when blah..bl
ah
> then id1 + 1 else
> ... blah blah.. end
>
> Sorry.. leaving for a party :)
> Try to decipher this.. If not will help u (in detail :)
> Bye.
>

Monday, March 19, 2012

Can i add SQL statement to Parameter Fields?

Hi,
I am new to Crystal Reports. May i know can i add SQL statement to parameter fields under setting default values?
WillyYes, you can add SQL statement, but the statement, is a litlle diferent... not much.
Menu -> Report -> Select Expert ->
Click Show formula >>> then hit the button Formula editor...

Hi,

I am new to Crystal Reports. May i know can i add SQL statement to parameter fields under setting default values?

Willy|||Thanks a lot

Sunday, March 11, 2012

Can FK be nullable/optional by design?

Hi All!

General statement: FK should not be nullabe to avoid orphans in DB.

Real life:
Business rule says that not every record will have a parent. It is
implemented as a child record has FK that is null.

It works, and it is simpler.
The design that satisfy business rule and FK not null can be
implemented but it will be more complicated.

Example: There are clients. A client might belong to only one group.

Case A.
Group(GroupID PK, Name,Code)
Client(ClientID PK, Name, GroupID FK NULL)

Case B(more cleaner)
Group(GroupID PK, Name, GroupCode)

Client (ClientID PK, Name, .)
Subtype:
GroupedClient (PersonID PK/FK, GroupID FK NOT NULL)

There is one more entity in Case B and it will require an additional
join in compare with caseA
Example: Select all clients that belongs to any group

Summary Q: Is it worth to go with CaseB?

Thank you in advance"Andy" <net__space@.hotmail.com> wrote in message <news:edb90340.0311301114.19718061@.posting.google.c om>...

> Hi All!
> General statement: FK should not be nullabe to avoid orphans in DB.
> Real life:
> Business rule says that not every record will have a parent. It is
> implemented as a child record has FK that is null.

Nulls suck. Dealing with Null is ugly any way you look at it.

> It works, and it is simpler.
> The design that satisfy business rule and FK not null can be
> implemented but it will be more complicated.
> Example: There are clients. A client might belong to only one group.
> Case A.
> Group(GroupID PK, Name,Code.)
> Client(ClientID PK, Name, GroupID FK NULL)

In this scheme, a client may belong to no group or one group but
cannot belong to more than one group. Is this the business rule?

> Case B(more cleaner)
> Group(GroupID PK, Name, GroupCode.)
> Client (ClientID PK, Name, ..)
> Subtype:
> GroupedClient (PersonID PK/FK, GroupID FK NOT NULL)
> There is one more entity in Case B and it will require an additional
> join in compare with caseA
> Example: Select all clients that belongs to any group

With one tweak, GroupedClient can be a many<->many link between
Client and Group. Otherwise, you can always use a view to turn
Case B into Case A for the convenience of a particular program.

> Summary Q: Is it worth to go with CaseB?

Case C. Use one or more "special" groups to "contain" otherwise
"groupless" clients. However, you now have the "special" groups
to deal with.

--
Joe Foster <mailto:jlfoster%40znet.com> Sign the Check! <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!|||net__space@.hotmail.com (Andy) writes:

> General statement: FK should not be nullabe to avoid orphans in DB.

I don't see the reasoning behind this statement. Any column that
references keys to another table should be explicitly specified as such
to avoid orphans.

If that column may sometimes be unknown/unspecified for perfectly valid
records, I see no reason not to make it nullable.

--
"Notwithstanding fervent argument that patent protection is essential
for the growth of the software industry, commentators have noted
that `this industry is growing by leaps and bounds without it.'"
-- US Supreme Court Justice John Paul Stevens, March 3, 1981.|||depends on what a Group is and how it is used...

e.g.,
is a Group a Super-Client? -- individual Clients may be subsidiaries of a
Super-Client?
is a Group in internal designation, like a Sales territory?

How many Clients are there likely to be w/o a group?
When you need to act on the clients that are grouped, do you also need to
act on the clients that are not grouped?

[ps. in Case B, where did PersonID come from? Is that the Client?]

> Example: There are clients. A client might belong to only one group.

> Case A.
> Group(GroupID PK, Name,Code.)
> Client(ClientID PK, Name, GroupID FK NULL)
>
> Case B(more cleaner)
> Group(GroupID PK, Name, GroupCode.)
> Client (ClientID PK, Name, ..)
> Subtype:
> GroupedClient (PersonID PK/FK, GroupID FK NOT NULL)
> There is one more entity in Case B and it will require an additional
> join in compare with caseA
> Example: Select all clients that belongs to any group
>
> Summary Q: Is it worth to go with CaseB?
> Thank you in advance|||"Trey Walpole" <treyNOpole@.SPcomcastAM.net> wrote in message news:<u3p24vCuDHA.3144@.tk2msftngp13.phx.gbl>...
> depends on what a Group is and how it is used...
> e.g.,
> is a Group a Super-Client? -- individual Clients may be subsidiaries of a
> Super-Client?
> is a Group in internal designation, like a Sales territory?
> How many Clients are there likely to be w/o a group?
> When you need to act on the clients that are grouped, do you also need to
> act on the clients that are not grouped?
> [ps. in Case B, where did PersonID come from? Is that the Client?]

Yes, it does.
It should be this way

[ps. in Case B, where did PersonID come from? Is that the Client?]

Case B
Group(GroupID PK, Name, GroupCode.)
Client (ClientID PK, Name, ..)
Subtype:
GroupedClient (ClientID PK/FK, GroupID FK NOT NULL)|||net__space@.hotmail.com (Andy) wrote in message news:<edb90340.0311301114.19718061@.posting.google.com>...
> Hi All!
> General statement: FK should not be nullabe to avoid orphans in DB.

Where did this statement come from? The idea of an orphan belongs to
network and hierarchical databases (old fashioned) or to
object-oriented databases (allegedly new), where the only way to get
to a record might be through its parent record. In a relational
database there is no such thing as an orphan.

You can find your "orphans" by some equivalent of (client where
groupcode not present) (worded that way to keep away from arguments
about NULLS).

In your example, what you have is

A client may be a member of at most one group.

If you meant to have

A client must be a member of exactly one group.

then (in your example) you would have to use NOT NULL.

Regards,

Eric|||"Andy" <net__space@.hotmail.com> wrote in message
news:edb90340.0311301114.19718061@.posting.google.c om...
> Hi All!
> General statement: FK should not be nullabe to avoid orphans in DB.
> Real life:
> Business rule says that not every record will have a parent. It is
> implemented as a child record has FK that is null.

I'm not too hot on all this, but here is what I was lead to believe: If
Client *must* belong to at least one group, then the client is dependent on
the group - it cannot exist without it. Therefore, it's primary key would
(at least logically) be a composite, where the group pk forms part of the
clients composite primary key. This would ensure that a client cannot exist
without a group!?

This might look like:
Client(GroupID PK, ClientID PK, Name )

Otherwise, if the Client could optionally belong to one Group, the
relationship would be captured in a link table, as you suggested in B?

GroupedClient (PersonID PK/FK, GroupID FK NOT NULL)

Just my 2 pennies worth 8-)

Tobes|||"Tobin Harris" <tobin_dont_you_spam_me@.breathemail.net> wrote in message <news:braub1$1cceh$1@.ID-135366.news.uni-berlin.de>...

> "Andy" <net__space@.hotmail.com> wrote in message
> news:edb90340.0311301114.19718061@.posting.google.c om...
> > Hi All!
> > General statement: FK should not be nullabe to avoid orphans in DB.
> > Real life:
> > Business rule says that not every record will have a parent. It is
> > implemented as a child record has FK that is null.

> I'm not too hot on all this, but here is what I was lead to believe: If
> Client *must* belong to at least one group, then the client is dependent on
> the group - it cannot exist without it. Therefore, it's primary key would
> (at least logically) be a composite, where the group pk forms part of the
> clients composite primary key. This would ensure that a client cannot exist
> without a group!?
> This might look like:
> Client(GroupID PK, ClientID PK, Name )

Did you really mean to claim that ALL non-nullable attributes MUST
'logically' be included as part of the primary key?!

> Otherwise, if the Client could optionally belong to one Group, the
> relationship would be captured in a link table, as you suggested in B?
> GroupedClient (PersonID PK/FK, GroupID FK NOT NULL)

This would avoid the null nonsense until someone does an outer join.

--
Joe Foster <mailto:jlfoster%40znet.com> L. Ron Dullard <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!|||"Joe "Nuke Me Xemu" Foster" <joe@.bftsi0.UUCP> wrote in message
news:1071189386.456990@.news-1.nethere.net...
> Did you really mean to claim that ALL non-nullable attributes MUST
> 'logically' be included as part of the primary key?!

Well, not really! I was just throwing in another option - where if the
existance of one entity is dependent on another, then you can make the PK of
that entity part of a composite key in the dependent entity. It's an
alternative to just non nullable foreign keys, where the related column(s)
become part of a primary key, rather than just a foreign key. Sorry, I think
I need to take my anti-waffle pill, can't seem to put a good explanation
together 8-)

> > Otherwise, if the Client could optionally belong to one Group, the
> > relationship would be captured in a link table, as you suggested in B?
> > GroupedClient (PersonID PK/FK, GroupID FK NOT NULL)
> This would avoid the null nonsense until someone does an outer join.

That's true. So which option would you go for?

Tobes

> --
> Joe Foster <mailto:jlfoster%40znet.com> L. Ron Dullard
<http://www.xenu.net/>
> WARNING: I cannot be held responsible for the above They're
coming to
> because my cats have apparently learned to type. take me away,
ha ha!|||"Tobes (Breath)" <tobin_dont_spam_me@.breathemail.net> wrote in message <news:brck8d$1t2ru$1@.ID-131901.news.uni-berlin.de>...

> "Joe "Nuke Me Xemu" Foster" <joe@.bftsi0.UUCP> wrote in message
> news:1071189386.456990@.news-1.nethere.net...
> > Did you really mean to claim that ALL non-nullable attributes MUST
> > 'logically' be included as part of the primary key?!
> Well, not really! I was just throwing in another option - where if the
> existance of one entity is dependent on another, then you can make the PK of
> that entity part of a composite key in the dependent entity. It's an
> alternative to just non nullable foreign keys, where the related column(s)
> become part of a primary key, rather than just a foreign key. Sorry, I think
> I need to take my anti-waffle pill, can't seem to put a good explanation
> together 8-)

The ClientID by itself should probably be the primary key, though
the GroupID could be made part of an alternate candidate key.

> > > Otherwise, if the Client could optionally belong to one Group, the
> > > relationship would be captured in a link table, as you suggested in B?
> > > > GroupedClient (PersonID PK/FK, GroupID FK NOT NULL)
> > This would avoid the null nonsense until someone does an outer join.
> That's true. So which option would you go for?

Maybe have a special "Loners" group? =) It's hard to say given
the information at hand. Yeah, I know, the usual cop-out...

--
Joe Foster <mailto:jlfoster%40znet.com> Sacrament R2-45 <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!|||"Tobes (Breath)" <tobin_dont_spam_me@.breathemail.net> wrote in message
news:brck8d$1t2ru$1@.ID-131901.news.uni-berlin.de...
> "Joe "Nuke Me Xemu" Foster" <joe@.bftsi0.UUCP> wrote in message
> news:1071189386.456990@.news-1.nethere.net...
> > Did you really mean to claim that ALL non-nullable attributes MUST
> > 'logically' be included as part of the primary key?!
> Well, not really! I was just throwing in another option - where if the
> existance of one entity is dependent on another, then you can make the PK
of
> that entity part of a composite key in the dependent entity. It's an
> alternative to just non nullable foreign keys, where the related column(s)
> become part of a primary key, rather than just a foreign key. Sorry, I
think
> I need to take my anti-waffle pill, can't seem to put a good explanation
> together 8-)

Please allow me to hang an important point off of your post. The bind you
find yourself in above is certainly not unique to you so there is no need to
take this personally.

Your bind above demonstrates a very real pitfall of confusing knowledge of a
specific tool with knowledge of fundamentals. I have seen numerous people
fall into this specific pit throughout my career. I figure at least a 90%
chance the tool you know is Erwin, and you are describing their
"identifying" vs. "non-identifying" relationships.

I have seen people using this tool create schemas with ridiculous six and
seven part compound primary keys and call it "normalization".

Your bind above also demonstrates the dangers of using a graphical crutch in
place of real thought and analysis.

I respectfully suggest you will find yourself much more effective if you
learn the fundamentals before the tools.|||Just a couple of things:

> Your bind above demonstrates a very real pitfall of confusing knowledge of
a
> specific tool with knowledge of fundamentals. I have seen numerous people
> fall into this specific pit throughout my career. I figure at least a 90%
> chance the tool you know is Erwin, and you are describing their
> "identifying" vs. "non-identifying" relationships.

Identifying and non-identifying relationships are not an Erwin thing. They
are an idef1x thing. Check FIPS publication 184:
http://www.itl.nist.gov/fipspubs/idef1x.doc.

> I have seen people using this tool create schemas with ridiculous six and
> seven part compound primary keys and call it "normalization".

Just because you have six and seven part compound keys does not mean that
you are not normalized. It may take that many different atomic bits to
uniquely identify something. If these compound keys are built from six
relationships, the chances of it being normalized are about as good as the
San Diego Chargers winning last years Super Bowl, but it is possible.

> Your bind above also demonstrates the dangers of using a graphical crutch
in
> place of real thought and analysis.

So you don't use data models? The graphical "crutch" as you call it is
pretty standard stuff. I have never considered data models controversial in
the least. Cannot question the need for thought and analysis though :)

> I respectfully suggest you will find yourself much more effective if you
> learn the fundamentals before the tools.

You are correct (cannot believe I am agreeing with you :) about just having
tool knowledge. Erwin is a great tool, but they do have some
terminology/practices that are not standard, and frankly the tool will let
you get away with murder. It's job is to let you draw pictures of your
data, not to give you a hard time. That is your job Bob :)

--
-----------------------
----
Louis Davidson (drsql@.hotmail.com)
Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)

"Bob Badour" <bbadour@.golden.net> wrote in message
news:Vf6dnepaArIqnkeiRVn-tw@.golden.net...
> "Tobes (Breath)" <tobin_dont_spam_me@.breathemail.net> wrote in message
> news:brck8d$1t2ru$1@.ID-131901.news.uni-berlin.de...
> > "Joe "Nuke Me Xemu" Foster" <joe@.bftsi0.UUCP> wrote in message
> > news:1071189386.456990@.news-1.nethere.net...
> > > Did you really mean to claim that ALL non-nullable attributes MUST
> > > 'logically' be included as part of the primary key?!
> > Well, not really! I was just throwing in another option - where if the
> > existance of one entity is dependent on another, then you can make the
PK
> of
> > that entity part of a composite key in the dependent entity. It's an
> > alternative to just non nullable foreign keys, where the related
column(s)
> > become part of a primary key, rather than just a foreign key. Sorry, I
> think
> > I need to take my anti-waffle pill, can't seem to put a good explanation
> > together 8-)
> Please allow me to hang an important point off of your post. The bind you
> find yourself in above is certainly not unique to you so there is no need
to
> take this personally.
> Your bind above demonstrates a very real pitfall of confusing knowledge of
a
> specific tool with knowledge of fundamentals. I have seen numerous people
> fall into this specific pit throughout my career. I figure at least a 90%
> chance the tool you know is Erwin, and you are describing their
> "identifying" vs. "non-identifying" relationships.
> I have seen people using this tool create schemas with ridiculous six and
> seven part compound primary keys and call it "normalization".
> Your bind above also demonstrates the dangers of using a graphical crutch
in
> place of real thought and analysis.
> I respectfully suggest you will find yourself much more effective if you
> learn the fundamentals before the tools.|||"Bob Badour" <bbadour@.golden.net> wrote in message
news:Vf6dnepaArIqnkeiRVn-tw@.golden.net...
> "Tobes (Breath)" <tobin_dont_spam_me@.breathemail.net> wrote in message
> news:brck8d$1t2ru$1@.ID-131901.news.uni-berlin.de...
> > "Joe "Nuke Me Xemu" Foster" <joe@.bftsi0.UUCP> wrote in message
> > news:1071189386.456990@.news-1.nethere.net...
> > > Did you really mean to claim that ALL non-nullable attributes MUST
> > > 'logically' be included as part of the primary key?!
> > Well, not really! I was just throwing in another option - where if the
> > existance of one entity is dependent on another, then you can make the
PK
> of
> > that entity part of a composite key in the dependent entity. It's an
> > alternative to just non nullable foreign keys, where the related
column(s)
> > become part of a primary key, rather than just a foreign key. Sorry, I
> think
> > I need to take my anti-waffle pill, can't seem to put a good explanation
> > together 8-)
> Please allow me to hang an important point off of your post. The bind you
> find yourself in above is certainly not unique to you so there is no need
to
> take this personally.
> Your bind above demonstrates a very real pitfall of confusing knowledge of
a
> specific tool with knowledge of fundamentals. I have seen numerous people
> fall into this specific pit throughout my career. I figure at least a 90%
> chance the tool you know is Erwin, and you are describing their
> "identifying" vs. "non-identifying" relationships.

Interestingly, I have used Erwin, but only briefly! My knowledge of this
technique came from something tought in relational theory during my degree.
Basically, we were being shown how to transition from conceptual ER diagrams
to a physical model, and this specific technique was to be used if one
entity's existance was dependent on another. I even recall the classroom
example! This was along the lines of if you had the entities Cinema and
CinemaScreen, then the existance of the screen might be dependent on the
cinema (no screen without a cinema kinda thing). Therefore, the PK of the
cinema would 'propogage' down to form part of the CinemaScreens PK. I'm not
really bothered about the context, this just did seem like a logical thing
to do.

Don't worry, I haven't taken this personally! However, having learnt this
approach well before sitting down and trying to use a RDBMS, I found that
when using any RDBMS, they seemed to support the concept of a column that is
part of a primary key, and a foreign key also. So, way back then I never
questioned it.

> I have seen people using this tool create schemas with ridiculous six and
> seven part compound primary keys and call it "normalization".

Yeah, I've fallen into this trap once or twice (although not quite so far!)

> Your bind above also demonstrates the dangers of using a graphical crutch
in
> place of real thought and analysis.
> I respectfully suggest you will find yourself much more effective if you
> learn the fundamentals before the tools.

A fair suggestion, although I thought I knew at least most of the
fundamentals! I've always put learning this before learnign the tools. That
way, when you come to learn the tools, it os interesting to see if/how they
supported the things you want to achieve, rather than pushing buttons seeing
what the tool could do, and then trying to understand it!

Just out of interest, what would you describe as the fundamentals?

Tobes|||"Tobin Harris" <tobin_dont_you_spam_me@.breathemail.net> wrote in message
news:brddal$26unq$1@.ID-135366.news.uni-berlin.de...
> "Bob Badour" <bbadour@.golden.net> wrote in message
> news:Vf6dnepaArIqnkeiRVn-tw@.golden.net...
> > "Tobes (Breath)" <tobin_dont_spam_me@.breathemail.net> wrote in message
> > news:brck8d$1t2ru$1@.ID-131901.news.uni-berlin.de...
> > > > "Joe "Nuke Me Xemu" Foster" <joe@.bftsi0.UUCP> wrote in message
> > > news:1071189386.456990@.news-1.nethere.net...
> > > > Did you really mean to claim that ALL non-nullable attributes MUST
> > > > 'logically' be included as part of the primary key?!
> > > > Well, not really! I was just throwing in another option - where if the
> > > existance of one entity is dependent on another, then you can make the
> PK
> > of
> > > that entity part of a composite key in the dependent entity. It's an
> > > alternative to just non nullable foreign keys, where the related
> column(s)
> > > become part of a primary key, rather than just a foreign key. Sorry, I
> > think
> > > I need to take my anti-waffle pill, can't seem to put a good
explanation
> > > together 8-)
> > Please allow me to hang an important point off of your post. The bind
you
> > find yourself in above is certainly not unique to you so there is no
need
> to
> > take this personally.
> > Your bind above demonstrates a very real pitfall of confusing knowledge
of
> a
> > specific tool with knowledge of fundamentals. I have seen numerous
people
> > fall into this specific pit throughout my career. I figure at least a
90%
> > chance the tool you know is Erwin, and you are describing their
> > "identifying" vs. "non-identifying" relationships.
> Interestingly, I have used Erwin, but only briefly! My knowledge of this
> technique came from something tought in relational theory during my
degree.
> Basically, we were being shown how to transition from conceptual ER
diagrams
> to a physical model, and this specific technique was to be used if one
> entity's existance was dependent on another. I even recall the classroom
> example!

I doubt, then, you were actually taught any relational theory. With the
current state of the education, I do not find that surprising.

> Don't worry, I haven't taken this personally! However, having learnt this
> approach well before sitting down and trying to use a RDBMS, I found that
> when using any RDBMS, they seemed to support the concept of a column that
is
> part of a primary key, and a foreign key also. So, way back then I never
> questioned it.

The candidate keys and foreign keys within a relation are generally
independent of one another and can overlap. Of course, a correspondence
exists between a foreign key in a referencing relation and a candidate key
in the referenced relation. I said "generally independent" above because in
the case that a relation refers to itself, the foreign key and candidate key
are in the same relation.

Whether some or all of a foreign key forms some or all of a candidate key
has no particular importance to me.

> > I have seen people using this tool create schemas with ridiculous six
and
> > seven part compound primary keys and call it "normalization".
> Yeah, I've fallen into this trap once or twice (although not quite so
far!)
> > Your bind above also demonstrates the dangers of using a graphical
crutch
> in
> > place of real thought and analysis.
> > I respectfully suggest you will find yourself much more effective if you
> > learn the fundamentals before the tools.
> A fair suggestion, although I thought I knew at least most of the
> fundamentals! I've always put learning this before learnign the tools.
That
> way, when you come to learn the tools, it os interesting to see if/how
they
> supported the things you want to achieve, rather than pushing buttons
seeing
> what the tool could do, and then trying to understand it!
> Just out of interest, what would you describe as the fundamentals?

Chris Date's _Introduction to Database Management Systems_ makes a good
start at them. I would seem foolish to try to teach them in an email
message.

One would start with "What is data?" and "What does it mean to manage data?"
From there, one would move to: "What principles facilitate or guide
effective data management?" And onward...

Since you apparently think one can easily enumerate them in an email, what
would you describe as the fundamentals?|||"Bob Badour" <bbadour@.golden.net> wrote in message
news:tPGdndKS74g91Eei4p2dnA@.golden.net...

> I doubt, then, you were actually taught any relational theory. With the
> current state of the education, I do not find that surprising.

> One would start with "What is data?"

If I add this data to that data do I have 2 datas?|||"Bob Badour" <bbadour@.golden.net> wrote in message <news:tPGdndKS74g91Eei4p2dnA@.golden.net>...

> I doubt, then, you were actually taught any relational theory. With the
> current state of the education, I do not find that surprising.

At my alma mater, UCSB, relational theory was an elective, but
at least it was available at all. =/

> Chris Date's _Introduction to Database Management Systems_ makes a good
> start at them. I would seem foolish to try to teach them in an email
> message.

I have the seventh edition. Is there a definitive list of the
changes made to the eighth, perhaps at http://dbdebunk.com/ ?

--
Joe Foster <mailto:jlfoster%40znet.com> "Regged" again? <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!|||"Bob Badour" <bbadour@.golden.net> wrote in message
news:tPGdndKS74g91Eei4p2dnA@.golden.net...
> Chris Date's _Introduction to Database Management Systems_ makes a good
> start at them. I would seem foolish to try to teach them in an email
> message.

Don't worry Bob, I wasn't expecting you to seem foolish, or give a full
tutorial.

> One would start with "What is data?" and "What does it mean to manage
data?"
> From there, one would move to: "What principles facilitate or guide
> effective data management?" And onward...

Ok, this makes sense.

> Since you apparently think one can easily enumerate them in an email, what
> would you describe as the fundamentals?

I hadn't even considered whether it was difficult or not. I was simply
interested in what your perceived "fundamentals" entailed, mainly so I could
go and learn more... I kind of expected you to mention some general topics,
which may or may not have included:

Normalization - learning how to extrapolate to 1st, 2nd and 3rd normal form
schemas
Integrety - learning that integrety applies at various levels - Domain,
Column, Table, Database (Referential)
Data Types - seen as sets of permissable values that enforce business rules
by constraining the data that is stored.
Top-Down Analysis - learning to identify entities and business rules by
reading existing documentation, verbal communication etc
Bottom Up Analysis - learning to derive and normalise attribute listings
Keys and Identity - different types and why|||"Tobin Harris" <tobin_dont_you_spam_me@.breathemail.net> wrote in message
news:brnvmp$5gqn4$1@.ID-135366.news.uni-berlin.de...
> "Bob Badour" <bbadour@.golden.net> wrote in message
> news:tPGdndKS74g91Eei4p2dnA@.golden.net...
> > Chris Date's _Introduction to Database Management Systems_ makes a good
> > start at them. I would seem foolish to try to teach them in an email
> > message.
> Don't worry Bob, I wasn't expecting you to seem foolish, or give a full
> tutorial.
> > One would start with "What is data?" and "What does it mean to manage
> data?"
> > From there, one would move to: "What principles facilitate or guide
> > effective data management?" And onward...
> Ok, this makes sense.
> > Since you apparently think one can easily enumerate them in an email,
what
> > would you describe as the fundamentals?
> I hadn't even considered whether it was difficult or not. I was simply
> interested in what your perceived "fundamentals" entailed, mainly so I
could
> go and learn more... I kind of expected you to mention some general
topics,
> which may or may not have included:
> Normalization - learning how to extrapolate to 1st, 2nd and 3rd normal
form
> schemas
> Integrety - learning that integrety applies at various levels - Domain,
> Column, Table, Database (Referential)
> Data Types - seen as sets of permissable values that enforce business
rules
> by constraining the data that is stored.
> Top-Down Analysis - learning to identify entities and business rules by
> reading existing documentation, verbal communication etc
> Bottom Up Analysis - learning to derive and normalise attribute listings
> Keys and Identity - different types and why

Your list of "fundamentals" does not answer any of the questions "What is
data?", "What does it mean to manage data?" or "What principles facilitate
or guide effective data management?"

Of the items in your list above, integrity and data types are fundamental,
but your elaborations above are anything but fundamental.

One can come up with any number of taxonomies for integrity
constraints--Chris Date has published enough of them in his career. The
taxonomy I find most enlightening is: All integrity constraints constrain
variables. Integrity is fundamental because it is fundamental to the
manipulation function when managing data.

A data type does not enforce business rules--the integrity function of the
dbms does this. Data type is fundamental to computing and not only to data
management. A data type comprises both a set of values and a set of
operations on those values. With respect to the relational model, Date and
Darwen have observed that data types define what we can make statements
about, and relations make statements about them.|||"Bob Badour" <bbadour@.golden.net> wrote in message
news:aoCdnbmkVbe0SUKiRVn-tA@.golden.net...
> Your list of "fundamentals" does not answer any of the questions "What is
> data?", "What does it mean to manage data?" or "What principles facilitate
> or guide effective data management?"

In that case I'd be interested in learning some of these fundamentals. I may
have to take myself to the library...

> Of the items in your list above, integrity and data types are fundamental,
> but your elaborations above are anything but fundamental.
> One can come up with any number of taxonomies for integrity
> constraints--Chris Date has published enough of them in his career. The
> taxonomy I find most enlightening is: All integrity constraints constrain
> variables. Integrity is fundamental because it is fundamental to the
> manipulation function when managing data.
> A data type does not enforce business rules--the integrity function of the
> dbms does this. Data type is fundamental to computing and not only to data
> management. A data type comprises both a set of values and a set of
> operations on those values. With respect to the relational model, Date and
> Darwen have observed that data types define what we can make statements
> about, and relations make statements about them.

Hmmm, I thought Data Types (including UDTs) did enforce business rules, by
constraining the set of possible values that can be stored in a column
constrained to that type. If a business rule dictates that data of a certain
type must fall within a spefic range, for example, then by defining a type
that imposes this constraint, the business rule could be enforced by the
Data Type?

Thanks for your reply

Tobes|||"Tobes (Breath)" <tobin_dont_spam_me@.breathemail.net> wrote in message
news:brq3iu$5nfbc$1@.ID-131901.news.uni-berlin.de...

> Hmmm, I thought Data Types (including UDTs) did enforce business rules, by
> constraining the set of possible values that can be stored in a column
> constrained to that type. If a business rule dictates that data of a
certain
> type must fall within a spefic range, for example, then by defining a type
> that imposes this constraint, the business rule could be enforced by the
> Data Type?

The type of data type chosen is the first step in enforcing business rules.
Clearly if the business rule states this will be an integer between -100 and
100, then you first choose a datatype. In this case, you might go with a
smallint, or just an integer. Then you apply a check constraint. A proper
Domain or a User Defined Type will include the datatype and some of the
checking needed. If you chose a varchar for instance, the user would be
able to insert whatever into the column, unless you built more elaborate
checking into your column.

--
-----------------------
----
Louis Davidson (drsql@.hotmail.com)
Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)|||"Tobes (Breath)" <tobin_dont_spam_me@.breathemail.net> wrote in message
news:brq3iu$5nfbc$1@.ID-131901.news.uni-berlin.de...
> "Bob Badour" <bbadour@.golden.net> wrote in message
> news:aoCdnbmkVbe0SUKiRVn-tA@.golden.net...
> > Your list of "fundamentals" does not answer any of the questions "What
is
> > data?", "What does it mean to manage data?" or "What principles
facilitate
> > or guide effective data management?"
> In that case I'd be interested in learning some of these fundamentals. I
may
> have to take myself to the library...

Try to find a library with a copy of the ISO/IEC Standard Vocabularies for
Information Technology. A friend drew my attention to an article in IEEE
Compute called _The Great Term Robbery_ a few years ago; I found both that
article and the standard vocabularies very informative with respect to "What
is data?".

I have never found a succinct list of principles, and if anyone knows of
one, I would love to see it. Codd's 12 Rules embody a lot of principles he
did not name explicitly; although, logical identity, guaranteed access,
physical and logical independence are all principles. Certainly, the
principle of separating concerns applies to data management in several ways.
As a general principle, one prefers to minimize, centralize and automate any
need for highly specialized or arcane knowledge. One prefers to maximize the
portability of one's data. One prefers to make easy things easy and to make
likely errors difficult. One prefers to minimize the learning curve for
casual users. etc.

> > Of the items in your list above, integrity and data types are
fundamental,
> > but your elaborations above are anything but fundamental.
> > One can come up with any number of taxonomies for integrity
> > constraints--Chris Date has published enough of them in his career. The
> > taxonomy I find most enlightening is: All integrity constraints
constrain
> > variables. Integrity is fundamental because it is fundamental to the
> > manipulation function when managing data.
> > A data type does not enforce business rules--the integrity function of
the
> > dbms does this. Data type is fundamental to computing and not only to
data
> > management. A data type comprises both a set of values and a set of
> > operations on those values. With respect to the relational model, Date
and
> > Darwen have observed that data types define what we can make statements
> > about, and relations make statements about them.
> Hmmm, I thought Data Types (including UDTs) did enforce business rules, by
> constraining the set of possible values that can be stored in a column
> constrained to that type.

Data types form part of the definition of some constraints, but the
integrity function of the dbms enforces constraints. What you suggest above
is similar to suggesting that legislation and street signs enforce traffic
laws. Police officers and the judiciary enforce traffic laws.

> If a business rule dictates that data of a certain
> type must fall within a spefic range, for example, then by defining a type
> that imposes this constraint, the business rule could be enforced by the
> Data Type?

The type does not impose the constraint; the integrity function of the dbms
imposes the constraint. The type merely describes the constraint. For a very
long time, almost all constraints in commerical SQL dbmses were nothing more
than comments. One was allowed to express them, but the integrity function
of the dbms ignored them (if one can really claim an integrity function even
exists in that situation).

Wednesday, March 7, 2012

Can CASE match more than once ?

Hi
I'm tring to write a statement to analyse what orders were open on the first
day of each month from a system and return one data set with the months
listed and all the orders open during that month. eg.
Mon Order_No
Jan 001
Jan 002
Jan 003
Feb 002
Feb 003
Feb 004
The orders all have an open and closed date, so I want to check for each
month if the first of the month falls between the open and closed date of
each order.
I've set up a dummy database for testing - what I'd like to know is whether
CASE statements be made to match more than once :
SELECT MyNewField =
CASE
WHEN data1 = 1 THEN 'Is One'
WHEN data1 > 1 then 'Not One'
end,
data2
FROM APW_Test
my table is as follows
data1
1
2
3
4
So I would hope to see one result for the number 1 (Is One) and two results
for the remaining numbers because they match both case statements. However,
CASE seems to match the first statement and then stop for each record.
Is there a way I can achieve the result I want fairly simply ?
Thanks in advance.
AndrewHi ... I made a mistake in my logic. What I meant was

> WHEN data1 = 1 THEN 'Is One'
> WHEN data1 > 0 then 'Greater Than Zero'
so I expect 1 to appear twice. All else the same.
"Andrew Webb" <andrew.webb@.eme-med.co.uk> wrote in message
news:uMs5zBpuFHA.1256@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm tring to write a statement to analyse what orders were open on the
> first day of each month from a system and return one data set with the
> months listed and all the orders open during that month. eg.
> Mon Order_No
> Jan 001
> Jan 002
> Jan 003
> Feb 002
> Feb 003
> Feb 004
> The orders all have an open and closed date, so I want to check for each
> month if the first of the month falls between the open and closed date of
> each order.
> I've set up a dummy database for testing - what I'd like to know is
> whether CASE statements be made to match more than once :
> SELECT MyNewField =
> CASE
> WHEN data1 = 1 THEN 'Is One'
> WHEN data1 > 1 then 'Not One'
> end,
> data2
> FROM APW_Test
> my table is as follows
> data1
> 1
> 2
> 3
> 4
> So I would hope to see one result for the number 1 (Is One) and two
> results for the remaining numbers because they match both case statements.
> However, CASE seems to match the first statement and then stop for each
> record.
> Is there a way I can achieve the result I want fairly simply ?
> Thanks in advance.
> Andrew
>|||Andrew
you can simply use procedure/function to use if condition.
However post DDL,Sample data to help you better
Regards
R.D
"Andrew Webb" wrote:

> Hi ... I made a mistake in my logic. What I meant was
>
> so I expect 1 to appear twice. All else the same.
>
> "Andrew Webb" <andrew.webb@.eme-med.co.uk> wrote in message
> news:uMs5zBpuFHA.1256@.TK2MSFTNGP09.phx.gbl...
>
>|||On Fri, 16 Sep 2005 08:39:06 +0100, "Andrew Webb"
<andrew.webb@.eme-med.co.uk> wrote:

> Hi ... I made a mistake in my logic. What I meant was
>
> so I expect 1 to appear twice. All else the same.
CASE returns only the first match.|||Andrew,
It sounds to me like you need a JOIN operation, not a CASE expression.
Post the CREATE TABLE and INSERT statements for some specific
data if you want a more careful answer, but this might be close:
select data1, DisplayAnswer
from T join (
select 'equal 1' as TestCondition, 'Is One' as DisplayAnswer
union all
select 'above 1', 'Not One'
) C
on (
TestCondition = 'equal 1' and data1 = 1
) or (
TestCondition = 'above 1' and data1 > 1
)
If you select only from your 4-row table, with no other table
joined in, you cannot obtain a result that contains any row
more than once.
Steve Kass
Drew University
"Andrew Webb" <andrew.webb@.eme-med.co.uk> wrote in message
news:uMs5zBpuFHA.1256@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm tring to write a statement to analyse what orders were open on the
> first day of each month from a system and return one data set with the
> months listed and all the orders open during that month. eg.
> Mon Order_No
> Jan 001
> Jan 002
> Jan 003
> Feb 002
> Feb 003
> Feb 004
> The orders all have an open and closed date, so I want to check for each
> month if the first of the month falls between the open and closed date of
> each order.
> I've set up a dummy database for testing - what I'd like to know is
> whether CASE statements be made to match more than once :
> SELECT MyNewField =
> CASE
> WHEN data1 = 1 THEN 'Is One'
> WHEN data1 > 1 then 'Not One'
> end,
> data2
> FROM APW_Test
> my table is as follows
> data1
> 1
> 2
> 3
> 4
> So I would hope to see one result for the number 1 (Is One) and two
> results for the remaining numbers because they match both case statements.
> However, CASE seems to match the first statement and then stop for each
> record.
> Is there a way I can achieve the result I want fairly simply ?
> Thanks in advance.
> Andrew
>|||On Fri, 16 Sep 2005 08:30:00 +0100, Andrew Webb wrote:

>Hi
>I'm tring to write a statement to analyse what orders were open on the firs
t
>day of each month from a system and return one data set with the months
>listed and all the orders open during that month. eg.
>Mon Order_No
>Jan 001
>Jan 002
>Jan 003
>Feb 002
>Feb 003
>Feb 004
>The orders all have an open and closed date, so I want to check for each
>month if the first of the month falls between the open and closed date of
>each order.
Hi Andrew,
You could use a calendar table or a table of integers to get this. I'll
give an example with a table of integers that's made up "on the fly".
You can expand it as needed, or make a real table of integers (as
explained on http://www.aspfaq.com/show.asp?id=2516).
DECLARE @.StartDate smalldatetime
,@.EndDate smalldatetime
SET @.StartDate = '20050101' -- Should be first of the month
SET @.EndDate = '20051201'
SELECT DATEADD(month, Numbers.n, @.StartDate) AS Mon,
Orders.Order_No
FROM Orders
INNER JOIN (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
SELECT 9 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL
SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL
SELECT 14 UNION ALL SELECT 15) AS Numbers(n)
WHERE Orders.OpenDate < DATEADD(month, Numbers.n, @.StartDate)
AND Orders.CloseDate > DATEADD(month, Numbers.n, @.StartDate)
AND DATEADD(month, Numbers.n, @.StartDate) <= @.EndDate
ORDER BY Numbers.n, Orders.Order_No
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Saturday, February 25, 2012

can anyone help with this update statement?

I am wondering how to write a sql update statement that would:

Update table 1
set col 2 = 'YES'
where (select col 1 from table 1 where col 2 = 'YES')

Table 1 BEFORE:
Col 1 Col 2
A
A
A
B
B YES
B
C
C
C
D
D YES
D

Table 1 After:
Col 1 Col 2
A
A
A
B YES
B YES
B YES
C
C
C
D YES
D YES
D YES

Can anyone give me some ideas how to write this statement or point me in the right direction?

Thanks,

Blair

Try something along the lines of:

Code Snippet

Update [table 1]
set [col 2] = 'YES'
from [table 1] a -- "a" is an alias
where exists
( select [col 1]
from [table 1] b -- "b" is an alias
where b.[col 2] = 'YES'
and a.[col 1] = b.[col 1]
)

|||

Here is one way.

Code Snippet

update table1

set col2=t.col2

from table1 join (select * from table1 where col2='YES') [t] on table1.col1=t2.col1

|||

While the queries given will fix this data, and you could correct the problem somewhat by embedding those queries in a trigger, this is a classic sign of a poorly normalized table. If one value in the row, in this case Col 2, is determined by the value in another column Col1, then you have the high potential for data problems. (Which clearly you do, since you are writing this query.)

You should definitely consider having a table that represents whatever Table1 represents where Col1 is the key, and the Col2 values is its column. Then, one modification takes care of everything, and you don't end up with inconsistent data.

|||

The simplest syntax:

Code Snippet

Update Table1

Set Col2 = 'Yes'

where Col1 in (Select Col1 from Table1 where Col2 = 'Yes')

|||

This worked perfectly.

Thanks,

Blair

Friday, February 24, 2012

can any body tell me what wrong in my sql statement

Hi,

can any body tell me what is wrong in my sql statement

SELECT title, price,

Budget

=CASE priceWHEN price> 20.00THEN'Expensive'WHEN priceBETWEEN 10.00AND 19.99THEN'Moderate'WHEN price< 10.00THEN'Inexpensive'ELSE'Unknown'END

FROM

titlesit gives me this error

Msg 170, Level 15, State 1, Line 3

Line 3: Incorrect syntax near '>'.

but when i use somthing like that i will works fine

SELECT

Budget

i am using sql server 2000

SELECT title, price, Budget=CASEWHEN price < 10.00THEN'Inexpensive'WHEN price < 20.00THEN'Moderate'WHEN priceISNULLTHEN'Unknown'ELSE'Expensive'ENDFROMtitles

I'm assuming that price is a Decimal datatype.

|||

Take the price after CASE out. There are two forms of CASE statement. Your syntax was wrong.

SELECT title, price, Budget=CASE

WHEN price> 20.00THEN'Expensive'

WHEN

priceBETWEEN 10.00AND 19.99THEN'Moderate'

WHEN

price< 10.00THEN'Inexpensive'

ELSE

'Unknown'

END

FROMtitles

Can an update statement be used for interpolating missing data?

Here is a small sample of data from a table of about 500 rows
(Using MSSqlserver 2000)

EntryTime Speed Gross Net
------ -- --
21:09:13.310 0 0 0
21:09:19.370 9000 NULL NULL
21:09:21.310 NULL 95 NULL
21:10:12.380 9000 NULL NULL
21:10:24.310 NULL 253 NULL
21:11:24.370 8000 NULL NULL
21:11:27.310 NULL 410 NULL
21:11:51.320 NULL 438 NULL
21:11:51.490 NULL NULL 10

After the first row, every row has only one value of the three.
I would like to replace all the NULL values with calculated
interpolations.

I can do it w/ cursors or while loops.
I could do it w/ VB (I think)

Can this be done w/ an Update statement using self joins?
What would be the best way?

The value for speed can increase or decrease over time, but can never
be < 0

Net is always less than gross, and neither can go below 0.

TIA for any helpful suggestions.

Thanks,
BMIt's not really clear how you want to calculate the new values, but
perhaps you can look at CASE and COALESCE in Books Online. If this
doesn't help, then you should post some more information about how you
want to calculate the new values.

Simon|||I expect it will be possible with an UPDATE and a join/subquery.

UPDATE YourTable
SET speed =
(SELECT ...
FROM YourTable
WHERE entrytime < YourTable.entrytime ...)
WHERE speed IS NULL

If you need a complete solution then explain the calculation, show the
result you want and post DDL for the table. Also, it's best to post sample
data as INSERT statements so that others can more easily test out possible
solutions. That way you'll get accurate and useful answers more quickly.
See:
http://www.aspfaq.com/etiquette.asp?id=5006

--
David Portas
SQL Server MVP
--|||Thank you greatly for the FAQ link. I learned a lot just reading it.

The table:
if exists (select * from dbo.sysobjects where id =
object_id(N'[tblProfileTemp]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [tblProfileTemp]
GO

if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblProfileTemp]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
BEGIN
CREATE TABLE [tblProfileTempX] (
--[Item] [int] IDENTITY (1, 1) NOT NULL ,
[Item] [int] NOT NULL ,
[EntryTime] [datetime] NULL ,
[RunTime] [numeric](12, 3) NULL ,
[Speed] [int] NULL ,
[gross] [int] NULL ,
[net] [int] NULL
) ON [PRIMARY]
END

The insert statements (the code to generate this was an education in
itself).
(10 rows should be enough) I commented out the identity contraint so
inserting w/o the column list would be possible.
INSERT INTO [tblProfileTempx] VALUES(1,'Jun 21 2005
9:09:13:310PM',0.000,0,0,0)
INSERT INTO [tblProfileTempx] VALUES(2,'Jun 21 2005
9:09:19:370PM',6.060,9000,NULL,NULL)
INSERT INTO [tblProfileTempx] VALUES(3,'Jun 21 2005
9:09:21:310PM',8.000,NULL,95,NULL)
INSERT INTO [tblProfileTempx] VALUES(4,'Jun 21 2005
9:10:12:380PM',59.070,9000,NULL,NULL)
INSERT INTO [tblProfileTempx] VALUES(5,'Jun 21 2005
9:10:24:310PM',71.000,NULL,253,NULL)
INSERT INTO [tblProfileTempx] VALUES(6,'Jun 21 2005
9:11:24:370PM',131.060,8000,NULL,NULL)
INSERT INTO [tblProfileTempx] VALUES(7,'Jun 21 2005
9:11:27:310PM',134.000,NULL,410,NULL)
INSERT INTO [tblProfileTempx] VALUES(8,'Jun 21 2005
9:11:51:320PM',158.010,NULL,438,NULL)
INSERT INTO [tblProfileTempx] VALUES(9,'Jun 21 2005
9:11:51:490PM',158.180,0,NULL,NULL)
INSERT INTO [tblProfileTempx] VALUES(10,'Jun 21 2005
9:13:51:310PM',278.000,NULL,446,NULL)

Explanation of data:
The data represents the output of a running press. Each data element
is recorded at EntryTime. RunTime represents the time elapsed since
the start, and is expressed in seconds. Gross is number of copies
printed. Net is number of copies not rejected automatically by various
defect detectors.

Desired Result:
Example:
Gross for item 1 is 0
Gross for item 2 is null
Gross for item 3 is 95

I need to replace the null in item 2 with a value that represents the
gross count for that time, assuming a constant press speed. It will
not necesarily be constant, but the error will be slight.

The formula for that value will be:

Gross2 = Gross1 + ((Gross3 - Gross1) * ((RunTime2-RunTime1) / (RunTime3
- RunTime1)))

Similar interpolations will be calculated for Net and Speed.

It gets harder where there are two or more nulls between known values.

I was working along the lines of:

Update t1
Set t1.Gross = t0.Gross + ((t2.Gross = t0.Gross) *
((t1.runtime-t0.runtime)/(t2.runtime-t0.runtime)))

from tblProfileTempX t1 inner join tblProfileTempX t0 on t0.item =
t1.item
inner join tblProfileTempX t2 on t2.item = t0.item

where t1.gross is null
and t0.EntryTime = (select Max(EntryTime) from tblProfileTempX
where gross is not null and item < t1.item)
and t2.EntryTime = (select Min(EntryTime) from tblProfileTempX
where gross is not null and item > t1.item)

I've reduce the errors to the following:
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '='.
Server: Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'and'.

Thanks in advance for your time and effort, and apologies for the group
etiquette breach,

Regards,
BM

David Portas wrote:
> I expect it will be possible with an UPDATE and a join/subquery.
> UPDATE YourTable
> SET speed =
> (SELECT ...
> FROM YourTable
> WHERE entrytime < YourTable.entrytime ...)
> WHERE speed IS NULL
> If you need a complete solution then explain the calculation, show the
> result you want and post DDL for the table. Also, it's best to post sample
> data as INSERT statements so that others can more easily test out possible
> solutions. That way you'll get accurate and useful answers more quickly.
> See:
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --|||Groan:
Tired eyes mistook an = sign for a - sign in line two.
With that fixed, it runs, but:

0 rwos affected|||[posted and mailed, please reply in news]

Vorpal (brumac@.gmail.com) writes:
> Here is a small sample of data from a table of about 500 rows
> (Using MSSqlserver 2000)
> EntryTime Speed Gross Net
> ------ -- --
> 21:09:13.310 0 0 0
> 21:09:19.370 9000 NULL NULL
> 21:09:21.310 NULL 95 NULL
> 21:10:12.380 9000 NULL NULL
> 21:10:24.310 NULL 253 NULL
> 21:11:24.370 8000 NULL NULL
> 21:11:27.310 NULL 410 NULL
> 21:11:51.320 NULL 438 NULL
> 21:11:51.490 NULL NULL 10
> After the first row, every row has only one value of the three.
> I would like to replace all the NULL values with calculated
> interpolations.
> I can do it w/ cursors or while loops.
> I could do it w/ VB (I think)
> Can this be done w/ an Update statement using self joins?

Not "an", but a couple. In the below script I get the data into a temp
table with an IDENTITY column, which has a consecutive number. I then
find the next and previous row with a non-NULL value for speed, for those
rows that have a NULL value. Once I have these pointers I can make the
interpolation. There is no extrapolation for the NULL values at the end.

The number of UPDATE statements could be reduced if you have three
sets of pointer columns, but I'm not sure that is worth the pain.

The script does not include handling of Net. That is left as an exercise
to the reader. :-)

CREATE TABLE tbl (entrytime datetime NOT NULL PRIMARY KEY,
speed int NULL,
gross int NULL,
net int NULL)
go
INSERT tbl(entrytime, speed, gross, net)
SELECT '21:09:13.310', 0, 0, 0 UNION
SELECT '21:09:19.370', 9000, NULL, NULL UNION
SELECT '21:09:21.310', NULL, 95, NULL UNION
SELECT '21:10:12.380', 9000, NULL, NULL UNION
SELECT '21:10:24.310', NULL, 253, NULL UNION
SELECT '21:11:24.370', 8000, NULL, NULL UNION
SELECT '21:11:27.310', NULL, 410, NULL UNION
SELECT '21:11:51.320', NULL, 438, NULL UNION
SELECT '21:11:51.490', NULL, NULL, 10
go
CREATE TABLE #temp (ident int IDENTITY UNIQUE,
entrytime datetime NOT NULL PRIMARY KEY,
speed int NULL,
gross int NULL,
net int NULL,
prevval int NULL,
nextval int NULL)

INSERT #temp(entrytime, speed, gross, net)
SELECT entrytime, speed, gross, net
FROM tbl
ORDER BY entrytime

UPDATE #temp
SET prevval = (SELECT MAX(t2.ident)
FROM #temp t2
WHERE t2.ident < t.ident
AND t2.speed IS NOT NULL)
FROM #temp t
WHERE t.speed IS NULL

UPDATE #temp
SET nextval = (SELECT MIN(t2.ident)
FROM #temp t2
WHERE t2.ident > t.ident
AND t2.speed IS NOT NULL)
FROM #temp t
WHERE t.speed IS NULL

UPDATE t
SET speed = p.speed +
1E0 * (n.speed - p.speed) * (t.ident - t.prevval) /
(t.nextval - t.prevval)
FROM #temp t
JOIN #temp p ON t.prevval = p.ident
JOIN #temp n ON t.nextval = n.ident
WHERE t.speed IS NULL

UPDATE #temp
SET prevval = NULL, nextval = NULL

UPDATE #temp
SET prevval = (SELECT MAX(t2.ident)
FROM #temp t2
WHERE t2.ident < t.ident
AND t2.gross IS NOT NULL)
FROM #temp t
WHERE t.gross IS NULL

UPDATE #temp
SET nextval = (SELECT MIN(t2.ident)
FROM #temp t2
WHERE t2.ident > t.ident
AND t2.gross IS NOT NULL)
FROM #temp t
WHERE t.gross IS NULL

UPDATE t
SET gross = p.gross +
1E0 * (n.gross - p.gross) * (t.ident - t.prevval) /
(t.nextval - t.prevval)
FROM #temp t
JOIN #temp p ON t.prevval = p.ident
JOIN #temp n ON t.nextval = n.ident
WHERE t.gross IS NULL

UPDATE #temp
SET prevval = NULL, nextval = NULL

go
UPDATE tbl
SET speed = t.speed,
gross = t.gross,
net = t.net
FROM tbl
JOIN #temp t ON tbl.entrytime = t.entrytime
go
SELECT * FROM #temp
SELECT * FROM tbl ORDER BY entrytime
go
DROP TABLE tbl
DROP TABLE #temp

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog (esquel@.sommarskog.se) writes:
> UPDATE t
> SET speed = p.speed +
> 1E0 * (n.speed - p.speed) * (t.ident - t.prevval) /
> (t.nextval - t.prevval)
> FROM #temp t
> JOIN #temp p ON t.prevval = p.ident
> JOIN #temp n ON t.nextval = n.ident
> WHERE t.speed IS NULL

So I did not consider time. This might be better:

UPDATE t
SET speed = p.speed +
1E0 * (n.speed - p.speed) *
datediff(ms, p.entrytime, t.entrytime) /
datediff(ms, p.entrytime, n.entrytime)
FROM #temp t
JOIN #temp p ON t.prevval = p.ident
JOIN #temp n ON t.nextval = n.ident
WHERE t.speed IS NULL

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I have settled on the following for now:
1. First, if the final value is a null, set it to the maximum value
for that field.
This is necessary so that the intervening values can be calculated.
This update does that:
update t1
set Gross = t0.gross
from tblProfiletemp t1, tblprofiletemp t0
where t1.gross is null
and t1.entrytime = (select max(entrytime) from tblprofiletemp)
and t0.gross = (select max(gross) from tblprofiletemp)

Once that is done, then the following update statement fills in all
intervening values with the correct value:
Update t1
set Gross=t0.Gross + ((t2.Gross - t0.Gross) *
((t1.runtime-t0.runtime)/(t2.runtime-t0.runtime)))
from tblProfiletemp t0 ,tblProfiletemp t1,tblProfiletemp t2
where t1.gross is null
and t0.entrytime = (select Max(EntryTime) from tblProfiletemp where
item < t1.item and gross is not null)
and t2.EntryTime = (select Min(EntryTime) from tblProfiletemp where
item > t1.item and gross is not null)

The reason I could not get the update statement to work before is that
I was erroneously creating self joins.

The above will work properly on the columns where the data always
increases. It may need some modification for the speed columns.

Thanks for all the input.
BM|||Don't rely on the IDENTITY column to drive the sequence. IDENTITY is
only supposed to be an arbitrary key. EntryTime should be a better way
to do it:

UPDATE tblProfileTemp
SET gross =
(SELECT T0.gross +
((T2.gross - T0.gross) *
((tblProfileTemp.runtime-T0.runtime)/(T2.runtime-T0.runtime)))
FROM tblProfileTemp AS T0,
tblProfileTemp AS T2
WHERE T0.entrytime =
(SELECT MAX(entrytime)
FROM tblProfileTemp AS T
WHERE entrytime < tblProfileTemp.entrytime
AND gross IS NOT NULL)
AND T2.entrytime =
(SELECT MIN(entrytime)
FROM tblProfileTemp AS T
WHERE entrytime > tblProfileTemp.entrytime
AND gross IS NOT NULL))
WHERE gross IS NULL

Thanks for posting the DDL and sample. It helped.

--
David Portas
SQL Server MVP
--|||For the speed table, I took a different approach.
Once the values for gross copy count have been inserted, then the speed
can be calculated backwards as
Speed = (Gross - Gross0)/(RunTime - RunTime0).

I changed the runtime so it is recorded in seconds, rather than
minutes, and round the speed value to the neares 100.
These two changes give a smoother graph.

Where a speed needs to be calculated from two gross values recorded
very short times apart, then the speed sometimes appears anomalously
high or low. (usually high).

The results of this are now in testing, and I'll see what feedback from
the users is before making further changes.

Thanks tremendously to all who helped.

can an INSERT statement RETURN a value?

I only know a little bit of SQL for Access databases, so sorry if this is a silly question!

Situation: I've to INSERT a new company in a SQL server table named 'companies'. This new company will automatically receive a unique ID (autonumber in Access terminology, don't know how to call it in SQL server)

Question: Can this insert statement return the ID it gave to the company? Or how do I get this ID to use it in an other table?

Thanks in advance!create proc <blah-blah> (@.blah1 varchar(blah), @.blah2 varchar(blah-blah) )
as
declare @.RetVal int, @.Error int
begin tran
insert <blah> (blah1, blah2) values (@.blah1, @.blah2)
select @.Error = @.@.error, @.RetVal = scope_identity()
if @.Error != 0 begin
raiserror ('failed to insert into blah', 16, 1)
rollback tran
return 1
end
commit tran
select NewIdentityValue = @.RetVal
return 0

OR, you can define @.RetVal as output parameter, this way you won't have to do a final SELECT. It's all up to your taste and preference.|||...in other words, NO, it can't, but you can put your Insert statement in a procedure that will return a value or an Output parameter.

blindman|||it actually appears that the answer is YES. explanation blindman?|||We're splitting hairs... yes you can get the ID... from the SELECT statement, no... from a stored procedure... yes...

ClipChips asked if the statement could return the ID. The statement itself cannot. But if you use a SP, you can retrieve it either as a SELECT to a recordset, or an OUTPUT parameter.|||It sounds like the answer is really our favorite "yes and no". Yes, @.@.error acts as a sort of return value, but in the strict definition of a return value, you can not have
exec @.retvalue = "insert into table values (...)" Does that explain it better?|||Yes, No?

You can get Id back without stored procedure!

create table test(id int identity primary key
,code varchar(10))
go
create trigger ins_test on test
for insert
as
select id from inserted
go
insert test values('A')
go
id
----
1

Just get recordset from command object...|||True, but you need a trigger instead.. :)... 6 and half a dozen.. take your pick :)|||Originally posted by Seppuku
True, but you need a trigger instead.. :)... 6 and half a dozen.. take your pick :)

But it is possible... ;)|||you can do it even without a trigger, but from a command object.

create table test1 (f1 int identity(1,1) not null, f2 char(1) not null)
go

Just assign the following command to it:
insert test1 (f2) values ('A') select RetVal=@.@.identity -- or scope_identity() for sql2k

Sunday, February 19, 2012

Can a stored procedure be executed from within a select statement?

Can a stored procedure be executed from within a select statement?

Given a store procedure named: sp_proc

I wish to do something like this:

For each row in the table
execute sp_proc 'parameter1', parameter2'...
end for
...but within a select statement. I know you can do this with stored functions, just not sure what the syntax is for a stored procedure.No, not within a select statment. Well, maybe with OPENQUERY, but even if that did work I would never use it.|||So convert it into a function...

Is there a question here?

Thursday, February 16, 2012

Can a DROP TABLE statement be rolled back?

I'm interested in finding out if its possible to rollback a DROP TABLE
statement if it is inside a transaction that fails.
For some reason, I dont think it is but would like have someone confirm.
Thanks!
JohnnyNevermind my question. The answer is YES, it can be rolled back provided the
transaction is not comitted. My bad.
"Johnny" wrote:

> I'm interested in finding out if its possible to rollback a DROP TABLE
> statement if it is inside a transaction that fails.
> For some reason, I dont think it is but would like have someone confirm.
> Thanks!
> Johnny|||HI,Johnny,
U cannot rollback the drop table or delete command except if u execute
it if u use transaction number or savepoint.
As u execute the drop table a checkpoint occurs and the transaction is
commited by default.
U can aslo recover it if u have backup.
for my information on rollback
read books on line
hope this helps u
from
killer|||What you are saying is not true with Explicit transactions, which is the typ
e
of transaction I am referring to.
You can rollback a transaction (using BEGIN TRANSACTION & explicitly ended
with a COMMIT or ROLLBACK statement) as long as the transaction is not
committed. That is the whole point of transactions. Deletes, inserts, etc.
can all be rolled back provided the transaction has not been comitted.
Read the "BEGIN TRANSACTION (Transact-SQL) " topic in books online to learn
more.
"doller" wrote:

> HI,Johnny,
> U cannot rollback the drop table or delete command except if u execute
> it if u use transaction number or savepoint.
> As u execute the drop table a checkpoint occurs and the transaction is
> commited by default.
> U can aslo recover it if u have backup.
> for my information on rollback
> read books on line
> hope this helps u
> from
> killer
>|||You are right Johnny
The drop command is allowed inside a transaction only if the ddl in tran
option to sp_dboption is set to true
To set ddl in tran to true, enter:
sp_dboption database_name,"ddl in tran", true
you can found more information about that reviewing the next url:
*http://manuals.sybase.com/onlineboo...r />
iew/53001
regards
"Johnny" wrote:
[vbcol=seagreen]
> What you are saying is not true with Explicit transactions, which is the t
ype
> of transaction I am referring to.
> You can rollback a transaction (using BEGIN TRANSACTION & explicitly ended
> with a COMMIT or ROLLBACK statement) as long as the transaction is not
> committed. That is the whole point of transactions. Deletes, inserts, etc.
> can all be rolled back provided the transaction has not been comitted.
> Read the "BEGIN TRANSACTION (Transact-SQL) " topic in books online to lear
n
> more.
> "doller" wrote:
>|||There is no 'ddl in tran' database option in Microsoft SQL Server. Since
Johnny posted his question to a Microsoft SQL Server forum, chances are that
he is using MSSQL instead of Sybase.
DDL is always allowed within a transaction in Microsoft SQL Server. An
explicit or implicit transaction must be started in order to issue a COMMIT
or ROLLBACK. DDL can't be explicitly rolled back in autocommit mode.
Autocommit, explicit and implicit transactions are described in the Bools
Online <tsqlref.chm::/ts_ta-tz_2x2y.htm>.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Hernandez" <DanHernandez@.discussions.microsoft.com> wrote in message
news:DAD308E2-D898-4C40-B811-AAE8C8CA74F1@.microsoft.com...[vbcol=seagreen]
> You are right Johnny
> The drop command is allowed inside a transaction only if the ddl in tran
> option to sp_dboption is set to true
>
> To set ddl in tran to true, enter:
> sp_dboption database_name,"ddl in tran", true
> you can found more information about that reviewing the next url:
> *http://manuals.sybase.com/onlineboo.../>
tView/53001
>
> regards
>
> "Johnny" wrote:
>

Can a DROP TABLE statement be rolled back?

I'm interested in finding out if its possible to rollback a DROP TABLE
statement if it is inside a transaction that fails.
For some reason, I dont think it is but would like have someone confirm.
Thanks!
Johnny
Nevermind my question. The answer is YES, it can be rolled back provided the
transaction is not comitted. My bad.
"Johnny" wrote:

> I'm interested in finding out if its possible to rollback a DROP TABLE
> statement if it is inside a transaction that fails.
> For some reason, I dont think it is but would like have someone confirm.
> Thanks!
> Johnny
|||HI,Johnny,
U cannot rollback the drop table or delete command except if u execute
it if u use transaction number or savepoint.
As u execute the drop table a checkpoint occurs and the transaction is
commited by default.
U can aslo recover it if u have backup.
for my information on rollback
read books on line
hope this helps u
from
killer
|||What you are saying is not true with Explicit transactions, which is the type
of transaction I am referring to.
You can rollback a transaction (using BEGIN TRANSACTION & explicitly ended
with a COMMIT or ROLLBACK statement) as long as the transaction is not
committed. That is the whole point of transactions. Deletes, inserts, etc.
can all be rolled back provided the transaction has not been comitted.
Read the "BEGIN TRANSACTION (Transact-SQL) " topic in books online to learn
more.
"doller" wrote:

> HI,Johnny,
> U cannot rollback the drop table or delete command except if u execute
> it if u use transaction number or savepoint.
> As u execute the drop table a checkpoint occurs and the transaction is
> commited by default.
> U can aslo recover it if u have backup.
> for my information on rollback
> read books on line
> hope this helps u
> from
> killer
>
|||You are right Johnny
The drop command is allowed inside a transaction only if the ddl in tran
option to sp_dboption is set to true
To set ddl in tran to true, enter:
sp_dboption database_name,"ddl in tran", true
you can found more information about that reviewing the next url:
*http://manuals.sybase.com/onlinebook...TextView/53001
regards
"Johnny" wrote:
[vbcol=seagreen]
> What you are saying is not true with Explicit transactions, which is the type
> of transaction I am referring to.
> You can rollback a transaction (using BEGIN TRANSACTION & explicitly ended
> with a COMMIT or ROLLBACK statement) as long as the transaction is not
> committed. That is the whole point of transactions. Deletes, inserts, etc.
> can all be rolled back provided the transaction has not been comitted.
> Read the "BEGIN TRANSACTION (Transact-SQL) " topic in books online to learn
> more.
> "doller" wrote:
|||There is no 'ddl in tran' database option in Microsoft SQL Server. Since
Johnny posted his question to a Microsoft SQL Server forum, chances are that
he is using MSSQL instead of Sybase.
DDL is always allowed within a transaction in Microsoft SQL Server. An
explicit or implicit transaction must be started in order to issue a COMMIT
or ROLLBACK. DDL can't be explicitly rolled back in autocommit mode.
Autocommit, explicit and implicit transactions are described in the Bools
Online <tsqlref.chm::/ts_ta-tz_2x2y.htm>.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Hernandez" <DanHernandez@.discussions.microsoft.com> wrote in message
news:DAD308E2-D898-4C40-B811-AAE8C8CA74F1@.microsoft.com...[vbcol=seagreen]
> You are right Johnny
> The drop command is allowed inside a transaction only if the ddl in tran
> option to sp_dboption is set to true
>
> To set ddl in tran to true, enter:
> sp_dboption database_name,"ddl in tran", true
> you can found more information about that reviewing the next url:
> *http://manuals.sybase.com/onlinebook...TextView/53001
>
> regards
>
> "Johnny" wrote:

Sunday, March 25, 2012

Can I do this in one SQL statement

Thursday, March 22, 2012

Can I create a Top n statement within a stored procedure using a parameter?

Tuesday, March 20, 2012

Can I call stored procedure inside the case statement

Monday, March 19, 2012

Can i add SQL statement to Parameter Fields?

Sunday, March 11, 2012

Can FK be nullable/optional by design?

Wednesday, March 7, 2012

Can CASE match more than once ?

Saturday, February 25, 2012

can anyone help with this update statement?

Friday, February 24, 2012

can any body tell me what wrong in my sql statement

Can an update statement be used for interpolating missing data?

can an INSERT statement RETURN a value?

Sunday, February 19, 2012

Can a stored procedure be executed from within a select statement?

Thursday, February 16, 2012

Can a DROP TABLE statement be rolled back?

Can a DROP TABLE statement be rolled back?

Calling Webservice

Blog Archive

About Me