Tag Info

Hot answers tagged

5

That is the schema, a container that holds primary entities like tables, views, and stored procedures. It is often used for logical or security-related separation. I often tell people that schema reference should always be included whenever mentioning any object, especially when you're using schemas other than dbo: Bad habits to kick : avoiding the schema ...


5

Assuming you want randomish ordering of your output you can just ORDER BY NEWID(). That would jumble up the ordering of the results reasonably well. If you only need to randomize after a certain order is established then you can still use the NEWID() trick. Below are some quick examples: -- Fully random ordering SELECT * FROM dbo.Foo F INNER JOIN ...


4

Another reason why you should use CONSTRAINTs instead of some inside-application-code: What happens if a developer / dba use an insert / update / delete statement to modify the data direct in the DB? In this case all your nice application based referential integrity will be useless. I know, some devs like the possibility to modify data direct without ...


4

To give you a flavour of JOINs and SQL, I created two tables - Customer and Cust_Order as shown. I then loaded these tables with data (see end of post for DML). These examples use both PostgreSQL and MySQL. A note on table names. I use singular names - you can, of course, use plural (as many do) - but decide and stick to one! A word of advice (and see ...


4

It gives an error: ORA-01790: expression must have same datatype as corresponding expression From the documentation for UNION: The corresponding expressions in the select lists of the component queries of a compound query must match in number and must be in the same datatype group (such as numeric or character). Tested at SQLFiddle


4

In this case, FullAccess is the schema of the object. You can learn more about multipart names from Books Online (scroll down to "Multipart Names" section), and there is also a good discussion about schemas in this Stack Overflow question. How are you determining that you don't have a schema named FullAccess on the database?


3

Problem This is a more complex problem than is obvious on a quick glance. You are sorting by two columns, each from a different table, while you join on two other columns. This makes it impossible for Postgres to use the provided indexes and it has to default to (very) expensive sequential scans. Here is a related case on dba.SE: Can spatial index help a ...


3

This answer is wrong, because the value for rownum is assigned before the order by is executed. Below is the correct one, SELECT start_date, end_date, resort_id, ROWNUM FROM (SELECT start_date, end_date, resort_id FROM employee_activity WHERE employee_id = 27 ...


3

Something like this (untested) should work for you: select *, case q20 when '8' then 1 when '9' then 1 else case q46 when '8' then 1 when '9' then 1 else 0 end end as promoter from ##csat_resp; You could also write the same logic as: case when q20 in ('8','9') or q46 in ('8','9') then 1 else 0 end or in SQL ...


3

Given the additional information that Aaron Bertrand didn't have access to when he posted his answer I would suggest a different tack. Instead of putting logic/business significance in table names I would have general table names and put the logic/business significance in attributes/data in the tables. This should make it easier to expand functionality, ...


2

Probably easiest would be an INSTEAD OF INSERT trigger. CREATE TRIGGER dbo.FixTable1 ON dbo.Table1 INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON; DECLARE @offset INT; -- decimal? Something else? SELECT TOP (1) @offset = offset_m FROM dbo.Table2 -- WHERE ...? ORDER BY [timestamp] DESC; -- terrible column name btw INSERT ...


2

There are any number of ways to express this in T-SQL. One that works (without using APPLY - which you should learn to love, by the way) is below, with comments inline: WITH SampleData (PERSON,TRANSACTDATE, STARTDATE, END_DATE, IN_PUNCH,OUT_PUNCH,HOURS, PAYCODE) AS ( SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '06:00','09:00','3', 'REG1' ...


2

There's probably something more elegant than this, but since you are doing a kind of pivot (transforming from columns to rows, i.e. metadata to data) it is never going to be pretty. Assuming a table like: create table t ( ts timestamp not null , EngOilP_sd smallint not null , CompOilLVL_sd smallint not null ) engine = innodb; insert into t (ts, EngOilP_sd, ...


2

You can use a CTE to supply the values: with data (street, city, user_id) as ( values ('street1','LA', 2) ) insert into addresses(street, city, user_id) select * from data where exists (select * from users where users.id = data.user_id and users.storeaddress = true); Or alternatively a derived table: insert ...


1

Try dumping the database, recreating, verifying it's empty, and re-importing? If it does it again then check the actual file you're importing and see if it has everything listed 2x.


1

SELECT [ID Code], statusCode FROM dbo.Table AS t WHERE NOT EXISTS ( SELECT 1 FROM dbo.Table WHERE [ID Code] = t.[ID Code] AND statusCode = 'A' ) GROUP BY [ID Code], statusCode;


1

This is a simple SQL: select t2.category, t2.materials, t2.shipping from categories t1, raw_data t2 where t1.cid ='1-9' and t1.category=t2.category; or select t2.category, t2.materials, t2.shipping from categories t1 join raw_data t2 on t1.category=t2.category where t1.cid ='1-9';


1

SQL> alter session set nls_Date_format = 'DD-MON-YY HH24:MI:SS'; Session altered. SQL> select trunc(sysdate, 'DD') + 13/24 from dual; TRUNC(SYSDATE,'DD' ------------------ 29-AUG-15 13:00:00


1

I just read this article adressing this topic and I suppose you might be interested, because it uses the lag function to solve exactly your Task: https://www.mssqltips.com/sqlservertip/3690/identify-sql-server-database-growth-rates/ The Author created the following Script using the LAG Window function: SELECT DISTINCT A.[database_name] , AVG( ...


1

Without a lot more info, etc. couldn't you just create an EngageTrips table, and create an CreatedTrips table and hook a foreign key into those and then join the data once the function is run to bring that data for view to the end-user? Also meaning control how the data is entered into each table at the data entry level based on criteria when the data is ...



Only top voted, non community-wiki answers of a minimum length are eligible