Newest sql Questions

Q&A for professional and enthusiast programmers

Large sql table using last_value+over

First of all, I'm not a DBA and this post my contain some ugly sql code. :) Context I have a table that represents all my product stock activities (in the SQL "TP_MOVI"), since the beginning of the ...

sql - order of case statements

have the following code: select a,b,c,d case when a > 5 or a < 2 then null end as a case when a is null then null when a = 0 then b else b/a end as e from table effectively I want to exclude ...

IntelliJ SQL Auto-Complete Not Working for DDL

Not sure what I'm doing wrong but whenever I create a new sql file in IntelliJ 2017.1 I can't get auto-complete to show me the right keywords. I have all of the sql plugins installed already. But for ...

How to export around 50000 rows from excel to mysql db

I have around 50000 records in an excel spreadsheet and i want to export it to mysql db. What is the best way to do it. I used MySql for Excel but the desktop is hanging. I even came across this ...

Database Design - Multiple Category and Multiple Sub Category

I have a question on how to design for multiple category and multiple sub category. A store will have multiple Categories and multiple sub categories. For example, Stores will have Women's clothing, ...

#1064 - SQL syntax ERROR, how to solve it?

I have 3 tables[table_a, table_b, dataz] in a my database, when I create a TRIGGER as follow, CREATE TRIGGER trigz AFTER INSERT ON table_a FOR EACH ROW IF EXISTS (SELECT * FROM table_b WHERE ...

Set column name for multiple select count (*) tables

How can I set a column name for the output from bellow query ? select (select count(*) from t_table1 id = 1) + (select count(*) from t_table2 id = 1) + (select count(*) from t_table3 id = 1)

Can columns be linked in MySql?

Is there a way to link two fields in a table so that one auto-increments, but resets to zero when the other field changes? For example, I have the following table in MySQL: CREATE TABLE searches( ...

Differents results for the same query in sql and hql

I have an application made with angular and java and I try to get datas from a database. So, I try to get my datas in hql (hibernate) but I don't have the same results in hql and in sql for the same ...

Selecting first N rows, where sum of lengths of TEXT field is up to some limit

I have a table like that: CREATE TABLE cache ( id BIGSERIAL PRIMARY KEY, source char(2) NOT NULL, target char(2) NOT NULL, q TEXT NOT NULL, result TEXT, profile TEXT NOT NULL DEFAULT '', ...

Convert Oracle collection type to SQL Server

I'm working on a database migration (Oracle to SQL Server) using the SQL Server Migration Assistant for Oracle tool by microsoft. However I get the following error message while performing the db ...

Find values which are present in all columns in a Cable

I would like a SQL Server query which finds the Values in a cell which fills multiple columns. For example, if I have table ID Value1 Value2 Value3 1 2 NULL NULL 1 ...

Get table name from sub-query to use in delete / update / insert clauses in Oracle in one line

The goal is to use the table name returned from subselect in insert/update/delete statements. It fails to achieve desired results. If we execute with TABLE (TABLE is oracle syntax word as described ...

Postgres Search Strategy

I have data that is segmented into:- postcode town place My aim is to construct a search function that will look for any text given using the above segmentations in order of precedence. ie. First ...

SQL - find mis matching values in two tables

Ok, here is the issue. Have two tables: - t1 - t2 Using two fields from both: - product - POSTED_TRAN_AMT (in t1) - BEG_AUM_AMT (in t2) I want to find a product for one period (AP=202, FY=2017) ...

Oracle PL/SQL switch between raw and hierarchical select

I need in one Oracle PL/SQL select to switch between raw select and hierarchical select statements depending on some variable. But, cannot use If statement in select. For example, I have hierarchical ...

Weekend dates between two dates in SQL

I need to find the dates of weekends between two given dates. Is there any way to find the same. i am working on SQL Server 2008.

Opening a VB Project form from command line

So, out of curiosity. I have written an application for handling our IT Departments 'Support Tickets' which has email notifications when tickets are assigned to engineers. I might receive an email (...

CHECKING if a value exists in a table SQL

I am making a bot where I need to store the commandname and commandreply in the database. I am using mySQL Workbench. I want to check if the commandname that the user tries to make is already in the ...

Swift 3: How can we connect Database using PHP in Swift and fetch data in UITableView

I am trying to build an app that connects to the database using PHP. When my app starts, it connects to database and fetches all the records in a tableView. Each cells have some records, and when a ...

convert trigger from FB to MSSQL

I need your help. I use a trigger for Firebird Database. Now I have changed to MSSQL 2014 Database. I have one trigger and I could not convert this. Please give me your help. I need the following ...

Postgresql OVER and GROUP

I have a problem with grouping I have a table(a lot more stuff in it, but not relevant) that looks something like: id user 0 1 1 1 2 1 3 2 4 2 5 2 6 1 7 1 I'm trying to get the following value: ...

How to order by Time using current time?

I haven't knowledge about DBA . I am a ios developer i have one question for DBA . `SELECT StartDate,Subject FROM tbl_Calendar WHERE StartDate BETWEEN ` date('2017-04-25') AND ('2017-04-26') Ex : ...

mysql count book per user and show it in each of row

For example I've table like: user_id | book | ------------------ 1 | aaaa | 1 | bbbb | 2 | aaaa | 3 | cccc | And now I want select data with sum of all ...

Syntax error in SQL RDS server for create table

I have requirement to update the created date and updated date column in MS sql server. I'm using below query to do the same. But while executing the query i'm getting a syntax error saying "Syntax ...

MySQL, equal or not equal?

I am a bit puzzled to see this on MySQL SELECT 1='1x'; gives me 1 Whereas SELECT 1='2x'; gives me 0 (as expected) When doing a binary compare SELECT CAST(1 AS BINARY)=CAST('1x' AS BINARY); it ...

Calling a Stored Procedure using django-mssql

I'm trying to call a stored procedure on a multi-database project I am working with. I have 2 of my stored procedures working, but this particular stored procedure is not working and returns an error: ...

execute procedures from column in table

Please help me with executing procedures. I have table with 3 columns: Create table PG_TABLE_1 (ID number(3), PROCEDURE varchar2(15), STATUS varchar2(4)); insert into PG_TABLE_1 values ('1','...

Sum two specific rows with different P-key values. PostgreSQL

So, i have a query that returns the following table: charvar | numeric | numeric | numeric | text cargo | qtd | valor_ust_servico | total_ust | valor_servco name1 | 30 | ...

Function is not unique error in postgresql

I have made the following function . CREATE OR REPLACE FUNCTION insertSecondProf(amka00 character varying(11)) returns character varying(11) as $$ BEGIN return amka00; END; $$ LANGUAGE 'plpgsql' ...

DB2 select multiple rows into single result

I have a DB2 table containing rows of customer data. This is used to determine if a customer saved money using our service. The older record is the purchase that triggered our process and the newer ...

output bill from rent database with mysql

i made a website where customers can rent a maschine for a specified dates. i use PHP and MYSQL. here is an image that shows my tables and the desired relations. i need to output a bill. the bill ...

Insert an array in postgresql

I use python to connect to my postgresql database. I parse a XML fill to populate my base, so I have an array[] of values for one line to insert. I have something like this: cur.execute("""INSERT ...

Update Stored Procedure - only update certain fields and leave others as is

I'm currently making a stored procedure which will update a products details. I want to make it (if possible) so that only the fields entered under 'values' column when being executed are updated and ...

SQL postgress JSON: Selecting data within JSON

I'd like to pull data using SQL in postgress but would like to go into the JSON result and only extract the data I need. If I write (in Valentina studio): Select "data" from "cars" The first row ...

SQL Order by group of specific values

I tried to found the solution, I might have done wrong researches, that's why I need your help :( There is 6 different categorie, with different values. I want to select all of them, but ordered in 2 ...

insert into sql database from selecting columns from excel

I tried using insert into.. select method as below to insert the data from excel into sql, Insert into table_fruits(type, weight, cut, date_import) SELECT type, weight, 'Yes', GetDate() from ...

Exception datetime in SQL

The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value. My code: string sqlthem = "INSERT INTO Infosp1 VALUES (@khach,@sp,@sl,@gia,@tg)"; string ...

SQL return record with earliest date

I am using Aginity Workbench with a database on Netezza and I am trying to return the record with the earliest date based on it featuring an 'IS' code in any one of three (serviceability) columns. ...

Trying to brief a sql sentence

Well I got this sql sentence and I would like to find a way to avoid writing all those day01 da02... SELECT endpoint month day01 day02 day03 day04 day05 day06 day07 day08 day09 day10 day11 day12 ...

SQL:How to get the data from a file based on a certain character pattern

I have a data file where I bulk insert data into my staging table. This file has a sequence like that on certain lines. 0123456789 0122580000 2017092 2017092 300,000.00 10,000.00 ....

try-catch clause doesn't work with

When I feed an illegal value to "ID", I expect the "try" to fail and trigger the "catch" clause. PreparedStatement ps1=conn.prepareStatement("insert into Table_1(id values(?)"); try{ ps1....

SSMS 2016: Can't see any highlighted text

I can't see the text I highlight when I click and drag over text. Is there a color setting for that? Highlighted text: The word 'Code' is highlighted in the pic below, but you can't tell. When I ...

Select values from different rows of same column INTO multiple variables in Oracle SQL

Here's the example: ID | value 1 51 2 25 3 11 4 27 5 21 I need to get first three parameters and place them into variables e.g. out_x, out_y, out_z. Is it possible to do it ...

SQL spatial determine which side of a line a point is on

I have a line and a series of points as geometry objects in SQL spatial. I would like to determine which side of the line each point falls on. The line represents a coastline - so some points are in ...

Insert multiple row with trigger: Postgresql

I have a trigger: CREATE OR REPLACE FUNCTION process_fillDerivedFromGenby() RETURNS TRIGGER AS $fillDerivedFromgenby$ DECLARE prog varchar(255); curent varchar(255); BEGIN ...

Spliting rows into columns when value occurs in SQL Server

I have a a table with this values : ITPROD PROD1 PROD2 Quantity 1 Quantity 2 45842 69640 63908 3 2 70690 91387 90734 1 2 The result table should ...

web2py SQLform.grid export query

I would like to use a different query to export data from table when using SQLform.grid export, compared to the one i use to represent data in the grid. Also I would like to export more columns than I ...

Raw SQL in .NET Core

I have this problem: I need to execute raw SQL from my .NET Core app. So I have this code var sqlConnection1 = new SqlConnection("Server=(localdb)\\mssqllocaldb;Database=MyDB;Trusted_Connection=True;...

MongoDB aggregate with sum and minimum treshhold

How can I show only results with count > 10? db.mydate.aggregate( [ { $group : { _id : "Ident.url", count: { $sum: 1 } } } ] ) Thank you!
Translating... 0%