Questions tagged [sql]

269013 questions
1

votes
1

answer
343

Views

Multiple Left Joins in MS Access using sub-queries

I'm trying to run a query with multiple left joins. I understand that MS Access has additional parameters requiring () around the joins, otherwise I get a 'Missing Operator' error, however I'm using sub-queries which adds even more complexity. I can't figure out the syntax. Can someone help? Here is...
Brian Michael
1

votes
1

answer
4.4k

Views

How to download SSIS Packages from SSISDB

I'm still fairly new to SSIS. I've figured out how to download packages from a server by connecting to Integration Services. How do I download packages from SSISDB? Background: We have a TON of packages that the previous people created. All packages SHOULD be kept in the same folder structure on...
Rich Uchytil
1

votes
2

answer
22

Views

Get last remaining characters if the field lenght is greater than 5

I was wondering if there is a way to select the remaining characters from the varchar field if the field length is greater than 5. For example: ID_Num 99984 99923GF 99100EFK 99341LM 99145RL4C What I am trying to get: ID_Num GF EFK LM RL4C
Milan Pk
0

votes
0

answer
27

Views

Pull data from database using AJAX on html form multi select

I have 3 columns in my database table, viz. name, category, city. Here is the link where the form is located : http://sms.thetelityellowpages.com/sms/send.php There in category and city are multi select fields which are being pulled by database. Now the actual issue is that when I select 2 categori...
-2

votes
0

answer
22

Views

ORACLE SQL - Show number of working hours between two dates

I'm trying to produce a report to show the number of working hours between 2 dates in Oracle SQL and have come a bit stuck so after help / advice. In the report I have 4 columns: - Job Logged (job.job_entry_date) - Target on site (job.target_comp_date) - Actual on site (job.actual_start_date) - Jo...
Rob Morris
-2

votes
0

answer
21

Views

MSSQL Server doesnt allow Insert of list of multiple values

I tried this query in MSSQL-Server: INSERT INTO Buchungsquelle (buchungsquelle) VALUES (34776),(35025),(35027),(35036),(35079),(35080),(35081),(35082),(35101); however, I get: 'Wrong syntax near ',' - what am I doing wrong? EDIT: Table Creation statement: USE [Doublettencheckui] GO /****** Object...
user2774480
0

votes
0

answer
20

Views

SQLite3 - Select returns bad data

I have a problem without my simply database. I have defined (fields - text, decimal, data) the sales table in the database. I load data into it (code). The problem is that: Select sum(salesvalue) from sales it shows bad data. The sum is too small in relation to reality. 'Salesvalue' -> DECIMAL Inter...
Qlikeers
1

votes
1

answer
19

Views

Display all tables for different schema

I have executed this query to show all tables into my db SELECT TABLE_NAME, num_rows counter FROM all_tables; but the db has got different schema and I want to execute this query for each of these. I already tried to select one table for every schema and the only way is from the main schema with th...
Matteo
1

votes
1

answer
33

Views

I'm having a problem in deleting rows from several tables

I want to delete all the rows which have productid = 'value' from several tables so I tried to join between them but gives me an error sql command not properly ended so I was asking if this could actually work and if not can anyone tell me how should I do such a thing? DELETE FROM products, product...
Maha
1

votes
4

answer
31

Views

PHP - Having trouble inserting dates from a date column with null values. Values are inside an array

I'm making a SQL Server to MySQL program that allows me to sync contents between two identical tables. However, I'm having trouble with inserting date values from a date column that has some of its cells set to NULL. The way this should work is that it should convert each value from the date column...
YMSmash
1

votes
1

answer
282

Views

Why does SQL Server have multiple cached plans for the same query?

If you select * from syscacheobjects you can see some simple stats about your database's queries. I liked to use this table to find queries to tune. I especially zero in on queries where usecounts = 1. Sometimes I see two rows for the same query and it's baffled me. NOTE: The queries are parame...
010110110101
1

votes
1

answer
240

Views

Azure SQL: Update Compatibility Level of [master] database

We have a single production database [ProdDatabase] on an Azure SQL Server instance that was created back in 2013. At the time of creation the compatibility level of [ProdDatabase] and [master] was hence set to 100 (SQL Server 2008). Over the past few weeks we've successfully updated the compatibili...
sammy34
1

votes
1

answer
848

Views

EXCEPT on Specific columns Spark 1.6

I'm trying to filter out rows from dfA using dfB. dfA: +----+---+----+------------+-----+ |year|cid|X| Y|Z| +----+---+----+------------+-----+ +----+---+----+------------+-----+. dfB: +----+---+ |year|cid| +----+---+ +----+---+ My goal is to fillter all couples year cid in dfB from dfA. I see...
RefiPeretz
1

votes
1

answer
728

Views

How do I select an ambiguous column reference? [duplicate]

This question already has an answer here: Enable case sensitivity for spark.sql globally 1 answer Here's some sample code illustrating what I'm trying to do. There is a dataframe with columns companyid and companyId. I want to select companyId, but the reference is ambiguous. How do I unambiguously...
chris.mclennon
1

votes
1

answer
2.2k

Views

How to set any column as primary key in Azure SQL data warehouse

I am trying to set one column in DB as primary key but I always get this error: Enforced unique constraints are not supported in Azure SQL Data Warehouse. To create an unenforced unique constraint you must include the NOT ENFORCED syntax as part of your statement. While doing R & D,I found that ther...
shweta_kaushish
1

votes
1

answer
167

Views

Biq Query regex_replace error (\? vs \\?)

I am having issues understanding what's wrong with this regex: \?.* select REGEXP_REPLACE(longstringcolumn, '\?.*', '') as newstring from tablename My example string aka 'longstring' has '?' character, and I am trying to match everything trailing '?' (including '?' itself). I have checked my regexp...
geekidharsh
1

votes
2

answer
1.3k

Views

Could not connect to sql server: SQL server Instance failure. (System.Data)

I get this error message when I try to connect to Sql Server =================================== Cannot connect to **SQL_ServerName**. =================================== Instance failure. (System.Data) ------------------------------ Program Location: at System.Data.SqlClient.TdsParser.Connect(Serve...
Justin Lessard
1

votes
3

answer
45

Views

Laravel changing the inner join via user input

My Plan is the following. I have this inner join: SELECT cd.fk_lend_id,cd.serialnumber ,customers.name,cd.created_at FROM cd INNER JOIN customers ON cd.fk_lend_id = customer.lend_id Where cd.fk_lend_id = 00000000; This is what it would look like in Laravel: $scores = DB::table('cd') ->join('cd', 'c...
Devi
1

votes
3

answer
4k

Views

How to create an empty dataFrame in Spark

I have a set of Avro based hive tables and I need to read data from them. As Spark-SQL uses hive serdes to read the data from HDFS, it is much slower than reading HDFS directly. So I have used data bricks Spark-Avro jar to read the Avro files from underlying HDFS dir. Everything works fine except w...
Vinay Kumar
1

votes
2

answer
142

Views

How to query a Google BigQuery table and remove duplicates based on a subset of columns?

I have a query that joins two google tables and produces a table with 6 columns (a, b, c, d, e, f). Next, I move that table to a google bucket and then download that google bucket to a bunch of CSV's. Finally I insert those CVS's into a postgres database table which has 2 primary keys, a and b. The...
user1367204
1

votes
2

answer
27

Views

Getting Duplicates from two columns in different tables

I have been looking at this all day. I am trying to return a list of duplicates from two columns in two separate tables. In MYSQL the only way to get a full outer join seems to be with a UNION, which I have tried: select mobile from firstTable group by mobile having count(mobile) > 1 union all selec...
mcaidans
0

votes
2

answer
32

Views

How to pass string with single quotes to Sql Stored Procedure

i'm using a stored procedure how can i pass a string like 'White','Black','Green' i'm trying to execute procedure like var data = new List(); string colorstr = ''White','Black','Green''; using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); string sql = $'exec G...
Faisal
0

votes
2

answer
19

Views

Is there a way to 'Update' existing table with new user list than running entire query to replace table everyday?

I am running query in a job that updates user list everyday in MS SQL. Query below is running everyday to update the data if object_id('report.dbo.data') is not null drop table report.dbo.data SELECT UserID, date into report.dbo.data from data a where date >= '2019-01-01' and date < getdate() O...
0

votes
0

answer
15

Views

How to search values using flexible search query?

I need to pass the below in a query suppose : 1,2,3(to be compared with column A) & 589,123,789,896(to be compared with column B) & check if there are available in the below table or not? Is it possible Flexible search quey or SQL Query? Below Table : ABC A B 1 123 1 456 1 789 2 568 2 8...
User2413
0

votes
1

answer
15

Views

how to retrieve the values that contains a part of value in the column

Suppose i have a column lastName and i have the data as kumar yadav kumar shah raghu. kumar Now i want to write a sql query that to get the values whose last name contains kumar.
Pankaj 4 U
0

votes
0

answer
15

Views

Update Column when date in other table is older then today (done) + Insert new row in other table with ID from first table

I update the Table dbo.Auftrag if all related columnes from the child table dbo.Positionen are outdated with this SQL: UPDATE dbo.Auftrag SET Status = 5 WHERE NOT EXISTS (SELECT * FROM dbo.Positionen WHERE dbo.Positionen.FremdID = dbo.Auftrag.ID AND dbo.Positionen.Lieferdatum >= convert(date, getdat...
Zam89
0

votes
0

answer
13

Views

SQL Database - Taking one columns max value for another table column

I design my database and have 3 tables: 1) Authors table has ID, Name, Surname, and Paper_ID. Name and Surname combination is unique. 2) Topics table has TopicName, SOTAResult, and Paper_ID. TopicName, SOTAResult are unique. 3) Papers table has Title(unique), Result, Abstract. I design it like that...
volkan yılmaz
0

votes
4

answer
15

Views

auto increment field in select query statement

I have a table with 2 columns, name and id with these following values Name Student_ID jack 231 dave 425 mike 001 lily 128 liam 358 I would like to add a new column to this result with auto incremented sequence values. I have tried using the below query. But i g...
1

votes
0

answer
9

Views

How can change a MYSQL Join Query in Linq Methods

I write a MySql join code, and want to retrive same value from the Dotnetcore linq methods. My Join code is below: SELECT GL.Id AS GradeLevels, CRS.Name AS CourseName, GL.Title AS GradlevelName, AVG (ASTSTU.ObtainedMarks) FROM GradeLevels GL INNER JOIN Courses AS CRS ON CRS.GradeLevelsID = GL.Id IN...
Vipin Jha
0

votes
0

answer
6

Views

How to show dimension members for which there are no values in fact in SSAS Multi-Dimensional?

I have two tables in my Ticket Management Application, 'ExpositionPeriods' and 'OrganisedVisits'. ExpositionPeriods - Defines the periods for which tickets can be purchased. OrganisedVisits - Stores the tickets purchased information. In the example below, we have 5 periods available, and tickets hav...
Aveek
1

votes
3

answer
144

Views

Why SQL query with SHA2_256 returns NULL in WHERE clause

I need a hand on this, any comments will be highly appreciated. I have a table Users with field UserPassword with datatype varchar(60) I insert on the table users from this select SELECT HASHBYTES('SHA2_256','test') it insert its value (0x9F86D081884C7D659A2FEAA0C55AD015A3BF4F1B2B0B822CD15D6C15B0F...
1

votes
2

answer
57

Views

How to make the mysql query using group by

Below is the table structure I have user_id quote_id bill_type(2=>proforma,1=>invoice) 474 676 2 674 676 2 474 676 1 445 680 2 422 120 2 411 240 2 411 240 2 410 240 2...
Nikhil Gupta
1

votes
1

answer
1.5k

Views

SQL error - ORA-00933: SQL command not properly ended

I'm aware that there are numerous questions similar to this that have been asked before, but I've looked through many of them and I haven't been able to find a solution to my following problem. This is my attempt at writing a query that displays the total number of reservations by room type in the m...
lefrost
1

votes
2

answer
452

Views

Can I use SQLCMD Commands in Visual Studio Database Project Stored Procedure?

I have a lot of SQL that is the same over multiple stored procedures. For example most procedures have the same variables declared, and is in the same try catch block to handle errors. I'd like to use the :r command so that I can write this code in one file and just import it into each sp. I can use...
Samuel O'Connor
1

votes
1

answer
490

Views

Spark dataframe calculate the row-wise minimum [duplicate]

This question already has an answer here: Comparing columns in Pyspark 4 answers Row aggregations in Scala 1 answer I'm trying to put the minimum value of a few columns into a separate column. (Creating the min column). The operation is pretty straight forward but I wasn't able to find the right f...
1

votes
2

answer
374

Views

How to check if key exists in spark sql map type

So I have a table with one column of map type (the key and value are both strings). I'd like to write spark sql like this to check if given key exists in the map. select count(*) from my_table where map_contains_key(map_column, 'testKey') I couldn't find any existing spark sql functions that can do...
seiya
1

votes
1

answer
142

Views

How to make GROUP BY in a cypher query?

I want to translate a SQL query to cypher. Please, is there any solution to make GROUP BY in cypher? SELECT dt.d_year, item.i_brand_id brand_id, item.i_brand brand, Sum(ss_ext_discount_amt) sum_agg FROM date_dim dt, store_sales, item WHERE dt.d_date_sk = store_sales.ss...
Hajer
1

votes
4

answer
321

Views

Laravel - whereHas checking latest record of a relationship without checking others

I have two tables that has One to Many relationship. Bookings - (id) booking_tasks - (id, booking_id,user_id) one booking has many task one task has one booking Booking Model: public function tasks() { return $this->hasMany('App\BookingTask'); } BookingTask Model public function booking() { retu...
ashanrupasinghe
1

votes
1

answer
85

Views

How do i connect to a ODBC Oracle database?

I have information about the connection like username, password, host, port, SID and i know that is an oracle database but i don't know how to connect. I am on lubuntu 18.04.
José Augusto
1

votes
1

answer
176

Views

write unicode data to mssql with python?

I'm trying to write a table from a .csv file with Hebrew text in it to an sql server database. the table is valid and pandas reads the data correct (even displays the hebrew properly in pycharm), but when i try to write it to a table in the database i get question marks ('???') where the Hebrew shou...
Dror Bogin

View additional questions