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
jeudi 13 août 2015
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
OpenQuery Not Working in C#
I can successfully connect to a remote server using an odbc connection in C#.
public void checkGradedSerials()
{
List<string> gradedHides = new List<string>();
string queryString = "SELECT COUNT(DISTINCT Serial_No) FROM Part_v_Container_Change2 WHERE Change_Date >= '2015-04-01' AND Location = 'H Grading'";
using (OdbcConnection connection = new OdbcConnection("DSN=PlexReport32; UID=odbc.lowe; PWD=banevuruw2u3;"))
{
OdbcCommand command = new OdbcCommand(queryString, connection);
command.Connection = connection;
connection.Open();
object test = command.ExecuteScalar();
Console.WriteLine(test.ToString());
}
}
The problem is that this keeps timing out. This, I think, is because I am sending requests from the client to the remote server (I am in New Zealand and the database is in the USA). The table has 50 million records in it and so the conundrum is that it is too big to pull as one table, but as I use more filters in my 'WHERE' the time outs occur as it tries to process the data.
In SQL Management studio I can successfully run the query through an OpenQuery which I believe runs the sql on the remote server.
But if I put that same query into the above code as so:
string queryString = "SELECT * FROM OPENQUERY (PLEXREPORT, 'SELECT COUNT(DISTINCT Serial_No) FROM Part_v_Container_Change2') WHERE Change_Date >= ''2015-04-01'' AND Location = ''H Grading''";
I get an error:
Does anyone know another way of getting the sql to run on the server, or what I am doing wrong? It's driving me nuts!
via Chebli Mohamed
Sql Data Reader doesn't read when column empty
I have a problem with the following code:
public void checkTypes(String sqlTable, String sqlColumn)
{
using (SqlConnection connection = new SqlConnection(conStr.Text))
{
String query = "SELECT " + sqlColumn + " FROM " + sqlTable;
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader read = command.ExecuteReader();
while (read.Read())
{
for (int i = 0; i < read.FieldCount; i++)
{
Type dataType = read.GetFieldType(i);
if (dataType == typeof(int))
{
// Do for integers (INT, SMALLINT, BIGINT)
typeOf = "Integer";
read.Close();
connection.Close();
return;
}
else if (dataType == typeof(double))
{
//and so on...
}
Now... I just want to check the types of my columns but the problem is that when the table gets created it has no entries and then the while loop does not get entered at all. How can I modify this slightly without writing completely new code? I don't want to insert pseudo values. Thank you! Hope someone can help me.
via Chebli Mohamed
Continue VBA code onto new line
I have written VBA code behind a CommandButton within Excel but seems like CommandText can only hold a certain amount of characters.
This is my code (on one line) :
.CommandText = "SELECT ID,Employee,WT,[Amount Per Kilometer],Currency,SUM([Number (Amount of km)]) AS [Number (Amount of km)],SUM([Total (per record)]) AS [Total (per record)] FROM (SELECT S.ID,S.FirstName + ' ' + S.LastName AS [Employee],C.Customer_Name,NULL AS [WT],EC.AA_Rate AS [Amount Per Kilometer],NULL AS [Currency],TS.Travel AS [Number (Amount of km)],TS.Travel * CONVERT(float,EC.AA_Rate) AS [Total (per record)] FROM [Timesheets].[dbo].[timesheets] TS INNER JOIN [Timesheets].[dbo].[staff] S ON TS.Staff_Code = S.Staff_Code INNER JOIN [Timesheets].[dbo].[enginecapacity] EC ON TS.EngineCapacityCode = EC.EngineCapacityCode INNER JOIN [Timesheets].[dbo].[customers] C ON TS.Cust_Code = C.Cust_Code WHERE TS.DateWorked BETWEEN LEFT('"& FromDate &"', 4) + SUBSTRING('"& FromDate &"',CHARINDEX('/','"& FromDate &"')+1,LEN('"& FromDate &"') - CHARINDEX('/','2014/12/31') - CHARINDEX('/',REVERSE('2014/12/31'))) + RIGHT('"& FromDate &"',2) AND LEFT('"& ToDate &"', 4) + SUBSTRING('"& ToDate &"',CHARINDEX('/','"& ToDate"')+1,LEN('"& ToDate &"') - CHARINDEX('/','"& ToDate &"') - CHARINDEX('/',REVERSE('"& ToDate &"'))) + RIGHT('"& ToDate &"',2)) as A GROUP BY ID,Employee,WT,[Amount Per Kilometer],Currency"
I need the above code to be split into 2 or 3 lines.
This is what I have tried :
.CommandText = "SELECT ID, " & Resp & _
"Employee, " & Resp & _
"WT, " & Resp & _
"[Amount Per Kilometer], " & Resp & _
"Currency, " & Resp & _
"SUM([Number (Amount of km)]) AS [Number (Amount of km)], " & Resp & _
"SUM([Total (per record)]) AS [Total (per record)] " & Resp & _
"FROM ( " & Resp & _
"SELECT S.ID, " & Resp & _
"S.FirstName + ' ' + S.LastName AS [Employee], " & Resp & _
"C.Customer_Name, " & Resp & _
"NULL AS [WT], " & Resp & _
"EC.AA_Rate AS [Amount Per Kilometer], " & Resp & _
"NULL AS [Currency], " & Resp & _
"TS.Travel AS [Number (Amount of km)], " & Resp & _
"TS.Travel * CONVERT(float,EC.AA_Rate) AS [Total (per record)] " & Resp & _
"FROM [Timesheets].[dbo].[timesheets] TS " & Resp & _
"INNER JOIN [Timesheets].[dbo].[staff] S ON TS.Staff_Code = S.Staff_Code " & Resp & _
"INNER JOIN [Timesheets].[dbo].[enginecapacity] EC ON TS.EngineCapacityCode = EC.EngineCapacityCode " & Resp & _
"INNER JOIN [Timesheets].[dbo].[customers] C ON TS.Cust_Code = C.Cust_Code " & Resp & _
"WHERE TS.DateWorked BETWEEN " & Resp & _
"LEFT('"& FromDate &"', 4) + SUBSTRING('"& FromDate &"',CHARINDEX('/','"& FromDate &"')+1,LEN('"& FromDate &"') - CHARINDEX('/','2014/12/31') - CHARINDEX('/',REVERSE('2014/12/31'))) + RIGHT('"& FromDate &"',2) " & Resp & _
"AND LEFT('"& ToDate &"', 4) + SUBSTRING('"& ToDate &"',CHARINDEX('/','"& ToDate"')+1,LEN('"& ToDate &"') - CHARINDEX('/','"& ToDate &"') - CHARINDEX('/',REVERSE('"& ToDate &"'))) + RIGHT('"& ToDate &"',2)) " & Resp & _
"as A GROUP BY ID,Employee,WT,[Amount Per Kilometer],Currency"
I get a Syntax Error when running the above.
Any help would be greatly appreciated.
via Chebli Mohamed
sql create function converting string to ascii integer
I want to sort records by its question no, e.g.: 1 , 2c , 10 , 12a. Is it good to convert them into sum of ascii code? if yes, what would be the error in the following code creating sql funcion? if no, what would be the better way to do that.
CREATE FUNCTION dbo.asciiCodeInteger (@string AS VARCHAR(3))
RETURNS int
AS
BEGIN
DECLARE @result int;
DECLARE @char CHAR(1);
SET @result = 0;
WHILE LEN(@string) > 0
BEGIN
SET @char = LEFT(@string, 1);
SET @result = @result + ASCII(@char);
SET @string = SUBSTRING(@string, 2, LEN(@string));
END
RETURN (@result);
END
via Chebli Mohamed
How to add 'n' number of spaces between two Column names in Query?
I am trying to write a basic query which gives me FullName from columns FirstName & LastName
Query:
select StudentID,
FirstName + ' ' + LastName as FullName
from Students
Now i am interested to see is there any chance we can add spaces between FirstName & LastName in query while joining dynamic ?
per say i need to have 50 spaces between FirstName & LastName ? Can i achieve this.
one solution would be adding spaces manually which does look the way to go here .
via Chebli Mohamed
SQL LOGIC using 3conditions
course_completions CC
id coursemodid userid state timemodified
370 23 2 1 1433582890
329 24 89 1 1427771915
333 30 39 1 1428309816
332 32 39 1 1428303307
327 33 40 1 1427689703
328 34 89 1 1427710711
303 35 41 1 1410258482
358 36 99 1 1432020067
365 25 2 1 1433142455
304 26 69 1 1410717866
353 37 95 1 1430387005
416 38 2 1 1438972465
300 27 70 1 1409824001
302 29 74 1 1412055704
297 30 2 1 1409582123
301 133 41 1 1410255923
336 133 91 1 1428398435
364 133 40 1 1433142348
312 133 85 1 1425863621
course_modules CM
id course
23 6
24 6
25 6
26 6
27 6
28 6
29 8
30 8
31 8
32 8
33 8
34 5
35 5
36 5
37 5
38 5
39 9
40 9
41 9
course_mod_settings CMS
id course modinstance
27 8 30
28 8 31
29 8 32
30 8 33
31 6 23
32 6 24
33 6 25
34 6 26
35 6 27
36 6 28
37 9 39
38 9 40
39 9 41
I need the count of each user has Completed modules, Inprocess modules and Notstarted modules for each course, where getting the count of userids from table CC by taking courseia from table CM, get number of modules that an user has completed from each course.
(A course can have morethan one module and a course can have number of users attempted all modules, few modules or not attempted at all).
So, I need number of users - has done number of modules - in a course. (3 logics)
Completed.Users means : If number of modules attempted is equal to number of modinstance from table CMS (ex: no. of modules attempted by a user per course= 9, no.modinstance = 9. Because 7 is not equal to 9, They are completed.)
Inprocess.Users means : Number of modules attempted should be >0, but not equal to [count(modinstance) per course] (ex: no. of modules attempted by a user per course= 7 , no.modinstance = 9. Because 7 is not equal to 9, They are Inprocess.)
Notstarted.Users means : Number of modules attempted should be equal to 0, (ex: no. of modules attempted by a user per course= 0. They are Notstarted).
OUTPUT :
Course No.Completed.Users No.Inprocess.Users No.Notstarted.Users
5 65 32 6
6 40 12 15
8 43 56 0
9 0 7 9
Sir, this is a very critical logic that I was trying, I couldn't get a solution. I hope stackoverflow developers could help me out. I tried with my query :
SELECT cm.course AS "Course",
(CASE WHEN
(SELECT count(cms.id) FROM course_mod_settings cms) =
(SELECT count(cmc.coursemodid) FROM course_completions cc JOIN course_modules cm ON cmc.coursemodid = cm.id WHERE cmc.state=1 )
THEN COUNT(SELECT count(cmc.coursemodid) FROM course_completions cc JOIN course_modules cm ON cmc.coursemodid = cm.id WHERE cmc.state=1 ) END) AS "No.Completed.Users",
(CASE WHEN
(SELECT count(cms.id) FROM course_mod_settings cms) > 0 AND
(SELECT count(cms.id) FROM course_mod_settings cms) !=
(SELECT count(cmc.coursemodid) FROM course_completions cc JOIN course_modules cm ON cmc.coursemodid = cm.id WHERE cmc.state=1 )
THEN COUNT(SELECT count(cmc.coursemodid) FROM course_completions cc JOIN course_modules cm ON cmc.coursemodid = cm.id WHERE cmc.state=1 ) END) AS "No.Inprocess.Users",
(CASE WHEN
(SELECT count(cms.id) FROM course_mod_settings cms) = 0
THEN COUNT(SELECT count(cmc.coursemodid) FROM course_completions cc JOIN course_modules cm ON cmc.coursemodid = cm.id WHERE cmc.state=1 ) END) AS "No.Notstarted.Users"
FROM
mdl_course c
GROUP BY c.id
via Chebli Mohamed
Update row with minimum value sql
I have this table:
-----------------------
summonerId | timestamp
-----------------------
253222 | 14395235091096
929112 | 14395235091056
(...)
I want to update the row with the lower timestamp but I can't, when I do this
UPDATE summoners_shell
SET
summonerId = ".$s.",
timestamp = ".$time."
WHERE timestamp = (SELECT MIN(timestamp))
It updates all rows! Why? How do I do what I want?
via Chebli Mohamed
Sqlite with Substr to catch parts of strings
Lets suppose that we have this value inside a row 23/06/2014 01:57:44 PM and we need to catch only the number 06, for this I try the code below:
substr(aRow, 3, -18)
Is correct?
via Chebli Mohamed
SQL Update error using Transaction
Hi reaching out to you guys for a help on this case since I couldn't narrow down and identify the root cause.
This is the scenario :
I am using a table to maintain sequence numbers that are being used as a primary key. Contains 3 columns wiz.
1.cur_value
2.incr_by_value
3.next_value
Here's my update statement (transaction managed):
update _seq set
cur_value = next_value,
next_value = next_value + incr_by_value,
@id = cur_value
@id(output)
I ended up in a situation where the current value and the next value are the same thus causing primary key violation. In a normal situation it does work fine. What are possible scenarios where this update statement could fail.
Any thoughts?
via Chebli Mohamed
SELECT MAX DATE when records have identical dates
I have table with many rows that have the same date. I want to query the max (latest) data but if the dates are the same, only return one. It doesn't matter which record but I could use the latest (GID).
I currently have this to to get the latest date:
SELECT ProjectNum,DateSaved,FilePath ,GID
FROM dbo.master_m_table
AS [alldata]
WHERE DateSaved = (
SELECT MAX(DateSaved)
FROM dbo.master_m_table
WHERE FilePath = [alldata].FilePath)
But if there are two identical dates then it will return both.
- edited -
to better explain my question here is the data:
ProjectNum DateSaved FilePath GID
12345 01.01.2015 a 1
12345 01.01.2015 a 2
12345 01.01.2015 a 88
12345 01.01.2015 c 104
12345 25.01.2015 c 101
12345 25.01.2015 c 68
...and I want the latest date unless the dates are the same and then I want the max GID.
example result:
ProjectNum DateSaved FilePath GID
12345 01.01.2015 a 88
12345 25.01.2015 c 101
via Chebli Mohamed
Sql query to fetch only few records from 1 column based on Condition and show/display it separately using Select query
EDITED: In my Database table, I have 4 column ID ,Action, Name and DateTime with below records:
Action Name DateTime
Submit AA 'date time'
Update BB 'date time'
Save CC 'date time'
Match DD 'date time'
Submit EE 'date time'
Submit FF 'date time'
Update GG 'date time'
Approve HH 'date time'
Approve II 'date time'
Update JJ 'date time'
Match KK 'date time'
Save LL 'date time'
Match MM 'date time'
etc 'date time'
etc 'date time'
These records can be repeate multiple time.
I want to select the Name and DateTime from above Table that has Action = Submit and Action = Update; into two separate columns using select query(Just like we get the data using select query).
Example Query:
SELECT Name as SubmitterName , DateTime AS SubmitterDateTime FROM [tbl_WorkflowHistory] Where Action = 'Submit'
SELECT Name as UpdateName , DateTime AS UpdateDateTime FROM [tbl_WorkflowHistory] Where Action = 'Update'
only thing is I want to show the records of both the query together but in 4 separate columns, like below Format:
Output Needed:
SubmitName SubmitDateTime UpdateName UpdateDateTime
AA 'datetime' BB 'datetime'
EE 'datetime' GG 'datetime'
FF 'datetime' JJ 'datetime'
etc etc
Under "SubmitName" column, all Names shall come that has Action=Submit along with there respective DateTime in SubmitDateTime column.
Similarly, under "UpdateName" column, all all Names shall come that has Action=Update along with there respective DateTime in UpdateDateTime column. I dont know how to write this query, its just sample to explain my requirement.
And after selecting records like above Output, using sql query, I have to display it in a RadGrid in which data coming inside "SubmitDateTime" column have to show separately (Date separate , Time separate) in 2 different columms of RadGrid i.e., SubmitDate, SubmitTime. Similarly for "UpdateDateTime" i.e., UpdateDate, UpdateTime; as described as Below:
SubmitAction SubmitDate SubmitTime UpdateAction UpdateDate UpdateTime
Submit 'date' 'time' Update 'date' 'time'
Submit 'date' 'time' Update 'date' 'time'
Submit 'date' 'time' Update 'date' 'time'
etc
I hope I made my requirement clear. Please let me know how to do this. I am using SQL server database. Please reply. Thanks in advance
via Chebli Mohamed
PHP string variable in MySQL Like query
so I'm attempting to take in a PHP variable and do insert it into a simple sql query, but it's not working and I can't seem to figure out the issue.
NOTE: I know this code has security issues
staff_model.php file:
function getSearches($searchterm) {
$sql = "SELECT *
FROM people
WHERE name
LIKE '%{$searchterm}%'";
$query = $this->db->query($sql);
return $query;
}
My table has several columns but it has columns like id, name, subject, type. The way I get $searchterm is something like
var searchText = document.getElementById('custom-search-text').value;
in my javascript file and I'll pass it to users.php through
$.get(url+"/api/users/staff", {id: id, name: name, type: type, subject: subject, search: searchText})
Just to make sure everything else was working correctly, I hardcoded something for searchTerm (so something like $sql = "SELECT * FROM people WHERE name LIKE 'Matt'") and I did get the correct results.
Something else I tried was $sql = "SELECT * FROM people WHERE name LIKE $searchTerm" and this didn't work.
Any ideas on how I can get it to work with wildcards?
via Chebli Mohamed
PostgreSQL: Flattening a relation with an array to emit one row per array entry
Given a table defined as such:
CREATE TABLE test_values(name TEXT, values INTEGER[]);
...and the following values:
| name | values |
+-------+---------+
| hello | {1,2,3} |
| world | {4,5,6} |
I'm trying to find a query which will return:
| name | value |
+-------+-------+
| hello | 1 |
| hello | 2 |
| hello | 3 |
| world | 4 |
| world | 5 |
| world | 6 |
I've reviewed the upstream documentation on accessing arrays, and tried to think about what a solution using the unnest() function would look like, but have been coming up empty.
An ideal solution would be easy to use even in cases where there were a significant number of columns other than the array being expanded and no primary key. Handling a case with more than one array is not important.
via Chebli Mohamed
SQL Server Object Delimiters
On the 461 certification exam, several questions require:
The code must NOT use any object delimiters
Any idea what this means?
Thanks
via Chebli Mohamed
How to extract xml string to make a new colums in sql using XQuery/XPath?
Hi I have here my code for now and Im not getting the result correctly using this codes;
Declare @txt varchar(max) = (select audit_val from AuditTable where audit_val like '%update_dt%' and audit_val like '%PEK150700019-001%')
Declare @xml XML = CONVERT(XML,@txt)
SELECT
@xml.value('(/i/@guest_id)[1]', 'varchar(100)') as guest_id
, @xml.value('(/i/@updated_by)[1]', 'varchar(100)') as updated_by
, @xml.value('(/i/@update_dt)[1]', 'datetime') as update_dt
what I am getting is null values and it says it returned more than one value, using select top 1 will do, but I need to extract all of the string needed on that column from the table rows, can someone help me how to fix this ? thanks in advance :)
Note** heres a sample output;
=================================================================
|guest_id | updated_by | update_dt |
|==================|==================|=========================|
|PEK150700019-001 | Wane | 2015-08-11T13:59:09.550|
|------------------|------------------|-------------------------|
via Chebli Mohamed
SUM of total per month
I have this table with two columns:
Price Date
45.00 12/06/2015 12:32:54 AM
455.98 22/06/2015 11:00:32 AM
32.00 08/07/2015 09:11:45 AM
98.00 11/07/2015 19:22:32 PM
(the date is in the format DD/MM/YYYY)
I need to get the sum of prices grouped by month and for this I need to find a code in sqlite that cut some parts of that date only getting the month (...06...)(...07...). Below is my code used for some parts of the SELECT:
SELECT SUM(CAST(price as INTEGER)) as TOTALPERMONTH FROM cart_history GROUP BY ....... ORDER BY ID
How I can grouped by some parts of that row?
via Chebli Mohamed
delete value from database using link
I have a page that shows comments, "comments.php", and I include the page on any other page that I want comments to show. I am trying to implement a way to delete comments if needed. Each comment has an auto-increment "commentID". Right now I'm using an action, and then just using a link to call the action.
When I hover over the link, the URL looks correct, but when I click it, the page refreshes and nothing happens. Any ideas?
Action:
if ($_POST['action'] == 'delete') {
$sql = "delete from " . $db_prefix . "comments where commentID = " . (int)$_GET['id'];
mysql_query($sql) or die('error deleting user: ' . $sql);
header('Location: ' . $_SERVER['HTTP_REFERER']);
}
Show comments and show link to delete: (unnecessary code has been left out)
echo '<a href="/comments.php?action=delete&id=' . $result['commentID'] . '">delete</a>
What am I doing wrong?
via Chebli Mohamed
How to extract certain xml strings from a column in sql server 2008 and make a new table and columns using it's data?
Hi I have a table named 'BOOKING' and it has 4 columns, the 4th column's data came from a trigger which will have the xml string. Here's the sample data on the 4th column;
<i guest_id="PEK150700019-001" updated_by="sherwin" update_dt="2015-08-11T13:59:09.550" place_birth="SHANGHAI"/>
What I need is to extract the guest_id value, the updated_by vlaue, the update_dt value and so on: in other words, the xml string can have more strings in xml format.
I want to extract those strings from the 4th column and make a new table for it. Here is the sample output;
=================================================================
|guest_id | updated_by | update_dt |
|==================|==================|=========================|
|PEK150700019-001 | sherwin | 2015-08-11T13:59:09.550|
|------------------|------------------|-------------------------|
|PEK150700019-002 | wane | 2015-09-11T13:00:09.540|
|==================|==================|=========================|
So Far, on my research I have done something like this;
declare @txt varchar(max)
set @txt ='<i guest_id="PEK150700019-001" updated_by="sherwin" update_dt="2015-08-11T13:59:09.550" place_birth="SHANGHAI"/>'
SELECT [updated_by] = SUBSTRING(audit_val,
(CHARINDEX('updated_by',audit_val,1)+12),
(CHARINDEX('"',audit_val,(CHARINDEX('updated_by',audit_val,1)+12)))-(CHARINDEX('updated_by',audit_val,1)+12)),''))
That code extracts the xml string 'updated_by' and making a new column for a table.
Can I ask for a help ? Is there a way to extract those xml strings and make a new columns for a new table on it ? I mean an easier way to do that without using substring or charindex like the one on my sample output? PLEASE HELP thanks in advance :)
via Chebli Mohamed
Why is "update foo ... where bar is null" letting multiple callers claim the same row?
I have a fairly basic query:
UPDATE the_table SET col1=[something], col2=[something else] WHERE col1 IS NULL AND col2 IS NULL;
Immediately after issuing the query, the caller does:
SELECT col3 FROM the_table where col1=[something], col2=[something else];
Unfortunately, concurrent callers are claiming the same row.
I'd rather not do a SELECT FOR UPDATE, because the [select, update, select] would involve three rpcs to the database instead of two (which is bad enough.)
I gather that some dialects of sql allow UPDATE the_table WITH(UPDLOCK), but mine (galera/MySQL) does not. I find it appalling that I'd have to go through this many DB hits to execute such a basic concept. I find that most of my searching efforts end on pages that discuss dialects that DO support UPDLOCK.
Where does it go from here?
via Chebli Mohamed
SQL Query for results from 7 days ago
I have a SQL query that I need to update to pull results from exactly 7 days ago. At the moment the query is looking at result 7 days and before. I can't seem to find a character that changes the > to an equals.
SELECT
distinct(cl.RIID_),
cl.EMAIL_ADDRESS_
FROM
$A$ cl
JOIN
$C$ bro
ON cl.EMAIL_ADDRESS_ = bro.EMAIL_ADDRESS_
JOIN
$D$ cms
ON cms.SKU = bro.ITEM
WHERE
cl.EMAIL_DELIVERABILITY_STATUS_ = 'D'
AND cl.EMAIL_PERMISSION_STATUS_ = 'I'
AND (
cms.CATEGORYNAME = 'Desktop Systems'
OR cms.CATEGORYNAME = 'Refurbished Laptops & Tablets'
or cms.CATEGORYNAME = 'Laptops & Notebooks'
)
AND (
trunc(bro.ACTIVITY_DATE)
) >= (
Trunc(Cast(From_Tz(Cast(Sysdate As Timestamp ), 'US/Pacific') At Time Zone 'Australia/Sydney' As Date)) - 7
)
Thanks for your help!
via Chebli Mohamed
Can this raw SQL be written using the Rails Active Record Query Interface? Should it be?
In my Rails 4 app I make a fairly simple search for one of my models using the following SQL 'OR' statements. It works fine. Is there any way (and reason) to achieve this without raw SQL using the Rails Active Record Query Interface?
Activity.where("
user_id = ? OR
category = ? OR
(secondary_id = ? AND secondary_model = ?) OR
(tertiary_id = ? AND tertiary_model = ?)",
user_id, "Announcement", user_id, "user", user_id, "user"
).uniq
via Chebli Mohamed
How to get the union of a different number of datasets? [SAS]
I'm running a code, almost in an automatic way. I just need to replace one value, and run. But there's one part where i have to do it 'by hand'.
It's the following code:
PROC SQL; CREATE TABLE DDATA.SUS_151_ALL AS
SELECT * FROM
(SELECT * FROM
DDATA.RFN_ID673
UNION
SELECT * FROM
DDATA.RPFN_ID472
UNION
SELECT * FROM
DDATA.RPFN_ID553);
QUIT;
In this case, the clients i want to get the union are here:
PROC SQL;
SELECT EN FROM DDATA.E5P_151;
SELECT COUNT(*) FROM DDATA.E5P_151;
QUIT;
Here, i obtain the following results:
673
472
553
---page break--
3
So, i want something that automatically would read the 3 datasets i wanted to get the union and create the table DDATA.SUS_151_ALL.
I have other clients where i have 8 id's to join, and having to do it by hand 100 times takes me some time. i would want just to replace the 151 , the source.
For example, for other client, let's say id=1000 like this one:
3
7
9
12
16
77
991
1028
I would want a program that would run this:
PROC SQL; CREATE TABLE DDATA.SUS_1000_ALL AS
SELECT * FROM
(SELECT * FROM
DDATA.RFN_ID3
UNION
SELECT * FROM
DDATA.RPFN_ID7
UNION
SELECT * FROM
DDATA.RPFN_ID9
UNION
SELECT * FROM
DDATA.RPFN_ID12
UNION
SELECT * FROM
DDATA.RPFN_ID16
UNION
SELECT * FROM
DDATA.RPFN_ID77
UNION
SELECT * FROM
DDATA.RPFN_ID991
UNION
SELECT * FROM
DDATA.RPFN_ID1028);
QUIT;
Is this possible? Could you give me some hints?
via Chebli Mohamed
How to delete all but most recent row where other columns are equal
I have a MySQL table, an excerpt of which is here:
I need to keep the most recent row for each row where the assoc_case, document, and participant are the same. For example, I only want to keep row 136 out of rows 133-136.
I'm working from this, but can't seem to adapt it for my needs:
SELECT id, assoc_case, participant, document, MAX(created) FROM `table` GROUP BY created, assoc_case, participant, document
via Chebli Mohamed
Having trouble executing the user id session after user logs in
Here is the following codes i have so far for the sign up page login page and homepage. note i am only posting the php I know everything is working except for the user id session. I am connected to my database and row 1 is id and is the primary key.
here is the signup php
<?php
require ("func/insert.php");
if(isset($_POST['Submit']))
{
$first_name = mysqli_real_escape_string($con, $_POST ['first_name']);
$last_name= mysqli_real_escape_string($con, $_POST ['last_name']);
$email= mysqli_real_escape_string($con, $_POST ['email']);
$password= $_POST ['password'];
$StorePassword= password_hash($password, PASSWORD_BCRYPT, array('cost' => 10));
$sql = $con->query("INSERT INTO users ( first_name, last_name, email, password)
VALUES ( ' {$first_name} ' , ' {$last_name} ' , ' {$email} ' , ' {$StorePassword} ' )");
header('Location: login.php');
}
?>
HEre is the login php
<?php require ("func/insert.php"); ?>
<?php
if(isset($_POST['Login']))
{
$email= mysqli_real_escape_string(htmlentities($con, $_POST ['email']));
$password= mysqli_real_escape_string(htmlentities($con, $_POST ['password']));
$result = $con->query(" select * from users where email='$email' AND password='$password' ");
$row = $result->fetch_array(MYSQLI_BOTH);
session_start();
$_SESSION['UserID'] = $row['id']; //thinking this is the problem
header ('Location: home.php');
}
And here is the home php
<?php require ("func/insert.php"); ?>
<?php
session_start();
if (isset($_SESSION['UserID'])) {
}
else {
echo "This session is not working.";
}
?>
via Chebli Mohamed
SQL: Choose from an array of items in a like clause with AND condition
I am trying to write some SQL which really needs an array of like this string and that string and so on, and to meet a datetime condition and make this syntax work as it should. All of this is psuedocode. For example [DueDate] >= someDateTimeControl.Value AND [Place] like "A", "B", "C"
So that only those places which qualify on the due date are also shown, A, B, C. I tried using AND [Place] like "A" Or [Place] like "B"... (so on) with the DueDate but it gets those places which do not meet the due date. So how would you array it so I could get the time and the place as any in the set that meet the time?
via Chebli Mohamed
how to do periodic migration from database to database using mapforce
I need help in migrating data from legacy database to the new database using MapForce. (Both databases are in MSSQL)
I am a student and I have pretty good knowledge of databases but bad at other languages.
Scenario
I have a CUSTOMER table in legacy database (We are still using legacy system) and a CLIENT table the new database (Which we need to populate before going live with new system)
I have already mapped fields between these two tables in MapForce and I have generated C# script.
Question
What to do next ? to make it work and migrate data from Database-1 to Database-2 between those tables?
How to do it periodically or only few records at t time so it won't affect the performance?
Will it also pick up the changes once the records are copied across ?
What I want to Achieve
I want to copy records from the "CUSTOMER" table from legacy system to the "CLIENT" Table in new system.
I also want the MapForce to re-copy them again if there are any changes in legacy records.
via Chebli Mohamed
PostgreSQL: Create an index to quickly distinguish NULL from non-NULL values
Consider a SQL query with the following WHERE predicate:
...
WHERE name IS NOT NULL
...
Where name is a textual field in PostgreSQL.
No other query checks any textual property of this value, just whether it is NULL or not. Therefore, a full btree index seems like an overkill, even though it supports this distinction:
Also, an IS NULL or IS NOT NULL condition on an index column can be used with a B-tree index.
What's the right PostgreSQL index to quickly distinguish NULLs from non-NULLs?
via Chebli Mohamed
Informix to SQL attribute constraint
I have a constraint as follows in my script & I learnt that it's Informix:
create table blaBla
{
var_name_1 not null constraint n255_153,
var_name_1 not null constraint n655_699,
}
I can't find an equivalent to this in SQL. I tried just typing it the same but it doesn't work. What's the equivalent in other DBMS?
via Chebli Mohamed
Is it Possible to create a relationship like this 1 to 0..1 + 0..1 to 1
Want to create the same RelationShip between Student and Result as i've created between Subject and Result is it possible ?? to create a relations like 1 to 0..1 -0..1 to 1??
via Chebli Mohamed
Google BigQuery - Parsing string data from a Bigquery table column
I have a table A within a dataset in Bigquery. This table has multiple columns and one of the columns called hits_eventInfo_eventLabel has values like below:
{ID:AEEMEO,Score:8.990000;ID:SEAMCV,Score:8.990000;ID:HBLION;Property ID:DNSEAWH,Score:0.391670;ID:CP1853;ID:HI2367;ID:H25600;}
If you write this string out in a tabular form, it contains the following data:
**ID | Score**
AEEMEO | 8.990000
SEAMCV | 8.990000
HBLION | -
DNSEAWH | 0.391670
CP1853 | -
HI2367 | -
H25600 | -
Some IDs have scores, some don't. I have multiple records with similar strings populated under the column hits_eventInfo_eventLabel within the table.
My question is how can I parse this string successfully WITHIN BIGQUERY so that I can get a list of property ids and their respective recommendation scores (if existing)? I would like to have the order in which the IDs appear in the string to be preserved after parsing this data.
Would really appreciate any info on this. Thanks in advance!
via Chebli Mohamed
Inscription à :
Articles (Atom)