Consolidation Query: syntax

Showing posts with label syntax. Show all posts

Thursday, March 29, 2012

Convert Access 2003 Function

I am converting an Access DB to SQL2000 and opne of the queries uses the
Format$ function. How should the syntax be for SQL?
Access: Format$(TimeTrackerEntry.EntryDate,'yyyymm') AS EntryMonthUse the CONVERT function with an optional date style argument. See the
following for syntax, samples and date arguments.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp
HTH
Jerry
"dj5md" <dj5md@.discussions.microsoft.com> wrote in message
news:073E808D-100D-4F1F-9842-4BEB4DF3F112@.microsoft.com...
>I am converting an Access DB to SQL2000 and opne of the queries uses the
> Format$ function. How should the syntax be for SQL?
> Access: Format$(TimeTrackerEntry.EntryDate,'yyyymm') AS EntryMonth|||Thanks Jerry, I already looked at Cast and Convert but cannot figure out the
correct syntax. It's a bit confusing for me.
"Jerry Spivey" wrote:
> Use the CONVERT function with an optional date style argument. See the
> following for syntax, samples and date arguments.
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp
> HTH
> Jerry
> "dj5md" <dj5md@.discussions.microsoft.com> wrote in message
> news:073E808D-100D-4F1F-9842-4BEB4DF3F112@.microsoft.com...
> >I am converting an Access DB to SQL2000 and opne of the queries uses the
> > Format$ function. How should the syntax be for SQL?
> >
> > Access: Format$(TimeTrackerEntry.EntryDate,'yyyymm') AS EntryMonth
>
>|||Try this:
SELECT LEFT(CONVERT(VARCHAR(25),GETDATE(),112),6)
HTH
Jerry
"dj5md" <dj5md@.discussions.microsoft.com> wrote in message
news:8212D4FC-A530-47DC-9E07-934516904C6D@.microsoft.com...
> Thanks Jerry, I already looked at Cast and Convert but cannot figure out
> the
> correct syntax. It's a bit confusing for me.
> "Jerry Spivey" wrote:
>> Use the CONVERT function with an optional date style argument. See the
>> following for syntax, samples and date arguments.
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp
>> HTH
>> Jerry
>> "dj5md" <dj5md@.discussions.microsoft.com> wrote in message
>> news:073E808D-100D-4F1F-9842-4BEB4DF3F112@.microsoft.com...
>> >I am converting an Access DB to SQL2000 and opne of the queries uses the
>> > Format$ function. How should the syntax be for SQL?
>> >
>> > Access: Format$(TimeTrackerEntry.EntryDate,'yyyymm') AS EntryMonth
>>|||Thats was it. I changed the GetDate with my DB field and removed the Select
and it worked with no problems. Thanks much Jerry. I also understand the
function better now that I have the correct syntax.
"Jerry Spivey" wrote:
> Try this:
> SELECT LEFT(CONVERT(VARCHAR(25),GETDATE(),112),6)
> HTH
> Jerry
> "dj5md" <dj5md@.discussions.microsoft.com> wrote in message
> news:8212D4FC-A530-47DC-9E07-934516904C6D@.microsoft.com...
> > Thanks Jerry, I already looked at Cast and Convert but cannot figure out
> > the
> > correct syntax. It's a bit confusing for me.
> >
> > "Jerry Spivey" wrote:
> >
> >> Use the CONVERT function with an optional date style argument. See the
> >> following for syntax, samples and date arguments.
> >>
> >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp
> >>
> >> HTH
> >>
> >> Jerry
> >> "dj5md" <dj5md@.discussions.microsoft.com> wrote in message
> >> news:073E808D-100D-4F1F-9842-4BEB4DF3F112@.microsoft.com...
> >> >I am converting an Access DB to SQL2000 and opne of the queries uses the
> >> > Format$ function. How should the syntax be for SQL?
> >> >
> >> > Access: Format$(TimeTrackerEntry.EntryDate,'yyyymm') AS EntryMonth
> >>
> >>
> >>
>
>

Tuesday, March 27, 2012

convert

Hi,
I need to change the format of a date column in one of the tables to reflect
a date that looks like mm/dd/yyyy. I used the following syntax that I know
it's wrong -it works in a select statement-:
alter table tbl_Reservation
convert(char(20),[from date],101) as [From Date]
Is there anyway to change the format on the table itself?
TSWhat datatype do you wish to have for that column?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TS" <TS@.discussions.microsoft.com> wrote in message
news:CB5714C6-AC38-4C2B-B5F8-396CE43DAAA1@.microsoft.com...
> Hi,
> I need to change the format of a date column in one of the tables to refle
ct
> a date that looks like mm/dd/yyyy. I used the following syntax that I know
> it's wrong -it works in a select statement-:
> alter table tbl_Reservation
> convert(char(20),[from date],101) as [From Date]
> Is there anyway to change the format on the table itself?
>
> --
> TS|||I just need to change the format of that column from for example 2005-05-16
00:00:00 to a format of mm/dd/yyyy
TS
"Tibor Karaszi" wrote:

> What datatype do you wish to have for that column?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "TS" <TS@.discussions.microsoft.com> wrote in message
> news:CB5714C6-AC38-4C2B-B5F8-396CE43DAAA1@.microsoft.com...
>|||Tibor asked the data type of the field because if it's a datetime then
there's no way to "change the format" -- datetimes aren't stored in a
particular format, that's a display issue. If it's stored as a character typ
e
(which would probably be less than optimal), we would probably be able to
recommend some options.
"TS" wrote:
> I just need to change the format of that column from for example 2005-05-1
6
> 00:00:00 to a format of mm/dd/yyyy
> --
> TS
>
> "Tibor Karaszi" wrote:
>|||> Tibor asked the data type of the field because if it's a datetime then
> there's no way to "change the format"
Exactly. :-)
For more information, TS, I suggest you check out
http://www.karaszi.com/SQLServer/info_datetime.asp.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"KH" <KH@.discussions.microsoft.com> wrote in message
news:E84225DE-A465-40C2-A343-246FF6551D42@.microsoft.com...
> Tibor asked the data type of the field because if it's a datetime then
> there's no way to "change the format" -- datetimes aren't stored in a
> particular format, that's a display issue. If it's stored as a character t
ype
> (which would probably be less than optimal), we would probably be able to
> recommend some options.
>
> "TS" wrote:
>|||Yes, the data type of the field is datetime. In the front-end, it appears in
the format mm/dd/yyyy which is the way I want it even though in the query I
ran from SQL it appears in the datetime format that there is no way to
change. I didn't know this is the case!!
Thanks a lot for your help.
--
TS
"KH" wrote:
> Tibor asked the data type of the field because if it's a datetime then
> there's no way to "change the format" -- datetimes aren't stored in a
> particular format, that's a display issue. If it's stored as a character t
ype
> (which would probably be less than optimal), we would probably be able to
> recommend some options.
>
> "TS" wrote:
>|||Hi,
You cant change the storage format if you use datetime data type. Only way
is during extraction you could use CONVERT function to format the date.
Alternatevely you could use the VARCHAR datatype to store the date format as
you need. Wile inserting you could format the field with CONVERT function.
But I recommend you to use datetime data type and while extraction you can
format it using CONVERT function.
Thanks
Hari
SQL Server MVP
"TS" <TS@.discussions.microsoft.com> wrote in message
news:FDF45283-3516-46A5-92FA-8AE1EF27AAE1@.microsoft.com...
> Yes, the data type of the field is datetime. In the front-end, it appears
> in
> the format mm/dd/yyyy which is the way I want it even though in the query
> I
> ran from SQL it appears in the datetime format that there is no way to
> change. I didn't know this is the case!!
> Thanks a lot for your help.
> --
> TS
>
> "KH" wrote:
>

Thursday, March 8, 2012

Contraints

Hi,
I have this (syntax not verified):
CREATE TABLE Colors (
Id INT PRIMARY KEY,
Color VARCHAR(20) NOT NULL
)
GO
INSERT INTO Colors VALUES (1, 'red')
GO
INSERT INTO Colors VALUES (2, 'green')
GO
INSERT INTO Colors VALUES (3, 'blue')
GO
CREATE TABLE Candy (
Id INT PRIMARY KEY,
Name VARCHAR(20) NOT NULL,
ColorId INT REFERENCES Colors(Id)
)
GO
INSERT INTO Candy VALUES (1, 'VanillaRocks', 1)
GO
INSERT INTO Candy VALUES (2, 'VanillaRocks', 3)
GO
INSERT INTO Candy VALUES (3, 'SweetMama', 2)
GO
CREATE TABLE CandyPacks (
PackId INT NOT NULL,
CandyId INT REFERENCES Candy(Id)
)
Now, you can put all kinds of candy in a pack:
INSERT INTO CandyPacks VALUES(1, 1)
GO
INSERT INTO CandyPacks VALUES(1, 1)
GO
INSERT INTO CandyPacks VALUES(1, 3)
GO
This puts 2 red colored VanillaRocks and 1 green colored SweetMama in bag 1.
Now, I want to make sure that no bag contains more than 2 candies of the
same kind and same color.
So the following should return ZERO records:
SELECT * FROM
CandyPacks P1, Candy C1, Colors CLR1,
CandyPacks P2, Candy C2, Colors CLR2
WHERE
C1.Id = P1.CandyId
AND CLR1.Id = C1.ColorId
AND C2.Id = P2.CandyId
AND CLR2.Id = C2.ColorId
AND CLR1.Id = CLR2.Id -- SHOULD NOT HAPPEN!
Now, perhaps this query sucks.. please correct, but my point should be
clear.
This is only an example for similar situations. Perhaps I could use primary
key constraints or something, but in other situations I have some 'complex
query' which should not yield results..
How can I turn a query like the above into a constraint on the CandyPacks
table?
LisaHi Lisa
Please consider using INSTEAD OF INSERT Trigger on the Table.
If the condition is satisified, insert the data
hope the problem is solved? If there are any more issues, do not hesitate to
revert back
thanks and regards
Chandra
"Lisa Pearlson" wrote:

> Hi,
> I have this (syntax not verified):
> CREATE TABLE Colors (
> Id INT PRIMARY KEY,
> Color VARCHAR(20) NOT NULL
> )
> GO
> INSERT INTO Colors VALUES (1, 'red')
> GO
> INSERT INTO Colors VALUES (2, 'green')
> GO
> INSERT INTO Colors VALUES (3, 'blue')
> GO
>
> CREATE TABLE Candy (
> Id INT PRIMARY KEY,
> Name VARCHAR(20) NOT NULL,
> ColorId INT REFERENCES Colors(Id)
> )
> GO
> INSERT INTO Candy VALUES (1, 'VanillaRocks', 1)
> GO
> INSERT INTO Candy VALUES (2, 'VanillaRocks', 3)
> GO
> INSERT INTO Candy VALUES (3, 'SweetMama', 2)
> GO
> CREATE TABLE CandyPacks (
> PackId INT NOT NULL,
> CandyId INT REFERENCES Candy(Id)
> )
> Now, you can put all kinds of candy in a pack:
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 3)
> GO
>
> This puts 2 red colored VanillaRocks and 1 green colored SweetMama in bag
1.
> Now, I want to make sure that no bag contains more than 2 candies of the
> same kind and same color.
> So the following should return ZERO records:
> SELECT * FROM
> CandyPacks P1, Candy C1, Colors CLR1,
> CandyPacks P2, Candy C2, Colors CLR2
> WHERE
> C1.Id = P1.CandyId
> AND CLR1.Id = C1.ColorId
> AND C2.Id = P2.CandyId
> AND CLR2.Id = C2.ColorId
> AND CLR1.Id = CLR2.Id -- SHOULD NOT HAPPEN!
> Now, perhaps this query sucks.. please correct, but my point should be
> clear.
> This is only an example for similar situations. Perhaps I could use primar
y
> key constraints or something, but in other situations I have some 'complex
> query' which should not yield results..
> How can I turn a query like the above into a constraint on the CandyPacks
> table?
> Lisa
>
>|||I would think that the best way to accomplish this would be using a trigger.
I would also consider changing the structure of the tables such that a
CandyPacks record contians the packid, a colorid, and a candyid. You may
find it easier to enforce the constraint with the data structured this way.
If you leave it as you have it you will have to write a more complex query i
n
the trigger.
"Chandra" wrote:
> Hi Lisa
> Please consider using INSTEAD OF INSERT Trigger on the Table.
> If the condition is satisified, insert the data
> hope the problem is solved? If there are any more issues, do not hesitate
to
> revert back
> thanks and regards
> Chandra
>
> "Lisa Pearlson" wrote:
>|||Try,
CREATE TABLE CandyPacks (
PackId INT NOT NULL,
CandyId INT not null REFERENCES Candy(Id),
quantity int not null default(1) check(quantity = 1 or quantity = 2),
constraint pk_CandyPacks primary key (PackId, CandyId)
)
AMB
"Lisa Pearlson" wrote:

> Hi,
> I have this (syntax not verified):
> CREATE TABLE Colors (
> Id INT PRIMARY KEY,
> Color VARCHAR(20) NOT NULL
> )
> GO
> INSERT INTO Colors VALUES (1, 'red')
> GO
> INSERT INTO Colors VALUES (2, 'green')
> GO
> INSERT INTO Colors VALUES (3, 'blue')
> GO
>
> CREATE TABLE Candy (
> Id INT PRIMARY KEY,
> Name VARCHAR(20) NOT NULL,
> ColorId INT REFERENCES Colors(Id)
> )
> GO
> INSERT INTO Candy VALUES (1, 'VanillaRocks', 1)
> GO
> INSERT INTO Candy VALUES (2, 'VanillaRocks', 3)
> GO
> INSERT INTO Candy VALUES (3, 'SweetMama', 2)
> GO
> CREATE TABLE CandyPacks (
> PackId INT NOT NULL,
> CandyId INT REFERENCES Candy(Id)
> )
> Now, you can put all kinds of candy in a pack:
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 3)
> GO
>
> This puts 2 red colored VanillaRocks and 1 green colored SweetMama in bag
1.
> Now, I want to make sure that no bag contains more than 2 candies of the
> same kind and same color.
> So the following should return ZERO records:
> SELECT * FROM
> CandyPacks P1, Candy C1, Colors CLR1,
> CandyPacks P2, Candy C2, Colors CLR2
> WHERE
> C1.Id = P1.CandyId
> AND CLR1.Id = C1.ColorId
> AND C2.Id = P2.CandyId
> AND CLR2.Id = C2.ColorId
> AND CLR1.Id = CLR2.Id -- SHOULD NOT HAPPEN!
> Now, perhaps this query sucks.. please correct, but my point should be
> clear.
> This is only an example for similar situations. Perhaps I could use primar
y
> key constraints or something, but in other situations I have some 'complex
> query' which should not yield results..
> How can I turn a query like the above into a constraint on the CandyPacks
> table?
> Lisa
>
>|||You could use a check constraint to see wheter how many candy are in the
package:
CHECK(Your Select statement)
Further help, just raise a hand.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Lisa Pearlson" <no@.spam.plz> schrieb im Newsbeitrag
news:%23aTIuu%23SFHA.2996@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have this (syntax not verified):
> CREATE TABLE Colors (
> Id INT PRIMARY KEY,
> Color VARCHAR(20) NOT NULL
> )
> GO
> INSERT INTO Colors VALUES (1, 'red')
> GO
> INSERT INTO Colors VALUES (2, 'green')
> GO
> INSERT INTO Colors VALUES (3, 'blue')
> GO
>
> CREATE TABLE Candy (
> Id INT PRIMARY KEY,
> Name VARCHAR(20) NOT NULL,
> ColorId INT REFERENCES Colors(Id)
> )
> GO
> INSERT INTO Candy VALUES (1, 'VanillaRocks', 1)
> GO
> INSERT INTO Candy VALUES (2, 'VanillaRocks', 3)
> GO
> INSERT INTO Candy VALUES (3, 'SweetMama', 2)
> GO
> CREATE TABLE CandyPacks (
> PackId INT NOT NULL,
> CandyId INT REFERENCES Candy(Id)
> )
> Now, you can put all kinds of candy in a pack:
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 3)
> GO
>
> This puts 2 red colored VanillaRocks and 1 green colored SweetMama in bag
> 1.
> Now, I want to make sure that no bag contains more than 2 candies of the
> same kind and same color.
> So the following should return ZERO records:
> SELECT * FROM
> CandyPacks P1, Candy C1, Colors CLR1,
> CandyPacks P2, Candy C2, Colors CLR2
> WHERE
> C1.Id = P1.CandyId
> AND CLR1.Id = C1.ColorId
> AND C2.Id = P2.CandyId
> AND CLR2.Id = C2.ColorId
> AND CLR1.Id = CLR2.Id -- SHOULD NOT HAPPEN!
> Now, perhaps this query sucks.. please correct, but my point should be
> clear.
> This is only an example for similar situations. Perhaps I could use
> primary key constraints or something, but in other situations I have some
> 'complex query' which should not yield results..
> How can I turn a query like the above into a constraint on the CandyPacks
> table?
> Lisa
>|||*raises hand*
My actual table is:
CREATE TABLE MatchResults
(
ParentId INT NOT NULL REFERENCES Bedrijven(Id),
DatMatch DATETIME NOT NULL,
Id INT NOT NULL REFERENCES Bedrijven(Id),
PRIMARY KEY(ParentId,DatMatch,Id),
Updated DATETIME DEFAULT GETDATE(),
Deleted BIT DEFAULT 0
)
And no data may be added to this table that would return any records in this
query:
SELECT COUNT(*)
FROM MatchResults M, Bedrijven B1, Bedrijven B2
WHERE M.Deleted!=1
AND B1.Id = M.ParentId
AND B2.Id = M.Id
AND (B1.ProfielId!=3 OR B2.ProfielId=3)
So the above query should always yield 0.
So I want to add a contraint to the above table so that the query below
always is 0.
I don't want to use INSTEAD OF INSERT trigger...
How do I turn it into a table constraint?
Thanks,
Lisa
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:ePt3i5%23SFHA.3980@.TK2MSFTNGP12.phx.gbl...
> You could use a check constraint to see wheter how many candy are in the
> package:
> CHECK(Your Select statement)
> Further help, just raise a hand.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Lisa Pearlson" <no@.spam.plz> schrieb im Newsbeitrag
> news:%23aTIuu%23SFHA.2996@.TK2MSFTNGP15.phx.gbl...
>|||On Wed, 4 May 2005 02:50:10 +0200, Lisa Pearlson wrote:

>*raises hand*
>My actual table is:
>CREATE TABLE MatchResults
>(
>ParentId INT NOT NULL REFERENCES Bedrijven(Id),
>DatMatch DATETIME NOT NULL,
>Id INT NOT NULL REFERENCES Bedrijven(Id),
> PRIMARY KEY(ParentId,DatMatch,Id),
>Updated DATETIME DEFAULT GETDATE(),
>Deleted BIT DEFAULT 0
> )
>
>And no data may be added to this table that would return any records in thi
s
>query:
>SELECT COUNT(*)
>FROM MatchResults M, Bedrijven B1, Bedrijven B2
>WHERE M.Deleted!=1
>AND B1.Id = M.ParentId
>AND B2.Id = M.Id
>AND (B1.ProfielId!=3 OR B2.ProfielId=3)
>So the above query should always yield 0.
>So I want to add a contraint to the above table so that the query below
>always is 0.
>I don't want to use INSTEAD OF INSERT trigger...
>How do I turn it into a table constraint?
>Thanks,
>Lisa
Hi Lisa,
Impossible with your current design, since a CHECK constraint can't use
any other data than the data in the same row.
The workaround is to replace the Bedrijven table with two tables: one
for the bedrijven with profiel equal to 3 and one for the bedrijven with
profiel unequal to 3:
CREATE TABLE BedrijvenType3
(Id INT NOT NULL,
ProfielID INT NOT NULL,
... other columns,
Deleted BIT NOT NULL DEFAULT 0,
PRIMARY KEY (Id),
CHECK (ProfielId = 3)
)
CREATE TABLE BedrijvenOverig
(Id INT NOT NULL,
ProfielID INT NOT NULL,
... other columns,
Deleted BIT NOT NULL DEFAULT 0,
PRIMARY KEY (Id),
CHECK (ProfielId <> 3)
)
CREATE TABLE MatchResults
(ParentId INT NOT NULL REFERENCES BedrijvenType3(Id),
DatMatch DATETIME NOT NULL,
Id INT NOT NULL REFERENCES BedrijvenOverig(Id),
PRIMARY KEY (ParentId, DatMatch, Id),
Updated DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
Deleted BIT NOT NULL DEFAULT 0
)
Note that I also included a Deleted column in the two bedrijven tables,
because the referential integrity can't be made dependant of the Deleted
column in the MatchResults. If you do want to really remove rows from
the bedrijven tables (or if I somehow misunderstood your requirements),
you'll have to use a different technique: make a redundant copy of the
ProfielId for both Id and ParentId (and possibly add a redundant UNIQUE
constraint to the Bedrijven table, so that the data stays synch'ed):
ALTER TABLE Bedrijven
ADD UNIQUE (Id, ProfielId)
CREATE TABLE MatchResults
(ParentId INT NOT NULL,
ParentIdProfiel INT NOT NULL,
FOREIGN KEY (ParentId, ParentIdProfiel)
REFERENCES Bedrijven (Id, ProfielId),
DatMatch DATETIME NOT NULL,
Id INT NOT NULL,
IdProfiel INT NOT NULL,
FOREIGN KEY (Id, IdProfiel)
REFERENCES Bedrijven (Id, ProfielId),
PRIMARY KEY (ParentId, DatMatch, Id),
Updated DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
Deleted BIT NOT NULL DEFAULT 0,
CHECK (Deleted = 1 OR (ParentIdProfiel = 3 AND IdProfiel <> 3)
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Why do you believe that every table has magical, universal column
called "id"? In an RDBMS, there are lots of kinds of identifiers,
not like a 1950's file system record number. And have you ever
researched for industry standards, like the Land color number or
Pantone numbers? Let's clean up the sample DDL:
CREATE TABLE Colors
(pantone_nbr INTEGER PRIMARY KEY,
pantone_description VARCHAR(20) NOT NULL);
CREATE TABLE Candies
(upc CHAR(13) NOT NULL PRIMARY KEY,
candy_name VARCHAR(20) NOT NULL,
pantone_nbr INTEGER REFERENCES Colors(pantone_nbr));
CREATE TABLE CandyPacks
(pack_upc CHAR(13) NOT NULL,
candy_upc CHAR(13) NOT NULL
REFERENCES Candies(upc),
PRIMARY KEY (pack_upc, candy_upc)
);
same kind AND same color. <<
The first condition is enforced by a PRIMARY KEY. In full SQL-92 you
can put this into a CHECK();
CHECK(1 = ALL (SELECT COUNT(C1.pantone_nbr)
FROM Candies AS C1
WHERE C1.upc = CandyPacks.candyupc))
In SQL server, you will need to use a trigger on the packages table.

Contracts ?

Hi There

I have noticed that one cannot alter a contract, there is no alter contract syntax.

I understand the reason is because one can break applications by altering messages in contracts used to build apps.

However now it seems that everytime i create a new message type i have to create a new contract which in turn i have turn alter a service to use it. I cannot drop and re-create contracts because they are bound by services.

So basically what i am saying is that it is really a mission to add new messages types in a complex SB application (create new message+create new contract+alter service), is this just the way it is or what am i missing here ?

Thanx

Indeed, changing contracts once an application started using them will break the apps. Similar to how one cannot change a COM interface after a component has shipped.

While in development, is an OK approach to drop the contract (and service) and recreate it again with the new message types. But once you deployed the app, the proper way to add new message types is to create new contracts. Typically this is done by creating a new contract that has all the previous contract message types and in addition, the new message types.

HTH,
~ Remus

|||Thanx Remus , just wanted to be sure.

Wednesday, March 7, 2012

Continuation of long SQL statement syntax

Hi All -

I am updating four values. What is the proper syntax to have the
following 4 update statements as one statement?

set objRec = objDB.Execute("Update orientform set session = '" &
strSession & "' where id = '" & strid & "'")
set objRec = objDB.Execute("Update orientform set fname = '" & strfname
& "' where id = '" & strid & "'")
set objRec = objDB.Execute("Update orientform set gender = '" &
strgender & "' where id = '" & strid & "'")
set objRec = objDB.Execute("Update orientform set lname = '" & strlname
& "' where id = '" & strid & "'")

Thanks,

Joey"update orientform set sessions = '" & strSession & "', fname = '" &
strfname & '", gender = etc etc
where id = '" & strid & "'"

Notes I see you called your command objRec... maybe just habit but you
aren't creating a recordset earlier in the piece are you? Not needed for
updates/inserts/deletes. Also if your id (in the table) has an int dataype
then forget the single quotes around your strid

Jay

<joseph.jasinski@.quinnipiac.edu> wrote in message
news:1102635650.764000.267730@.z14g2000cwz.googlegr oups.com...
> Hi All -
> I am updating four values. What is the proper syntax to have the
> following 4 update statements as one statement?
> set objRec = objDB.Execute("Update orientform set session = '" &
> strSession & "' where id = '" & strid & "'")
> set objRec = objDB.Execute("Update orientform set fname = '" & strfname
> & "' where id = '" & strid & "'")
> set objRec = objDB.Execute("Update orientform set gender = '" &
> strgender & "' where id = '" & strid & "'")
> set objRec = objDB.Execute("Update orientform set lname = '" & strlname
> & "' where id = '" & strid & "'")
> Thanks,
> Joey

Tuesday, February 14, 2012

constraints

Hiya peops!!
Listen i was wondering if anyone could tell me whether they know the correct syntax for creating constraints or where i could find it on the net?
ThanksSearch BOL for ALTER TABLE|||Check out CREATE TABLE in BOL.

online at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp|||Or that...

ALTER TABLE
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp

Thursday, March 29, 2012

Convert Access 2003 Function

Convert Access 2003 Function

Convert Access 2003 Function

Tuesday, March 27, 2012

convert

Thursday, March 8, 2012

Contraints

Contracts ?

Wednesday, March 7, 2012

Continuation of long SQL statement syntax

Tuesday, February 14, 2012

constraints

Consolidation Query

Blog Archive

About Me