Relational Database Design for N dimensional function, I have some problems - Database Discussion Boards

Hi,

I have a table (Table123) that has Columns ColA, ColB, ColC, ColD, ColE, ColF, ColG, I have many rows entered in the same table but want to check for two rows RowA, RowB, if for example I have three columns that are different in these two rows, I want to select those Column Names, like if ColA, ColC and ColE are changed then my query should return me as below

TableName, ChangedColumnName, OldValue, NewValue
Table123 , ColA, 123 Greetings 123 New Greetings
Table123 , ColC, 123 World 123 Hello World
Table123 , ColE, 123 Test World 123 New World

etc.

Is there anyway I can do it, but without using dynamic sql any help would be greatly helpful, thanks in advance.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

You lost me with the "three columns that are different in these two rows" - how can you have different columns in specific rows of a single table?

You've also mentioned that your dynamic SQL is taking a long time to run - I don't believe that is because it is dynamic SQL. It's more likely to be the way you have constructed the actual query.

If you share the code you use to build the dynamic query it might help me to understand what you are trying to achieve (and we might also be able to solve your performance problem)

Chilli is right, dynamic sql is unlikely to be your problem, query design is almost certainly the cause of slow processing.

You need to create a temp table that hold all the field from the various sources and feed the records into that table to make the structure the same.

Never underestimate the power of human stupidity
RAH

Yeah Could be but most of the times I used Dynamic Sql if the rows are in like 10000s or 100000s then performance of the Stored Procedure decreased even after enabling and disabling the Query plans in both situations it decreased heavily, but I was able to bring that up 10 or 20 minutes, which is not bad for ETLs. But then the Politics involve and make us inferior just for that reason. That's what happens most of the times. But yes normal queries I ran so far run within couple of minutes but the dynamic sql took 10s or 20s of minutes and I could not able to minimize it more than that.

Any help would be greatly helpful - thanks for all help my friends.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

Have you taken the script of the dynamic query and run a query profile on it to see if there are indexes that can be tuned.

Never underestimate the power of human stupidity
RAH

Quote:
Any help would be greatly helpful - thanks for all help my friends.

As I said last week, if you share the code that you are using to generate the dynamic SQL we may be able to help you improve it

Hi,

I am trying to run my application in debugging mode. Logon screen appears and when I try to login "context::Context(void) - cs_ctx_alloc() failed" error is encountered.

On debugging using break point and selecting continue when the code crashes, following Database Access Error Message is displayed
"<ctsybasedaport::ctsybasedaportdanger>:ct_con_props(CS_USERDATA)failure".

Following are the environment setup in which I am finding error.
OS: Windows 10
IDE: Visual Studio 2013, VC++ Code
Access: Elevated
Database: Sybase OCS 15_0

The application is working well on following environment:-
OS: Windows 7
IDE: Visual Studio 2013, VC++ Code
Access: Admin
Database: Sybase OCS 12_5

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 Wink | ;)

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: The Main Table to define a specific N Dimensional User- Function)
TBLS		        
  ID            (P)
  NAME

// TBLS_BASES: In this table the bases of the User Function will be defined
TBLS_BASES	       
  ID            
  TBLS_ID       (P)   FK: TBLS.ID
  BASE_ID       (P)
  BASE_NAME

// TBLS_BASES_VALUES: In this table the bases values will be defined
TBLS_BASES_VALUES
  ID
  TBLS_ID       (P)   FK: TBLS_BASES.TBLS_ID 
  TBLS_BASES_ID (P)   FK: TBLS_BASES.BASE_ID
  POS           (P)
  VALUE               // The base value, basically the candidate for PrimKeySeg, but 
                      // replaced by POS for the sake of simplifying

// TBL_VALUES: The table to finally save the function values. Here I have my big problem
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               // Finally the function 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 Smile | :)

Ok, it was not only preview Smile | :)

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! Thumbs Up | :thumbsup:

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

Google is a wonderful resource for questions like this...Google[^]

In the meantime have a read of this article : Join Operations – Hash Match - SQLServerCentral[^]

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 D'Oh! | :doh:

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 Hmmm | :|

Bastard Programmer from Hell Suspicious | :suss:

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.