texts: How to convert minutes to hours using MYSQL?

texts:
Personal I have this query that is returning the time in minutes for me. But I want this query to return in hours and minutes rather than in minutes. It's already tried using  FLOOR() function, but to no avail.

SQL

SELECT
   vencido.tickets_id,
   Tickets.name,
   Tickets.due_date,
   Tickets.solvedate,
   Cat.completename,
   TIMESTAMPDIFF(hour, Tickets.date, Tickets.solvedate) as Tempo
FROM
   glpi_vencidos as vencido
   INNER JOIN
      glpi_tickets as Tickets
      ON (Tickets.id = vencido.tickets_id)
   INNER JOIN
      glpi_itilcategories as Cat
      ON (Tickets.itilcategories_id = Cat.id)
WHERE
   Tickets.solvedate between STR_TO_DATE('01/06/2017', '%d/%m/%Y') and STR_TO_DATE('01/07/2017', '%d/%m/%Y')
   and vencido.situacao = 1

My result in minutes


texts: ORA-40341: Access violation on model storage object in Oracle?

texts:
While I was trying to drop a table, it throws following error in Oracle SQL Developer:

ORA-40341: Access violation on model storage object

The tables are temporary tables created while pushing into the database using Oracle R Enterprise. The names of the tables are: DM$PRORE$21_473, DM$PGORE$21_473, ...

I need to drop all these tables as these tables have occupied large space of my database. While googling, I found this link but it provides no solution clues.


texts:
I am using an older version of Postgres (8.4.20). I know that the autovacuum process is executed frequently to free disk space of queries that deleted or updated data in tables. I have a database which does not have frequent deletes or updates.

Does it take less time and memory to process autovacuum on such a database, or does it solely depend on the size and number of objects in the database?


texts: Load New Foreign Key in Facts Table, When Update Dimension with SCD Type 2

texts:
I'm working on the production of a Data Warehouse with SSIS, and today we create the first dimension with SCD of type 2. The fact table is linked in the DIM_CIPESSOA dimension through the idcipessoa column.

When updating the dimension, as shown below, a new PK is generated and with this we need to update the FK in the fact table to connect the lines correctly.

Dimension Table:

idcipessoa  cgccpf_AltKey  tppessoa nomepessoa        effectiveDate            expiryDate               IsCurrent
----------- -------------- -------- ----------------- ------------------------ ------------------------ ---------
88393       00000000001    1        CUSTUMER OLD      2018-04-02 11:52:47.040  2018-04-02 22:29:05.243  0
169852      00000000001    1        CUSTOMER UPDATED  2018-04-02 22:55:08.540  9999-12-31 23:59:59.997  1

Fact Table

idFact  idcipessoa  tppessoa
------- ----------- --------
1       88393       1.99

So, as in the example above, we need a strategy to align the fact table to be pointed to the new new key 169852.


texts: How to get the foreign and primary key details for a table in SQL Server 2008

texts:
I would like to use 2 queries, one that lists all the foreign keys for a given table with the following information Schema name, foreign table name and the other that lists all the primary keys.

Is this query that lists the primary keys correct:

select syssc.name as schemaname , cast(c.name as  varchar(255)) as foreign_table ,
cast(p.name as varchar(255)) as primary_table
from sysobjects f
inner join sysobjects c on  f.parent_obj = c.id
inner join sysreferences r on f.id =  r.constid
inner join sysobjects p on r.rkeyid = p.id
inner  join syscolumns rc on r.rkeyid = rc.id and r.rkey1 = rc.colid
inner  join syscolumns fc on r.fkeyid = fc.id and r.fkey1 = fc.colid
left join  syscolumns rc2 on r.rkeyid = rc2.id and r.rkey2 = rc.colid
left join  syscolumns fc2 on r.fkeyid = fc2.id and r.fkey2 = fc.colid
inner join sys.tables syst on rc.id=syst.object_id
inner join sys.schemas syssc on syst.schema_id=syssc.schema_id
where  f.type =  'F' AND c.name in ('table name' )

This is the query that i use now to get the foreign keys

select syssc.name as schemaname ,cast(c.name as  varchar(255)) as foreign_table ,
cast(p.name as varchar(255)) as primary_table
from sysobjects f
inner join sysobjects c on  f.parent_obj = c.id
inner join sysreferences r on f.id =  r.constid
inner join sysobjects p on r.rkeyid = p.id
inner  join syscolumns rc on r.rkeyid = rc.id and r.rkey1 = rc.colid
inner  join syscolumns fc on r.fkeyid = fc.id and r.fkey1 = fc.colid
left join  syscolumns rc2 on r.rkeyid = rc2.id and r.rkey2 = rc.colid
left join  syscolumns fc2 on r.fkeyid = fc2.id and r.fkey2 = fc.colid
inner join sys.tables syst on rc.id=syst.object_id
inner join sys.schemas syssc on syst.schema_id=syssc.schema_id
where  f.type =  'F' and p.name in ('table name')

Kindly check whether these queries are the right ones and the efficient ones.


texts: How do I determine how much data is being written per day through insert, update and delete operations?

texts:
The longevity of SSDs is largely determined by the amount of bytes written by insert, update and delete operations. What is the best way to accurately determine how much data is being written by MariaDB 5.5 on a daily basis so that I can use this to estimate the possible longevity of SSDs if used in a heavy write database environment?

Current setup is that all tables are InnoDB. Can I use Innodb_data_written and Uptime to determine a rough idea of bytes written per day, or is there a better way of doing it?


texts: Mongodb won't start after adding 2nd net option to mongod.conf file

texts:
I try to add another net with the its corresponding bind ip and port to my AMI ec2 mongod.conf file so I will be able to create a 2 node replica set but when I run the command "sudo service mongos restart", mongos won't start. Here is my mongos.conf file:

# mongod.conf

# for documentation of all options, see:
#   http://docs.mongodb.org/manual/reference/configuration-options/

# where to write logging data.
systemLog:
  destination: file
  logAppend: true
  path: /var/log/mongodb/mongod.log

# Where and how to store data.
storage:
  dbPath: /var/lib/mongo
  journal:
    enabled: true
#  engine:
#  mmapv1:
#  wiredTiger:

# how the process runs
processManagement:
  fork: true  # fork and run in background
  pidFilePath: /var/run/mongodb/mongod.pid  # location of pidfile
  timeZoneInfo: /usr/share/zoneinfo

# network interfaces
net:
  port: 27017
  bindIp: 172.31.90.193 # Enter 0.0.0.0,:: to bind to all IPv4 and IPv6 addresses or, alternatively, use the net.bindIpAll setting.
net:
  port: 27017
  bindIp: 172.31.92.31
#security:

#operationProfiling:

replication:
 replSetName: "Midterm"

#sharding:

## Enterprise-Only Options

#auditLog:

#snmp:

Does anyone know why this is happening? I tried doing this in a vm using vagrant and it worked. Why would be any different in AMI ec2?

**UPDATE:

This is what the security group of one of my instances looks like:


texts: sql server query execution plan via jdbc driver

texts:
is there a way to fetch the XML generated by the "Actual Execution Plan" in SQL server 2012 - 2014, but via JDBC driver for Java -- sqljdbc_6.0.8112.100_enu? (preferable forward compatible with 2016)

in reference to https://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan
-->SET STATISTICS XML ON


texts:
I am currently working on structuring out a database in MySQL Workbench to keep track of expenses for Truck drivers that would be turned into an application using Java.

Objective:

Create a report of expenses in a table format
View or Edit past expenses
Display data Yearly report
        Total Company Salary
        Total Gas Expense
        Total Toll Expense
        Total Miscellaneous Expense

Input Needed:

Enter the Company Income earned weekly
Enter the Gas Expense weekly
Enter Toll Expense Weekly
Enter Miscellaneous expense weekly
        Create a Miscellaneous category

Calculation:

Get Personal Salary =
        Company Income - (Gas Expense + Toll Expense + Miscellaneous Expense)

Result:

Display information in a table

Right now the issue is structuring out MySQL tables.
Currently I have:

The portion of the results and calculations will be done in Java but I would like to know if this table structure is correct? I also want the user to input the type of miscellaneous expense.


texts: Specify file target filename dynamically

texts:
I need to create an Extended Event session to capture all the executed queries while my server is running and save them in a file under a folder with the database name.

DECLARE @file varchar(50);
declare @path nvarchar(250);
SET @file = 'D:\XE\ ' + DB_NAME()
set @path = 'D:\XE\ ' + DB_NAME() + 'day';
EXECUTE master.dbo.xp_create_subdir @file
CREATE EVENT SESSION newSession1
ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(    ACTION (sqlserver.sql_text, sqlserver.tsql_stack,
     sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.username )
    WHERE ( sqlserver.database_name = 'School' )
    ),
ADD EVENT sqlserver.sql_statement_starting
(    ACTION (sqlserver.sql_text, sqlserver.tsql_stack,
     sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.username )
    WHERE ( sqlserver.database_name = 'School')
    )
ADD target package0.event_file (SET filename = @path) -- problem
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,
  MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

This code shows an error in set Filename = @path.


texts: How to export the output of sp_AskBrent?

texts: We have an instance that randomly spikes the CPU. I want to create an alert that fires on CPU over 90% and automatically calls a job that runs sp_AskBrent and emails me the output. However, the output is unreadable in either Text or HTML output. It doesn't even go well into an Excel spread sheet. How can I get the info in a readable format?


texts: Shouldn't Windows Server Failover Clustering already take care of SQLServer Failover Clustering?

texts:
After reading this question, Are "Always On Failover Cluster Instances" and "SQL Server Failover Clustering" the same thing?

If Windows Server Failover Clustering is installed, why would it not allow failover of SQL server?

What would be the effects of having Windows Server Failover Clustering, without SQL AlwaysOn FailoverClustering? Meaning in case of failover, would the second Sql server node would not operate?

Does this mean AlwaysOn Failover Clustering is an additional Installation package which needs to be installed, because Windows Server Failover Clustering did not do its job correctly?

I always inquired about this, seems like Windows Server Failover Clustering should encompass the whole VM, and not leave SQL server out of the picture.


texts:
I am going to restore my MySQL database table using log files. My database name is "decsys" .

I have already created sql using log files

C:\wamp\bin\mysql\mysql5.5.8\bin>mysqlbinlog  -d decsys C:\wamp\bin\mysql\mysql5.5.8\data\mysql-bin.0000xx > sql1.sql (file size 8GB)

I want recover not whole database, only single table. (table name :cm).


texts:
I have a table that stores sensor data (in SQLite) and looks like this;

TABLE Timestream:
idTimestream PK autoincrementing integer,
time int not null,
value float not null,
idSensor integer not null FK

Some but not all sensors have matching times, but I'll only be considering those that do. What I want to do is rearrange the table into the following format,based on a set of sensors listed in the query, not whole set from the table:

Time Sensor1 Sensor2 etc.

I was thinking of Creating a temporary table and then inserting time and the first column, then doing a join on the time for subsequent queries, and finally selecting the whole lot. This doesn't sound too efficient though and I was wondering if there was a better way?


texts:
I have three tables, call them table1, table2 and table3. table1 has a foreign key column referencing table2,
and table2 has a column referencing table3. Both foreign key columns have a B-tree index over just that column;
in addition, table1 has an additional composite index over two columns: (table2_fk, another_column). The tables also have
numerous other indexes on their other columns (table1 has five other indexes, each over a single column).

table1 has 67 million entries, table2 has 7400.

The problematic query is

(a) SELECT *
    FROM table1
    WHERE table2_fk IN (SELECT id FROM table2 WHERE table3_fk = 3);

This for some reason runs for several minutes. The two component queries,

(b) SELECT id FROM table2 WHERE table3_fk = 3;
(c) SELECT * FROM table1 WHERE table2_fk IN (13, 14);

both complete instantly. The query (b) returns two IDs (13 and 14). The queries (a) and (c) return 16 rows.

Results of query explainer (with table/column/index names replaced with the above-mentioned aliases) look as follows (edit: added EXPLAIN (ANALYZE, BUFFERS))

db=# explain (ANALYZE, BUFFERS) select * FROM table1 WHERE table2_fk IN (13, 14);
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on table1  (cost=1869.86..313991.96 rows=98673 width=193) (actual time=0.033..10.826 rows=18 loops=1)
   Recheck Cond: (table2_fk = ANY ('{13,14}'::integer[]))
   Heap Blocks: exact=6
   Buffers: shared hit=9 read=5
   I/O Timings: read=10.614
   ->  Bitmap Index Scan on table1_table2_fk_idx  (cost=0.00..1845.19 rows=98673 width=0) (actual time=0.016..0.016 rows=18 loops=1)
         Index Cond: (table2_fk = ANY ('{13,14}'::integer[]))
         Buffers: shared hit=8
 Planning time: 49.277 ms
 Execution time: 10.868 ms
(10 rows)

db=# explain (ANALYZE, BUFFERS) select * FROM table1 WHERE table2_fk IN (SELECT id FROM table2 WHERE table3_fk = 3);
                                                                     QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Semi Join  (cost=15.16..2417375.77 rows=2153302 width=193) (actual time=39974.333..127352.240 rows=18 loops=1)
   Hash Cond: (table1.table2_fk = table2.id)
   Buffers: shared hit=74 read=1536993
   I/O Timings: read=99114.790
   ->  Seq Scan on table1  (cost=0.00..2215354.00 rows=67829000 width=193) (actual time=0.005..119121.005 rows=67398453 loops=1)
         Buffers: shared hit=71 read=1536993
         I/O Timings: read=99114.790
   ->  Hash  (cost=15.11..15.11 rows=4 width=4) (actual time=0.009..0.009 rows=2 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 1kB
         Buffers: shared hit=3
         ->  Index Scan using table2_table3_fk_idx on table2  (cost=0.28..15.11 rows=4 width=4) (actual time=0.006..0.007 rows=2 loops=1)
               Index Cond: (table3_fk = 3)
               Buffers: shared hit=3
 Planning time: 0.441 ms
 Execution time: 127352.285 ms
(15 rows)

I.e query (c) uses the index on table2_fk, but query (a) for some reason does not, performing an
inefficient sequential scan instead. What could be causing this?

The same database structure exists on many computers (some of which have even more entries in table1),
but query (a) is slow only on one machine.

I have tried re-indexing and vacuum/analyze with no help. The behavior didn't change when selecting
a single column instead of "select *", or when re-writing (a) using JOIN.


texts: Query stuck in mattermost postgresql communication

texts:
Ubuntu 18.04
PostgreSQL 10.10
Mattermost 5.15

On mattermost startup process stay forever on

  {"level":"info","ts":1570609055.2422519,"caller":"sqlstore/supplier.go:223","msg":"Pinging
  SQL master database"}

Running this query

SELECT pid, age(clock_timestamp(), query_start), usename, query, state
FROM pg_stat_activity
WHERE query != '' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;

Gives me

 pid  |      age       | usename |         query         | state
------+----------------+---------+-------------------------------
 8700 | 00:08:54.84669 | mmuser  | SELECT * FROM Systems | idle

From there, how do I diagnose what makes this query never end ?


texts: Logical operators OR AND in condition and order of conditions in WHERE

texts:
Let's examine these two statements:

IF (CONDITION 1) OR (CONDITION 2)
...

IF (CONDITION 3) AND (CONDITION 4)
...

If CONDITION 1 is TRUE, will CONDITION 2 be checked?
If CONDITION 3 is FALSE, will CONDITION 4 be checked?

What about conditions on WHERE: does the SQL Server engine optimize all conditions in a WHERE clause? Should programmers place conditions in the right order to be sure that the SQL Server optimizer resolves it in the right manner?

ADDED:

Thank to Jack for link, surprise from t-sql code:

IF  1/0 = 1 OR 1 = 1
      SELECT 'True' AS result
ELSE
      SELECT 'False' AS result

IF  1/0 = 1 AND 1 = 0
      SELECT 'True' AS result
ELSE
      SELECT 'False' AS result

There is not raise a Divide by zero exception in this case.

CONCLUSION:

If C++/C#/VB has short-circuiting why can't SQL Server have it?

  To truly answer this let's take a look at how both work with
  conditions. C++/C#/VB all have short circuiting defined in the
  language specifications to speed up code execution. Why bother
  evaluating  N OR conditions when the first one is already true or M
  AND conditions when the first one is already false.

  We as developers have to be aware that SQL Server works differently.
  It is a cost based system. To get the optimal execution plan for our
  query the query processor has to evaluate every where condition and
  assign it a cost. These costs are then evaluated as a whole to form a
  threshold that must be lower than the defined threshold SQL Server has
  for a good plan. If the cost is lower than the defined threshold the
  plan is used, if not the whole process is repeated again with a
  different mix of condition costs. Cost here is either a scan or a seek
  or a merge join or a hash join etc... Because of this the
  short-circuiting as is available in C++/C#/VB simply isn't possible.
  You might think that forcing use of index on a column counts as short
  circuiting but it doesn't. It only forces the use of that index and
  with that shortens the list of possible execution plans. The system is
  still cost based.

  As a developer you must be aware that SQL Server does not do
  short-circuiting like it is done in other programming languages and
  there's nothing you can do to force it to.


texts: How to output to same line with relation information same table?

texts:
This is my current SQL Server query and almost what I want to do:

select "dokumentgruppe" + ', ' + convert(varchar(10), dokumentgruppenr)
    + ', ' + 'Parent:' + convert(varchar(10), parentgruppenr)
    as "Dokumentgruppe"
from "dbo"."dokumentgruppe"
where aktiv = '1'
order by "Dokumentgruppe"

It gives me this result:

Budsjetter, 29, Parent:0
E-post, 36, Parent:0
Fyllearrester, 40, Parent:38
Inngående, 42, Parent:36
JuksOgFanteri, 38, Parent:0
Legeattester, 39, Parent:38
Ligning, 26, Parent:0
Periodiske regnskapsrapporter, 30, Parent:0
Permanente opplysninger, 23, Parent:0
Produksjon Fakturering, 33, Parent:0
Produksjon Lønn, 32, Parent:0
Produksjon Regnskap, 31, Parent:0

The thing is, I want to show the actual name of the Parentgruppe instead of ParentgruppeID. The Parentgruppe has an ID and a corresponding name to it.

Like this example:

Legeattester, 39, Parent:*JuksOgFanteri


texts: Hierarchy of Employee "names" with CTE: Cannot create a correct version

texts:
I am trying to create the correct structure of my sql (CTE) query, but I can not. This is a version I want to have .

And this is my current version: . As you can see there are duplicates and i have no idea how to get rid of them. Futhermore the structure is wrong. I already tried to look for solutions like here but was unable to come up with solution.
CTE query doesn't print the right structure?

My Employee table looks like this http://sqlfiddle.com/#!6/428d2/2

Here is my SQL query I use to create the correct name column: http://sqlfiddle.com/#!6/bf4c1/4

I use MsSQL 2012, so feel free to use newest fuctions etc. Any help appreciated.


texts: How to make MySQL join one-to-many with lowest value?

texts:
I have following database structure

Domain has many URL which has many positions which belongs to phrase which belongs to user which belongs to subscription.

In subscriptions table I have period field and I need to fetch all domains with lowest period value from subscriptions. To accomplish this challenge I've created following query

 select
        domains.domain,
        domains.id,
        subscriptions.period

        from
            domains
        inner join urls ON urls.domain_id = domains.id
        inner join positions ON positions.url_id = urls.id
        inner join phrases ON phrases.id = positions.phrase_id
        inner join users ON users.id = phrases.user_id
        inner join subscriptions ON subscriptions.id = users.subscription_id

Which return me results similar to

"domain": "http://www.example.com",
"id":     60,
"period": 7

"domain": "http://www.example.com",
"id":     60,
"period": 30

"domain": "http://www.other-example.com",
"id":     61,
"period": 7

In the results one of the domains occurs more than on time, because two different user with varied subscription have the same domain.

How can I filter results and get only lowest period value for given domain?

"domain": "http://www.example.com",
"id":     60,
"period": 7

"domain": "http://www.other-example.com",
"id":     61,
"period": 7


texts: How can I view how long a query has spent waiting for locks, without a profiler?

texts:
I'm trying to diagnose a query that is intermittently taking a long time. I suspect it may be blocked trying to acquire a lock. I don't have permissions to use a profiler in the environment experiencing the issue.

Is there any way for me to obtain statistics on how long this individual query is blocked, without using an external profiler?


texts: Pros and cons of setting super admin in users table

texts:
I'm structuring a RBAC in a Web system. My database tables looks like:

actions

id
name

resources

id
name

permissions

id
action_id
resource_id

roles

id
name

permission_role

permission_id
role_id

users

id
role_id
is_super_admin

The roles are dynamics (users can CRUD them) so if I seed "super admin" role it could be deleted in future. In this way, the first solution that I found was to create a boolean column "is_super_admin" directly in users table and seed the first user (me) with it as true.

What would be possibly the advantages and disadvantages of setting the super admin (the user that can do anything) in users table?


texts: Applying a rule dynamically to all columns in different tables

texts:
I am trying to apply a rule to remove double quotes from any value in my DB.
I declared the table name as an Input parameter (This is required). Now I want the rule to run dynamically against all columns & all rows in the table. I have this so far. The rule runs successfully when executed but it only runs against the first column that appears in the information schema, How to make it run against the rest of columns ?

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_tf_RemoveDoubleQuotes]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].usp_tf_RemoveDoubleQuotes
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_tf_RemoveDoubleQuotes]
(
     @tablename VARCHAR(100)
)
AS
SET NOCOUNT ON;

-- Declare variables

DECLARE  @FieldName varchar(100)
        ,@sql varchar(1000)

BEGIN
    -- Get the table and field for validating

    SELECT   @FieldName = COLUMN_NAME
    FROM     INFORMATION_SCHEMA.COLUMNS
    WHERE    TABLE_NAME = @tablename

    -- Update field

    SET @sql = '
    UPDATE  ' + @tablename + '
    SET     ' + @FieldName + ' = (SELECT REPLACE(' + @FieldName + ',''"'',''''))'

    EXECUTE(@sql)

END
GO


texts: Remove Duplicate Entries from DB w/ Unique ID that is NOT Primary Key - Keep one of course!

texts:
I have a CDR Table with a "unique id" column which is not a Primary Key. During an upgrade, there were a few days where instead of one entry for a caller ringing a queue, there was multiple entries for this caller. All the entries have the same unique ID.

What I would like to do, is delete all entries with the same unique ID, but leaving one entry. Is there a simple way to do this? All my searching turned up results on either, DB's with no unique ID, or with a Primary key. I have both a unique ID and No Primary key lol.

Thanks for your guidance!

PS: MySQL 5.5

Eg:


texts: Handling sql column with mixed varchar and float data types

texts:
We have some Excel spreadsheets with parcel numbers for properties that should be in a standard varchar format similar to:

[Parcel # 1]          [Building Address]
038-60-171-16-000     1750 W 4th St
038-60-238-09-000     2003-2027 W 4th St
01-15393              201 5th St NE
01-13504              101 5th St SE
25-20-00-03-008.501   1800 E 5th St
36-102-0700-000       2932 S 5th St
280615                2600-B 6th St SW
31-0101.000           1202 16th St
054-259620-00.000     843 N 21st St

Unfortunately we have some parcel numbers that were converted to scientific notation. This means they are in the wrong format for Parcel Numbers and should be converted back to regular decimal or numeric format. I am encountering difficulty converting just those values that have the scientific notation. The column is of nvarchar(255) data type but these values need to be manipulated back into numeric or decimal data type.

Parcel Number 1(Min)    Building Address
3.4007000221e+015   247 S Burnett Rd
3.1844401004e+013   433 N Cleveland Ave
3.4007000222e+016   2415 Lexington Ave
E020-0010-006-0211  7271 N Main St
3.6360705006e+013   825 W Market St
3.4007000353e+015   148 W North St
1.40109139e+011     4453-4461 SR-159
16-B-029-E-00-004   7314-7352 Industrial Park Blvd

The above table was retrieved with the following SELECT statement

SELECT [Parcel Number 1]
,[Building Address]
FROM XLSimport
WHERE [Parcel Number 1] like '%e%'

My understanding is that I should be able to cast the value to a decimal when it's in scientific notation. However, other parcel numbers that have a legitimate 'E' need to remain the same. How can I account for the different 'e' values and still convert the parcel numbers in scientific notation back to their standard parcel number format?

I frequently run into the error converting nvarchar to numeric error messages and I'm wondering if it's because of the parcel numbers that have an E but are not in scientific format.

For instance I run this query:

SELECT
CASE
    WHEN [Parcel Number 1] like '%E+%' THEN CAST(CAST([Parcel Number 1] AS FLOAT) AS DECIMAL)
    ELSE [Parcel Number 1]
END
FROM XLSimport

I then get the following error:

Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.


texts: Substract two database entries based on value of a certain field

texts:
Good evening everyone,

I am looking for the most elegant way to substract two values of one column based on the value in another column. Until now I could only find hacky and unreadable solutions:

I have a table containing entries when orders on an exchange were opened and closed. It looks like this:

|| DATETIME || ORDERID || ACTION || TYPE || PRICE || ...
  2018-01-01   a34hsbf    OPEN      LONG    0.23
  2018-01-02   ghs7fa0    OPEN      SHORT   0.45
  2018-01-03   a34hsbf    CLOSE     LONG    0.47
  ...

Now I want to return a new table containing the differences between entries with the same ORDERID. There can always only be two entries with the same ORDERID. The new table should be indexed by the greater DATETIME of those two entries. Also the difference should take into account if the TYPE was LONG or SHORT. E.g. for LONG a rising price must give a positive difference, for SHORT it must give a negative difference and vice versa.

The new table should look like the following:

|| DATETIME || ORDERID || TYPE || DIFF ||
  2018-01-03   a34hsbf    LONG    0.24
  ...

Many thanks in advance for your help. :)


texts: Security permission to only read SSAS Cube with Excel

texts:
I have an SSAS database set up on a SQL Server 2008 server.  I have a Role set up with a group of users that I want to be able to access the Cubes in that database.  I set up "Read definition" and "Read Access" wheverever it says, but that didn't seem to work.

The users were able to open up a pivot table that I had previously set up, but none of the hours would show up, but the dimension data would show up just fine (users, departments, etc).

I then set up an individual user and gave them Full control (Admin), which worked just fine.  What am I missing that allows them to access the Cube properly?  Process database?  Something else?


texts: PostgreSQL Cluster vs PostgreSQL Master-Slave Replication

texts:
I want to setup PostgreSQL and PostGIS to run some experiments (geospatial queries).

If I setup a PostgreSQL cluster, this means that the read requests will be distributed across the nodes of the cluster leading to a better performance?

In case of master-slave replication, that is a process of copying data from a database on one server to a database on another server, the main benefit is the distribution of databases to multiple machines, so when the master server has a problem, there is a backup machine with same data available for handling requests without interruption. If I undestand right, the reads will always goes to masternode and when master is down, slave can take it's place.

In PostgreSQL Cluster what is the situation?


texts: mysqldbcompare giving Compare table checksum FAIL error

texts:
Running mysqldbcompare on two test databases on same server. There is no data discrepancy in any table but showing following message which I'm not able to understand. Any help is appreciated.

TABLE         a                                     pass    pass    -
            - Compare table checksum                                FAIL
            - Find row differences                                  pass

TABLE         b                                      pass    pass    -
            - Compare table checksum                                FAIL
            - Find row differences                                  pass


texts: Multiple Primary-Foreign Key connections between Tables Redundant?

texts:
I have two tables, Reviews and Critic

I'm pretty new to RDBMS and I was wondering if the connection between Review.rID and Critic.Review is redundant?
My reasoning is that a single critic can have many reviews but each review is unique so I need a way to enforce the uniqueness of the Reviews. I did this via Review.rID which is unique. I also need to express that multiple critics can leave multiple reviews.
However, I am already connecting the critic to the review by including a criticID within Review, so is it necessary for me to also connect a reviewID to a critic within Critic?


texts: Will I need to create a pivot or can I alter this query

texts:
The following Query that I wrote is working to a point.  The problem that I found is that in each pump, cable, etc can be listed in each of the part columns.  Example:

What I would like to get is the well listed with each of the pumps, cables, etc that are there instead of what I am currently getting which is just the first pump, cable, etc.

Here is the query I am running:

        With CTE AS(
SELECT Pull_Date, Well_Name, Part1 Part, Part1_PN PartPN, Part1_SN PartSN FROM testtest
UNION ALL
    SELECT Pull_Date, Well_Name, Part2, Part2_PN, Part2_SN from testtest
UNION ALL
    SELECT Pull_Date, Well_Name, Part3, Part3_PN, Part3_SN from testtest
)

Select Pull_Date, Well_Name,
    MIN(CASE WHEN Part='BODH' THEN 'BODH' ELSE NULL END) [BODH],
    MIN(CASE WHEN Part='BODH' THEN PartPN Else NULL END) BODH_PN,
    MIN(CASE WHEN Part='BODH' THEN PartSN ELSE NULL END) BODH_SN,
    MIN(CASE WHEN Part='Cable' THEN 'Cable' ELSE NULL END) [Cable],
    MIN(CASE WHEN Part= 'Cable' THEN PartPN Else NULL END) Cable_PN,
    MIN(CASE WHEN Part= 'Cable' THEN PartSN ELSE NULL END) Cable_SN,
    MIN(CASE WHEN Part= 'Pump' THEN 'Pump' ELSE Null END) [Pump],
    MIN(CASE WHEN Part= 'Pump' THEN PartPN ELSE NULL END) Pump_PN,
    MIN(CASE WHEN Part= 'Pump' THEN PartSN ELSE NULL END) Pump_SN,
    MIN(CASE WHEN Part= 'MLE' THEN 'MLE' ELSE NULL END) [MLE],
    MIN(CASE WHEN Part= 'MLE' THEN PartPN ELSE NULL END) MLE_PN,
    MIN(CASE WHEN Part= 'MLE' THEN PartSN ELSE NULL END) MLE_SN,
    MIN(CASE WHEN Part= 'AGH' THEN 'AGH' ELSE NULL END) [AGH],
    MIN(CASE WHEN Part= 'AGH' THEN PartPN ELSE NULL END) AGH_PN,
    MIN(CASE WHEN Part= 'AGH' THEN PartSN ELSE NULL END) AGH_SN
FROM CTE
GROUP BY Pull_Date, Well_Name

And here is an example of what I am currently receiving:

I am not very familiar with SQL Server PIVOT or if this is even needed in this case.

I appreciate any assistance on this and thank you in advance.

EDIT:

Unfortunately I cannot add a picture of what I would like so I will try to explain it.

I would like to have it all in one row so it would look somewhat like this:

Pull Date| Well_Name | BODH1 | BODH1_PN | BODH1_SN | BODH2 | BODH2_PN | BODH2_SN

Pump, Cable, MLE, etc would also repeat.  I have 20 part columns and would like to pull them so I could easily sort the pump1 through possibly pump5 (some wells have 5 some less).  Eventually this data will be inserted into another table to update it.

EDIT 2:

Link to test data I created: SQLFiddle


texts: How many threads being used while query execution?

texts:
I have AdventureWorks2014 database installed on my test SQL Server 2014. I am planning to execute the following query:
SELECT *
FROM Sales.SalesOrderDetail sod
INNER JOIN Production.Product p ON sod.ProductID = p.ProductID
ORDER BY Style

Before actually executing it, I have the following picture of my worker threads:

While query execution, I again checked the state of worker threads:

As you can see, total active_workers_count in the 2nd picture is higher than that of in the 1st screenshot. From the 2nd screenshot I can assume that total worker threads needed for query execution was 1+2+1+2=6. However when I take a look at properties of index scan operator in my execution plan, I see:

It seems that my degree of parallelism was 4 which means that 4 threads were parallelly processing rows.
I am confused and can't determine how many threads were running during query execution. Can someone explain to me why active_workers_count is different from DOP?


texts: What All Is Factored Into A MySQL Checksum? (Why Could They Be Different?)

texts:
I'm currently going through the excruciating task of moving MySQL databases from one shared web hosting environment to another.

This has been a multi-month battle (and I'm learning a lot). After having issues with PHPMyAdmin due to large tables (and getting annoyed having to always check it had moved the data correctly when it, in fact, was silently failing), and experiencing trouble with every other package I could find to help (since I have some large tables that have to be chunked and some packages would not let me exclude corrupt tables properly), I've custom coded my own solution in PHP and AJAX to migrate data from the old host to the new host. As a bonus, I can now finally have a working solution to back up the data as well.

I've overcome many challenges, and at this point I almost have things fully working. The checksum feature is super handy to be able to know if I moved the data across correctly (or there was some problem) without checking row by row (which would take a really long time on larger tables).

In order to do a checksum, I run this query:

CHECKSUM TABLE `<tablename>`

On both the old web host/database (accessed remotely) and the new web host/database (accessed locally). For most tables, these numbers match.

My transfer process copies the table structure (SHOW CREATE TABLE) then copies the data row by row. Larger tables are chunked, moved across, then reassembled. The entire process is fully automated through AJAX calls.

However, I'm running into some problems where some of the tables have a different checksum after having been moved across. The problem is, both the structure and data appear to be exactly intact. When I create a PHP script to check row by row, it reports that every row matches in the old and new databases. This is my script to check the tables:

  $qGetTable = "SELECT * FROM `".$sTableName."`";
  if (!($rGetTableOld = $sqlOld->query($qGetTable))) {
    die("Failed to get table in old database! ".$sqlOld->error);
  }
  if (!($rGetTableNew = $sqlNew->query($qGetTable))) {
    die("Failed to get table in new database! ".$sqlNew->error);
  }

  while ($aGetTableOld = $rGetTableOld->fetch_assoc()) {
    if (!($aGetTableNew = $rGetTableNew->fetch_assoc())) {
      print_r($aGetTableOld);
      die("Ran out of rows in new table!");
    }
    if ($aGetTableOld != $aGetTableNew) {
      echo "Found the mismatch!\n";
      print_r($aGetTableOld);
      print_r($aGetTableNew);
    }
  }

As you see, I'm even checking row by row so even order changes should be discovered. It does not find any mismatches. SHOW CREATE TABLE gives the exact same response on both servers. (Double checked with DiffChecker.)

As per, https://dev.mysql.com/doc/refman/5.7/en/checksum-table.html

  If the checksums for two tables are different, then it is almost
  certain that the tables are different in some way.

However, it's quite vague about what to check to troubleshoot any difference.

  CHECKSUM TABLE reports a checksum for the contents of a table. You can
  use this statement to verify that the contents are the same before and
  after a backup, rollback, or other operation that is intended to put
  the data back to a known state.

I noted one thing, but I'm not using TIME, DATETIME, or TIMESTAMP. I always store time in plain UNSIGNED INT.

  The checksum value depends on the table row format. If the row format
  changes, the checksum also changes. For example, the storage format
  for temporal types such as TIME, DATETIME, and TIMESTAMP changed in
  MySQL 5.6 prior to MySQL 5.6.5, so if a 5.5 table is upgraded to MySQL
  5.6, the checksum value may change.

I can't figure out what is different since my data looks fine and intact. However, the different checksum bothers me a lot and I want to understand what could possibly be different. Only a few tables are affected and the rest seem to work fine. Does anyone have any insight into what I should be checking or looking into to get to the bottom of this?

Thanks very much and I really appreciate your help.


texts: How to delete control file in oracle 11g

texts:
I am practicing the rman backup in oracle 11g.and how to dalete the control file. Please help.

rm -rf \oracle\app\oracle\oradata\xe\control.dbf

it did not work at hosting server.


texts: Performing frequent backups of a large MySQL slave

texts:
We replicate our production MySQL databases (120 GB on production) to a dedicated local desktop machine.  The local slave is a 2014-era machine with 8 GB of RAM.

A full mysqldump of the databases on the slave takes just under 48 hours.  The resulting dumps, after zipping, are 5.4 GB.  This process yields usable dumps, but the duration of the dump process prevents us from doing daily dumps of the database.

At most, it seems that we can only expect to achieve a full dump every other day with our current setup.  Something needs to change in our approach in order to facilitate daily dumps again.

One possibility is simply adding another slave and having the two slaves alternate days of dumping.

A different approach (which I loathe, but could technically work) would be to do a full dump every, say, week, and then also keep all of the binary logs in case more fine-grained data is needed.  (We could restore the weekly dump and then execute the binary logs up until a certain timestamp)

I suppose a totally different approach would be to use a RAID array and hot-swap out drives to effectively get a "snapshot" of the slave at the time the drive was pulled.  (This seems like it may work but also feels quite risky and like a gross abuse of what RAID is supposed to do)

Another completely different approach would be to simply "prioritize" certain tables/databases that are dumped every day, while other "less important" tables/databases are dumped every week.

What am I missing?  Is there a "canonical" solution for archiving daily snapshots of "large" MySQL slaves?  This seems like something that would be encountered quite frequently by those maintaining larger databases.

Is the best option really to just continue throwing more hardware at the problem?


texts: Is it possible to join on column placeholders?

texts:
I have a query below in which i was wondering whether it is possible to join on my placeholders and if so are my joins in my CTE's correct?

WITH
DCM AS (SELECT
date,
placement as creative,
SUM(clicks) as engagements,
'' AS advertiser,
'' AS advertiser_currency,
'' AS insertion_order,
'' AS campaign,
'DCM' AS platform,
0 AS spend,
0 AS impressions,
0 AS clicks
FROM `crate-media-group-client-data.DCM_ALL.GOOGLECM_AD_*`
GROUP BY 1,2,4,5,6,7,8),
CELTRA AS (SELECT date,
placement as creative,
SUM(engagements) as engagements,
'' AS advertiser,
'' AS advertiser_currency,
'' AS insertion_order,
'' AS campaign,
'CELTRA' AS platform,
0 AS spend,
0 AS impressions,
0 AS clicks
FROM `crate-media-group-client-data.CRATE_CLIENT_DATA_ALL.CELTRA_DATA`
GROUP BY 1,2,5,6,7,8),

DCM_AND_CELTRA AS ( SELECT date,
creative,
advertiser,
advertiser_currency,
insertion_order,
campaign,
platform,
SUM(COALESCE(DCM.spend,0) + COALESCE(CELTRA.spend,0)) as spend,
SUM(COALESCE(DCM.impressions,0) + COALESCE(CELTRA.impressions,0)) as impressions,
SUM(COALESCE(DCM.engagements,0) + COALESCE(CELTRA.engagements,0)) as engagements,
SUM(COALESCE(DCM.clicks,0) + COALESCE(CELTRA.clicks,0)) as clicks
FROM DCM
FULL JOIN CELTRA
USING (date, creative, advertiser, advertiser_currency, insertion_order, campaign, platform, spend, impressions, engagements, clicks)
GROUP BY 1,2,3,4,5,6,7),

DV360 AS (SELECT
advertiser,
advertiser_currency,
date,
insertion_order,
creative_name as creative,
'' AS campaign,
'DV360' AS platform,
SUM (revenue) as spend,
SUM(impressions) as impressions,
SUM(clicks) as clicks,
0 AS engagements
FROM `crate-media-group-client-data.DV360_ALL.GGLDV360BM_CREATIVE_*`
GROUP BY 1,2,3,4,5,6,7)

SELECT
advertiser,
advertiser_currency,
date,
insertion_order,
CASE
WHEN insertion_order LIKE '%Topman_Pro_MOB_Display_AW19  Student Promo%' THEN 'Topman AW19'
WHEN insertion_order LIKE '%Topman_Pro_MOB_Display_AW19 New Season%' THEN 'Topman AW19'
WHEN insertion_order LIKE '%Topman_Pro_MOB_Display_AW19 Value Led%' THEN 'Topman AW19'
WHEN insertion_order LIKE '%Topshop_Pro_MOB_Display_AW19  Student Promo%' THEN 'Topshop AW19'
WHEN insertion_order LIKE '%Topshop_Pro_MOB_Display_AW19 New Season' THEN 'Topshop AW19'
WHEN insertion_order LIKE '%Topshop_Pro_MOB_Display_AW19 Value Led%' THEN 'Topshop AW19'
WHEN insertion_order LIKE '%Topshop_Dresses_Pro_DSK_MOB_Display_GENERIC%' THEN 'Topshop Dresses'
WHEN insertion_order LIKE '%Topshop_Pro_MOB_IDOL_Display%' THEN 'Topshop IDOL'
WHEN insertion_order LIKE '%Topshop_Pro_MOB_IDOL_Video%' THEN 'Topshop IDOL'
ELSE 'NOT SET'
END AS campaign,
creative,
platform,
spend,
impressions,
clicks,
engagements
FROM DV360
FULL JOIN DCM_AND_CELTRA
USING (advertiser,
advertiser_currency,
date,
insertion_order,
campaign,
creative,
platform,
spend,
impressions,
clicks,
engagements)


texts: Stored procedure with case statement for 'AND' based on parameter value

texts:
I have a table in my db that stored both successful and failed log-in attempts. I am creating a stored procedure that allow us to delete records which are older than X days old. So far so good, but I want to raise it a notch (or two) and allow us to specify whether to delete records on whether the [Success] column is true, false or both. I am having some issues with the concatenation of the script that needs to be executed, though.

Here is what I did so far:

-- CREATE PROCEDURE [dbo].[sp_delete_log_attempts]
DECLARE @backDays INT = 1 -- Default to 30 days (one test finishes)
DECLARE @successArg BIT = NULL -- default to both true and false success logins
DECLARE @successAnd VARCHAR(50)
DECLARE @query VARCHAR(MAX)

SET @successAnd = CASE
WHEN @successArg = 'true' THEN
    'AND [Success] = ''true'''
WHEN @successArg = 'false' THEN
    'AND [Success] = ''false'''
ELSE
    'AND [Success] = ''true'' OR [Success] = ''false'''
END

PRINT @successAnd -- just for debugging purposes

SET @query = 'SELECT * FROM [audit].LoginAttempt WHERE [TimeStamp] <= DATEADD(day,-' + @backDays + ', GETDATE())'
EXEC @query

At this point I am just trying to select the rows based on the @backDays variable, but I cannot concatenate the @query string with the variable. Not sure of what I am doing wrong here. I am fairly new to dynamic queries, though.


texts: "Specify SQL Server administrators" - unusual default value

texts:
When installing SQL Server, you get to specify SQL Server administrators, i.e., the list of users initially in the sysadmin role. By default, this list contains the current user.

(Image quoted from https://msdn.microsoft.com/en-us/library/dd578652.aspx.)

This default value strikes me as odd. Wouldn't BUILTIN\Administrators be a more sensible choice than the current user? After all, isn't the whole point of Windows authentication not having to manage two separate user/group directories? And local administrators can gain sysadmin rights anyway, if they want to, so it's not really a security feature either.

On the other hand, SQL Server was written by people much smarter than I am, so there might actually be a very good reason for this default value. What is it?


texts:
I was running performance_schema query to check for users performing DDL on our databases, and found a NULL user, which however does not exists when i check the mysql database for users using the query below:

select user, host from user;

The query that is showing the NULL user accounts when using performance_schema is:

SELECT user, host, SUBSTR(event_name, 15) AS event_name, count_star
  FROM performance_schema.events_statements_summary_by_account_by_event_name
 WHERE count_star > 0 AND
     ( event_name LIKE 'statement/sql/create%'
    OR event_name LIKE 'statement/sql/alter%'
    OR event_name LIKE 'statement/sql/drop%'
    OR event_name LIKE 'statement/sql/rename%'
    OR event_name LIKE 'statement/sql/grant%'
    OR event_name LIKE 'statement/sql/revoke%'
    OR event_name LIKE 'statement/sql/optimize%'
    OR event_name LIKE 'statement/sql/truncate%'
    OR event_name LIKE 'statement/sql/repair%'
    OR event_name LIKE 'statement/sql/check%'
     );

Results:

+--------------+---------------+------------------+------------+
| user         | host          | event_name       | count_star |
+--------------+---------------+------------------+------------+
| NULL         | NULL          | create_table     |         50 |
| NULL         | NULL          | alter_table      |         79 |
| NULL         | NULL          | truncate         |         10 |
| NULL         | NULL          | drop_table       |         25 |
| NULL         | NULL          | grant            |          6 |
| NULL         | NULL          | create_db        |          2 |
| NULL         | NULL          | rename_table     |          6 |
| NULL         | NULL          | create_user      |          6 |
| NULL         | NULL          | drop_user        |          6 |
| NULL         | NULL          | create_trigger   |         18 |
| NULL         | NULL          | drop_trigger     |         18 |
| root         | localhost     | create_table     |        919 |
| root         | localhost     | alter_table      |       1918 |
| root         | localhost     | truncate         |         13 |
| root         | localhost     | drop_table       |        850 |
| root         | localhost     | grant            |          3 |

So what is this NULL user account?


texts: How to backup a secondary log shipped standby mode database

texts:
We have a secondary transaction log shipped database configured for standby mode. Its primary purpose is for data recovery and for select only queries for reports etc.

As the database is very big we were hoping to be able to do a READ_WRITE file backup (the larger tables like audit trail history etc. are on a secondary file group) for us by developers who need a copy of live when investigating recent issues.

After trial and error and researching so far the only options I can see are:

Do a restore with recovery and take a backup - but this breaks log shipping and we have to set it up again
Take the secondary DB offline and physically take a copy the actual underlying databases data and log files - but then I still have to run a truncate table on the data we don't want/need and shrink to get it down to a manageable size

Has anyone had any luck with other options, 3rd party tools etc?


texts: How to create a trigger that checks if a value in 2 fields is present and if it is change it to something else

texts:
I am new to db, and trying to create a trigger that checks some fields in a table for a value and if the value is present than it updates it to something else. These are my thoughts so far.

CREATE TRIGGER check_Load
AFTER UPDATE ON PODIUM_CORE
FOR EACH ROW ( WHERE PODIUM_CORE.SNAME OR PODIUM_CORE.INTERNAL = "LOAD")
UPDATE PODIUM_CORE.SNAME = "LOAD_GENERAL";

EXECUTE PROCEDURE check_Load();


texts: multiple case statements using multiple columns

texts:
I have two columns in a table , say "colA", "colB" and "colC" need to be computed.

Case 1: Here, colC is a combination of colA and colB with delimiter as space.

For example , if colA = 'abc' and colB = 'xyz' then colC = 'abc xyz'.

Case 2: Here , if either of the colums (colA , colB) is null then colC should contain the non-null value among colA and colB.

For example , if colA = null and colB = 'xyz' then colC = 'xyz'. if colA = 'abc' and colB = null then colC = 'abc'.

How can I achieve this in a select statement using CASE or some other best approach?


texts: increment row and calculate amount - follow up question

texts:
This is a follow up to my original question, but anyone can answer nonetheless.

Increment each row according to condition in SQL C#

 WITH upd AS
   ( SELECT
         [Bill No],
         Item_Name,
         Stock_in,
         Stock_out,
         stock_in_hand,
         sum_stock = COALESCE(
             SUM(stock_in_hand) OVER (ORDER BY Item_Name, [Bill No]
                                      ROWS BETWEEN UNBOUNDED PRECEDING
                                               AND 1 PRECEDING)
             , 0)
     FROM
         stock_recieve
   )
 UPDATE upd
 SET Stock_out = Stock_out
         + CASE WHEN 100 - sum_stock >= stock_in_hand
               THEN stock_in_hand
               ELSE 100 - sum_stock
           END,
     stock_in_hand = stock_in_hand
         - CASE WHEN 100 - sum_stock >= stock_in_hand
               THEN stock_in_hand
               ELSE 100 - sum_stock
           END  WHERE sum_stock < 100 ;

The code worked perfectly Ypercube don't have enough reputation to vote up or comment otherwise would have done it, I changed the 100 to a textbox value and it worked like a charm, just one new thing I want to add to this code, I tried but could not figure it out with CTE. I have one more column with Cost_Price and the price is different each time the same item is entered, like in case chicken is 350$ and then again chicken is 450$. What I want to do is calculate item_value according to the no of quantity added in the stock_out column with the price in that particular row.

+----------+-----------+----------+-----------+---------------+------------+---------------+
|  Bill No | Item_Name | Stock_in | Stock_out | stock_in_hand | Cost_Price | Total_Price   |
+----------+-----------+----------+-----------+---------------+------------+---------------+
|        1 |   chicken |      100 |        40 |            60 |        350 |         14000 |
|        2 |   chicken |       40 |         0 |            40 |        450 |             0 |
+----------+-----------+----------+-----------+---------------+------------+---------------+

Result to be obtained. If from the value of 100 from our previous code, 60 is added according to your cte code in stock_out in first row then total price should be calculated according to 60 x 350 and if 40 was added to second row than 40 x 450.

+----------+-----------+----------+-----------+---------------+------------+-------------+
|  Bill No | Item_Name | Stock_in | Stock_out | stock_in_hand | Cost_Price | Total_Price |
+----------+-----------+----------+-----------+---------------+------------+-------------+
|        1 |   chicken |      100 |       100 |             0 |        350 |     21000   |
|        2 |   chicken |       40 |        40 |             0 |        450 |      18000  |
+----------+-----------+----------+-----------+---------------+------------+-------------+


texts: Get SQL server instance installation date

texts:
I would like to know the date when the sql server installed on the system.
I did try to look in %programfiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\ directory but there are so many files and folder not sure from where I can get the required information.


texts: Editing built-in templates in Management Studio

texts:
I'm running SQL Server Management Studio 2008 (10.0.5500.0) and was attempting to edit the built-in template "Create T-SQL Trigger (New Menu)". I open the Template Explorer, right-click the template, choose Edit, modify it, then save.

But when I try to use the template by right clicking "Triggers" underneath any table and selecting "New Trigger", it still uses the default template.

In Process Explorer, I can see it accessing this file when I edit the template:

C:\Users\username\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql\Trigger\Create T-SQL Trigger (New Menu).sql

When I try to use the template, it loads the script from this path:

C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql\Trigger\Create T-SQL Trigger (New Menu).sql

Is there a certain setting/patch to help it keep the file paths sorted out?


texts: Besides using SQL Server Profiler, is there any way to track which stored procedures are being used, or at least when they were last executed?


texts: How are my SQL Server constraints being bypassed?

texts:
We have found a handful of rows in our DB that violate an active constraint.  How is this possible?

The constraint is active, as we can't just manually add a row that bypasses this constraint.  However, when we run CHECKCONSTRAINTS(Files), we find that it has been bypassed on a handful of occasions during our test runs.  The rows in question were all created within a half-second of one another, suggesting some kind of race condition.

Here's the constraint being applied to the table.  The rule is meant to ensure name uniqueness in a given parent folder:

ALTER TABLE Files ADD CONSTRAINT UniqueNameInParentFolder CHECK
    CheckUniqueNameInFolder(ParentFoldersID, Name) = 1;

This constraint calls a function that looks like this:

-- first check for the new name in the Folders table
IF ((SELECT COUNT(*) FROM Folders
     WHERE ParentFoldersID = @FoldersID AND Name = @Name) = 0)
BEGIN
    -- then check for it in the Files table
    IF ((SELECT COUNT(*) FROM Files
         WHERE ParentFoldersID = @FoldersID AND Name = @Name) <= 1)
        RETURN 1
END
RETURN 0

Individual rows are added inside transactions, so I'm having a hard time understanding how duplicate rows are sneaking past this constraint.


texts: SQLRemote: Implicit synch of connected objects

texts:
I'm looking for the best way to synchronise foreign key relations with SQLRemote. So having e.g. this two tables

CREATE TABLE  table1(
    id integer not null default autoincrement,
    name varchar(40) NOT NULL,
    primary key (id)
);

CREATE TABLE table_items(
    id integer NOT NULL default autoincrement,
    name CHAR(40) NOT NULL,
    table_1_id integer NOT NULL,
    cl1 bit default 0,
    cl2 bit default 0,
    FOREIGN KEY ( table_1_id )
    REFERENCES table_1( id ),
 PRIMARY KEY (id));

Implementing maybe an invoice with invoice items. cl1 and cl2 shall trigger which client shall this dataset by synched with. But now I'm wondering, what's is the best way to synch also corresponding data of table_1.

A first approach would be from my mind to put also cl1 and cl2 fields into table_1 and add them to pubcliation so publication looks little like:

CREATE PUBLICATION sync_cl1 (
    TABLE table_1,
    TABLE table_items
    WHERE cl1 = 1
);

CREATE PUBLICATION sync_cl2 (
    TABLE table_1,
    TABLE table_items
    WHERE cl1 = 2
);

Is there any better way doing this maybe with integrated integrity check?


texts: Why do I not see Configure Distribution when I right-click on replication?

texts:
I'm using SSMS 2014 for SQL Server 2008 R2.

Why don't I see 'configure distribution' when I right-click on Replication?


texts:
While trying to create deadlock I've faced locking issue that I'm having trouble to understand:
I'm updating large table partitioned by timekey column:

I am trying to run two update statements:

BEGIN TRAN
    UPDATE [Transform].[AllCommunications_Arch]
        SET Callid = 10300503454
    WHERE Callid = 10348103154
    AND TimeKey = 20161205

and

BEGIN TRAN
    UPDATE [Transform].[AllCommunications_Arch]
        SET Callid = 1234576704
    WHERE Callid = 4321276791
        AND TimeKey = 20160720

Each one should update one row, each one runs on different partition, but for some reason one statement still blocks another. DB has default isolation lever(read committed).

Execution plan:

CREATE TABLE [Transform].[AllCommunications_Arch](
    [SessionId] [varchar](100) NULL,
    [TimeKey] [int] NULL,
    [Callid] [bigint] NULL,
    [MemberId] [int] NULL,
    [Duration] [int] NULL,
    [CalledAt] [datetime] NULL,
    [EndTime] [datetime] NULL....
) ON [Transform_Timekey_Daily_Arch_PS]([TimeKey])
WITH
(
DATA_COMPRESSION = PAGE
)

GO

CREATE CLUSTERED INDEX [CL_Transform_AllCommunications_Arch] ON [Transform].[AllCommunications_Arch]
(
    [TimeKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = PAGE) ON [Transform_Timekey_Daily_Arch_PS]([TimeKey])
GO

I've changed LOCK_ESCALATION from default table to auto and now they do not block each other, but still I don't understand why server escalates lock in this case.


texts: Is there anyway to check what is the PostgreSQL data directory programatically?

texts:
I'm doing an installer to my program and I'd like to check if the databases already exists in the computer. I do this in a horrible way, calling psql -lqt | findstr dbname. If someone knows a better way I`ll be glad to learn it. Well, now I know if the databases exists but I would also know where its data directory is installed. Is there a programatically way to find it out?

Thanks,
Leandro


texts: T-SQL equivalent to Python Pandas Concatenate

texts:
I have a few tables that I would like to merge in a similar way that Python Pandas Concatenate does.  I do not want to replicate the information for it is rather large. I'd like to have a VIEW like behavior.  Let me illustrate by a fake example that as far as I know would not work but just to properly explain what I would like to achieve.
Let us say I have these two tables:
CREATE TABLE table_1 (
    [a] [bigint] NOT NULL,
    [b] [bigint] NOT NULL,
    [c] [varchar] (32) NULL,
    [d] [bigint] NULL,
)
GO

CREATE TABLE table_2 (
    [a] [bigint] NOT NULL,
    [b] [bigint] NOT NULL,
    [c] [varchar] (32) NULL,
    [e] [varchar](256) NULL,
)
GO

Where a, b, c are equivalent but hold different data.  d exists in table_1 but not in table_2 and e exists in table_2 but not in table_1.
Tables do not hold any common data. That is, a JOIN on any field would bring zero results.
I would like to be able to do the following (Or something equivalent rather. I know what I am showing is not doable):
CREATE VIEW MyUnion FROM(
SELECT * FROM table_1 UNION SELECT * FROM table_2) AS alldata;

In such a way that if I run:
SELECT * FROM MyUnion;

And would get something like (1 and One are just filler representing a generic number or string):
a       b       c       d       e
1       1       One     1       NULL
1       1       One     NULL    One

Is there any way to do anything like this without duplicating the data? (i.e. creating another table holding everything)
Thank you!


texts: Microsoft SQL Server 2016 Service Pack 1 (SP1) support for TDE (transparent data encryption)

texts:
Does Microsoft SQL Server 2016 Service Pack 1 add support for TDE (Transparent Data Encryption) to Standard Edition as well?

As per What's new in Database Engine - SQL Server 2016 and A Big Deal : SQL Server 2016 Service Pack 1 it looks like many of the Enterprise Grade features were added to SQL Server 2016 standard edition as well.

However, there's no clear answer about TDE, I know TDE was always a Enterprise feature, but just wanted to be sure before proposing SQL server 2016 with SP1.

Can anyone please refer to a written/documented proof whether SP1 with Standard Edition will have TDE or not?

Many thanks!


texts: I need to do a fresh install in a lab environment. Where's a good place to download it?


texts: I am new to MySQL after using Postgres for long time, I wonder if there's an equivalent way to backup the data like pg_basebackup in MySQL or the only option is dump?


texts:
I'm running SQL Server 2008 and a web based application, on a single dedicated server, with only 2Gb of memory available.

As is noted elsewhere, SQL Server regularly takes up to 98% of physical memory, which appears to slow down the web application running on the server.

In Server Properties in SSMS, under Memory, Maximum Server Memory (in Mb) is set to: 2147483647

My question is, what would be the recommended number to put in the maximum server memory box, given the amount of memory I have available, and that the same server is also running the web application?

Additionally, is it safe to make a change to this number, while SQL Server is running?

Thank you for your advice.


texts:
I am completely new to PL/SQL. I have written the following PL/SQL script. But it doesn't execute and gives compilation error:

set serveroutput on SIZE 1000000;
IF EXISTS (select * from my_table)
begin
dbms_output.put_line('has rows');
end;
else
begin
dbms_output.put_line('no rows');
end;

Can anyone tell me what is wrong with this?

How can I do this?


texts:
If so, which one?

How do we know?

I have 3 indexes in a query. Actually only the first 2 should be useful

{
   "v": NumberInt(1),
   "key": {
     "LongitudeLatitude": "2d",
     "Prominent"▼: NumberInt(-1),
     "indexContents": NumberInt(1)
  },
   "ns": "database.tablebusiness",
   "name": "LongLat_Prominent_indexContents",
   "dropDups": false,
   "background": false
}

{
   "v": NumberInt(1),
   "key": {
     "LongitudeLatitude": "2d",
     "Prominent": NumberInt(-1)
  },
   "ns": "database.tablebusiness",
   "name": "LongLat_Prominent",
   "dropDups": false,
   "background": false
}

This one is not necessary:

{
   "v": NumberInt(1),
   "key": {
     "indexContents": NumberInt(1)
  },
   "ns": "database.tablebusiness",
   "name": "indexContents",
   "dropDups": false,
   "background": false
}

Now I do this query

(the query is from PhP so I'll just paste what I see in mongodb log and mongovue

database.tablebusiness query: { LongitudeLatitude: { $nearSphere: [ 106.772835, -6.186753 ], $maxDistance: 0.04498373205078301 }, Prominent: { $gte: 15 }, indexContents: { $all: [ /^aru/, /^op/ ] } } ntoreturn:20 ntoskip:80 nscanned:100 nreturned:20 reslen:1147 1927ms

Now copying { LongitudeLatitude: { $nearSphere: [ 106.772835, -6.186753 ], $maxDistance: 0.04498373205078301 }, Prominent: { $gte: 15 }, indexContents: { $all: [ /^aru/, /^op/ ] } } into mongovue find command gets me the real database command

db.tablebusiness.find({ "LongitudeLatitude" : { "$nearSphere" : [106.772835, -6.186753], "$maxDistance" : 0.044983732050783008 }, "Prominent" : { "$gte" : 15 }, "indexContents" : { "$all" : [/^aru/, /^op/] } }).limit(50);

Which is good enough (except that the limit change a little bit). Doing explain in mongo.exe I got

db.tablebusiness.find({ "LongitudeLatitude" : { "$nearSphere" : [106.772835, -6.186753], "$maxDistance" : 0.044983732050783008 }, "Prominent" : { "$gte" : 15 }, "indexContents" : { "$all" : [/^aru/, /^op/] } }).limit(50).explain();

{
        "cursor" : "GeoSearchCursor",
        "nscanned" : 50,
        "nscannedObjects" : 50,
        "n" : 50,
        "millis" : 3291,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "isMultiKey" : false,
        "indexOnly" : false,
        "indexBounds" : {

        }
}

Now based on that, what index is used? Does mongodb use any index at all? Any tips on how to improve this query will also be appreciated.


texts: Can I export a PostgreSQL database from Heroku and Import as a PostGIS database in Digital Ocean?

texts:
Disclaimer: This may be a dumb question. This is honestly way over my head... one of the pitfalls of using Heroku for too long.

I have a PostgreSQL database on Heroku. Now I'm migrating to Digital Ocean where I will be using PostgreSQL with PostGIS on Ubuntu 14.04. I would like to export my PostgreSQL database from Heroku and import it into Digital Ocean.

Is this possible? Is there any potential issues I should look out for?


texts:
How does the Database Control work, since Apache is not running on this computer? The Url says https://localhost:1158/em/console/logon/logon. I do have a WAMP apache server on here but I have to shut it down to get the Oracle Control to work , other wise I get a 404.
Thanks


texts: Count rows with max per group and additional condition

texts:
I have the following table:

 horse_main_id | race_id |    horse_name     | rating100 | race_result
---------------+---------+-------------------+-----------+-------------
            23 |     159 | Angelology        |       126 |           1
            24 |     159 | Arwoc             |       118 |
            25 |     159 | Marlo Man         |       118 |
            26 |     159 | Hucknall          |       113 |
            27 |     159 | It's Electrifying |       110 |           2
            28 |     159 | Monte Carlo       |       107 |
            29 |     159 | Cross Constance   |       103 |
            30 |     160 | Auld Burns        |       119 |
            31 |     160 | Diamond Jim       |       117 |
            33 |     160 | Livery            |       115 |
            34 |     160 | Paraggi           |       115 |
            35 |     160 | I'll'ava'alf      |       110 |           2
            36 |     160 | Jacks 'n' Kings   |       108 |
            32 |     160 | Fastnet Isle      |       115 |           1

I would like to operate on groups of race_id, find all groups that have the max(rating100) for their group and race_result = 1, and count all the times this happens.

So this example should return only 1, as for race_id 159 group the max rating100 of 126 also has race_result = 1, but for race_id 160 group, the max rating100 is 119, but does not have race_result = 1.

This is the closest I've got (was told I should use window functions):

SELECT * FROM (
    SELECT horse_name, race_id, race_result
         , max(rating100) OVER (partition by race_id) AS max_rating
    FROM horse_main) t
WHERE race_result = 1;


texts: BEFORE INSERT validation trigger for GPS coordinates

texts:
I have a MySQL database table that contains zipcodes and their corresponding lat/lng values.

I have another table that contains business locations (including lat/lng)

Prior to inserting new records for a particular business, or updating existing records, I need to validate that the lat/lng values being inserted or update ARE within specified tolerances for the the business location and zipcode. For example, if the coordinates are within one mile of the business location, then insert, otherwise skip over the insert or update.

Essentially the trigger or stored procedure needs to perform calculations on fields from the tables and make comparisons of the resulting calculated values against maximum allowable tolerances to DECIDE whether to allow the INSERT/UPDATE to occur.

I have the necessary calculations themselves, I just don't have the knowledge of how to structure it within a trigger or stored procedure.


texts: How do I specify a timeout in sqlite3 from the command-line?

texts:
I have a couple basic scripts that kick out a little information while my sqlite3 DB is being populated, but about half the time the command instantly fails:

$ sqlite3 outgoing.db "select * from edges where worker is not null;"
Error: database is locked
$ sqlite3 outgoing.db "select * from edges where worker is not null;"
Error: database is locked
$ sqlite3 outgoing.db "select * from edges where worker is not null;"
1014->9000|1014|9000||-1.0|2
1014->9001|1014|9001||-1.0|2
...

If I add .timeout 1; to the beginning of the command I just get a syntax error; how do I pass sqlite the special . parameters non-interactively via the command-line?


texts: How to Use LOAD DATA to import JSON data and specify date data type on import

texts:
I'm importing JSON data in sql and would like like to convert human readable date data to yyyy-mm-dd format.

Here's the flow:

I'm using the following json file stored under /home/name/json_data/sample.json

{
    "price": null,
    "sale_list": [
        {
            "buyer": "SmackMe089",
            "date": "April 29th 2019 21:20:50",
            "id": "1234",
            "item_desc": ""
        }
}

Create the table in mysql:

CREATE TABLE example_table (
         id INT NOT NULL AUTO_INCREMENT,
         json_data JSON NOT NULL,
         PRIMARY KEY (id)
);

Loading the file into mysql:

LOAD DATA INFILE '/home/name/json_data/sample.json' INTO TABLE example_table (json_data);

When loading data into the table is possible convert the date data to yyyy-mm-dd? On querying I am able to assign data types to the columns, but not sure how to properly convert date data. Can this be done during querying or during import?

EX:

select test.* from example_table,
JSON_TABLE(json_data, '$.sale_list[*]' COLUMNS (
buyer VARCHAR(40) PATH '$.buyer',
date VARCHAR(40) PATH '$.date',
id VARCHAR(40) PATH '$.id',
item_desc VARCHAR(40) PATH '$.item_desc'
) ) test;


texts: Is it OK to keep a value which updates in a table?

texts:
We are developing a platform for prepaid cards, which basically holds data about cards and their balance, payments, etc.

Up until now we had a Card entity which has a collection of Account entity, and each Account has an Amount, which updates in every Deposit/Withdrawl.

There is a debate now in the team; someone has told us that this breaks Codd's 12 Rules and that updating its value on each payment is trouble.

Is this really a problem?

If it is, How can we fix this?


texts:
We have two filegroup PRIMARY and SECONDARY. Objective is to merge SECONDARY partition with PRIMARY.
On running below command, following error is coming:
DBCC SHRINKFILE (N'ABC_Secondary', EMPTYFILE);
GO

DBCC SHRINKFILE: Heap page 3:54888929 could not be moved.
Msg 2555, Level 16, State 1, Line 7
Cannot move all contents of file "ABC_Secondary" to other places to complete the emptyfile operation.
The statement has been terminated.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 1105, Level 17, State 2, Line 7
Could not allocate space for object 'dbo.Contact' in database 'ABC' because the 'SECONDARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Completion time: 2021-01-23T10:51:18.9766221-07:00
See below database usage:
DbName  FileName    type_desc   CurrentSizeMB   FreeSpaceMB
ABC     ABC             ROWS    1791360.000000  357524.062500
ABC     ABC_log         LOG      904.000000       858.640625
ABC     ABC_Secondary   ROWS    483274.000000   437450.437500


texts: Delete into new table (like select into)

texts:
The select into syntax will create a new table to insert data into (I know that types may not be consistent etc. but it gives you a rough copy).

Is it possible for a delete output into statement to also create a new table in the same way, without having to define the table first?


texts:
We have a 2 node AAG setup in the DC data center, and due to an application requirement, it requires a third node to be created at the DR site and the only option I have is at the Data center in San Jose. What I want to know is - is creating an asynchronous connection a feasible option with AAG's (not AAG+FCI) . The 3rd node will be in a different subnet.

Thanks!


texts:
The following query returns no results, yet it seems like it should.

SELECT * FROM gv$session where SID = SYS_CONTEXT('USERENV','SID');

In my 11.2.0.2 Standard Edition RAC environment the above query returns no results, yet the following variations all return results.

SELECT sid FROM gv$session where SID = SYS_CONTEXT('USERENV','SID');

SELECT * FROM gv$session where SID = 256; --Your SID will likely be different.

SELECT * FROM v$session where SID = SYS_CONTEXT('USERENV','SID');

Can anyone confirm this behavior and/or explain it?


texts: How to change the replication State Transfer SST to IST in Galera Config

texts:
I would like to migrate 700GB data to galera cluster(2node+1arbitrator). I found that IST is the best method for huge DBs.So how to forecfully change the replication from SST to IST in Galera Config.
Configured sst method:
my.cnf
wsrep_sst_method=rsync


texts: Is it possible to find the user whose aggregated score reaches X value first?

texts:
Given this sample fiddle containing sales value and date for two users: http://sqlfiddle.com/#!2/8148be/3

How can I find the first user who has reached a total saleValue of 30 since the beginning of 2001?

The background: assume there is a contest running from date X, which ends the moment someone passes a specific threshold of sale value, and that person becomes the sole winner. There can be multiple contest running at the same time, so it is not feasible to check for winner in all contest when inserting new sales into the database.


texts:
Can we create a table in MariaDB 10.2 with XtraDB engine?

I have installed MariaDB 10.2 and I am trying to create a table with storage engine XtraDB and I am getting error

*mysql> CREATE TABLE T1(ID smallint(6),NAME varchar(30)) ENGINE=XtraDB;

ERROR 1286 (42000): Unknown storage engine 'XtraDB'

If I lists the engines of MariaDB here, there are no XtraDB engine type available.

Part of show engines output:


texts: SSMS Disk Usage report does not show auto grow events

texts:
I have for some time suspected that the Disk Usage report in SSMS have stopped showing autogrow events. To test I created an empty database with a very small auto grow setting. I then inserted a lot of data into a table in the DB so that I knew it would have to grow. The file was bigger when looking at file properties of the DB, but the Disk Usage report did not show any autogrow events.

I then tried running Aaron Bertrand's script from this post, and that clearly shows every autogrow event.

So, why will not the Disk Usage report show the same growth? Any suggestions?
 Btw, it is SQL Server 2014 SP2.


texts: Method for Finding Top Record with WHERE conditions in Child Tables

texts:
Setup

Recently I worked on an optimization for a SQL Query which returned the Prescription Product most recently used by a Patient.  The original query handled this via 3 nested CTE's and had really poor performance after migrating to a new server.  Instead of trying to make the original version work with either server tuning or index changes we modified the query using ROW_NUMBER() partitions.  This took the query execution time from about 10 minutes to about 6 seconds for approximately 215,000 rows in the final output.

Question

I am looking to see if there is a better way than how I re-wrote it since in my mind this almost seems like a hack.  I just want to see if this is only working well because of the size of our data set or if this really is a good solution.  I would assume we would rather use some type of solution that utilizes a couple of different JOINs that find this kind of information instead of using ROW_NUMBER() and a CTE.

Additional Details

For the sake of this question I am going to leave out certain details in our database that are less than desirable (composite primary keys, using VarChar(1) instead of a BIT, etc...) so that the root of the question can be better addressed.

We have 4 tables in scope:

Patient - we are only going to return the Identity Column from this table
OrderHeader - holds information on the package, like the address it is going to.  And is the parent record to OrderDetail.  This table is tied to Patient via a PatientID column.
OrderDetail - creates a relationship between OrderHeader and Product to indicate which Products went in the actual box when it was shipped out
Product - holds the list of products

We are needing a query that returns for each patient, the top OrderDetail record, for the top OrderHeader record, where the Product record tied to the OrderDetail record Product.IsRx = 1.

Unfortunately we can't just get the MAX value for OrderDetail.ID where Product.IsRx = 1 since that OrderDetail.ID may not belong to the MAX value for OrderHeader.ID.  We also can't rely on MAX value for OrderHeader.ID since it isn't guaranteed to have a record in OrderDetail nor is any record in there guaranteed to have a Product.IsRx = 1

I solved this problem with the below query:

WITH CTE
(
    PatientID,
    OrderHeaderID,
    OrderDetaillID,
    ProductNDCCode,
    ProductNDCDescription,
    RowNumber
)
AS
(
    SELECT P.ID AS 'PatientID',
    H.ID AS 'OrderHeaderID',
    D.ID AS 'OrderDetaillID',
    P.NDCCode AS 'ProductNDCCode',
    P.NDCDescription AS 'ProductNDCDescription',
    ROW_NUMBER()
    OVER
    (
    PARTITION BY P.ID
    ORDER BY
        P.ID ASC, --This part in the ORDER BY may not be needed, I apologize if it is unnecessary
        SH.OrderHdrID DESC,
        SD.OrderDtlID DESC
    ) AS 'RowNumber'
    FROM Patient P
        INNER JOIN OrderHeader H
            ON P.ID = H.PatientID
        INNER JOIN OrderDetail D
            ON H.ID = D.OrderHeaderID
        INNER JOIN Product PR
            ON PR.ID = D.ProductID
    WHERE PR.IsRx = 1
)
SELECT PatientID,
OrderHeaderID,
OrderDetaillID,
ProductNDCCode,
ProductNDCDescription
FROM CTE
WHERE RowNumber = 1

An guidance or expertise I could gain would be very helpful.


texts: SQL Server 2008 R2 copy Schema with all user security permissions

texts:
I have a prod support request from a high value user to adjust the settings on a company-wide dashboard so that he can manage a section of his workflow currently being excluded from these reports. Given the two immediately obvious options of

altering the dashboard settings for ALL users company-wide or
replicating the dashboard for HVU only

...it was decided that I should create a "dev" instance where HVU can see a variant of his dashboard. To accomplish this, I ported the .rdl family to another ReportServer instance in an identical directory tree & cloned all the stored procedures in the pertinent datasets from Schema1 to Schema1_dev.

Question: the stored procedures & security schema all exist on the same server & database so there's no risk of orphan users. Is it possible / what's the best way to clone all current user permissions from Schema1 to Schema1_dev other than checking through the SSMS GUI?


texts: Migrate Oracle BLOB to Postgres BYTEA or OID

texts:
After the BLOB columns of Oracle (11g r2) tables are migrated into a Postgres (9.4.5) table such as using Ora2Pg, I have two questions:

1) Which data type will the new column assume, is it BYTEA or OID?

2) Will the (BLOB) data be copied over correspondingly?  Or I need to do a separate migration of the data?


texts: SQL Server Bulk Insert properly interprets some Unicode characters but not others?

texts:
For some reason the MS SQL Server 2016 bulk insert misinterprets/translates Unicode characters:

C9 (É) into 2B (+)
A1 (¡) into ED (í)
A0 ( ) into E1 (á)
AE (®) into AB («)
CB (Ë) into 2D (-)
D1 (Ñ) into 2D (-)
92 (’) into C6 (Æ)
96 (–) into FB (û)

i.e. Notepad++ and xxd show the flat file has 0xC9, but after bulk insert the table shows "+", and cast as varbinary in SQL Server shows it as 0x2B. The backup also has 0xC9.

I'm bulk inserting 25 flat files into MS SQL Server 2016. It's 15Gb data and I'm using a pipe (|) field delimiter and CRLF row delimiter.

I bulk insert into the truncated structure of a backup I am provided. And when I compare to the backup there are differences. NOTE: I have to wait 25 hours for the backup from the data source, but can get the flat files in 15 minutes.

Some differences are acceptable (find and replace I'm applying to the flat files), but many are due to Unicode characters being misinterpreted.

The structure of one example table is:

CREATE TABLE [dbo].[obfuscated_name](
    [ob_1] [int] NOT NULL,
    [ob_2] [int] NOT NULL,
    [ob_3] [int] NOT NULL,
    [ob_4] [nvarchar](300) NULL
) ON [PRIMARY]

The database collation is default: SQL_Latin1_General_CP1_CI_AS. No columns have a different collation. This collation should use code page 1252, which should properly interpret the chars I'm having trouble with.

My process is running against production data that is constantly in flux, so I'm worried other changes may pop up, and I'd like to know the source of the issue rather than try to isolate the issue and manually update the misinterpretations.


texts: SQL Server Index, difference between Average fragmentation and Total fragmentation

texts:
Using SQL server, If i navigate to a table in the object explorer, go to its indexes, go to its properties, and view the fragmentation tab, the top section has 2 lines for me, the Page fullness in percent, and the Total Fragmentation in percent.

I also have a query i occasional use for index information. Trimming out some unrelated fields, this is the query:

SELECT t.name, ix.name, avg_fragmentation_in_percent
FROM sys.indexes AS ix
INNER JOIN sys.tables t ON t.object_id = ix.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.dm_db_index_physical_stats  (db_id(), NULL, NULL, NULL, 'DETAILED') ps ON t.object_id = ps.object_id AND ix.index_id = ps.index_id
WHERE  ix.index_id > 0
    AND ix.name IS NOT NULL

When i run this query, i get 3 columns, the first 2 pointing to the index itself, and for this query, the third is a fragmentation in percent for that index.

These 2 numbers are Usually they are very close, sometimes identical, but it also can be and frequently is different. I have had cases where the discrepancy is massive, over 50% in some cases, but normally it is something like <1% vs 15-30%. The queries result is almost always the higher percent.

Rebuilding the index will completely or nearly zero both of these numbers (though not necessarily will both be zero). So i now have 2 numbers for fragmentation on an index. According to the documentation for sys.dm_db_index_physical_stats and Index Properties(FragmentationPage) Both are supposed to be the logical Fragmentation of the index, so they should always be the same number. How is it that i am getting different numbers for what should be the same statistic?


texts: Composite primary key from multiple tables / multiple foreign keys

texts:
I want to build a composite primary key made up of foreign keys from two different tables. I'm still pretty green to this...

A great answer was given to building a composite key from multiple foreign keys on this question but I want to do this from multiple tables. I am using Oracle 11g.

foreign key (Name, BoughtFrom, TimeBought)
  references the_other_table_name (Name, BoughtFrom, TimeBought)

Imagine that the "references" line actually includes multiple tables. I'm not even sure that the above syntax will work with Oracle.

I have one table "Student" and one table "Parent". It is a many-to-many relationship. My association table is named "Relation" and I would like to make a composite primary key from "PAR_ID" and "STU_ID". Both are foreign keys.


texts: All but one of my SQL Server 2017 databases are GONE after Windows 10 nightly update failure

texts:
EDIT: Restored to a restore point from three days ago, prior to a "Critical Update", and the databases are back.

This is, I believe, the culprit update: September 10, 2019-KB4514601 Cumulative Update for .NET Framework 3.5, 4.7.2 and 4.8 for Windows 10 Version 1809 and Windows Server 2019

Last night at 10:01 PM, per the System Log, the SQL Server 2017 service on my development PC terminated:

  The MSSQL$SQL2017 service terminated with the following
  service-specific error:  Only one usage of each socket address
  (protocol/network address/port) is normally permitted.

and this other error was also there 15 seconds later:

  Installation Failure: Windows failed to install the following update
  with error 0x80070643: Feature update to Windows 10, version 1903.

So it appears to have had something to do with the #(@*@$#&U Windows nightly update mechanism.  There is only one of my databases remaining (in addition to the system databases).  How do I get them back??

EDIT: When I attempt to reattach the files I am getting an error:

  Unable to open the physical file [H:\foo\my.mdf] Operating system error 5:
  (Access is Denied.).  Microsoft SQL Server, Error: 5120

And when I go to the folder in question where the MDF and LDF files are stored, and attempt to give NT Service\MSSQL$SQL2017 read/write access to the folder, I get this error:

  An error occurred while applying security information to:
  [path to the LDF file of a database that accesses the CLR]
  Failed to enumerate objects in the container. Access is denied.

and then it goes on to give the same error for the other LDF files in the folder as well as certain MDFs, not all of them.

Upon rebooting, I get this error again:

  The SQL Server (SQL2017) service terminated with the following service-specific error:
  Only one usage of each socket address (protocol/network address/port) is > normally permitted.


texts: Sessionizing Web Log Data using MySQL (perhaps by simulating window functions)

texts:
I have a large MySQL Table containing about 15 Million rows of web log data. Depending on the IP, useragent and site_id (different sections on the website) I want to create user sessions. The session delimiter should be 30 minutes. So if a unique user (same IP and useragent) is inactive for 30 minutes or if he visits a different site area (2, 3, or 4) a new session starts.

I found a very good blog entry, that describes the general procedure: http://randyzwitch.com/sessionizing-log-data-sql/ But I can't implement this 1:1 because MySQL doesn't support window functions and I'm not proficient enough with MySQL to figure it out on my own, e.g. with GROUP_CONCAT().

Here is anonymized example data on sqlfiddle: http://www.sqlfiddle.com/#!9/a8b1f/1

It would be really great if you could help me.

I already posted this question on Stack Overflow.


texts: Is Query Performance different for different versions of SQL Server?

texts:
I have fired 3 update queries in my stored procedure for 3 different tables. Each table contains almost 2,00,000 records and all records have to be updated. I am using indexing to speed up the performance. It is quite working well with SQL Server 2008. A stored procedure takes only 12 to 15 minutes to execute. (updates almost 1000 rows in 1 second in all three tables)

But when I run the same scenario with SQL Server 2008 R2 then the stored procedure takes more time to complete execution. It's about 55 to 60 minutes. (updates almost 100 rows in 1 second in all three tables). I couldn't find any reason or solution for that.

I have also tested same scenario with SQL Server 2012, but the result is same as above.

Here is my 3 table update query in stored procedure.

if (select COUNT(*) from table where conditions)>0

begin

    update table1
    set Coulmnname= @ColumnName
    where Conditions

    update table2
    set Coulmnname= @ColumnName
    where Conditions

    update table3
    set Coulmnname= @ColumnName
    where Conditions

end

execution plan

Image 1

Image 2


texts: Teradata 'Column Dictioanry Name' and 'Column SQL Name' definitions and differences between them

texts:
With the following sql query Teradata shows a lot of information about the columns of a table.

    HELP TABLE Database_Name.Table_Name;

Two of them being Column Dictionary Name and Column SQL Name. So far, they appear to be identical in my query, but I wonder what are the exact definition of each and what are the differences between them.

DaveWellman offered some insight on Teradata community.
For HELP TABLE Syntax, please see Teradata Database SQL Data Definition Language Syntax and Examples, but I didn't find anything really relevant to my question on this page.


texts:
I have experiences of administration with MySQL and Oracle Database.
I started to work with PostgreSQL recently and cannot find the reason why PostgreSQL managing WAL files by removing or adding
according to setting with wal_keep_segments, max_wal_size and min_wal_size.

MySQL just keeps on generaing new transaction files and Oracle rotates the trancation files with REDO log files and also archiving.
I don't know why PostgreSQL managing WAL by changing the number of WAL files. Why not just keep the fixed number of WAL files?


texts: How to show number of tables for each database in Postgres?

texts:
Related to my previous question, I use the code below to get all databases:
SELECT datname
FROM pg_database
WHERE NOT datistemplate
  AND datallowconn
  AND datname <> 'postgres';

How to get the number of tables from each database returned from the command above?
There is no master database in Postgres like Microsoft SQL Server, where I could do a query for all the databases.
Is it possible in Postgres?
Thank you


texts:
I'm running a large query that for various reasons, I've broken into a series of  smaller queries. The example below is just to show how the query is broken up by id; the actual query is much more complex:

UPDATE target_table SET col1_target = col1_source FROM source_table WHERE id >= 0  AND id < 10;
 UPDATE target_table SET col1_target = col1_source FROM source_table WHERE id >= 10 AND id < 20;;
  UPDATE target_table SET col1_target = col1_source FROM source_table WHERE id >= 20  AND id < 30;

etc...

I've written a function in R that generates the queries sequentially and sends them to the database using the R library RPostgreSQL:

library(RPostgreSQL)

num <- seq(0, 100, 10)

 query.func <- function(num){

      con <- dbConnect(PostgreSQL(), dbname = "name", post = 5432, user = "user_name", password = "password")

      num2 <- num + 10

       q1 <- paste('UPDATE target_table SET col1_target = col1_source FROM source_table WHERE id >=', num, 'AND id <', num2, sep = "")

         dbSendQuery(con, q1)

           dbDisconnect(con)
}

lapply(num, query.func)

Rather than having this long sequence of queries run one after another, I wanted to try and take advantage of my server's multiple CPU's and run them in parallel. Using the R library parallel I've created a cluster and sent multiple queries to the cluster simultaneously:

no_cores <- detectCores() - 1

 cl <- makeCluster(no_cores)

  clusterExport(cl, "query.func")
  clusterExport(cl, "num")

  clusterEvalQ(cl, library(RPostgreSQL))

    parLapply(cl, num, query.func)

stopCluster()

Where query.func is defined as above.

When I run this R script at the terminal I receive no errors and if I check pg_stat_activity I see a list of active queries, each incrementally operating on a separate block of data. However, when I use top to check the resource management on my system, I still only see one postgres process. When I look at the CPU usage, I only see one CPU active at a time.

Are these queries really running simultaneously on separate CPU's? My understanding was that, prior for 9.6, a single postgres query could not be split across multiple cores but each connection could utilize a separate core (related question). Does the process I've outlined above open multiple connections and send a query to the database via each connection?

PostgreSQL 9.3/ Ubuntu 14.04 / R 3.3.2


texts: Interested in counting number of threshold crossings

texts:
I would like to determine the number of times that a value has crossed a threshold in either direction in a specified time frame.

As an example, lets say that I am interested in the number of times that an engine has started or stopped. Engine oil pressure is recorded in my table. When this value is < 5, I say that the engine is stopped. When it is > 15, I say that the engine is running. In between those values is a "deadzone"

In the time frame below, the engine has started 3 times and stopped three times.

+-------------+-------------+
| timestamp   | EngineOil_P |
+-------------+-------------+
| 01-01 00:09 |         0.0 |
| 01-01 00:10 |         0.0 |
| 01-01 00:11 |         4.9 |
| 01-01 00:12 |         7.0 |
| 01-01 00:13 |        12.0 |
| 01-01 00:14 |        50.0 | // START!
| 01-01 00:15 |        50.0 |
| 01-01 00:16 |        25.0 |
| 01-01 00:17 |        15.5 |
| 01-01 00:18 |         9.0 |
| 01-01 00:19 |         0.3 | // STOP!
| 01-01 00:20 |         0.3 |
| 01-01 00:21 |         0.0 |
| 01-01 00:22 |         8.0 |
| 01-01 00:23 |        50.0 | // START!
| 01-01 00:24 |        50.0 |
| 01-01 00:25 |        50.0 |
| 01-01 00:26 |        50.0 |
| 01-01 00:27 |        18.0 |
| 01-01 00:28 |         0.0 | // STOP!
| 01-01 00:29 |         0.3 |
| 01-01 00:30 |         6.0 | -
| 01-01 00:31 |         8.0 | - careful!
| 01-01 00:32 |         4.0 |
| 01-01 00:33 |         0.0 |
| 01-01 00:34 |        25.0 | // START!
| 01-01 00:35 |        12.0 | -
| 01-01 00:36 |         9.0 | - careful!
| 01-01 00:37 |        23.0 |
| 01-01 00:38 |         0.0 | // STOP!
+-------------+-------------+

How might I get a result like starts = 3, stops = 3?

The question is tricky because of the deadzone in between 5 and 15.

The first curveball is at 00:29 to 00:33, where the value jumps above 5, but NOT above 15, so that doesn't count as an engine start.

The second curveball is at 00:35 to 00:36, where the value drops below the upper threshold but not below the lower threshold. Here, it would not count as an engine stop

I am using MySQL (Mariadb 10)


texts: getting error when i use merge statement

texts:
I am using sql 2008 r2. when i use merge statement i am gettig error
when i execute following code:

merge products as trgt
using updatedproducts as src
      on (trgt.productid = src.productid)
when matched and trgt.productname <> src.productname or trgt.rate <> src.rate then
     update set trgt.productname = src.productname,
     trgt.rate = src.rate
when not matched by trgt then
     insert (productid, productname, rate) values (src.productid, src.productname, src.rate)
when not matched by src then
     delete output $action,
     deleted.productid as trgtproductid,
     deleted.productname as trgtproductname,
     deleted.rate as trgtrate,
     inserted.productid as srcproductid,
     inserted.productname as srcproductname,
     inserted.rate as srcrate;

select @@rowcount;
go

I am getting error like this:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'as'.

Why am i getting this error?


texts: Do I need to redesign my Database and get rid of foreign keys for successful replication?

texts:
I plan to set up MariaDB replication either in master-master or master-slave mode. The main issue I have now are worries after reading this documentation.

We use foreign keys a LOT and we also use a lot of cascading effects, especially for ON DELETE. I really do not understand why this would be an issue if both slave and master have the same table definitions, with the same cascading ON DELETE triggers.

Can I safely assume that if both DB servers have exact same storage engine (InnoDB) and MariaDB version, then foreign keys and related DELETE statements will work as expected? Or should we remove all of these CASCADEs and rework software to work without them?

Thanks!


texts:
Here are two SQL statements. One is an update statement and the other is a select statement. I want to combine them.

Update

update customer_entity
set website_id = 4
where website_id = 3

Select

SELECT count(*) c, email
FROM `customer_entity`
where website_id in (3,4)
group by email
having c <= 1) e

I tried:

update customer_entity
set website_id = 4
where website_id = 3
where email in
(
    SELECT count(*) c, email
    FROM `customer_entity`
    where website_id in (3,4)
    group by email
    having c <= 1
) e)

I want to update the website_id according to the select statement. I tried to implement it but not successful, does anyone know how to do that?


texts: update table with data from other table in remote oracle db

texts:
I have table X from remote Oracle DB A, and table Y in local Oracle DB B.

The data in Y is a partial copy of the data in X (X has some columns that aren't needed in Y; the columns I need in Y have the same names in both tables).

I periodically need to synchronize the data Y with what's in X (this is unidirectional - there's nothing in Y that needs to be copied back to X). Y has constraints that prevent simply dropping all the data in Y and repopulating it; I need to update what's already in 'Y', and insert any new rows from X that don't already exist in Y.

I've looked at a couple of options that won't work:

The copy feature in SQL Developer would copy X into a brand new table, not into Y
The export option would insert everything as new records, not update existing rows

What's the best way to do this?