Categories
Database Development

`ts_rank` is returning a numeric/decimal/real that fails a “greater than” check

The following yields (seemingly exactly) 0.1: select ts_rank_cd(to_tsvector(‘cat’), to_tsquery(‘cat’)); Yet the following yields t: select ts_rank_cd(to_tsvector(‘cat’), to_tsquery(‘cat’)) > 0.1; I would expect it to yield t if the operation was >=, not > How can I get the "real" value of the ts_rank_cd so that it returns f? As background, I’m writing a full text […]

Categories
Database Development

i want to count the number of occurrences of certain values of my TagName column

i want to count the number of occurrences of different sensor rows in SQL, but i seem to be doing it wrong and seemingly i am not visualizing it correctly. if i was doing this in pseudocode in a c style language i would do it like this: FOR i in range(taglist[i]): print(taglist[i], count(taglist[i]) ) […]

Categories
Database Development

Deleting all records on a table except the latest 10 for each user

I have an activities table that has a foreign key reference to a users table. A user can have many activities, and I have a job that will populate their activities on a daily basis. After that job runs, I want to run a SQL query that will delete the oldest records per user and […]

Categories
Database Development

Oracle – LRM-00109: could not open parameter file

Sometimes I get the following error: SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 10 16:59:04 2020 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount; ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file ‘/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initimootstb.ora’ But I don’t understand that issue because I have a […]

Categories
Database Development

How to import MSSQL .sql file on sql Server ….?

How many ways to import .sql file on MSSQL Mangement Studio.

Categories
Database Development

How can I go about building a relationship with variable but ordered number of columns in a table?

First, I’m not a DBA or even much of an SQL person, so I apologize if this is too much of an entry-level question! I have been tasked with redoing an old csv file and turning it into a relational database on an old Microsoft SQL Server 2008 we have. It needs to track both […]

Categories
Database Development

SQL Server compatibility level 80 for SQL Server 2012

We have SQL Server 2012 and I know the minimum compatibility it supports is 90. But we have databases in SQL Server 2000 which we have restored and we want the databases to be used as compatibility 80. But there is no way I am finding any alternative. It’s very critical as the applications only […]

Categories
Database Development

How to store a reference to any other column in another table

I have a table "Discounts" with a column "share" which holds the percentage to be applied to the value of column "cost", "price" or "shipping" of the table "Items". "Discounts" also holds a foreign key to "item_id". I need to add another column "base" to table "Discounts" where to store a reference to one of […]

Categories
Database Development

path on ASM for standby database after switchover

The question is quite complicated for me to describe so please be patient and ask for more details of needed. I have a primary database (ABC) and a standby one (STBABC). Both are located on ASM. For both primary and standby there are several parameter in spfile (control_files, db_create_file_dest, db_recovery_file_dest etc. ) to point where […]

Categories
Database Development

Query tuning update with hash flow distinct operator

Need some help in understanding the slowness in one of the UPDATE statements like below:- UPDATE TOP (100) xyz SET xyz.flag = 1 OUTPUT inserted.Rcode, inserted.EDR, inserted.id, abc.EID,abc.CID,abc.ENID,abc.Cdate FROM dbo.table1 xyz WITH (UPDLOCK, READPAST) INNER JOIN dbo.table2 abc WITH (NOLOCK) on xyz.id=abc.id WHERE xyz.flag = 0 Table1 has approx. 0.5 million rows and Table 2 […]