w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
  Home » SQL » Page 3
SQL Syntax error with foreign keys
The error is correct. Subqueries are not allowed in check constraints. But, you already have a foreign key reference between user_name and likes(user_name), so this condition is already in place. The only thing is would really be checking is that user_name is not NULL, but that is already true by the definition of the column. Now, there are other issues. Your foreign keys should be to primary

Categories : SQL

Oracle performance after removing duplicate records
i, create a temp table from the source table(with the indexes of course) ii, after it delete the duplicated rows from the temp table (or the source, its egal) iii, see the explain plans both of these tables and you will get the answer

Categories : SQL

How to pass the CHAR as the input to the IDENTIFIED BY inside CREATE USER
Ok, i guess that for whatever reason it's impossible. If somebody else will be looking for it, you will have to manually edit the mysql.user (and maybe mysql.db) table: DROP PROCEDURE IF EXISTS testUserCreate; DELIMITER // CREATE PROCEDURE testUserCreate (IN sDbUser CHAR(16), IN sDbPass CHAR(40)) BEGIN INSERT INTO mysql.user (Host, User, Password) VALUES('%', sDbUser, PASSWORD(sDbPass)); FLU

Categories : SQL

inner join 3 tables, condition where not working
Hard to say without seeing data samples and knowning your column properties, however, I can imagine that it might be a problem with the way you try to compare c.StartTime to your condition in plain text. Try this: [...] c.StartTime > to_date('2014-10-01','yyyy-mm-dd') [...] Edit: Damn, missed your last sentence... Probably not the solution to your problem then ;-)

Categories : SQL

How to work with a iterator variable on a dynamic select query in PL/pgSQL?
The query providing the rows for your FOR loop is executed once before the first iteration of the loop. You would have to call the function getBalanceLine() in the loop, not in the base query. However, your whole approach is needlessly verbose and expensive. Simplify, step 1 CREATE FUNCTION report_cash_flow() RETURNS TABLE(Date date, Company text, Bank text, Partner text, Document text, Credi

Categories : SQL

Oracle select statement with a function call return value. But when doing insert to a table with same query, function column shows null
That is strange. Sometimes Oracle gets confused when columns exist in more than one table and you use them without qualifier. (Rather then reporting ambiguity Oracle uses one of the columns then sometimes.) So maybe it helps to add qualifiers; especially for some_id in get_another_id(some_id,'X'). That would be get_another_id(some_id_table.some_id,'X'), if some_id belongs to some_id_table, as I

Categories : SQL

Delete all ID's that don't have a specific value
This sort of construct will help. delete from houses where house_id in (select house_id from houses except select house_id from pets where pet = 'dog' ) However, it's a strange plan. Once those records are gone you can't use them anymore. Another option is to keep your records and simply select the ones that do have pet dogs.

Categories : SQL

Replicate tables or use multiple Entity Framework models
I would definitely NOT create two table and try to replicate the data. That would just be creating an on-going headache. If both apps use some of the same tables you could create a base context containing the shared tables, then create an application specific subclass of the context for each application. That would help avoid the duplicate code.

Categories : SQL

SQL Reporting Services: 2008 R2, web app showing unauthorized access
I ran into this when SSRS2008R2 was new and shiny. At the time I did get to the bottom of it but I've long forgotten why it happens. The long and the short of it is (as you say) no double auth. For public facing web access the usual way to sort this out is to wrap the report in a report control in an asp.net page. The report control authenticates on the user's behalf. The origin of the credential

Categories : SQL

How to Generate Alphanumeric Random numbers using function in SQL Server 2008
Try this: select cast((Abs(Checksum(NewId()))%10) as varchar(1)) + char(ascii('a')+(Abs(Checksum(NewId()))%25)) + char(ascii('A')+(Abs(Checksum(NewId()))%25)) + left(newid(),5) Random_Number Also, DECLARE @exclude varchar(50) SET @exclude = '0:;<=>?@O[]`^/' DECLARE @char char DECLARE @len char DECLARE @output varchar(50) set @output = ''

Categories : SQL

Best relation between one and three other tables
So relation is more or less a contact, which is either a private relation (a contact to a person) or to a company or to a particular person in a company. So a relation should have two optional (i.e. nullable) fields: for a person and for a company. Person (id_person, firstname, surname, ...) Company (id_company, name, ...) CompanyPerson (id_person, id_company, job, salary, ...) Relation (id_re

Categories : SQL

Calculate time difference in minutes in SQL Server
Use DateDiff with MINUTE difference: SELECT DATEDIFF(MINUTE, '11:10:10' , '11:20:00') AS MinuteDiff Query that may help you: SELECT StartTime, EndTime, DATEDIFF(MINUTE, StartTime , EndTime) AS MinuteDiff FROM TableName

Categories : SQL

xlsm into sql server
follow this : 1-Create an Excel Connection Manager to refer to ANY other spreadsheet with a "valid" XLS or XLSX extension. (You don't have to save your XLSM file "as" an XLSX.) 2-Select the Excel Connection Manager in your Connection Managers pane. 3-Open the Properties Window (F4). 4-Change the "Server Name" property to the full pathname of your XLSM file. note: check the file should not be in

Categories : SQL

Rrearrange order of column by columns name
You can use the dictionary tables for this (assuming your source dataset is called 'dataset' and resides in the work library, make changes to the WHERE clause if not, but make sure you use upper-case for the values): PROC SQL; SELECT name INTO: mob_cols SEPARATED BY ',' FROM dictionary.columns WHERE libname = 'WORK' and memname = 'DATASET' AND upcase(name) LIKE 'MOB%' ORDER BY name; QU

Categories : SQL

DYNAMIC SQL FOR TABLE NAME AT RUN TIME
If you are doing it in SQL*Plus, then SET SERVEROUTPUT ON is what you need to do first. If you are using any GUI based client tool, then check the option to enable dbms_output And use, DBMS_OUTPUT.PUT_LINE Edit : See this test case. SQL> set serveroutput on; SQL> SQL> create or replace procedure ankit 2 (table_name varchar2) 3 is 4 BEGIN 5 dbms_output.put_line('select NAME

Categories : SQL

How to evaluate expression in standard sql?
Try like this please: select case when 'A' ='B' or 10 > 5 then 1 else 0 end Also maybe this post can help Boolean Expressions in SQL Select list

Categories : SQL

Is that good to wrap a sql query to do a row count query
Specefic to SQL Server: COUNT is the only aggregate function that considers NULL marks. So if you want to handle only occurrences of known values in foo column or any other column in select list use COUNT(COLUMN) else go for COUNT(*).

Categories : SQL

Sequelize target postgres 8
It seemns that CREATE IF NOT EXISTS is not supported in postgres 8. I searched around a bit, but achieving the same behaviour seems to be quite involved. IF NOT EXISTS is added to table creation when you do sync({ force: false }) in sequelize (or leave out force completely). I would recommend you to either call sync({ force: true }), which will drop and recreate the tables; or simply not call syn

Categories : SQL

How does EXISTS return things other than all rows or no rows?
your condition in where clause for main query SELECT DISTINCT PNAME FROM P is dependent to Exist , if your subquery returns any rows , then exists returns true ,otherwise it returns false and the main query where clause return all of records in p if Exists return true and nothing if it returns false

Categories : SQL

Optimize select Query performance that is having join with various other tables in MS SQL
With (NOLOCK) will improve the performance but it will give you dirty reads which are not committed yet. Idealy this is not recommended on transactional tables, if you are fine with this dirty reads, you can use it, And other optimizations are like maintain proper indexes on tables columns which are being used in joins. and the other one point is join the tables from small to bigger in data, and

Categories : SQL

Select values from XML child nodes
Maybe something like this: DECLARE @xml XML SET @xml='<customers> <customer> <kunnr>1</kunnr> <kdgrp>2</kdgrp> </customer> <customer> <kunnr>2</kunnr> <kdgrp>2</kdgrp> </customer> </customers>' And then a query like this: SELECT

Categories : SQL

ActiveRecord: complex query with Sum, Join, Group By and Select multiple fields
Ок. I've found a solution. Here is right query. Object.joins(:user_objects). where(user_objects: {user_id: [user1_id,user2_id]}). group(objects: :id). having('COUNT("user_objects"."id")=2'). pluck("objects.importance, SUM(user_objects.score)") Problem was in select method because it creates an object of a class which invoke this method. So it is impossible to choo

Categories : SQL

Self join without same record appearing twice
Use self join: mysql> create table test (a integer, b integer, count integer); Query OK, 0 rows affected (0.00 sec) mysql> insert into test values(1,2,10); Query OK, 1 row affected (0.00 sec) mysql> insert into test values(4,5,20); Query OK, 1 row affected (0.00 sec) mysql> insert into test values(2,1,5); Query OK, 1 row affected (0.00 sec) mysql> insert into test values(5,4,6)

Categories : SQL

Remove last number digit in T-SQL
this will Work For You .. select distinct browser from ( select REPLACE (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE (browser, '0', ''),'1', ''),'2', ''),'3', ''),'4', ''),'5', ''), '6', ''),'7', ''),'8', ''),'9', '') as browser from TestTable group by browser) temp

Categories : SQL

CTE for Count the Binary Tree nodes
You should use aggregate SQL as a last statement (Group by Lebvel,mode): with cte (advId,mode,Level) as ( select advId,mode,0 as Level from table1 where advid=1 union all select a.advId,a.mode ,b.Level+1 from table1 as a inner join cte as b on b.advId=a.referId ) select count(*),Level,mode From cte where Level<>0 group by level,mode order by level,mode SQLFiddle demo

Categories : SQL

Postgres Throws Error When using Two select statement in One query
You need to select from the common table expressions: to put the common table expression (the with part) before the insert: insert into temptable with date as ( select date from generate_series('2014-04-01','2014-04-30', '1 day'::interval) as date ), name as ( select name from table12 where id=1912 ) select * from date cross join name; As an alternative, you can put the

Categories : SQL

How to get the minimum value in a set of columns while excluding a certain value?
How about this: use tempdb create table myTable( col1 int, col2 int, col3 int ) insert into myTable values (1, 1, -998), (2, -998, 2), (3, 2, 1), (-998, 1, 3) ;with cte as( select rn = row_number() over(order by (select null)), col = col1 from myTable union all select rn = row_number() over(order by (select null)), col = col2 from

Categories : SQL

Using Commit in a SQL Server Trigger
You can make the trigger put the record into the StagingTable2 - or not, so the absence of it will indicate an unsuccessful validation. You can even put error descriptions, along with row' key, into some other table, so that you will always know which ones failed to pass. Or you can use Service Broker for that, if your servers are far enough away from each other.

Categories : SQL

SQL Server recursive function getting middle data tree
This should get you what you need: WITH CTE AS( SELECT ID,Name,parentID, 1 Depth FROM YourTable UNION ALL SELECT E.ID,E.Name,E.ParentID,Depth+1 Depth FROM YourTable E INNER JOIN CTE ON E.ParentID=CTE.ID) SELECT * FROM CTE WHERE Depth = 2 NOTE that the where clause in the second select gets the depth, and the answer assumes that you are looking for the second level.

Categories : SQL

Eliminating pairs of records leaving the third one out in SQL server
OK - I created a table called "test" like you have and managed to get this query to return what you want. It's a bit involved, but I think it works. select * from test where id not in ( select t1.id from test t1 full join test t2 on t1.balance+t2.balance=0 and t1.someamt+t2.someamt=0 where t1.id is not null and t2.id is not null and t1.id<t2.id and t1.id not in ( select

Categories : SQL

Auto update two columns from 2 tables
You may create a trigger like this: on updating dbo.Parts table update dbo.part_numbers table and vice versa. Here is a MSDN article about triggers: http://msdn.microsoft.com/en-us/library/ms189799.aspx

Categories : SQL

Error in SQL Developer during creating tables
The first two tables are getting created, but third table has error. Drop the tables and recreate with correction to third table syntax The error "missing left parenthesis" is with the foreign key constraint declaration, Use this instead CONSTRAINT fk_no FOREIGN KEY ("no") REFERENCES Song("song_code"), CONSTRAINT fk_alb_code FOREIGN KEY ("in_alb_code") REFERENCES Album("alb_code") With

Categories : SQL

Error converting varchar to datetime in SQL
I don't have your database to play with, but would something like this work? select try_convert(datetime,LEFT([CRSE_EDTE], 4)+'-'+RIGHT([CRSE_EDTE],2)+'-28',111) as new_CRSE_EDTE FROM [SMBM_DBPELJ].[DBPELJ].[MSTUMASTER]

Categories : SQL

Why SQL Server Optimizer do not use CHECK constraint definitions to find which table contains the rows?
The CHECK CONSTRAINT expression must be sargable in order for the optimizer to eliminate the unneeded tables in the execution plan. The constraints below avoid applying a function to the column and are sargable: CREATE TABLE dbo.A2013 ( Id int IDENTITY(1, 1) , CountA int , Name varchar(50) , ADate datetime NULL CONSTRAINT CK_A2013_ADate CHECK ( AD

Categories : SQL

Why does this SQL order null values last?
This is your order by: ORDER BY (CASE WHEN myDate IS NULL THEN 1 ELSE 0 END), myDate The first expression for the order by says "Give the NULL values a value of 1 (for the sort) and non-NULL values a value of 0". Well, you are sorting in ascending order, so the NULL values go last. If you want them first, use desc: ORDER BY (CASE WHEN myDate IS NULL THEN 1 ELSE 0 END) DESC,

Categories : SQL

Finding IDs that join with multiple alternate IDs matching criteria
I'm not 100% that I understand what you are looking for. I think it is fees of a certain type applied to a particular meeting for a particular client. If so, your basic query is on the right track, but it needs a group by and some simplification in the case (the case is redundant with the where): select c.client_name as "Client", m.meeting_id as "Meeting ID", m.meeting_date as "Meeting Date",

Categories : SQL

Updating sql table with results from left join (sql server)
You just need to move the case around so it's like this: select distinct a.column1 , a.column2 , a.column3 , a.column4 , a.column5 , case when x.column9 is not NULL then 1 else 0 end AS column6_flag , a.column7 , x.column9 from #Alldata a left join ( select m.column9 from #Masterfile m left join table2 n on m.id=n.id ) x on a.id=x.id

Categories : SQL

Hive self join based on previous date
The second query won't work, the date compare shall be within the join, as following: select c.date, count(distinct c.name) as count_name from db c left join db p on lower(c.name) = lower(p.name) and p.date < c.date where p.name is null group by c.date You can test it on http://sqlfiddle.com/ using the below schema: CREATE TABLE db ( id int, name varchar(255), date varchar(20) ); insert

Categories : SQL

How to change mileage representation forms in sql
There are a variety of ways to do this. Which one depends on your situation, how the data needs to be stored, and how it is being interacted with. Some of these options include: Changing the datatype. This option would potentially require you to change how the data is being stored currently. The conversion of the data would have to be done by whatever is writing the data to the schema cur

Categories : SQL




© Copyright 2018 w3hello.com Publishing Limited. All rights reserved.