|
My task is: Our users get from their chemical suppliers some tables do determine the required quantity for chemical Y based on the given quantity of chemical X.
BTW: The task is_not_ to find a linear regression formula.
Easy example- 2 Dim: Depending on X the component Y has to be used with different quantities:
X Y= f(X)
1 0.1
2 0.2
3 0.5
In Praxis this will be not only y= f(x) it is more r=f(x,y,z, ...) and much more it is (r, s, t, ...)= f(x, y, z, ....) but the later is not the discussion here.
Now I'm asking me how one can design a General database layout with a constant number of tables/fields to save an N dimensional function.
With constant number of tables/fields I mean I don't like to solve this task by creating "dynamicly" columns according to the bigest dimension in use... or so 
Note: I have also to say most probably I’m not going to save this kind of data in a set of related tables, but anywhere I’m interesting how one would do it theoretically, maybe this gives me some more ideas and last but not least to learn.
The dimensions are usually around 3 to 5, but I like to solve: How can one design a General Database Table Layout -for N dimensions - for this task?
Is started like this
TBLS
ID (P)
NAME
TBLS_BASES
ID
TBLS_ID (P) FK: TBLS.ID
BASE_ID (P)
BASE_NAME
TBLS_BASES_VALUES
ID
TBLS_ID (P) FK: TBLS_BASES.TBLS_ID
TBLS_BASES_ID (P) FK: TBLS_BASES.BASE_ID
POS (P)
VALUE
TBL_VALUES
TBLS_ID FK: TBLS_BASES_VALUES.TBLS_ID
TBLS_BASES_ID FK: TBLS_BASES_VALUES.TBLS_BASES_ID
POS FK: TBLS_BASES_VALUES.POS
VALUE
Now an example for three dimension z= f(x, y)
First the three "base" tables for the function
TBLS TBLS_BASES TBLS_BASES_VALUES
====== ========================== ==========================================
ID BASE_ID TBLS_ID BASE_NAME TBLS_ID TBLS_BASES_ID POS VALUE
(P) (P) (P) (P) (P) (P)
---- ---- ------- --------- ------- ------------- --- -----
T1 T1B1 T1 e1 T1 T1B1 1 0.1
T1 T1B1 2 0.2
T1 T1B1 3 0.3
T1 T1B2 T1 e2 T1 T1B2 1 0.7
T1 T1B2 2 1.2
T1 T1B2 3 3.4
And here my Problem comes
In case of 3Dim always for 2 bases are needed to save one function value, in case if 4Dim it Needs a Group of 3 bases to save a function value, and so on...
Does any body has a sugestion for a better layout? At the moment I'm blocked and don't see a way how I can link a "Group" of items to one specific "Point".
TBL_VALUES
==========================================
TBLS_ID TBLS_BASES_ID POS VALUE
------- ------------- --- -----
T1 T1B1 1 1
T1 T1B2 1 1
T1 T1B1 1 2
T1 T1B2 2 2
T1 T1B1 1 3
T1 T1B2 3 3
T1 T1B1 2 4
T1 T1B2 1 4
T1 T1B1 2 5
T1 T1B2 2 5
T1 T1B1 2 6
T1 T1B2 3 6
T1 T1B1 3 7
T1 T1B2 1 7
T1 T1B1 3 8
T1 T1B2 2 8
T1 T1B1 3 9
T1 T1B2 3 9
Sorry for my english and thank you in advance for some tips.
Bruno
[Edit]
Sorry for that, after about 2h edit I hope it is finally done, but preview does not really works nice 
Ok, it was not only preview
modified 19-Jan-21 21:04pm.
|
|
|
|
|
In your TBLS have a parent ID which is self referencing. I would also store a text structure of your formula in that table.
This is a straight forward bill of materials structural requirement.
Have you considered a mathematical formula parser it may allow you to store the text and decompose it to elements on the fly (caveat I have no idea of their capabilities or accuracy).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thank you for your Response. Yes finally I will save it as text and parse it. It is only curiosity how a structure should look for a relatinal DB.
After reading agein
Quote: In your TBLS have a parent ID which is self referencing
Great idea, thank you so much!
modified 19-Jan-21 21:04pm.
|
|
|
|
|
Hi,
I am getting the following error when running the sql script
Cannot initialize the data source object of OLE DB provider Microsoft.ACE.OLEDB.12.0 for linked server (null)
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Text;Database=\\xxxx\xxx\xxx\DataExchange\xcxcxcx\xxxx\;HDR=Yes;Format=TabDelimited',
'SELECT * FROM TIN_Solicitation_Response_Error_01032017.txt')
I tried online reading different articles and answers, I could not get answer for it. Any help like why does it happen, when it happens and any fixes etc can be greatly helpful.
Thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
-- modified 5-Apr-17 17:03pm.
|
|
|
|
|
Have you confirmed that the file exists in that path FROM THE SERVER. Not from your local machine.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
what is merge and hash join ?
if anyone know and use it then please share the knowledge.
when one should go for merge and hash join.
if possible discuss it with a good example the significance of merge and hash join.
also tell me how merge and hash join is different from normal join
thanks
tbhattacharjee
|
|
|
|
|
|
CHill60 wrote: Google is a wonderful resource
OP is pathologically averse to using Google. Maybe it's a rude word in his language.
|
|
|
|
|
I see what you mean 
|
|
|
|
|
searching google for merge join in sql server brings lots of result but i like to know when it is good or should i do it when joining one key is pk or other key is fk or regular key.
in this kind of scenario merge join in sql server will be good.
share your idea if you know. thanks
tbhattacharjee
|
|
|
|
|
This article (from the first page of the Google search I posted) sums it up pretty well
Understanding SQL Server Physical Joins[^]
I draw your attention to the statement at the end of the article
Quote: SQL Server does a pretty good job in deciding which join operator to use in each condition. Understanding these condition helps you to understand what can be done in performance tuning. It's not recommended to use join hints (using OPTION clause) to force SQL Server to use a specific join operator (unless you have no other way out), but rather you can use other means like updating statistics, creating indexes or re-writing your query.
Given the way you have worded your question I would just stick to using normal join syntax and allow SQL Server to do the decisions on how they are physically implemented.
These resources may be helpful:
SQL Server Join Example[^]
Visual Representation of SQL Joins[^]
|
|
|
|
|
Tridip Bhattacharjee wrote: searching google for merge join in sql server brings lots of result but i like to know when it is good or should i do it when joining one key is pk or other key is fk or regular key.
Try reading the first pages that come back as a result 
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi All,
I have an application which reads all the Jobs and its related packages which failed, but its reading only those Packages which are executed as part of the Jobs. Is there any way that I can check all the Packages that are failed like the ones that are executed externally like from an Application or Manually etc.
The code to get the failed packages that are part of the SQL Agent Jobs:
DECLARE @PreviousDate datetime<br />
DECLARE <a href="https://www.codeproject.com/Members/Year">@Year</a> VARCHAR(4)<br />
DECLARE <a href="https://www.codeproject.com/Members/month">@Month</a> VARCHAR(2)<br />
DECLARE @MonthPre VARCHAR(2)<br />
DECLARE <a href="https://www.codeproject.com/Members/Day">@Day</a> VARCHAR(2)<br />
DECLARE @DayPre VARCHAR(2)<br />
DECLARE @FinalDate INT
-- Initialize Variables<br />
SET @PreviousDate = DATEADD(dd, -1, GETDATE()) -- Last 1 day<br />
SET <a href="https://www.codeproject.com/Members/Year">@Year</a> = DATEPART(yyyy, @PreviousDate)<br />
SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))<br />
SELECT <a href="https://www.codeproject.com/Members/month">@Month</a> = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2)<br />
SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))<br />
SELECT <a href="https://www.codeproject.com/Members/Day">@Day</a> = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2)<br />
SET @FinalDate = CAST(@Year + <a href="https://www.codeproject.com/Members/month">@Month</a> + <a href="https://www.codeproject.com/Members/Day">@Day</a> AS INT)
-- Final Logic
SELECT h.instance_id, j.[name],
s.step_name,
h.step_id,
h.step_name,
h.run_date,
h.run_time,
h.sql_severity,
h.message,
h.server
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j
ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps s
ON j.job_id = s.job_id
AND h.step_id = s.step_id
WHERE h.run_status = 0 -- Failure
AND h.run_date > @FinalDate
ORDER BY h.instance_id DESC
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
-----------------------------------DATABASE-------------------------------------------------------------
CREATE TABLE ACCOUNTS(ACCNO INT PRIMARY KEY,BAL PRIMARY KEY);
insert into accounts values(77,10000);
insert into accounts values(88,10000);
---------------------------------HERE IS JDBC CODE----------------------------------------------------
import A.JDBCUtil; // ****JDBCUtil is the class having connection code
import java.sql.*;
public class Test12 {
public static void main(String[] args) {
Account acc=new Account();
acc.transfer(77,88,5000); //******LINE 8******
}
}
class InvalidAccountNumberException extends Exception{
int accno;
public InvalidAccountNumberException() {
}
public InvalidAccountNumberException(int accno){
this.accno=accno;
}
<a href="https://www.codeproject.com/Members/OverRide">@Override</a>
public String toString() {
return "Accno"+accno+"not found";
}
}
class InsufficientFundsException extends Exception{
public InsufficientFundsException() {
}
<a href="https://www.codeproject.com/Members/OverRide">@Override</a>
public String toString() {
return "sufficent funds are not available";
}
}
class Account{
int bal;
int sab,dab,danb,sanb;
public void transfer(int sa,int da,int amt){
Connection con=null;
PreparedStatement ps1=null;
PreparedStatement ps2=null;
try{
con=JDBCUtil.getOracleConnection();
con.setAutoCommit(false);
ps1=con.prepareStatement("select bal from accounts where accno='?'");
ps1.setInt(1,da); ******//LINE 12******
ResultSet rs1=ps1.executeQuery();
if(rs1.next())
dab=rs1.getInt(1);
else
throw new InvalidAccountNumberException(da);
danb=dab+amt;
//updating destination account balance
ps2=con.prepareStatement("update accounts set bal='?' where accno='?' ");
ps2.setInt(1,danb);
ps2.setInt(2,da);
ps2.executeUpdate();
System.out.println("**"+da+"*updated*");
//checking source account
ps1.setInt(1,sa);
rs1=ps1.executeQuery();
if(rs1.next()){
sanb=rs1.getInt(1);
}else{
throw new InvalidAccountNumberException();
}
if(sab>=amt){
sanb=sab-amt;
}else{
throw new InsufficientFundsException();
}
//updating Source account
ps2.setInt(1,sanb);
ps2.setInt(2,sa);
ps2.executeUpdate();
con.commit();
System.out.println("**"+sa+"updated");
System.out.println("funds transferred");
}catch(Exception e){
e.printStackTrace();
try{
con.rollback();
}catch(Exception e1){
}
}finally{
JDBCUtil.cleanup(ps1, con);
JDBCUtil.cleanup(ps2, con);
}
}//transfer
}//account
|
|
|
|
|
Where is line 8, where is line 12?
|
|
|
|
|
CHECK THE CODE I HAVE WRITTEN THE LINE NO ON THE CODE
|
|
|
|
|
-----------------------------------DATABASE-------------------------------------------------------------
CREATE TABLE ACCOUNTS(ACCNO INT PRIMARY KEY,BAL PRIMARY KEY);
insert into accounts values(77,10000);
insert into accounts values(88,10000);
---------------------------------HERE IS JDBC CODE----------------------------------------------------
import A.JDBCUtil; // ****JDBCUtil is the class having connection code
import java.sql.*;
public class Test12 {
public static void main(String[] args) {
Account acc=new Account();
acc.transfer(77,88,5000); //******LINE 8******
}
}
class InvalidAccountNumberException extends Exception{
int accno;
public InvalidAccountNumberException() {
}
public InvalidAccountNumberException(int accno){
this.accno=accno;
}
<a href="https://www.codeproject.com/Members/OverRide">@Override</a>
public String toString() {
return "Accno"+accno+"not found";
}
}
class InsufficientFundsException extends Exception{
public InsufficientFundsException() {
}
<a href="https://www.codeproject.com/Members/OverRide">@Override</a>
public String toString() {
return "sufficent funds are not available";
}
}
class Account{
int bal;
int sab,dab,danb,sanb;
public void transfer(int sa,int da,int amt){
Connection con=null;
PreparedStatement ps1=null;
PreparedStatement ps2=null;
try{
con=JDBCUtil.getOracleConnection();
con.setAutoCommit(false);
ps1=con.prepareStatement("select bal from accounts where accno='?'");
ps1.setInt(1,da); //**************//LINE 12******************//
ResultSet rs1=ps1.executeQuery();
if(rs1.next())
dab=rs1.getInt(1);
else
throw new InvalidAccountNumberException(da);
danb=dab+amt;
//updating destination account balance
ps2=con.prepareStatement("update accounts set bal='?' where accno='?' ");
ps2.setInt(1,danb);
ps2.setInt(2,da);
ps2.executeUpdate();
System.out.println("**"+da+"*updated*");
//checking source account
ps1.setInt(1,sa);
rs1=ps1.executeQuery();
if(rs1.next()){
sanb=rs1.getInt(1);
}else{
throw new InvalidAccountNumberException();
}
if(sab>=amt){
sanb=sab-amt;
}else{
throw new InsufficientFundsException();
}
//updating Source account
ps2.setInt(1,sanb);
ps2.setInt(2,sa);
ps2.executeUpdate();
con.commit();
System.out.println("**"+sa+"updated");
System.out.println("funds transferred");
}catch(Exception e){
e.printStackTrace();
try{
con.rollback();
}catch(Exception e1){
}
}finally{
JDBCUtil.cleanup(ps1, con);
JDBCUtil.cleanup(ps2, con);
}
}//transfer
}//account
|
|
|
|
|
I cannot see anything wrong with your parameter indices, unless something else is corrupting those values. You need to use your debugger to step through the code and see exactly what is happening.
|
|
|
|
|
I am looking to create a tool for my office to simplify the storing of certain logs. Right now it is done via excel and during weekly calls the staff needs to organize this and distribute to recipients, update daily, etc.
I am thinking (thinking) if I went to a Database application I could write a better means to store the information and more importantly add some code to have a button (for example) to email all of the reports to anyone in the database flagged as a recipient.
This is just one small first step of functionality as I aim to overtime add more and more slightly to it.
I am thinking that I could put a template of the database in each project folder and have people just manage it that way so I dont have to worry about too many concurrent users. It also simplifies the location of the storage vs a central place everyone gets to it (which means they forget since they are in the project folders all the time).
Alternative, is I just start the endeavor of writing a true .net application on top of the database. Although I am thinking this is more future proof as I honestly dont know where this would feature wise I am thinking it would be way more complex and lengthy on development time.
So how do you weigh where to build the application? I can probably knock out basic functionality in access relatively quickly, but am I limiting myself?
|
|
|
|
|
If you NEVER expect to consolidate the data then Access on each client is a viable option.
However I would start with SQL Server express and a Winforms/WPF client application.
Access will give you a quick and dirty solution with a minimal learning curve. You will run into limitations fairly quickly, especially when you decide you need to consolidate the data for whole of company reporting.
SQL/Client will have quite a steep learning curve, however it is a professional environment and you can plug in a professional developer when you reach you limitations.
This is an ideal project for a junior developer just starting out, they get to learn the skills of analyst and application design and you get to manage and learn the development skills.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for the response. The combination of data is not a big deal as I could always recreate a unified solution later. It never would really hurt me. I am just torn on how useful a standalone DB will be. If not useful, it won't get buy in.
I am sure this may not be well received in these forums but I have been researching Mendix a lot. Model based development takes the technicalities out of it. Any thoughts on that? Seems like it could be a good route to go.
|
|
|
|
|
Sorry I know nothing about Mendix but it looks like a web development suite. I would look into how mature it is and peruse their support forum to see how responsive they are to problems.
[edit]
As a caveat to using custom IDEs look at this question then look a the last release date for an update of the product he is using.
How to set up QuickSharp to run program in separate window? - C# Discussion Boards
Never underestimate the power of human stupidity
RAH
modified 12-Mar-17 20:39pm.
|
|
|
|
|
Hi All,
I want some help in getting all the steps which are either disabled or failed along with their job information of SQL Server, any help is going to be very helpful.
Thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
indian143 wrote: getting all the steps which are either disabled or failed along with their job information of SQL Server,
Get how? You can go look at them through the UI. Do you want sql to query for them? C# to run a report? Not sure what you want.
There are two kinds of people in the world: those who can extrapolate from incomplete data.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
Hi,
I need to get all the Packages that are run either by using Job or without using the Jobs, I need the statuses of those Packages, whether they are failed or success.
This is because I have a console application that's executing all the Packages one after the other, but if the Packages failed internally we are not informed of that, so I want to go ahead and check status of the Package after execution using query and find out what has happened.
Any help would be greatly helpful. Thanks in advance. I want to get all the packages that are executed either by Job or without using the Job on a database.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
