Why do Companies make start columns' id from one?

Why do Companies make start columns' id from one ? When i tried id as 1 -100, it couldn't be find. for example. countryId : 4005 cityId: 13400

For Example; foursquare

via Chebli Mohamed

sql delete double output

I'm running a microsoft sql server. I need to do something along the lines of the following: (not working code, its just to get my point across)

DECLARE @old TABLE ( locatie NVARCHAR(256), gebruiker NVARCHAR(256), tijd DATETIME )
DECLARE @tot INT
DELETE FROM dbo.DW_D_Locaties_Inpak_Productie
    OUTPUT deleted.locatie, deleted.gebruiker, deleted.productiedatum INTO @old (locatie, gebruiker, tijd)
    OUTPUT SUM(deleted.aantal) INTO @tot
    WHERE DocumentNr='B424609'

how do I do this?

via Chebli Mohamed

using SQL to rank scores

What is the correct way in SQL to add a value to a field that is an ordinal ranking.

I say "correct" because I think I need to pull the records in order, and update the field in sequence as I loop over them - only I know this isn't efficient - I know it should be able to be done strictly in the db engine.

Here's the scenario - I have 1000 students with test score averages thru the year.

I want to rank them highest to lowest, and store their ranking int the db, such that when the record is pulled (either singularly or in a group) the 'rank' comes with the record... in other words, yes, if i pull the WHOLE set, and order by avg_score DESC, I'll get the ranking, but it wont 'stick' with the record.

So how would I do that in SQL. Specifically MySQL 5.5

STUDENTS (table)
    id (primary key)
    name
    avg_score
    rank

Thanks.

via Chebli Mohamed

Distribute two SELECTs with a given ratio

I have two tables with the following data

table "group1":

id  | sequenceNo
----+-----------
101 | 1
102 | 2
103 | 3
104 | 4
105 | 5

table "group2":

id  | sequenceNo
----+-----------
201 | 1
202 | 2
203 | 3
204 | 4
205 | 5

I have a given ration of 3:1 which should build a mix of the groups.

The result would be:

id
--
101
102
103
201
104
105

Ideally the mixing stops when one of the groups is empty.

I've implemented a solution for the problem as an OO-program. However, I am curious if there is also a simple SQL-only solution.

Many thanks,

Maik

via Chebli Mohamed

From subquery giving error in oracle

I am having this query :

SELECT T.custno,T.custlastname,AVG(T.OrderAmount) , T.OrderCount
FROM(
      SELECT  A.custno,A.custlastname,count(b.ordno) as OrderCount,    sum(c.qty*d.prodprice) AS  OrderAmount
  FROM customer A 
      JOIN ordertbl B ON A.custno=b.custno
      JOIN ordline C ON b.ordno=c.ordno
      JOIN  product D ON c.prodno=d.prodno
  WHERE A.custstate='CO'
  GROUP BY A.custno,A.custlastname, b.ordno) AS T
GROUP BY T.custno,T.custlastname;

I get this error :

ORA-00933: SQL command not properly ended

When i execute inner subquery explicitly, it runs fine. Please let me know the reason.

One can try at http://ift.tt/1fbp56t

via Chebli Mohamed

MySQL: How can I check whether Users are listed or not

I was wondering how I can check whether users are already in the database or not.

In PHP I have an array with some UserIDs. i.e. userIDs[0] = 1234; userIDs[1] = 2345;

Now I wanted to build a query to make just one sql call if possible to get following result:

############################
#    UserID    #   Exists  #
############################
#     1234     #     0     #
#     2345     #     1     #
############################

Is there a sql solution or do I have to check each ID with a seperate call? Thank you for your help!

via Chebli Mohamed

How to use 'Insert' in a nativeSQL query in Hibernate outside the mapped class ?

In Hibernate, you can use the 'SELECT' queries in native SQL like this :

Query query = session.createSQLQuery("SELECT ... FROM ...");

But I would want to use an 'INSERT' query.

So, I looked at the documentation, and it seems you must go directly to the mapped class and write the code inside it.

But I would want to use it as I do for a 'SELECT' query (outside the mapped class) since it looks much more pratical.

Indeed, why would the treatment be different between 'SELECT' and 'INSERT' for a hibernate native SQL query ?

via Chebli Mohamed

nhibernate with a restriction outside of sql

Is it possible to add restriction in nHibernate (version 3.3) that is based on a calculation outside of the database? For example, say someCalculation below calls into some other method in my code and returns a boolean. For the sake of argument, someCalculation() can not be made in the database. Is there a way to get it to work? It's currently throwing and I'm not sure if it's because I am way off or I'm doing something else wrong.

 query.UnderlyingCriteria.Add(Restrictions.Where<MyEntity>(x => someCalculation(x.id)); 

via Chebli Mohamed

granted table with select but procedure doesn't work

I have one table granted with SELECT - so I can access the table with select..

I can also create copy of this table with:

CREATE TABLE my_table AS
SELECT *
FROM read_only_tbl;

And also manualy reloadtable ->

DELETE FROM my_table;
INSERT INTO my_table
SELECT *
FROM read_only_tbl;

But when I want to run the "reload" from procedure it gives me an error while compiling that the procedure can't see the "read_only_table"...

CREATE OR REPLACE PROCEDURE prcd_reload AS
BEGIN

    DELETE FROM my_table;
    INSERT INTO my_table
    SELECT *
    FROM read_only_tbl;

   /*** .. rest of code ***/

END;
/

-> PL/SQL: ORA-00942: table or view does not exist

what grant do I need to access that table in procedure?

via Chebli Mohamed

OracleCommand.Executenonquery throws 'System.InvalidOperationException'

The following function throws the System.InvalidOperationException:

internal void executeNonQuery(string connectionString, OracleCommand cmd)
    {
        using (OracleConnection conn = new OracleConnection(connectionString))
        {
            using (cmd)
            {
                conn.Open();
                cmd.ExecuteNonQuery(); //here is the error
                conn.Close();
            }
        }
    }

The additional information is:

Operation is not valid due to the current state of the object.

I try to insert a row into a table. Is there another way to do this or to fix this error?

EDIT: I build the query in the binaryManager class with the following methods:

internal object[] binaryInsert(string tblName, string tblQuery, int conStrgID, int cq)
    {
        object[] retValues = new object[3];
        Stream myStream = null ;
        OracleConnection con = null;
        string conString = qm.getConnectionString("ConnectionStringToMyDB"); //is correct

        byte[] data = GetBytes(tblQuery);

        String sql = "INSERT INTO MYTABLES VALUES (NULL, '" + tblName + "', ':tblQueryBlob', " + conStrgID + ", " + cq + ")";


        OracleCommand cmd = new OracleCommand();
        cmd.CommandText = sql;  // Set the sql-command
        cmd.Connection = con;   //con is an OracleConnection

        OracleParameter param = cmd.Parameters.Add("tblQueryBlob", OracleDbType.Blob); //Add the parameter for the blobcolumn
        param.Direction = ParameterDirection.Input;

        param.Value = data;     //Asign the Byte Array to the parameter

        //command containts the parameter :tblQueryBlob with its value
        retValues[0] = cmd;
        retValues[1] = conString;

        return retValues;
    }

private byte[] GetBytes(string str)
    {
        byte[] bytes = new byte[str.Length * sizeof(char)];
        System.Buffer.BlockCopy(str.ToCharArray(), 0, bytes, 0, bytes.Length);
        return bytes;
    }

I call the binaryInsert method from another class with the following code:

BinaryManager bm = new bBinaryManager();
string sqlQuery = "large string with 5100 characters";
object[] binaryValues = bm.binaryInsert("TextTbl", sqlQuery, 1, 1);
string conString = binaryValues[1].ToString();
OracleCommand cmd = (OracleCommand)binaryValues[0];

QueryManager qm = new QueryManager();
qm.executeNonQuery(conString, cmd);

via Chebli Mohamed

What's the basis to choose SQL over HQL in hibernate for some queries?

I am using hibernate in my project and I have always used HQL. However, I have seen in other projects where at times SQL queries are used by createSqlQueries, rather than HQL. I wanted to know, what could be the deciding factor to choose SQL over HQL in certain scenarios. Also if there are some queries which can't be performed by HQL and we need to choose only SQL, please cite example.

via Chebli Mohamed

Changing the dataset on a server executed SSRS Report

I'm trying to narrow down the results returned from a server generated SSRS report, but the customer is requesting too many fields to do be able to do it easily with parameters into a predefined SQL statement.

Is it possible to pass a statement into the reporting server from .NET that the server will execute as its datasource, instead of the preconfigured one? Either the complete statement or the WHERE clause would be fine.

If not, is it possible to eval a parameter sent into a stored procedure? I'm aware of the security implications.

via Chebli Mohamed

get latest row from foreign key table in left join in sql

i have a table with multiple columns ,and there are 5 more tables ,which have reference foreign key relation .in one table we have more than 5 columns for one ref. but I want only latest one ..can you please tell me how can I take it by single query without using temp ...

via Chebli Mohamed

What are the pitfalls of using a separate SSIS package for centralized error management?

We have a few loosely coupled SSIS packages that are in charge of batch integration. When they have an error (validation issue with data, or an actual OnError error) then they all do the same thing, they email a message to a distribution list. The content of the message varies, and sometimes other people need to be cc'd on the message. But it is basically the same process for everything.

I am thinking of creating a single ErrorHandler package that has a few parameters (error message, cc address, subject line etc) and just getting the parent packages to run an Execute Package Step when they need to send an error message.

The way I see it, we then have one single SSIS package that allows us to manage what we do with the incoming errors. If we decide we want to write stuff into a log file, or call a web service, it only has to be changed in the one place.

Limited testing so far looks fine. Am I missing something obvious here? Why doesn't everybody do this? Is there a transactional or cascading issue that could be a problem?

via Chebli Mohamed

How to copy data in identity column?

I have a table with an identity column in a server and have a other table with same structure in another server.. Now I want to copy all data from one table to other table but I can't help it...

I have already created a linked server..

I use this:

insert into [server].[database].[dbo].[table1]
    select *
    from table2

I also use this query without identity column in the place of *

insert into [server].[database].[dbo].[table1]
   select column1, column2
   from table2

What should I do ?

via Chebli Mohamed

Is it mandatory to use addScalar() in createSqlQuery() in hibernate? Why do we need to specify the Data type to hibernate while executing SQL queries?

String sql = "select Band.band_id bandId from guest_band Band";
   sessionFactory.getCurrentSession().createSQLQuery(sql)
    .addScalar("bandId", Hibernate.LONG)
    .list();

I got to know that addScalar() is used to state hibernate the DataType of the selected item, bandId in this case. But my question is, why do we need to specify the type to hibernate? What does it internally perform? Secondly is it an exception if we don't addScalar()? Lastly, is there any alternate way how this can be achieved?

via Chebli Mohamed

sql server parse column and insert into another column

I have a column with both chars and numbers that are separated by an Underscore

Ex: PI (column Name) = ID_32,ID_43,ID_03

I also created a new column called UniqueColumn. In this column I just want the numbers that are in the PI column

therefore it should look like this: UniqueColumn=32,43,03

My code thus far:

UPDATE table
  SET UniqueColumn = RIGHT(PI,LEN(PI)-CHARINDEX('_',PI));


select top 10 dbo.table.UniqueColumn from dbo.table;

via Chebli Mohamed

Adding a custom column on QSqlTableModel for QTableView

here is what i want to appear on my QTableView

col1 col2 cus1
r1
r2
r3
..

cus1 will be my custom column and i want to put some text or notes on it. col1 and col2 are columns from the database and it will be automatically populated.

Ive been reading on how to add virtual columns they say that it can be done by using QProxyModel.I checked the documentation and found out that it's "obsolete".

What alternatives do i have and where should i start?

via Chebli Mohamed

delete a row with prestatement?

I would like to delete a row but I can not connect to my database. I have difficulties to connect with me. My problem is after DELETE FROM outil WHERE id_outil=?"; try { public class DeleteOutil extends SwingWorker { private final String outil; private final JButton toEnable;

    public DeleteOutil(String outil, JButton toEnable) {
        this.outil = outil;
        this.toEnable = toEnable;
    }
    @Override
   public Void doInBackground() {
      PreparedStatement stmt=null;
        String wql = "DELETE  FROM outil WHERE id_outil=?";
        try {
            Connexion con = Connexion.getConnection();
            stmt = con.prepareStatement(wql);

            stmt.setString(1, "outil");
            stmt.executeUpdate();
      }
      catch (Exception e)   
        }
     finally {
   if ( stmt!=null ) {
          // fermer/libérer la ressource
       try {
          stmt.close();
       }
       catch (Exception e) {   
        }
   }
        }  return null;
    }
    @Override
    protected void done() {
        toEnable.setEnabled(true);
    }    
}
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {                                         
// TODO add your handling code here:

    jButton1.setEnabled(false);
    DeleteOutil worker = new DeleteOutil(TableOutil.getValueAt(TableOutil.getSelectedRow(), 0).toString(), jButton1);
    worker.execute();
}

this my connect code:

public class Connexion  {
    String urlPilote="com.mysql.jdbc.Driver";//Direction pour charger le pilote
    String urlBasedonnees="jdbc:mysql://localhost:3306/bdboiteoutil";// Direction pour la connexion à la base de données
    Connection conn;
    public Connexion () {
    //On charge  notre pilote
    try{
       Class.forName(urlPilote);
       System.out.println("Le pilote est chargé");
      }
    catch(ClassNotFoundException ex){
      System.out.println(ex);
     }
    // On se connecte à la base de donnée
    try{
        conn=DriverManager.getConnection(urlBasedonnees,"root","");
        System.out.println("La Base de données est chargé");
    }
    catch(SQLException  ex){
        System.out.println(ex);
    }
        }
    Connection ObtenirConnexion(){
         return conn;
     }
     public void setAutoCommit(boolean autoCommit) throws SQLException {
       conn.setAutoCommit(autoCommit);   
}
    public void close() throws SQLException {
       conn.close();
    }
     public void rollback() throws SQLException {
       conn.rollback();
     }
     public void commit() throws SQLException {
       conn.commit();
    }
     public PreparedStatement prepareStatement(String sql) throws SQLException {
       return conn.prepareStatement(sql);
}

public static void printSQLException(SQLException ex) {
    for (Throwable e : ex) {
      if (e instanceof SQLException) {
        if (ignoreSQLException(((SQLException)e).getSQLState()) == false) {
          e.printStackTrace(System.err);
          System.err.println("SQLState: " + ((SQLException)e).getSQLState());
          System.err.println("Error Code: " + ((SQLException)e).getErrorCode());
          System.err.println("Message: " + e.getMessage());
          Throwable t = ex.getCause();
          while (t != null) {
            System.out.println("Cause: " + t);
            t = t.getCause();
          }
        }
      }
    }
  }
public static boolean ignoreSQLException(String sqlState) {
    if (sqlState == null) {
      System.out.println("The SQL state is not defined!");
      return false;
    }
    // X0Y32: Jar file already exists in schema
    if (sqlState.equalsIgnoreCase("X0Y32"))
      return true;
    // 42Y55: Table already exists in schema
    if (sqlState.equalsIgnoreCase("42Y55"))
      return true;
    return false;
  }
}      

via Chebli Mohamed

What sort of Index for 'AND' columns?

I have a Table with people, and want to select where the person is not deleted. I have a non-clustered primary key on the ID (PersonID). 'Deleted' is a DATETIME, nullable, and is populated when deleted.

So, my query looks like this:

SELECT * FROM dbo.Person
WHERE PersonID = 100
AND Deleted IS NULL

This table can grow to around 40,000 people. Should I have an index that covers the Deleted flag as well?

I may also query things like:

   SELECT * FROM Task t
   INNER JOIN Person p
      ON p.PersonID = t.PersonID
      AND p.Deleted IS NULL
   WHERE t.TaskTypeId = 5
   AND t.Deleted IS NULL

Task table can estimate is 1.5 million rows.

I think I need one that covers both the pk and the deleted flag on both tables? (Task.TaskId, Task.Deleted) and (Person.PersonID and Person.Deleted)?

via Chebli Mohamed