w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
  Home » SQL » Page 2
Remove Duplicates and SUM
SELECT sku_master.sku , SUM(location_inventory.qty_on_hand) FROM [FCI].[dbo].[location_inventory] JOIN [FCI].dbo.[sku_master] ON location_inventory.sku = sku_master.sku WHERE sku_master.min_on_hand > 0 GROUP BY sku_master.sku

Categories : SQL

MS SQL - how to take certain data out of tables based on ID of another table
You will need to INNER JOIN the tables together. Try this: select [Name], [job], [Table2].[desc] from [Table1] inner join [Table2] on [id] = [gender] where [Table1].[Name] = 'Bob' You can use the WHERE clause to limit to a specific row. If you want all the rows, then remove the WHERE clause.

Categories : SQL

SQL - Update same table based on a condition in WHERE
This will update the single row value you return in your original script: UPDATE [table] SET Field1 = 1, Field2 = abc WHERE ID = ( SELECT MIN(ID) FROM [table] WHERE GETDATE() < ExpirationDate )

Categories : SQL

Bigquery - Substitute field if other field is blank
You need IFNULL(expr, null_default) If expr is not null, returns expr, otherwise returns null_default. You should read more about them in the manual. Your query would look like: SELECT IFNULL(DealID,sales_rule) as DealID, ExternalReference, order_number, sales_rule FROM flostream.orders JOIN mobileheads.surveys ON flostream.orders.ExternalReference = mobileheads.survey

Categories : SQL

Table with one row vs Table with vertical list
You use name/value pairs when the structure needed varies. For example you store products and 50% have a material mix, 2% have a CPU type, 3% have an rpm, and 20% have an energy class. Rather than building a table of maybe a thousand such attributes, you may use the genric approach then. But you do this at a cost: the dbms doesn't know of your content and cannot help you ensure integrity. In your

Categories : SQL

Filter XML SQL query result
If you want to filter data, then it should be a WHERE clause, not a projection list: SELECT ... FROM "TABLE" WHERE XMLSERIALIZE(XMLQUERY ('//xmlList/XMLEntity/sug[.!="ACHER" and .!="DOA"]' passing KTOVET ) as char large object) != '' -- or whatever comparison you need here But I would recommend using xml.nodes() method instead. D'oh obviously not on DB2.

Categories : SQL

How to list the total sal of a emp and tsal >1500
No, you can't use column alias in WHERE condition; rather use the same expression/calculation in your WHERE condition like select ename,sal, (sal+comm) as tsal from emp where sal + comm > 1500 You can as well use a outer query/inline query, to use the alias as column select * from ( select ename, sal, (sal+comm) as tsal from emp ) tab where tsal > 1500

Categories : SQL

Join Statements in Access SQL
Try like below; get the filtered data and do a JOIN with that result set SELECT tbl_historicOrg.NAME, tbl_historicOrg.Racf, tbl_historicOrg.STATUS, tbl_historicOrg.PCF, tbl_historicOrg.[Date Finalized], tbl_historicOrg.[Hist Month], tab.SCHDLE_EXCPT_NM, tab.HOURS FROM tbl_historicOrg LEFT JOIN ( select SCHDLE_EXCPT_NM, HOURS, RACF from import_data where SCHDLE_EXCPT_NM = "S

Categories : SQL

Need sql statement review for vb.net windows forms express desktop 2013
In your string-building for your SQL statement, you have no spaces where you need them. They way its written, here's what your select statement looks like when it's compiled, and probably explains why it doesn't work. INSERT INTO Production.dbo.tblFabWipLog (FabWipId, Shear, CheckedIn, NameIn, CartType)SELECT TOP 1 FabWipID, cast('[somevalue]' as varchar), Cast(getdate() as smalldatetime), cast(

Categories : SQL

Unable to create a View that lists all "Locations," alongside number of "departments" and number of "employees" in those departments
First, figure out how to JOIN the two tables into one big table, so you new table has all of the columns from both tables. CLUE: Look up JOIN and use the DEPTNO field select * from Emp join Dept on emp.deptNo=Dept.deptNo Now, GROUP BY this result by Location, Department name select Loc,dName,count(*) from Emp join Dept on emp.deptNo=Dept.deptNo group by loc,dName This should get your

Categories : SQL

Linq to SQL select one of every different result
You simply have to group the data by template, and take the first element of each group. var firsts = from e in g_lisFilters where f.Template == "3" group by e.Sortering into groups select groups.First(); Or var firsts = g_lisFilters .Where(e.Template="3") .GroupBy(e=>e.Sortering ,(key,g)=>g.First());

Categories : SQL

Sql Function Issue "The last statement included within a function must be a return statement"
Function expect a return at the last line. Just modify your function by assigning the result to variable @nReturn and return it in last line. create FUNCTION [dbo].[GetBatchReleaseQuantity] ( @i_LocationID VARCHAR(50), @i_ProductID INT, @i_StartDate VARCHAR(50), @i_EndDate VARCHAR(50), @i_ProductInFlow int ) RETURNS numeric(18,3) --WITH ENCRYPTION AS BEGIN D

Categories : SQL

Loading up data from a SQL Server database?
If that is MYSQL you can do something like this SELECT studentId from [TABLE_NAME] LIMIT 0,2000 you just need to loop around your code or SP to and change the LIMIT numbers.

Categories : SQL

Converting Rows to column values - Return single row
You are aggregating by too many fields. Try this: SELECT MAX(Case When B.ValueOrdinal='1' Then B.Displayvalue end) as Account, MAX(case when B.ValueOrdinal='2' then B.Displayvalue end) as Department, MAX(case when B.ValueOrdinal='3' then B.Displayvalue end) as Misc, A.Text, Sum(A.reportingcurrencyamount) as PostedAmount, A.Recid From GeneralJournalAccountEntry

Categories : SQL

Generating Primary key without set it as key
As mentioned here Entity framework automatically identifies the primary key based on ideal naming like Id in your case :- As detailed in document :- Other default conventions supported by EF include the ability to automatically identify primary-key and foreign keys based on common naming patterns (for example: an ID or DinnerID property on the Dinner class will be inferred as the primar

Categories : SQL

How to get a correlated subquery as column
I think this is a basic aggregation query with a join: select a.cod, a.name, max(ar.date) as date from Artiles a join ArticlesRegisterds ar on ar.cod_article = a.cod group by a.cod, a.name

Categories : SQL

What is this Oracle code doing?
Oracle can use both -1403 and +100 for 'no data found', as mentioned in the exception_int documentation. It's also mentioned in the precompiler manuals; +100 is used in ANSI mode. So the IN (100, -1403) is just covering both possible values for the error raised when no data is found. The final part seems to be declaring and immediately raising a named exception. You can only use raise application

Categories : SQL

Pulling Part of string only with SQL
The script following will bring back everything after '%' DECLARE @string nvarchar(200) SET @string = '0.010% Senior notes, due May 12, 2016' SELECT LTRIM(RTRIM(RIGHT(@string, CHARINDEX('%', REVERSE(@string))-1))) RESULT : Senior notes, due May 12, 2016 Hope this helps.

Categories : SQL

2 different aggregates in a query, can't figure it out
You are showing the average of salaries, where the max() in the department is greater than 10000. So, only one of the salaries needs to be greater than 10000 for the department to show. Perhaps you mean: SELECT department_id, MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary) > 10000;

Categories : SQL

Check and Change for empty or null value of column in SQL?
I'm not entirely sure I understand your question, but if you are trying to catch nulls and empty strings "in one go", try this: select TOP 1 case when length(trim(coalesce(DR.Name, ''))) = 0 then 'Not Exists' else DR.Name as Name .... The coalesce catches the NULLs and sets a replacement value. The trim gets rid of any padding and the length checks if what is left is an empty str

Categories : SQL

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




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