|
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
|
|
|
|
|
I can not use the Import and Export wizard, i must use in my soft by functions add on write by c#.
I think can backup the rows with command SQL but I do not how to edit the code.
thank for you repl.
|
|
|
|
|
Here is my attempt at providing a solution for you ….
You can convert this to C# and provide some more paramters such as table name, special Select statement, etc
Give this a shot and see if it works for you.
David
Private Sub ExportTable()
Dim rdr As SqlDataReader = Nothing
Using SW As New StreamWriter("c:\temp\zMyTable.txt")
Using sqlconn As New SqlConnection("<put your connection string here>")
sqlconn.Open()
Using sqlcmd As New SqlCommand("SELECT * FROM <some desired table>", sqlconn)
rdr = sqlcmd.ExecuteReader
While (rdr.Read)
Call ReadSingleRow(SW, "~", rdr)
End While
rdr.Close()
End Using
End Using
End Using
End Sub
Private Sub ReadSingleRow(sw As StreamWriter, delim As Char, datareader As SqlDataReader)
Dim i As Integer
For i = 0 To datareader.FieldCount - 1
If (i > 0) Then sw.Write("{0}", delim)
sw.Write("{0}", datareader(i))
Next
End Sub
|
|
|
|
|
thank for David.
This function are only backup one table.But in my database contain 30 tables, in every one table has department column.I want to backup database (all rows of all tables where department equal '10').
and write by c#.
again thanks you very much.
|
|
|
|
|
OK.
Here is what you need to do:
1) Add a parameter to the ExportTable subroutine passing the select statement you want to use.
For example "SELECT * FROM TABLE1 WHERE DEPARTMENT = '10'"
2) Add another parameter to the ExportTable function which is the filename you want the table exported to.
There are lots of Free Online VB to C# code converters out there. I have confidence that you can find one.
|
|
|
|
|
I used a this free Online tool: Code Converter C# to VB and VB to C# – Telerik[^]
public void ExportTable()
{
SqlDataReader rdr = null;
using (StreamWriter SW = new StreamWriter(@"c:\temp\zMyTable.txt"))
{
using (SqlConnection sqlconn = new SqlConnection("<put your connection string here>"))
{
sqlconn.Open();
using (SqlCommand sqlcmd = new SqlCommand("SELECT * FROM <some desired table>", sqlconn))
{
rdr = sqlcmd.ExecuteReader;
while ((rdr.Read))
ReadSingleRow(SW, "~", rdr);
rdr.Close();
}
}
}
}
Convert Code
public void ExportTable()
{
SqlDataReader rdr = null;
using (StreamWriter SW = new StreamWriter(@"c:\temp\zMyTable.txt"))
{
using (SqlConnection sqlconn = new SqlConnection("<put your connection string here>"))
{
sqlconn.Open();
using (SqlCommand sqlcmd = new SqlCommand("SELECT * FROM <some desired table>", sqlconn))
{
rdr = sqlcmd.ExecuteReader;
while ((rdr.Read))
ReadSingleRow(SW, "~", rdr);
rdr.Close();
}
}
}
}
|
|
|
|
|
Dear David.
Is your ExportTable() function only export one table in database?
In my DB have 30 tables, so how can I do embled the ExportTable() to my CreateScriptDataBase() function?
Thank you for your enthusiastic help!
|
|
|
|
|
anybody can help me improve the code belowing in order to export rows that department column is '10':
foreach (string s in scripter.EnumScript(new Urn[] { table.Urn }))
{
sb.Append(s);
sb.Append(Environment.NewLine);
}
|
|
|
|
|
Hello.
I have a problem with sa login to ms sql server 2014 and 2008 on Windows 10.
I have new computer with Windows 10. I am login to windows by domain user. When I installed MSSQL Server 2008 i had error 0x84B10001. I created local user and installed sql with him. When i loggin to windows by domain user i'm not login to sql by sa. I have error nr 18456 with state nr 8 (The password is incorrect). But when I'm login to windows by local user i can logi to sql by sa with the same password.
What i'm doing to working sa login on the domain user?
|
|
|
|
|
Lots of possible causes for that error:
MSSQLSERVER_18456 - SQL Server | Microsoft Docs[^]
It sounds like your connection is using Windows authentication instead of SQL authentication. The full error message should give you more details.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hey
What is the difference between SQL Server Management Studio VS SQL Server in Visual Studio 2019? Can I use it in Visual Studio 2019 for Large database? Thank you so much 
|
|
|
|
|
|