spot7.org logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML Categories
  Home » SQL » Page 5
Query rows between dates and before
Joining on the @users table variable and using "row_number() where = 1" should be faster for selecting the top record per UserId... select t.Id, t.UserId, t.StartUnixTime, t.StopUnixTime from MyTable t inner join @users u on t.UserId = u.Id where t.StartUnixTime between @start and @end union all select Id, UserId, StartUnixTime, StopUnixTime from ( select

Categories : SQL

Left join expression and amount of rows returned by Oracle
Your assumption that t2.id = t2.id is always true is wrong. If the value were NULL that would be treated as false. I don't believe that is relevant for this particular example, but just to clarify. The question is how is a left join processed. The idea is simple. The on clause is processed. If there are no matches, then the row from the first table is kept. This is regardless of what is in

Categories : SQL

Number of repeating customers in SQL Server
try this: For numbers of the customers who used more than one payment type. select COUNT(PaymentType) CountOfCust,PaymentType,CustNum from Customers Having COUNT(PaymentType)>1 GRPUP BY PaymentType,CustNum

Categories : SQL

Unable to retrieve the month from date parameter using vba function in mdx
(try uppercase 'MM' rather than 'mmm' - even 'mm' is wrong as it will look for minutes rather than months) This question and answer looks at working with dates: Changing a date format to a shorter date MSDN is a good reference for the available vba functions in mdx that you can use to play around with dates. Current link is here: http://msdn.microsoft.com/en-us/library/hh510163.aspx I'm assumin

Categories : SQL

SQL Query CASE WHEN Statement Probelm
My guess is that the line: WHEN tr_time > '75600' or tr_time < '14400' and tr_loc = '6-LDT2' then '3' is being interpreted as: WHEN tr_time > '75600' or (tr_time < '14400' and tr_loc = '6-LDT2') then '3' Then would come to the first clause: WHEN tr_time > '75600' Which satisfies the OR statement, giving a value of '3' .

Categories : SQL

Display column name with max value between several columns
You can do this with a big case statement: select t.*, (case when denver = greatest(denver, seattle, new_york, dallas, sanfran) then 'denver' when seattle = greatest(denver, seattle, new_york, dallas, sanfran) then 'seattle' when new_york = greatest(denver, seattle, new_york, dallas, sanfran) then 'new_york' when dallas = greatest(denver, seattle, new

Categories : SQL

Generic set returning function
The answer is yes. But it's not trivial. As long as you return anonymous records (returns setof record) a column definition list is required for the returned record to work with it in SQL. Pretty much what the error message says. There is a (limited) way around this with polymorphic types: CREATE OR REPLACE FUNCTION cs_get(_tbl_type anyelement, _colname text) RETURNS SETOF anyelement AS ...

Categories : SQL

Buggy query for dropping databases
This script will make sure you are connected to that database, then change database mode to single user, it means only one user can be connected to a database, then you will connect to Master database it will leave database to be dropped with no connections, and finally you can drop the database. Also use appropriate data types, your database will never be 2000 characters then why use a variable

Categories : SQL

SQL - returning two matching values
Just use order by and limit or top or fetch first 1 row only. For example: SELECT PARTNER_WORKORDER.statusNo as number, count(statusNo) as total FROM PARTNER_WORKORDER inner join PARTNER_WORKORDER_ADDRESS ON PARTNER_WORKORDER.salesOrderNo = PARTNER_WORKORDER_ADDRESS.salesorder_no WHERE orderDt <= '2012-11-20 00:00:00.000' AND country_code <> 'US' group by PARTNER_WORKORDER.sta

Categories : SQL

Conversion failed when converting the varchar value '.03' to data type int
You have this expression: SUBSTRING(cast((NoOfHoursWorked) as varchar(500)),3,4)*10/60.00 ------------------------------------------------------ You are multiplying a substring by integers. I have no idea what NoOfHoursWorked is. It would seem strange to me if this were stored as anything other than a numeric. So, perhaps this will work: NoOfHoursWorked*10/60.00 If you do need the strange

Categories : SQL

Query to Sum Numbers Based on Location (State)
I went back and used a UNION statement to give me the correct answer: select sum(procperf) as 'Procedures Performed' from ( select sum(proceduresperformed) as procperf from DriveProjectionAndCollectedTotals DPaCT inner join rpt_DriveMaster DM on DPaCT.DriveID=DM.DriveID left outer join rpt_Accounts Acct on DM.AccountID=Acct.AccountID inner join rpt_AddressDetail AD on Acct.AccountID=AD.AccountID

Categories : SQL

Change database ownership SQL Server 2012 Express
For this to work, two things must be true, per Books Online: Server objects (such as databases) must be owned by a server principal (a login). This means the account you want to give ownership must have a login on the server (not just a user in the database -- it is possible to have users without logins if you restore from a backup). And second, Requires TAKE OWNERSHIP permission on t

Categories : SQL

How a query can verify itself that all template parameters are replaced before executing
Can you replace the params with actual variables, but not actually declare the variables? That way, if they haven't replaced the variable with the desired values, the parser will complain about the variable not being initialized. IF ( @Template_param + @Value = @Template_param_Value ) BEGIN RAISERROR (@ErrorMsg, 18, -1, N''); END

Categories : SQL

find column number in a SQL table in IBM netezza database
You can get that information by joining the _v_table and _v_relation_column views. SELECT tablename, attname AS COL_NAME, b.FORMAT_TYPE AS COL_TYPE, attnum AS COL_NUM FROM _v_table a JOIN _v_relation_column b ON a.objid = b.objid WHERE tablename = 'TEST1' ORDER BY attnum; TABLENAME | COL_NAME | COL_NUM -----------+----------+--------- TEST1 | COL1 |

Categories : SQL

SQL Rounding and Selecting
To do them all UPDATE MyTable SET value1 = ROUND(Value1,0) To just do the ones that need it UPDATE MyTable SET value1 = ROUND(Value1,0) WHERE Value1 - cast(Value1 as int) > 0

Categories : SQL

Unexpected AST Node LEFT OUTER JOIN
Couple things: defm doesn't appear to be a column so change it to * or the columns you want. also =: isn't needed here as you're doing a left join syntax. I belive the := or =: was old ANSI style SQL for joins. . SELECT * FROM DefermentAccount defm LEFT JOIN defm.division division ON division.divisionID=defm.defermentID

Categories : SQL

SQL working select with group by - how to get the id of found records?
You can use your grouped query (slightly modified), to get the lat and lng you're interested in, and then join those results back to the facilities table to get the other data - id in this case.. Something like: SELECT a.lat, a.lng, a.locationCount, b.id FROM (SELECT lat, lng, count(*) locationCount FROM facilities GROUP BY lat, lng HAVING count(*) > 1) a INNER JOIN facili

Categories : SQL

select rows from main table based on highest date in child table between a date range
You will need to pull the MAX per ApplicantId with a GROUP BY in a sub-query, then JOIN to that result. This should work for you: Select A.ApplicantId, A.[Applicant Name] From ApplicantTableName A Join ( Select D.ApplicantId, Max(D.Dt) DT From DetailsTableName D Group By D.ApplicantId ) B On A.ApplicantId = B.ApplicantId Where B.DT Between '03/05/2014' And '04/20/2014'

Categories : SQL

Pull field from master table where two child tables have matching data
Questions is a bit unclear on which fields are in which table but choose which version works for you. SELECT ID FROM FileName fn INNER JOIN DepartmentA da ON fn.ID = da.LastNameA INNER JOIN DepartmentB db ON fn.ID = db.LastNameB WHERE da.LastNameA = db.LastNameB Or SELECT ID FROM FileName fn INNER JOIN DepartmentA da ON fn.ID = da.ID INNER JOIN DepartmentB db ON fn.ID = db.ID WHERE da.LastName

Categories : SQL

In SQL, split an amount among multiple rows
The following uses CTE approach to run the calculate the totals. DECLARE @Travel TABLE (ID INT, State VARCHAR(10), Days_Count INT) DECLARE @Travel_Sum TABLE (State VARCHAR(10), Exceeding_Amt INT) INSERT @Travel VALUES (1, 'AL', 20), (2, 'AL', 2), (3, 'AL', 14) INSERT @Travel_Sum VALUES ('AL', 24) ;WITH TravelRows AS ( SELECT ID, State, Days_Count, ROW_NUMBER(

Categories : SQL

"Standard" method for refreshing production cubes SSAS
I prefer to use an SSIS package, mainly for the logging. I send the logs to the msdb..sysssislog table so you get detailed logging with minimal effort. It's very useful when a build fails, as SSAS typically puts out hundreds of irrelevant "process cancelled" messages. I do all the design work in Visual Studio, with an Environment configuration pointing to the DEV environment, then switch to a P

Categories : SQL

How do I exclude multi-line set of data when ONE line of data has a specific value? SQL-ORACLE
You need a correlated subquery for the NOT EXISTS: NOT EXISTS (SELECT 1 FROM sqlmgr.PRICE P2 WHERE p2.ITEM = p.ITEM AND P2.PRICE = 'PRICE1' ) Your version returns nothing because there are records that have PRICE1 in them -- without the correlation piece (p2.ITEM = p.ITEM), it would check the entire table instead of just the item of interest on a given row. N

Categories : SQL

Use value from qry using Count and update to a table in Access 2007
If I understand correctly, you can do this with a correlated subquery. The following does the update for all records: update tbl_CalcReg set NumRecords = (select count(*) from tbl_Interpolation as i where i.unit = tbl_CalcReg.unit and i.car = tbl_CalcReg.car and i.pad = tbl_CalcReg.pad ) You can ad

Categories : SQL

unexpected LEFT OUTER JOIN behaviour in SQLITE
How about something like this? SELECT table2.ticker, table1.weight, table2.weight FROM table2 LEFT OUTER JOIN table1 ON table1.ticker = table2.ticker

Categories : SQL

How to select what button is being clicked on a multi-button click event, backed by sql
This is how I determine which button was clicked, and then use a Select Case to determine what to do with said button click. Dim bt As Button = o Dim btName As String = bt.Text In your case if the name of the button or the id is tied directly to a value you can just pass that into your parameters by accessing it from the bt variable.

Categories : SQL

SQL Server : query multivalued table recursive
I think that the following recursive CTE will give you what you want: ;WITH cte AS ( SELECT m2.object_id AS groupID, m2.attribute_name FROM @mms AS m1 INNER JOIN @mms_mv AS m2 ON m1.object_id = m2.reference_id INNER JOIN @mms m3 ON m2.object_id = m3.object_id WHERE m1.attribute_type = 'user' AND m3.attribute_type = 'group' UNION ALL SELECT m.object_id AS groupID, m.attribut

Categories : SQL

Joining two tables with PreparedStatement
+ "JOIN ACCOMODATION a" + "ON p.ACCOMNO = a.ACCOMNO " there is no space between "....ACCOMODATION a" and "ON p.AC....." replace them with something like this: + "JOIN ACCOMODATION a " + "ON p.ACCOMNO = a.ACCOMNO "

Categories : SQL

Unicode to Non-unicode conversion
No, Unicode is Unicode. The standard allows for innumerable "special" characters beyond ASCII. The best you can do is search for your desired subset of characters before the conversion and convert them to your favorite ASCII stand-ins.

Categories : SQL

Finding maximum value of group of rows POSTGRESQL
Try this: SELECT Name FROM President pr INNER JOIN (SELECT Party, MAX(years_served) as YS FROM president GROUP BY Party) as MaxYS on MaxYS.Party=Pr.Party WHERE pr.years_served>=MaxYS.YS Hope is what you are looking for.

Categories : SQL

comparing the results in two tables
I think you can use full outer join and some conditions in the select clause: select coalesce(a.id, b.id) as id, (case when a.plan_hash_value is null then 'Plan not found in table t11' when b.plan_hash_value is null then 'Plan not found in table t22' else 'plans same' end) as status, a.cost as cost_t11, b.cost as cost_t22 from t11 a full outer join

Categories : SQL

Insert missing rows (with zero values) into table, following a display pattern
You can do this by generating all the rows first (using a cross join) and then bringing in the values: select y.val_year, g.val_name, g.Group_A, g.Group_B, g.Group_C, coalesce(in_stock, 0) as in_stock from (select distinct val_year from temp_data) y cross join (select distinct val_name, Group_A, Group_B, Group_C from temp_data) g left join temp_data d on d.val_year = y.val_y

Categories : SQL

PostgreSQL Trigger with duplicate values
Finally I found a way to make a TRIGGER wich solves my problem, the code is... CREATE OR REPLACE FUNCTION mvp_player_equal() RETURNS trigger AS $$ DECLARE max_points INTEGER; BEGIN SELECT MAX(mvp_score) INTO max_points FROM MATCH_STATISTICS; IF (TG_OP = 'INSERT') THEN IF max_points = NEW.mvp_score THEN UPDATE match SET mvp_player = (SELECT player FROM MA

Categories : SQL

Cannot assign a default value to a local variable in SQL
Prior to SQL Server 2008, assigning a default value (or initial value) to a local variable is not allowed; otherwise this error message will be encountered. Solution 1: (Use SET) DECLARE @thresholdDate DATETIME set @thresholdDate = '2014-11-30' For more details about the error : http://www.sql-server-helper.com/error-messages/msg-139.aspx Solution 2: (Upgrade) Another way of avoiding this e

Categories : SQL

Is it possible to set no lock or TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at database level?
The short answer is "no". The default isolation level in SQL Server is READ COMMITTED, and there is no way to change this to UNCOMMITTED, either globally or per-database. And that's a very good thing too. WITH (NOLOCK) is a recipe for trouble when it comes to getting accurate results from your database, and in bad cases it can even cause timeouts from queries that run forever due to data getting

Categories : SQL

SQL constraint check with date from table linked with foreign key
This is not possible to do using check constraints, since check constraints can only refer to columns inside the same table. Furthermore, foreign key constraints only support equi-joins. If you must perform this check at the database level instead of your application level, you could do it using a trigger on INSERT/UPDATE on the action-table. Each time a record is inserted or updated, you check w

Categories : SQL

Filter SQL table with max record within two specific dates range
Are you over-thinking it? Why not something like: SELECT EmployeeName, TargetTxDate, sum(ActualTarget) as Actual, sum(RequiredTarget) AS ReqTarget, sum(variance) AS Variance FROM dbo.TargetsTx as TargetsTx WHERE TargetTxDate >= @BgnDate AND TargetTxDate < @EndDate group by EmployeeName,TargetTxDate ORDER BY TargetTxDate DESC LIMIT 1

Categories : SQL

Loop for stored procedure
Try this using CURSOR. DECLARE @sItemNumber AS VARCHAR(MAX) DECLARE TestCursor CURSOR FOR ( select ITEM_NUMBER from xTable ) OPEN TestCursor FETCH NEXT FROM TestCursor INTO @sItemNumber WHILE @@FETCH_STATUS = 0 BEGIN EXEC ProcedureName @sItemNumber FETCH NEXT FROM TestCursor INTO @sItemNumber END CLOSE TestCursor DEALLOCATE TestCursor

Categories : SQL

No rows returned when column has a null value - sql
Edit: Completely rewrote my answer, as I think I understand your question now. Try something like this: WITH UniqueItems AS ( SELECT DISTINCT cid, price FROM Items ) SELECT t.cid,t.price,i2.value,t.name,t.subname FROM (select * from UniqueItems i, Features f) t LEFT JOIN Items i2 ON i2.cid = t.cid AND i2.fid = t.fid UNION ALL SELECT cid, price, value, null, null FROM Items WHE

Categories : SQL

is there anything like timestamp(9) in teradata
Perhaps you can accomplish this by storing three fields in your table: Message_Queue_Timestamp TIMESTAMP(6) NOT NULL Message_Queue_Date DATE NOT NULL Message_Queue_Seconds_From_Midnight FLOAT NOT NULL Using dnoeth's suggestion to convert the input using to_timestamp() to retain a DBA-friendly timestamp in the table. This is Message_Queue_Timestamp. Then split the incoming timestamp into two f

Categories : SQL


Recently Add
If you set a field name as an Alias, can you utilize that name within SQL code?
Increment next month with dayOfTheMonth SQL sybase
Update Table Set From is not working as expected
How do I find one matching strings in two txt files
SQL help needed (oracle application express)
How to Roll Up Weekending Data in SQL
How do you call a variable (that is a list of values) in a conditional statement?
Oracle INSTR backward in Oracle SQL
SQL Server 2008 Agent jobs. Two procedures and a view
Can't create table in Oracle Developer
Trigger error "ORA-01403: no data found" when It is not fire
SQL: alternatives and substitutions for GROUPING SETS and PIVOT
Excel Vlookup too Slow
Is WHERE field IS NULL quicker than >0
BFILE error on oracle
SQL Server : Select from two tables with default value 0
Use of uninitialized value after fetchrow()
Why did this UPDATE change all rows instead of just one row?
Oracle / SQL Counting Different values from one Column and storing it in a variable
How can I calculate the percentage of occurrences of a value in a column?
To compute sum regarding to a constraint
Enterprise Architect - Execute SQL Issue
First business day of the current month - SQL Server
Incorrect no. of rows fetched in SQL
SQL query design with configurable variables
Grouping dates by dateadd and datediff
SQLServer get top 1 row from subquery
SQL Using CONTAINS() doesn't work for AND & OR
SQL Server: Persisting computed column
Improve SQL Server query
© Copyright 2017 spot7.org Publishing Limited. All rights reserved.