w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
  Home » SQL » Page 1
Add more than one filter to a query using same column
Change the Where condition like this. Use OR operator or IN Clause instead of AND The AND operator displays a record if both the first condition AND the second condition are true. The OR operator displays a record if either the first condition OR the second condition is true. WHERE DATEDIFF(YEAR, A.AcquiredDate, GetDate()) >=5 AND A.AssetTypeID = 7 AND (A.CategoryID = 5 or A.Catego

Categories : SQL

Is there a way to perform a cross join or Cartesian product in excel?
Try using a CROSS JOIN. Read more at MSDN You can use the expression CROSSJOIN(table1, table2) to create a cartesian product.

Categories : SQL

Show same columns with different values (and empty values)
This is not something you are supposed to do with SQL. Don't do it with SQL, it would be complex to develop and maintain. SQL helps you retrieve your data, you are able to do some kind of presentation stuff, but as soon as it becomes too complex, you know you are having the wrong approach. You should do this kind of presentation in your front end application

Categories : SQL

How to show that something does increases the expressive power?
First you must decide what is that is being expressed by two notations. (Ie what it is that they are expressing, ie are expressive of, ie are denoting.) Otherwise, the problem doesn't make much sense. Eg: As long as two notations' sets of expressions are countably infinite they can be set in 1:1 correspondence. So anything that one set's expressions can express the corresponding expression from t

Categories : SQL

Interleave two SQL columns into paired array
This solution is very specific to this case (i.e. 2 items per id) so it might not be sufficient but it does work in your case: select id , ARRAY[min(kv), max(kv)] from (select id , ARRAY[key, val] kv from fruit ) s group by id order by id; ┌────┬───────────────────────┐ │ id │ array

Categories : SQL

SQL query use of Group by functions with tricky logic
select y.Item, y.SubItem, y.GroupQty from ( -- Rank the items by quantity and exclude the items that only have 1 subitem. select x.Item, x.SubItem, x.GroupQty, dense_rank() over (partition by x.Item order by x.GroupQty) as GroupRank from ( -- Group by item and subitem. Sum quantity and count the number of subitems per item. select

Categories : SQL

Fast way to count rows of a cursor
You can do like this, pass a variable to function and function would store amount of rows in it: function search_foo(param1 varchar2, param1 varchar3, PARAM_ROWCOUNT OUT NUMBER) -- here return g_ref -- a ref cursor is l_return g_ref; { open l_return for select col1_i_need, col2_i_need from foo join bar on --[...] where [...]; PARAM_ROWCOUNT := l_return%ROWCOUNT;

Categories : SQL

Aggregation query: group and count by multiple number of dynamically selected columns
select user_profile, page_viewed, count(*) as cnt, '# session_ids ' || STRING_AGG(session_id, ' and ' order by session_id) as sess from ( select max(case when event_type = 'user_profile' then event_value else null end) as user_profile, max(case when event_type = 'page_viewed' then event_value else null end) as page_viewed, ses

Categories : SQL

Access Linking Subform to Tab Control in Main Form
You wouldn't be able to update any information if the table is opened simultaneously 5 times. If it's at all possible, I'd suggest using a Combo Box to allow your users to filter based on any number of criteria, and then pass the SelectedValue of the Combo Box into the RecordSource of the form: Dim MySQL as String MySQL = "SELECT * FROM MyTable WHERE MyField = '" & Me.MyComboBox.SelectedVal

Categories : SQL

SQL: How do I combine tables on a single but non-unique identifier?
You need a key for joining them. The implicit key is the ordering. You can add that in explicitly, using row_number(): select coalesce(t1.id, t2.id) as id, t1.value, t2.value from (select t1.*, row_number() over (partition by id order by (select NULL)) as seqnum from table1 t1 ) t1 full outer join (select t2.*, row_number() over (partition by id order by (select NULL)) as

Categories : SQL

Import from CSV file to SQL Error
You need to pre format the cell in excel to "number", set decimal place to 0, currently it will be set at general but this struggles with larger numbers such as yours. Had the same issue myself earlier. EDIT As commented below you will obviously need to account for such a large number in SQL server bigint will be enough. EDIT 2 Think the only way for you to go would be to set this as a text fi

Categories : SQL

Improve sql query without using 'union'
This would do it, provided that value is mandatory in yy and ww. But like Gordon Linoff already commented, a query like this doesn't necessary perform better. Most likely it won't, and it isn't much more readable as well. I would choose the union. SELECT xx.info, nvl(yy.value, ww.value) as value FROM table_xx xx LEFT JOIN table_yy yy ON xx.uniqeId = 1 LEFT JOIN table_ww ww ON xx.uniqeId

Categories : SQL

postgresql to_json() function escapes all doublequote characters
This answer points out that simply casting to json should suffice: UPDATE factor_value SET params = jsonResult::json WHERE id = _id; The weird escaping you see is probably due to postgresql not realizing you already have valid JSON and your varchar is just converted to a plain javascript/JSON string.

Categories : SQL

Why does it take more time when I fetch an info from more than two tables?
There's no way to know for sure without seeing the structure of your tables (and it's not clear what "more time" means). But the most likely reasons are: Table3 does not have an index on building_name. There are a lot more records in Table3 than Table1. Table3 has many more columns than Table1.

Categories : SQL

Power Query SQL code issue
Close the parentheses.... SELECT Groups.GroupName, AgentTeams.TeamName, Agent.Firstname, Agent.Lastname, CRC.Description, Count(History.HistoryID) AS CountOfHistoryID FROM ((GroupAgent INNER JOIN Groups ON GroupAgent.GroupID = Groups.GroupID) INNER JOIN ((Agent LEFT JOIN History ON Agent.AgentID = History.AgentID) LEFT JOIN CRC ON History.CRC = CRC.CRC) ON GroupAgent.AgentID

Categories : SQL

SQL-Oracle: Difficulties in resolving basic problems
You can probably change your query to something like SELECT COUNT(employee_id) AS "Total", SUM(case when hire_date BETWEEN '01-01-2005' AND '31-12-2005' THEN 1 else 0 end) as '2005', SUM(case WHEN hire_date BETWEEN '01-01-2006' AND '31-12-2006' THEN 1 else 0 end) as '2006', SUM(case WHEN hire_date BETWEEN '01-01-2008' AND '31-12-2008' THEN 1 else 0 end) as '2008', SUM(case WHEN hire_date BETWEE

Categories : SQL

Removing ' ' from sql query and stored proc
Since you have said that you have written an SP, I think the inpput parameter is specified as NVARCHAR or VARCHAR Below is one way of doing but i'm guessing that the column called data will only have integer values in the first solution. DECLARE @X VARCHAR(5) SET @X = '1 ' SELECT CAST(@X AS INT) The above is only if the Data column specified above is Integer. If the same is string (VARCHAR)

Categories : SQL

Oracle SQL - retrive data by GROUP BY and then derive the data based on the group by result
Use a CASE construct for this. For the 'C' and 'Y' thing you'd have to count conditionally (i.e. also with CASE). select col1, case when min(col3) = max(col3) then min(col3) when count(case when col3 not in ('C','Y') then 1 end) = 0 then 'B' else 'U' end as col3 from mytable group by col1;

Categories : SQL

Oracle join of two tables where joined to table returns highest of a result set restricted by the joining (parent?) table?
You can do this with a correlated subquery: select aml.*, (select max(atl.ending_odometer) from auto_trip_log atl where alt.auto_id = aml.auto_id and atl.trip_datetime <= aml.maint_datetime ) as ending_odometer from auto_maint_log aml; This query uses max() because (presumably) the odometer readings are steadily increasing. EDIT: select aml.*,

Categories : SQL

sql server how to decollapse data
As a starting point you can use the following query: SELECT ID, Idx, StartStop, LEAD(Idx) OVER (PARTITION BY ID ORDER BY IdxRowNum) AS NextIdx, LEAD(StartStop) OVER (PARTITION BY ID ORDER BY IdxRowNum) AS NextStartStop FROM ( SELECT ID, Idx, StartStop, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Idx, StartStop) AS IdxRowNum FROM (SELECT ID, ST

Categories : SQL

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




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