|
But the book says it's unreasonable.
|
|
|
|
|
The book should say it is incorrect or redundant and should recommend using a transaction. All books are not 100% correct all the time.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
Tara11 wrote: Why it would be unreasonable to declare an integrity constraint to that effect?
Mainly because integrity constraint operate on row-basis. Transactions then again ensure that the operation is completed as a whole even when multiple commands are affected.
But neither of these guarantee that the total sum is unaffected when money is transferred. If this would be a single table operation, a trigger could be utilized to do that check.
modified 2-Feb-20 15:32pm.
|
|
|
|
|
Hi
I have to migrate a database from Oracle to MS-SQL. The database migration went well, but i have a query that gives me trouble. I have tried to convert it using this on-line tool - http://www.sqlines.com/online, but it is not working since the Oracle alias seems to be a problem for MS-SQL.
Is there any way to use a similar syntax in MS-SQL?
The original Oracle SQL:
UPDATE folk.individ c
SET (c.stat,
c.statusdto,
c.pnrgaeld,
c.koen,
c.fornvnmrk,
c.mellemnvnmrk,
c.efternvnmrk,
c.nvndto,
c.fornvn_solo,
c.fornvn,
c.mellemnvn,
c.efternvn,
c.adr_fornvn,
c.adr_efternvn,
stilling,
z_stilling,
c.z_fornvn,
c.z_mellemnvn,
c.z_efternvn,
c.z_adr_fornvn,
c.z_adr_efternvn,
c.z_fornvn_solo,
c.haenstart_umrk_navne,
c.stillingdto,
c.mynkod_ctnavn,
c.myntxt_ctnavn,
c.indrap,
c.adrnvndto,
c.soegnvn,
c.soegnvndto
) =
(SELECT l.status,
TO_DATE(DECODE(SUBSTR(l.statushaenstart,7,2),'00',NULL,
SUBSTR(l.statushaenstart,1,8)),'YYYYMMDD'),
l.pnrgaeld,
l.koen,
a.fornvn_mrk,
a.melnvn_mrk,
a.efternvn_mrk,
TO_DATE(DECODE(SUBSTR(a.nvnhaenstart,7,2),'00',NULL,
SUBSTR(a.nvnhaenstart,1,8)),'YYYYMMDD'),
Upper(a.fornvn), -- c.fornvn_solo
substr(decode(a.melnvn, null, upper(a.fornvn), upper(a.fornvn)|| ' ' || upper(a.melnvn) ),1,50), -- skal være som før: fornavn indh. også mlnavne
upper(a.melnvn),
upper(a.efternvn),
upper(LTRIM(SUBSTR(a.adrnvn, INSTR(a.adrnvn,',')+1))), --adr_fornvn
upper(SUBSTR(a.adrnvn,1,INSTR(a.adrnvn,',') - 1)), --adr_efternavn
upper(l.stilling),
l.stilling,
substr(decode(a.melnvn, null, a.fornvn, a.fornvn || ' ' || a.melnvn),1,50), -- z_fornvn, fornavn indh. også mlnavne
a.melnvn, -- z_mellemnvn
a.efternvn,
LTRIM(SUBSTR(a.adrnvn, INSTR(a.adrnvn,',')+1)), --z_adr_fornvn
SUBSTR(a.adrnvn,1,INSTR(a.adrnvn,',') - 1), -- z_adr_eftermnvn
a.fornvn, -- z_fornvn_solo
a.haenstart_umrk_navne, -- anvendelse ukendt
null, --stillingdto udgået
null, --mynkod_ctnavn udgået
null, --myntxt_ctnavn udgået
null, --indrap udgået
null, -- adrnvndto udgået
null, --soegnvn udgået
null --soegnvndto udgået
FROM folk.lperson2010 l, folk.laktnvnopl2010 a
WHERE l.pnr = c.pnr
AND l.pnr = a.pnr
AND l.lseq BETWEEN 1 AND 6000 )
where c.pnr in (select l.pnr FROM folk.lperson2010 l
where l.lseq BETWEEN 1 AND 6000 );
The code after conversion parsing it through http://www.sqlines.com/online:
UPDATE folk.individ c
SET (c.stat,
c.statusdto,
c.pnrgaeld,
c.koen,
c.fornvnmrk,
c.mellemnvnmrk,
c.efternvnmrk,
c.nvndto,
c.fornvn_solo,
c.fornvn,
c.mellemnvn,
c.efternvn,
c.adr_fornvn,
c.adr_efternvn,
stilling,
z_stilling,
c.z_fornvn,
c.z_mellemnvn,
c.z_efternvn,
c.z_adr_fornvn,
c.z_adr_efternvn,
c.z_fornvn_solo,
c.haenstart_umrk_navne,
c.stillingdto,
c.mynkod_ctnavn,
c.myntxt_ctnavn,
c.indrap,
c.adrnvndto,
c.soegnvn,
c.soegnvndto
) =
(SELECT l.status,
CONVERT(DATETIME, CASE SUBSTRING(l.statushaenstart,7,2) WHEN '00' THEN NULL
ELSE SUBSTRING(l.statushaenstart,1,8) END,'YYYYMMDD'),
l.pnrgaeld,
l.koen,
a.fornvn_mrk,
a.melnvn_mrk,
a.efternvn_mrk,
CONVERT(DATETIME, CASE SUBSTRING(a.nvnhaenstart,7,2) WHEN '00' THEN NULL
ELSE SUBSTRING(a.nvnhaenstart,1,8) END,'YYYYMMDD'),
Upper(a.fornvn), -- c.fornvn_solo
substring(case when a.melnvn is null then upper(a.fornvn) isnull( else upper(a.fornvn), '')+ ' ' + isnull(upper(a.melnvn), '') end,1,50), -- skal være som før: fornavn indh. også mlnavne
upper(a.melnvn),
upper(a.efternvn),
upper(LTRIM(SUBSTRING(a.adrnvn, CHARINDEX(',', a.adrnvn)+1, LEN(a.adrnvn)))), --adr_fornvn
upper(SUBSTRING(a.adrnvn,1,CHARINDEX(',', a.adrnvn) - 1)), --adr_efternavn
upper(l.stilling),
l.stilling,
substring(case when a.melnvn is null then a.fornvn isnull( else a.fornvn, '') + ' ' + isnull(a.melnvn, '') end,1,50), -- z_fornvn, fornavn indh. også mlnavne
a.melnvn, -- z_mellemnvn
a.efternvn,
LTRIM(SUBSTRING(a.adrnvn, CHARINDEX(',', a.adrnvn)+1, LEN(a.adrnvn))), --z_adr_fornvn
SUBSTRING(a.adrnvn,1,CHARINDEX(',', a.adrnvn) - 1), -- z_adr_eftermnvn
a.fornvn, -- z_fornvn_solo
a.haenstart_umrk_navne, -- anvendelse ukendt
null, --stillingdto udgået
null, --mynkod_ctnavn udgået
null, --myntxt_ctnavn udgået
null, --indrap udgået
null, -- adrnvndto udgået
null, --soegnvn udgået
null --soegnvndto udgået
FROM folk.lperson2010 l, folk.laktnvnopl2010 a
WHERE l.pnr = c.pnr
AND l.pnr = a.pnr
AND l.lseq BETWEEN 1 AND 6000 )
where c.pnr in (select l.pnr FROM folk.lperson2010 l
where l.lseq BETWEEN 1 AND 6000 );
|
|
|
|
|
 
At a guess, you're looking for something like:
UPDATE
c
SET
stat = l.status,
statusdto = CASE SUBSTRING(l.statushaenstart, 7, 2)
WHEN '00' THEN NULL
ELSE CONVERT(datetime, SUBSTRING(l.statushaenstart, 1, 8))
END,
pnrgaeld = l.pnrgaeld,
koen = l.koen,
fornvnmrk = a.fornvn_mrk,
mellemnvnmrk = a.melnvn_mrk,
efternvnmrk = a.efternvn_mrk,
nvndto = CASE SUBSTRING(a.nvnhaenstart, 7, 2)
WHEN '00' THEN NULL
ELSE CONVERT(datetime, SUBSTRING(a.nvnhaenstart, 1, 8))
END,
fornvn_solo = UPPER(a.fornvn),
fornvn = SUBSTRING(CASE
WHEN a.melnvn Is Null THEN UPPER(a.fornvn)
ELSE UPPER(a.fornvn) + ' ' + UPPER(a.melnvn)
END, 1, 50),
mellemnvn = UPPER(a.melnvn),
efternvn = UPPER(a.efternvn),
adr_fornvn = UPPER(LTRIM(SUBSTRING(a.adrnvn, CHARINDEX(',', a.adrnvn) + 1, LEN(a.adrnvn)))),
adr_efternvn = UPPER(SUBSTRING(a.adrnvn, 1, CHARINDEX(',', a.adrnvn) - 1)),
stilling = UPPER(l.stilling),
z_stilling = l.stilling,
z_fornvn = SUBSTRING(CASE
WHEN a.melnvn Is Null THEN a.fornvn
ELSE a.fornvn + ' ' + a.melnvn
END, 1, 50),
z_mellemnvn = a.melnvn,
z_efternvn = a.efternvn,
z_adr_fornvn = LTRIM(SUBSTRING(a.adrnvn, CHARINDEX(',', a.adrnvn) + 1, LEN(a.adrnvn))),
z_adr_efternvn = SUBSTRING(a.adrnvn, 1, CHARINDEX(',', a.adrnvn) - 1),
z_fornvn_solo = a.fornvn,
haenstart_umrk_navne = a.haenstart_umrk_navne,
stillingdto = Null,
mynkod_ctnavn = Null,
myntxt_ctnavn = Null,
indrap = Null,
adrnvndto = Null,
soegnvn = Null,
soegnvndto = Null
FROM
folk.individ As c
INNER JOIN folk.lperson2010 As l ON l.pnr = c.pnr
INNER JOIN folk.laktnvnopl2010 a ON a.pnr = c.pnr
WHERE
l.lseq BETWEEN 1 AND 6000
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thanks a lot. I did not realize that Oracle SQL and MS-SQL is that different from each other
|
|
|
|
|
|
I have a Schema in mongodb like:-
{
_id: ObjectId("5e05c1089b3e4e333cee8c39"),
name:"Alex",
activity:[{
{
_id: ObjectId("5e05c1089b3e4e333cee8c39"),
type: 'run',
start_timestamp: ISODate("2020-01-11T11:34:59.804Z"),
end_timestamp: ISODate("2020-01-11T11:40:00.804Z")
},
{
_id: ObjectId("5e05c1089b3e4e333cee8c40"),
type: 'stop',
start_timestamp: ISODate("2020-01-11T11:40:00.804Z"),
end_timestamp: ISODate("2020-01-11T11:42:00.804Z")
},
{
_id: ObjectId("5e05c1089b3e4e333cee8c41"),
type: 'wait',
start_timestamp: ISODate("2020-01-11T11:42:00.804Z"),
end_timestamp: ISODate("2020-01-11T11:52:00.804Z")
},
}]
}
This is a schema for a man activity, i need to find brake-up of every 15 minute (brake-up duration in minute) like
{
_id: "2020-01-11T11:34 to 2020-01-11T11:49" ,
duration: 15,
brake-up:{
run:6,
stop:2,
wait:7
}
}
here type: wait should be split into 2 duration "2020-01-11T11:34 to 2020-01-11T11:49" 7 min and "2020-01-11T11:49 to 2020-01-11T12:04" 3 min .
{
_id: "2020-01-11T11:34 to 2020-01-11T12:04" ,
duration: 15,
brake-up:{
wait:3
}
},
Thanks
modified 11-Jan-20 4:10am.
|
|
|
|
|
I am trying to set up a SQL query and I have a select statement that I need to setup a loop in it. It looks like this:
select jods.Company
, case when jos.SetupComplete = 0
then
-- Here is where I need to loop through the rows to get a calculation totaled up with the below equation
(((jods.SetupLabRate * jods.CrewSize) * jos.EstSetHours) * (1 - (jos.SetupPctComplete/100)))
else 0
end as SetupLabCost
any thoughts on this?
|
|
|
|
|
You have to show us the entire query. It appears as if you've setup a join, but we can't see it.
Typically, if you need a loop in a join, it's implemented as a sub-query, something like this:
select...
from mytable as a
join (select col1, col2, col3 from mytable) AS b on b.col = a.col
...
I can't be more specific based on your original question.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
I also tried something like this and still not working:
, case when jos.SetupComplete = 0
then
--for ($i=0; $i<$count_row;$i++)
--{
-- (((jods.SetupLabRate * jods.CrewSize) * jos.EstSetHours) * (1 - (jos.SetupPctComplete/100)))
--}
else 0
end as SetupLabCost2
|
|
|
|
|
Again, without the associated table/view schemas, along with the entire query, we can't really help you, short of speculating as to what *might* be the proper approach.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
select jh.Company, jh.JobClosed, jh.JobComplete, jh.JobEngineered, jh.JobReleased
, jh.JobNum, jh.PartNum, jh.RevisionNum, jh.PartDescription, jh.ProdQty, jh.IUM
, jh.StartDate as JobStartDate, jh.DueDate as JobDueDate, jh.ReqDueDate, jh.ProdCode, pg.Description as ProdDesc
, jh.JobFirm
, jh.Plant, pl.Name as Site
, jo.StartDate as OpStartDate, jo.DueDate as OpDueDate, jo.OpCode
, jo.OprSeq, jo.OpDesc
, jo.RunQty, jo.QtyCompleted
, SetupCost.SetupLabRate, SetupCost.SetupBurRate, ProdCost.ProdLabRate, ProdCost.ProdBurRate
, pp.MfgLotSize, jo.SetupComplete, jo.SetUpCrewSize, jo.EstSetHours, SetupCost.OpenSetupHrs, jo.SetupPctComplete, jo.ProdCrewSize, jo.EstProdHours, jo.ProdStandard
, SetupCost.SetupLabCost
, SetupCost.SetupBurCost
, jo.StdFormat
from JobHead jh
inner join ProdGrup pg on pg.Company = jh.Company and pg.ProdCode = jh.ProdCode
inner join Erp.PartPlant pp on pp.Company = jh.Company and pp.Plant = jh.Plant and pp.PartNum = jh.PartNum
inner join Erp.Plant pl on pl.Company = pp.Company and pl.Plant = pp.Plant
inner join JobOper jo on jo.Company = jh.Company and jo.JobNum = jh.JobNum
left outer join (select jods.Company, jods.JobNum, jods.OprSeq, jods.AssemblySeq, pps.MfgLotSize, Sum(jods.SetupLabRate) as SetupLabRate, Sum(jods.SetupBurRate) as SetupBurRate
, case when jos.SetupComplete = 0
then (Sum(jods.SetupLabRate) * jos.EstSetHours ) * (1 - (jos.SetupPctComplete / 100))
else 0
end as SetupLabCost
--This is the area needing the loop below***********************************
, case when jos.SetupComplete = 0
then
for ($i=0; $i<$count_row;$i++)
{
(((jods.SetupLabRate * jods.CrewSize) * jos.EstSetHours) * (1 - (jos.SetupPctComplete/100)))
}
else 0
end as SetupLabCost2
from Erp.JobOpDtl jods
inner join JobHead jhs on jhs.Company = jods.Company and jhs.JobNum = jods.JobNum
inner join JobOper jos on jos.Company = jods.Company and jos.JobNum = jods.JobNum and jos.AssemblySeq = jods.AssemblySeq and jos.OprSeq = jods.OprSeq
left outer join Erp.PartPlant pps on pps.Company = jhs.Company and pps.PartNum = jhs.PartNum and pps.Plant = jhs.Plant
where jods.SetupOrProd <> 'P'
group by jods.Company, jods.JobNum, jods.AssemblySeq, jods.OprSeq, pps.MfgLotSize, jos.EstSetHours, jos.SetupComplete, jos.SetupPctComplete
) as SetupCost
on SetupCost.Company = jo.Company and SetupCost.JobNum = jo.JobNum and SetupCost.OprSeq = jo.OprSeq and SetupCost.AssemblySeq = jo.AssemblySeq
where jh.JobEngineered = 1 and jh.JobComplete = 0 and jo.OpComplete = 0
|
|
|
|
|
You can not do loops in SQL.
I have a feeling that what you need is as simple as:
SUM(CASE
WHEN jos.SetupComplete = 0
THEN (((jods.SetupLabRate * jods.CrewSize) * jos.EstSetHours) * (1 - (jos.SetupPctComplete / 100)))
ELSE 0
END) AS SetupLabCost
Otherwise you need to tell us what you're trying to achieve
|
|
|
|
|
Hi - I have a script file which does lot DDL Operations - it all included as part of the Transaction but I am getting an Error at the Begin statement of Alter Procedure as "incorrect syntax near begin expecting external" - is there any way to put the Alter Procedure as part of the Transaction Script? Here is my Script for it:
USE XXXXXX;
BEGIN TRY
BEGIN TRANSACTION
DROP INDEX IF EXISTS [IX_NOVId] ON [dbo].[Violations]
PRINT N'Altering [dbo].[OneToManies]...';
ALTER TABLE [dbo].[OneToManies] ALTER COLUMN [ChildEntity] NVARCHAR (MAX) NULL;
ALTER TABLE [dbo].[OneToManies] ALTER COLUMN [ParentEntity] NVARCHAR (MAX) NULL;
PRINT N'Altering [dbo].[usp_Report_ClosedReports]...';
ALTER PROCEDURE [dbo].[usp_Report_ClosedReports]
@EnforcementSectionId INT,
@IsPreCase VARCHAR(3) = NULL,
@FromDate DATE = '01/01/2017',
@ToDate DATE = '01/01/2018'
AS
BEGIN
DECLARE @IsPreCaseBool Bit;
SET @IsPreCaseBool = CASE WHEN @IsPreCase = '0' THEN 0 ELSE
CASE WHEN @IsPreCase = '1' THEN 1 ELSE null END END;
SELECT
ReferenceNumber,
CONVERT(NVARCHAR(10), CaseStartDate, 101) AS CaseStartDate,
CONVERT(NVARCHAR(10), DateCreated, 101) AS DateCreated,
CONVERT(NVARCHAR(10), DateUpdated, 101) AS DateUpdated,
CONVERT(NVARCHAR(10), StatuteOfLimitationsDate, 101) AS StatuteOfLimitationsDate,
ApplicablePenalties,
InvestigatorFullName,
ContactName,
CompanyCity,
CompanyZip,
CaseStatus,
EnforcementSectionName,
ISNULL(ViolationsCount, 0) AS ViolationsCount,
Program
FROM (SELECT cs.CaseId,
(CASE
WHEN [cst].IsPreCase = 1
THEN 'I'
ELSE 'C'
END) + dbo.CIntToChar([cs].CaseId, 5) AS ReferenceNumber,
[cs].CaseStartDate,
[cs].DateCreated,
[cs].DateUpdated,
[cs].StatuteOfLimitationsDate,
[cs].ApplicablePenalties,
inv.FirstName +' '+inv.LastName AS InvestigatorFullName,
ISNULL([cnt].FirstName, '')+' '+ISNULL([cnt].LastName, '') AS ContactName,
[cnt].Address_City AS CompanyCity,
[cnt].Address_Zip AS CompanyZip,
[cst].CaseStatusName AS CaseStatus,
[ens].EnforcementSectionName,
vl.ViolationsCount,
[PROG].Program
FROM Cases AS [cs]
JOIN vw_CasePrograms AS PROG ON PROG.CaseId = [cs].CaseId
LEFT JOIN CaseAssignedToInvestigators ctoi ON ctoi.CaseId = cs.CaseId
LEFT JOIN Contacts AS [cnt] ON [cnt].ContactId = [cs].CaseCompanyId
LEFT JOIN CaseStatus AS [cst] ON [cst].CaseStatusId = [cs].CaseStatusId
LEFT JOIN (select Id, UG.GroupId, FirstName, LastName
from AspNetUsers AS U
join UserGroup AS UG on UG.UserId = U.Id)
AS inv ON (inv.Id = ctoi.UserId AND CST.IsPreCase <> 1)--AND INV.GroupId = 10)
OR (inv.Id = cs.AssignedToInspectorId AND cst.IsPreCase = 1)-- only on investigation
LEFT JOIN EnforcementSections AS [ens] ON [ens].EnforcementSectionId = [cs].EnforcementSectionId
LEFT JOIN (
SELECT COUNT(1) AS ViolationsCount,
v.CaseId
FROM dbo.Violations v
GROUP BY v.CaseId
) vl ON vl.CaseId = cs.CaseId
WHERE([cst].IsPreCase = @IsPreCaseBool
OR @IsPreCaseBool IS NULL)
AND [cst].IsCaseClosed = 1
AND [cs].DateUpdated BETWEEN @FromDate and @ToDate
AND [cs].EnforcementSectionId = @EnforcementSectionId) AS QR
GROUP BY
ReferenceNumber,
CaseStartDate,
DateCreated,
DateUpdated,
StatuteOfLimitationsDate,
ApplicablePenalties,
InvestigatorFullName,
ContactName,
CompanyCity,
CompanyZip,
CaseStatus,
EnforcementSectionName,
ViolationsCount,
Program
END;
PRINT N'Altering [dbo].[SP_ViolationTypesBranched]...';
ALTER PROCEDURE [dbo].[SP_ViolationTypesBranched]
(@Types as NVARCHAR(max) = '',
@Search AS NVARCHAR(MAX) = '')
AS
BEGIN
select
BranchId
,BranchName
,ViolationTypeName
,BranchViolationName
,ViolationTypeCode
,ViolationTypeId
,ViolationTypeSortOrder
,UploadedPhotographCategoryGroup
,Id = null
--Sections
FROM (
select
VT.BranchId,
BR.Name AS BranchName,
BR.Name + ' - ' + ViolationTypeName AS BranchViolationName,
ViolationTypeName,
ViolationTypeCode,
VT.ViolationTypeId,
ViolationTypeSortOrder ,
UploadedPhotographCategoryGroup,
(SELECT
ES.EnforcementSectionName + ', '
FROM EnforcementSections AS ES
WHERE ES.BranchId = VT.BranchId
FOR XML PATH('')) AS Sections
from BranchViolationTypes AS VT
JOIN Branches AS BR ON BR.BranchId = VT.BranchId
--JOIN EnforcementSections AS FS ON FS.BranchId = BR.BranchId
JOIN ViolationTypes AS VV ON VV.ViolationTypeId = VT.ViolationTypeId
) AS X
WHERE dbo.DynoSearch(
ISNULL(CAST(X.BranchName AS NVARCHAR(MAX)),'') +
ISNULL(CAST(X.ViolationTypeCode AS NVARCHAR(MAX)),'') +
ISNULL(CAST(X.ViolationTypeName AS NVARCHAR(MAX)),'') +
ISNULL(CAST(x.Sections AS NVARCHAR(MAX)),''),
@Search) = 1
END
ALTER TABLE [dbo].[ViolationTypeNOVs] WITH CHECK CHECK CONSTRAINT [FK_dbo.ViolationTypeNOVs_dbo.ViolationTypes_ViolationTypeId];
PRINT N'Update complete.';
ROLLBACK TRAN -- Transaction Success!
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRAN --RollBack in case of Error
-- you can Raise ERROR with RAISEERROR() Statement including the details of the exception
RAISERROR(ERROR_MESSAGE(), ERROR_SEVERITY(), 1)
END CATCH
Any help please? Thank you.
|
|
|
|
|
You cannot use CREATE or ALTER PROCEDURE inside a transaction.
But you can cheat, like this:
EXEC ('create procedure dbo.whatever
as
begin
select * from some_table
end')
|
|
|
|
|
Hi - I have an alter command written as below:
ALTER TABLE [dbo].[Cases]
ADD [DocketNumber] NVARCHAR (50) NULL,
[AdministrativeHearingDate] DATETIME DEFAULT ('1900-01-01T00:00:00.000') NULL,
[Locations] NVARCHAR (1000) NULL,
[InterpreterNeeded] BIT DEFAULT ((0)) NOT NULL,
[VoluntaryDisclosure] BIT DEFAULT ((0)) NOT NULL,
[PenaltyAdjustment] FLOAT (53) DEFAULT ((0)) NOT NULL,
[FinancialHardship] INT NULL,
[AdministrativeHearing] INT NULL;I am assuming this statement is adding all those columns so
How can I write conditional sql statement that says if exists then add, do I have to write for each individual column or is there any way I can write all this with one sql statement? Any help please? Thanks in advance.
|
|
|
|
|
Are you asking how to add a specific column only if a condition exists? That is not possible.
You could change the structure to use an Attribute table
Cases
CaseID - PK
DocketNumber
Date
Locations (should this be an attribute as many are implied)
Attributes
AttrID
CaseID
Key - eg InterpreterNeeded
Value - Y (I would use a string type for the value field)
Then you can select from Cases and inner join the Attributes needed for a particular query.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
Try this:
IF NOT EXISTS (
SELECT *
FROM sys.columns
WHERE object_id = OBJECT_ID(N'dbo.Cases')
AND name = 'DocketNumber'
)
BEGIN
ALTER TABLE [dbo].[Cases]
ADD [DocketNumber] NVARCHAR (50) NULL
END
But you have to check and add every column separately.
|
|
|
|
|
Thanks a lot to add another column - AdministrativeHearingDate, do I need to have another if statement? And thanks for jumping in and helping me buddy.
|
|
|
|
|
|
|
Hello All,
I am really stumped at this behavior of SAP Crystal reports 2016 Support pack 2.
I have defined the a parameter as a date. When creating the report, the application is changing this date to a string and hence the date selector is not longer visible. But if i keep the parameter as datetime, then the date selector is visible. Our business user only wants a date value, with no time.
Has anyone seen this behaviour?
Thanks a bunch!
|
|
|
|
|
I am following this article to backup database, but in that, will be backup all rows of all tables in database. But I want backup only rows of all tables that value of department column is '10' (in my database, all tables also having department column).
Can someone help me the how to changes CreateScriptTable() backup. My code app is winform C#
public void CreateScriptDataBase(string dataBaseName, string connectionString)
{
SqlConnection con = new SqlConnection(connectionString);
ServerConnection serverConnection = new ServerConnection(con);
Server server = new Server(serverConnection);
Database database = server.Databases["" + dataBaseName + ""];
if (database != null)
{
Scripter scripter = new Scripter(server);
scripter.Options.ScriptData = true;
scripter.Options.ScriptSchema = true;
scripter.Options.ScriptDrops = false;
var sb = new System.Text.StringBuilder();
foreach (Microsoft.SqlServer.Management.Smo.Table table in database.Tables)
{
sb.Append("DROP TABLE " + table.Name);
sb.Append(Environment.NewLine);
foreach (string s in scripter.EnumScript(new Urn[] { table.Urn }))
{
sb.Append(s);
sb.Append(Environment.NewLine);
}
string folder = Server.MapPath("~/Scripts/");
string filename = folder + dataBaseName + ".sql";
System.IO.StreamWriter fs = System.IO.File.CreateText(filename);
fs.Write(sb);
fs.Close();
}
}
}
|
|
|
|
|
That method is not backup up the database; it is creating a script to drop and recreate the tables in the database.
And it's not doing a very good job of it. It won't work with tables in different schemas, and it won't work with tables that have "special" characters in their names. Also, the generated script will only work in a database where the tables already exist.
A database backup will backup the whole database. If you only want a copy of certain rows from certain tables in your database, then you need to export that data to a file. You can either write code to do it, or use the Import and Export wizard[^] to do it.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
