|
Dear all,
Currently, I need to create a database for control Process in manufacturing.
One Item have got many LotNo,
One LotNo thought many Process(ex: Process A, Process B,Process C...)
with every Process maybe yes or maybo no use Material
How can I build database for:
Select all Lot no and Process by ItemNo
Slect all Process and status in every Process by LotNo
Input ItemNo show all information about Material used on every Process
This is my database:
Item(Id_Item,ItemNo,ItemName)
Lot(Id_Lot,Item_Id,LotNo)
Process(Id_Process, ProcessNo, ProcessName)
Lot_Process(Id,Lot_Id,Process_Id)
Material(Id,Process_Id,MaterialName, Quantity)
|
|
|
|
|
I am importing Data from excel-sheet to our sql server which comes from another system. Example In our sql server database Client name is Zee Entertainment Limited but in Another System Client Name is Zee Ent. ltd. or any other name format. In this way there are 10 thousands of records which is having data variations and We can not change our data which is connected to another business.
Kindly give me solution.
Thanks & Regards
Shankar Chaurasia
|
|
|
|
|
Create a mapping table that maps the company name from the 2 source systems to your system.
This will work but is a solution fraught with disaster, presumably there is no control of the source systems input (excel as a data source ) so whenever a user miss types a company name your mapping goes into the garbage.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I would take a look at fulltext search.
You can add synonyms to the index, and it can also search for proximity terms (spilling errors).
The subject is much to big to cover in a forum answer, but you can start reading here[^] to get a feeling for what can be done.
|
|
|
|
|
Hello,
I'm having some problems getting an update running. I have done similar updates some times without problems (MySql instead of MariaDB). Maybe I'm too tired today to see the problem, it was a long working day...
Maybe someone can illuminate me
Table:
CREATE TABLE ps_data.test (
Test_ID int(11) NOT NULL AUTO_INCREMENT,
Idx int(11) NOT NULL,
Grp int(11) DEFAULT NULL,
PRIMARY KEY (Test_ID)
)
ENGINE = INNODB
AUTO_INCREMENT = 10
AVG_ROW_LENGTH = 1820
CHARACTER SET utf8
COLLATE utf8_unicode_ci;
Sample-Data:
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(1, 1, 1);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(2, 2, 1);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(3, 3, 1);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(4, 4, 1);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(5, 5, 2);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(6, 6, 2);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(7, 7, 2);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(8, 8, 2);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(9, 9, 2);
Update commands (none working):
UPDATE test
SET Idx=(SELECT COALESCE(MAX(tbl.Idx), 0)+1 FROM (SELECT * FROM test) tbl WHERE tbl.Grp=1)
WHERE Test_ID=1
UPDATE test
SET Idx=(SELECT COALESCE(MAX(tbl.Idx), 0)+1 FROM (SELECT * FROM test WHERE Grp=1) tbl)
WHERE Test_ID=1
UPDATE test t, (SELECT * FROM test WHERE Grp=1) tbl
SET t.Idx= (SELECT COALESCE(MAX(tbl.Idx), 0)+1)
WHERE t.Test_ID=1
The first update command produces even a null result on the MAX() function. Strange...
Best Regards,
Raul
modified 17-Sep-16 15:09pm.
|
|
|
|
|
Have you tried the update statement as a select just to see if it returns the expected value.
I would also change select * from test to select Idx from test
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
yes, normally I would use Select Idx, but on samples I use the most simplified syntax.
The bad news is that the first two examples work on MySql but not on MariaDB. The third syntax doesn't work on any DBMS, but I never used it and got it yesterday from the internet because I was desperated.
I always thought that MariaDB is fully compatible to MySql, but this is unfortunatelly not true. Really bad news for those who migrated a productive system from MySql to MariaDB and are using systax like that
|
|
|
|
|
In terms of db object like table,store proc,view ect what are difference is there between sql server express and sql server localdb ?
What we can do with sql express and what we can't with localdb? What missing in localdb compared with sql express db?
please share the knowledge.
tbhattacharjee
|
|
|
|
|
Tridip Bhattacharjee wrote: please share the knowledge. Has already been shared. Google, and you'll find blogs/articles like this[^] one.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi
I'm running the below query, I want to get records for multiple fundCodes in this case Bateleur and Tower Fund.I'm only getting records for Bateleur only from 2016-04-20 to 2016-04-29.
Thank you
Declare @FundCodes VarChar(Max)='Bateleur|Tower Fund',
@StartDate DateTime='2016/04/20',
@EndDate DateTime='2016/04/29',
@Zero integer=1,
@LongShortAll integer = 0,
@PhysEff integer = 0
select fulldate into #TableDates
from AssetData.dbo.Calendar cal where cal.FullDate between @StartDate and @EndDate
and IsWeekDay = 1 and IsHoliday = 0
SET NOCOUNT ON;
declare @NextDate DateTime
set @NextDate = @StartDate
declare @TempValues table(FundCode varchar(200),EffectiveDate DateTime, Value Float)
declare @fundcodestable table (ID int, fundcode varchar(300))
declare @Val float
declare @DateCounter int = 0
select @DateCounter = count(Fulldate) from #TableDates
declare @Date date
while @DateCounter <> 0
begin
set @Date = (select top 1 fulldate from #TableDates order by 1)
insert into @fundcodestable
select pn, s
from StagedFundReportingData..FnxSplit('|', @fundcodes)
Declare @counterFundId int = 0
select @counterFundId = count(fundcode) from @fundcodestable
declare @fundC varchar(300)
while @counterFundId <>0 and @NextDate<=@EndDate
begin
set @fundC = (select top 1 fundcode from @fundcodestable)
Set @Val = 0
if @Zero = 0
begin
if @LongShortAll = 0 begin
exec GetFundValue @fundC, @NextDate, @Val output
end else begin
if @LongShortAll = 1 begin
if @PhysEff = 0 begin
exec GetFundValueLongOnly @fundC, @NextDate, @Val output
end else begin
exec GetFundUnderlyingEffectiveValueLongOnly @fundC, @NextDate, @Val output
end
end else begin
if @PhysEff = 0 begin
exec GetFundValueShortOnly @fundC, @NextDate, @Val output
end else begin
exec GetFundUnderlyingEffectiveValueShortOnly @fundC, @NextDate, @Val output
end
end
end
end
else
begin
If @LongShortAll = 0 begin
exec GetFundValue_ZeroFutures @fundC, @NextDate, @Val output
end else begin
if @LongShortAll = 1 begin
if @PhysEff = 0 begin
exec GetFundValueLongOnly_ZeroFutures @fundC, @NextDate, @Val output
end else begin
exec GetFundUnderlyingEffectiveValueLongOnly_ZeroFutures @fundC, @NextDate, @Val output
end
end else begin
if @PhysEff = 0 begin
exec GetFundValueShortOnly_ZeroFutures @fundC, @NextDate, @Val output
end else begin
exec GetFundUnderlyingEffectiveValueShortOnly_ZeroFutures @fundC, @NextDate, @Val output
end
end
end
end
Set @Val = isnull(@Val,0)
if @Val <> 0
begin
insert into @TempValues (FundCode,EffectiveDate, Value) Values (@fundC,@NextDate, @Val)
end
if datepart(dw,@NextDate) = 7
begin
Set @NextDate = DATEADD(Day,2,@NextDate)
end
else
begin
if datepart(dw,@NextDate) = 6
begin
Set @NextDate = DATEADD(Day,3,@NextDate)
end
else
begin
Set @NextDate = DATEADD(Day,1,@NextDate)
end
end
end
delete #TableDates where FullDate = @NextDate
select @DateCounter = @DateCounter-1
end
Select * from @TempValues order by FundCode
drop table #TableDates
|
|
|
|
|
And what is your question?
|
|
|
|
|
Just a suggestion. Print the results from the following
insert into @fundcodestable
select pn, s
from StagedFundReportingData..FnxSplit('|', @fundcodes)
|
|
|
|
|
Thank you Jschell, I managed to fix the problem.
|
|
|
|
|
Hi,
I have a Oracle DB in which all the information is stored and this information is been consumed by different teams and below is the two options i have to decide on :
1. Write a Stored procedure and give the stored procedure to the respective teams and they can call the stored procedure.
2. Write a stored procedure wrap it as an API and expose the API, different team will call the API exposed and API will in turn hit the SP and return the response to the team.
Like to know what is the PRON and CRONS with these options and what is the best possible solution to go with.
Thanks
|
|
|
|
|
|
We are working on separate computers in different locations so we are not connected to a network.
We are using SQL Server 2016 Express to develop a C# application in Visual Studio (college project).
My group mate sent me the (.bak) and (.mdf) files of the database he was working on so I can view it and make the necessary updates. I saw the data in the files after restoring and attaching them respectively. Some columns that were in (.bak)file were not in the (.mdf) file, and I need the (.mdf) file to be updated/contain the same data with the .bak file so I can add a datasource to the application for easy retrieval of data.
What could possibly have been the problem?
|
|
|
|
|
kmllev wrote: What could possibly have been the problem? Could have been any one of a million things. Without much more information it is impossible to guess.
|
|
|
|
|
kmllev wrote: What could possibly have been the problem? Are you sure the columns are in the .bak file?
Restore it to a new database, and make sure you have rights to write to the database; post any errors you see here
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
|
How to speedup the execution of a query in Sql Server?
|
|
|
|
|
In SSMS under tool the first entry is SQL Server Profiler - use that as your first step.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You need to present a bit more detail so that some suggestions can be given.
Generally, are you linking tables together ? How many? How many rows are in each table?
How many rows are being returned in your query?
With the SQL profiler you will be able to tell whether the server is scanning the table or seeking data via an index.
Your question is too vague to get a valuable answer.
|
|
|
|
|
Hi David Mujica,
Thanks for your reply,but I need just rough idea or tips.
|
|
|
|
|
My response was a tip, without more information we can't really be more specific. Statements like check you indexes (profiler helps) and check your syntax are going to be basically all we can do.
Tuning a DB and queries is almost an art so you need to give us some subject matter. What you have given us is like asking why your painting is rubbish!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
After checking your keys and indexes, you could try to omit as much functions in the query as possible, limit the case-when switches and remove any unused tables and/or columns.
MSDN also has a few pages on the subject
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|