Questions tagged [sql]

268383 questions
1

votes
0

answer
24

Views

SSIS - Write a log in existing package

I would like to include a log to check the DB source in existing package to track DB source details in different environments. This is existing package and I am totally new to SSIS. I am not sure how to implement in SSIS package. Is there any built in functionality available in SSIS 2008? It would...
Ask_SO
1

votes
1

answer
40

Views

Select and count in the same query on two tables

I've got these two tables: ___Subscriptions |--------|--------------------|--------------| | SUB_Id | SUB_HotelId | SUB_PlanName | |--------|--------------------|--------------| | 1 | cus_AjGG401e9a840D | Free | |--------|--------------------|--------------| ___Rooms |--------|--...
1

votes
1

answer
37

Views

Sql stored procedure combining like materials and adding lengths together

I have a SQL Stored procedure that looks through SQL tables, then gives me the material and its length. What I am wanting is the query results to only show the material 1 time, and have all the lengths be added to its material. I am including the stored procedure and a snapshot of the results I am...
ronnogal
1

votes
0

answer
102

Views

MySQL: Distance Between Two Points (using MBRContains)

In the post Fastest Way to Find Distance Between Two Lat/Long Points, user Quassnoi provides the following code for calculating the distance between two locations: SELECT * FROM table WHERE MBRContains ( LineString ( Point ( @lon + 10 / ( 111.1 / COS(RADIANS(@lat))), @lat + 10 / 111.1 ), Point...
Greeen
1

votes
1

answer
724

Views

Hierarchical data manipulation in Apache Spark

I am having a Dataset in Spark (v2.1.1) with 3 columns (as shown below) containing hierarchical data. My target objective is to assign incremental numbering to each row based on the parent-child hierarchy. Graphically it can be said that the hierarchical data is a collection of trees. As per be...
Sridher
1

votes
0

answer
204

Views

Scala reflection error while registering a Spark UDF

I'm using Spark UDFs all over my code, but there is one registration the fails intermittently with the following error: scala.reflect.internal.Symbols$CyclicReference: illegal cyclic reference involving package at scala.reflect.internal.Symbols$TypeSymbol.tpe(Symbols.scala:2768) at scala.reflect.in...
Hagai
1

votes
1

answer
36

Views

SQL: Total entries in past 7 days, group by day, if no entries for that day set it as 0

I have a table with following columns: id | time (SQL datetime) | url I need to get totals for last 7 days from today. If totals for that day are 0 - return it as 0 instead of not returning at all. I've tried this: SELECT time, COUNT(*) AS `num` FROM entries WHERE time > DATE_SUB(CURDATE(), INT...
nonToxicGuy22
1

votes
2

answer
523

Views

running SSIS package as SQL agent job failed

I created SSIS an project, which exports some data to Excel. In Visual Studio 2017 it works fine, but when I run it using a SQL Server Agent job (MS SQL Server 2017), it throws this error: 'Executed as user: Administrator. Microsoft (R) SQL Server Execute Package Utility Version 13.0.1601.5 for 32...
WhiteB1
1

votes
0

answer
41

Views

How to make Spark Worker read data from local mongodb with mongodb-spark-connector?

I have got two 'mongodb' on two computers. And there is also a 'Spark Worker' on each computer. But when I run 'spark', it doesn't read data from its local 'mongodb'. Instead, it reads from one of them. Therefore, only got partial data. There is a page. https://docs.mongodb.com/spark-connector/maste...
BobXWu
1

votes
1

answer
483

Views

Searching 13 million records using full text search with additional conditions

Performance issue while doing SQL Server full text search with additional conditions. (SQL Server 2012) I am trying to filter the data based on search filters list (table value parameter), which will return all the records for match filters and single record for the filter doesn't have any record fr...
K.Engineer
1

votes
1

answer
28

Views

How to get MS SQL Stored Procedure return value In PHP with PDO?

Simple example CREATE PROCEDURE [dbo].[InsertLoginInPHP] @login as nvarchar(50), @password as nvarchar(50), @resultOK as int, @resultnot as int AS BEGIN SET @resultOK = 10 SET @resultnot = 1 IF EXISTS (SELECT * FROM Users WHERE Login = @login) BEGIN SELECT @resultnot as return // 1 END ELSE BEGIN IN...
Afqan Haciyev
1

votes
1

answer
471

Views

create cte multiple times and drop cte [closed]

I am using a cte in stored procedure. I have to use it multiple times ie populate cte for different ids how can I drop or remove current cte thanks ;WITH PAYOUT_CTE(REGNKEY, REGNTPE) AS ( SELECT REG_KEY, 'Parent' FROM ML_MSTR_REGN A (NOLOCK) WHERE A.COMP_NO = @COMP_NO AND A.REG_KEY = @CUR_...
GL SOFT INDIA
1

votes
0

answer
62

Views

SSIS: Sequence container counts Warnings as Errors

I'm debugging an SSIS package that I've just developed. I've found the package failed because a sequence container is Red though all the tasks on it are green. I checked the process results and found there were 4 Warinigs (=exclamations) and no fatal errors. I want to have the container ignore Warni...
yobioo
1

votes
1

answer
39

Views

Vehicle Fitment Data Merge Overlapping Years

I have a large data set of vehicle fitment information for products, each on their own row. I am struggling to create a query to select only the minimum and maximum years for each overlapping entry. For example, I have data such as: fromyear toyear makename modelname submodelname wheelba...
user9147574
1

votes
1

answer
114

Views

DataGridView is not committing updates to database

I used the designer to set the datagridview's data source to my SQL database table. On the RowLeave event, I use the BindingSource.EndEdit to commit the changes. However, the changes in the datagridview aren't being reflected in the database. The EndEdit is being called correctly. private void UI_DG...
B-Rian
1

votes
0

answer
30

Views

Group by SQL (or if value is on another table)

I have a table of tournament results with points earned. I want to publish SUM of points for each player and ORDER BY SUM of points DESC. To do this I need to get all results and then group by LVID. The problem is that players LVID is on table A or table B. This is how my code looks right now: SELEC...
Nick
1

votes
0

answer
114

Views

PostgreSQL: Index is used - but not when querying with LIMIT OFFSET

Let's say I have a messages table in a PostgreSQL database that contains many entries. The primary key of the table is just a simple serial column (like int but with 'auto increment'). Then I want to select the TOP-25 messages that a user received and that meet certain criteria. The result is ordere...
MrSnrub
1

votes
1

answer
64

Views

SQL Query Execution time , SQL Server, Nested Query

I have a query as following: SELECT Brand,Id,Model FROM product Which takes time in order of seconds as Product table has more than 1 million records. But the query executes within no time. (less than even one second)) select count(*) as numberOfRows from (SELECT Brand,Id,Model FROM product) result...
Sahil Kumar Kansal
1

votes
1

answer
45

Views

I need to pull unique patients that meet certain criteria

I pulled person_nbrs that have never had an EventType1 before or after an EventType2. I need to pull person_nbrs that have not had an EventType1 prior to having an EventType2. If they had an EventType1 after an EventType2, than it is to be ignored. Here is my query that pulls person_nbrs that have...
zdsnide
1

votes
0

answer
41

Views

Is there intermediate computation optimization when using functions.window [Spark]

I am using functions.window to create sliding window computation using Spark and Java. Example code: Column slidingWindow = functions.window(singleIPPerRow.col('timestamp'), '3 hours', '1 seconds'); Dataset aggregatedResultsForWindow = singleIPPerRow.groupBy(slidingWindow, singleIPPerRow.col('area')...
Anton.P
1

votes
0

answer
253

Views

MySQL connection issue in gcloud

I have deployed codeigniter project in google cloud app engine . I am getting database connection problem. Database connection is below - $db['default'] = array( 'dsn' => 'mysql:unix_socket=/cloudsql/tastyeater-190406:us-central1:tastyeater;dbname= databasename', 'hostname' => 'localhost', 'username...
Farman Ali Khan
1

votes
2

answer
222

Views

SQL adjacency list query

Trying to model dependencies on a service that is accessed by users. I have created a parent/child adjacency list style table that sets out that 'the service' relies on Component 1, which relies on Component 2, etc, etc. and the type of dependency e.g. full or partial This image shows the layout -...
Mo R
1

votes
3

answer
36

Views

SQL - Get Records within Date Range using 2 tables (Date Range/Hours given)

I have 2 tables: Table 1: Name, StartDate, EndDate Ex: Timmy, 9/12/17 08:00:00, 9/13/17 00:00:00 Timmy, 9/13/17 05:00:00, 9/13/17 07:00:00 Table 2: Name, StartHour, Data... Ex: Timmy, 9/13/17 06:00:00, Data1... Timmy, 9/13/17 04:00:00, Data2... Timmy, 9/13/17 07:00:00, Data3.....
Future44
1

votes
2

answer
139

Views

Inner join with three tables with different columns

I have three tables. Table 1 and Table 2, I can join with User name and Table 3, I can join Table 1's ip column. Here is the sample Sql Script I written. SELECT R.[First Time], L.[USER], R1.[Destination Port] , L1.[IP Address] AS 'L1 IP Address',L2.[IP Address] AS 'L2 IP Address' FROM [SAPA_SOCTem...
Jzl
1

votes
2

answer
74

Views

SSIS SCD Wizard with multiple incoming records

PlEASE FORGIVE. I am very desperately looking how to ask this eloquently. Not doing to good here. In SSIS, if you have an incoming dataset that has multiple records for the same PK (like intraday records in a daily processing method), how do I tell the SCD Wizard to expect that? in my case, even th...
arcee123
1

votes
0

answer
429

Views

Sync NoSQL with SQL

Is there a way to sync NoSQL and SQL databases? My problem is: We have a software that uses MSSQL. We also have a mobile application that uses MongoDB. We want to sync data (on create/update) between those databases. Mostly from MongoDB to MSSQL. It is not a problem for us (if we have to) to use di...
italomarca
1

votes
1

answer
40

Views

creating a trigger in php-my-admin

i would like to create a trigger using the phpmyadmin 'add trigger' option. The Trigger should run AFTER every INSERT in table comments that takes the highest 'number of comments' (nc in DB) from the table 'POST' and takes the 'ID' and the 'question' of that row and puts it in the 'top_questions' t...
Zyzzx
1

votes
1

answer
54

Views

Laravel get only models with current version

I have implemented custom model versioning in Laravel Framework. There is couple of columns handling it in the database table: sibling_id (links model's different versions together), version, accepted and validFrom. For example: | id | sibling_id | version | accepted | validFrom | ... | |----|-----...
lingo
1

votes
1

answer
334

Views

Snapshot agent not starting SQL Server error 15404 error code 0x54b

I have run into this issue and I am stuck with it for hours. I have 2 databases one which is publisher and distributor and second which is subscriber I am going to use them as snapshot replication. Installation went without issues but when I look in Replication monitor publication status is ok but a...
Macieyerk
1

votes
2

answer
215

Views

SYBASE :Join Two table where Value of one table column is column name of other table

Table1 PRICE ID_1 ID_2 ID_3 500 1 2 3 750 2 3 4 Table2 ID VALUE ID_1 1 ID_2 2 ID_3 3 I have two tables and want to join these tables like Select * from table1 T1 Join Table2 T2 on T1.(T2.ID) = T2.Value In short I want to convert one table co...
gaurav bharadwaj
1

votes
1

answer
507

Views

Combine ASP.NET MVC and Web API projects using identity

I am new to ASP.NET and I have to create an ASP.NET MVC project using the identity framework for authentication and authorization, but the access to the database (SQL Server) must be done in an independent web service project so I created a Web API project with also identity containing all services...
sam
1

votes
1

answer
160

Views

Unable to execute R code on U-SQL using R extensions

I have been trying to execute R code on U-SQL using the R extensions mentioned in the documentation (https://docs.microsoft.com/en-us/azure/data-lake-analytics/data-lake-analytics-u-sql-r-extensions). When I try to execute the example scripts mentioned in the link above, it throws the error: C# err...
Absolute Beginner
1

votes
3

answer
93

Views

SQL, how to order by score and get position of ID in ordered list

This is what I have: SELECT * FROM scores WHERE userId ='${message.author.id}' This will get the row in the table scores that has the userId of ${message.author.id}. I want to use this: SELECT * FROM scores ORDER BY points Now I don't know what to do from here. I would like to take this ordered list...
Cian Flint
1

votes
3

answer
63

Views

Add columns of old purchases per customer to compare them

Reading a book on Sqlite and decided to test certain queries for fun. This one got me thinking. How would you return the number of the continuous buyers who got a new item with a lower price than their previous item? no joins required its all in the same table. relevant table names: day: date bought...
J_p
1

votes
1

answer
383

Views

Writing contents of XML Variable to File

The code below creates me an XML file that shows up as a link as shown in image1 DECLARE @xmlentire AS XML = ( select (select @xmlheader as returnXml), (select @xmlbody as returnXml) for xml path ('IndirectSalesMessage')) select @xmlentire SELECT @loop = @loop + 1 the contents that are returned to m...
jp3nyc
1

votes
1

answer
85

Views

Insert into datetime field without duplicates in interval sql

i have table with tv shows, in this table there are columns, one for start time and one for finishing time. I need to make that if you want to insert tv show that you cannot put 2 tv shows in same time, like in real television, can you please help me to make this. Thanks. Table tvshows have columns:...
iposave
1

votes
1

answer
113

Views

Parse SQL like query with boost spirit x3

I'm trying to parse a simple SQL like query using boost spirit x3. There were a similar post for the previous version of spirit. But With spirit x3, we don't need grammar. So here is my attempt: // SELECT chr, pos FROM table // Select chr, pos FROM table WHERE a=5 and b = 6 // SELECT chr, pos FROM...
DrIDK
1

votes
1

answer
46

Views

how to keep the date unchanged when changing other data?

I want to update the status column in my table, but the date column of the message is also updated. How can I only update the status column only without the date field ? Data type: Status => Varchar messgae_date => Timestamp Query : 'UPDATE mytable SET status='1' WHERE status='0'' Thank's before ..
Aditya
1

votes
0

answer
46

Views

Neither recordset or SP approach to validating salted hashbyte fields working

UPDATE: ASP page still always returns a '1', even when correct password is supplied, but when stored proc is run in SSMS, it works properly. I added SELECT statements for the fields being tested and modified the running of the stored proc to return a recordset when executing the command. Set rs2 =...
Jan Paul
1

votes
1

answer
15

Views

How to count how many times a group of words appear in a column, in any orders?

I have a table that has a column showing pairs of students, such as 'Anna-Bob', 'Anna-Charlie', 'Bob-Dan', 'Bob-Anna','Charlie-Anna'... I want to count how many times each of the pairs of students appears in that column, in any order. For example, for pair 'Anna-Bob', I will count those that have ei...
Annie

View additional questions