Hot answers tagged

5

I suppose you know the date_start and date_end and you won't work on more than 1000 days although it can be extended. This example works with these values: SET @date_start := CAST('20150101' as date); SET @date_end := CAST('20150108' as date); Below queries return each day between @date_start (2015-01-01) and @date_end(2015-01-08). This is similar to a ...


5

As long as they are not nested, you should be fine. @@FETCH_STATUS is set at the time the FETCH NEXT is performed. @@FETCH_STATUS is global to the session, not the entire server. There is no equivalent to SCOPE_IDENTITY() for @@FETCH_STATUS. If you need to nest looped FETCH NEXT operations, make sure that your outer FETCH NEXT is performed after the inner ...


4

According to the My Oracle Support, ORA-01792 is caused due to an unpublished bug. Oracle suggest to alter the query and/or view definitions to avoid the error. However in cases where the SQL cannot be adjusted then the checking can be disabled by: SQL> alter system set "_fix_control"='17376322:OFF'; Alternatively an interim patch for this fix (patch ...


4

rn cannot be referenced in WHERE because it's defined in the SELECT clause. Try using a subquery instead: SELECT * FROM ( select payee_id,start_dt, row_number() over(partition by payee_id order by start_dt) as rn from xxx ) AS sub where rn = 1; For more reading, google "logical query processing order".


3

mysql error 1064 The error that gets thrown if the syntax is wrong. You did not clearly state what you are doing. If you are changing a column ALTER TABLE table_name MODIFY COLUMN column_name datatype If you are trying to change column definition ALTER TABLE table_name CHANGE old_column_name new_column_name data_type(size) If you are adding a ...


3

You cannot use the CASE expression in DB2 this way. The result of a CASE expression cannot be a boolean value. Your WHERE clause might look something like this, if one were to blindly translate your code: WHERE COUNTRY_CD = '81930' AND LANG_CD = '02' AND ( ( PARM_ADTR_ID = 'ALL' AND (AS_ADTR_ID_P IS NULL OR AS_ADTR_ID_P LIKE '%') ) ...


3

Normalizing this will result in three tables with an many:many relation, the columns in parens () indicate the logical Primary Key: groups: (group_id) ingredients: (ingredient_id), name ingredients_per_group: (group_id, ingredient_id), position This is the junction table between groups and ingredients, the position is just an attribute of the ingredient ...


3

Most likely because the expression 1/10/1971 is interpreted as a numeric expression, i.e. 1 divided by 10 divided by 1971 which is 0 (with integer division rounding values down). Telling Access that this is a date can be done using quotes (not really recommended) or by using a date function, like this: IIf([DOB]<DateSerial(1971, 10, 1), "Mature", "Not ...


3

Joining tables is a fundamental principle of relational databases. In your case, A and B are related with the id column, which means that you can use a syntax similar to this one: SELECT a.id, a.name, a.num, b.date, b.roll FROM a INNER JOIN b ON a.id=b.id; INNER JOIN means that you'll only see rows where there are matching records in A and B. If you want ...


2

In general what you want here is an explicit transaction around your code. BEGIN TRANSACTION ... your code .... COMMIT However there are down sides to concurrency here as well. While your transaction is open any locks needed to perform the tasks will be held until the end of the transaction. That means that the next set of code that needs to run will ...


2

You can use the the max() with an over() clause partitioned by Venue to get the max date for each venue and then use that as the first column in the order by clause. select T.Event_number, T.Venue, T.Date, T.Attendance, T.rn from ( select S.Event_number, S.Venue, S.Date, S.Attendance, ...


2

You could just use %_99_% instead of %99%. % means 0 ore more characters _ means exactly 1 character So %_99_% means at least 1 character before and after 99, and anything before and after. SQL> select * from t1; C1 -------------------- 1991 1199 9911 SQL> select * from t1 where c1 like '%_99_%'; C1 -------------------- 1991


2

I believe MySQL requires the ALTER TABLE ... CHANGE ... statement to restate the column definition (datatype, default value, column constraints &c.) even if it's only the column name being changed.


2

You can write your query like this: ALTER TABLE database_name.sample62 CHANGE old_column_name new_column_name INT(11) DEFAULT 0 NULL;


2

You can assign any numbers with a derived table. generate_series() is instrumental for a simple solution. With a table like this: CREATE TABLE things_to_do AS SELECT * FROM generate_series (1,6) AS thing_id; A simple solution for the given example SELECT * FROM things_to_do JOIN ( -- derived table with prepared timestamps SELECT row_number() OVER ...


2

That can be done with a simple SELECT statement. SELECT count(*) AS count_ , favorite_color_ FROM person_ GROUP BY favorite_color_ HAVING count(*) > 1 ORDER BY count_ DESC ; The count(*) command is an aggregate function supported by Postgres and gives the number of rows with duplicate values in a column specified by the GROUP BY clause. To omit the ...


1

I don't know much of T-SQL but I believe that to compare strings you need the logical operator AND. Try this: UPDATE StaffCopy SET StaffCopy.LastName=IIF(([LastName]='Avon' AND [FirstName]='Sarah'), 'Tyne', [LastName]);


1

There is an error in this line: between 's.proddate 22:30:00' and 'DATE_ADD(s.proddate,INTERVAL 1 DAY) 02:30:00' It should be: select count(*) as t, m.machine1 from sap_prod_data s left join sap_machine_speed m on s.mch_desc = m.machine where year(s.proddate) = '2016' and m.machine1 in ('bla','bla') and s.total_down_time>30 and ...


1

There's no replication or out-of-the-box solution to your problem, particularly since you don't have that type of access to the remote machine. As other commenters here have noted, you're probably going to have to build your own ETL logic, perhaps in one of the following ways: A linked server and stored procedures using OPENQUERY() or OPENROWSET(), An SSIS ...


1

You can use ROW_NUMBER, like this: SELECT ROW_NUMBER()OVER(ORDER BY ID ASC) AS [Count], body FROM @Article AS A WHERE authorid=1 Count body 1 Hi 2 there 3 foo


1

A case for window functions: SELECT count(*) As longest_streak FROM ( SELECT row_number() OVER w - count(status = 'WON' OR NULL) OVER w AS grp FROM game WHERE player_id = 1 -- %some id% WINDOW w AS (ORDER BY date) ) sub GROUP BY grp ORDER BY count(*) DESC LIMIT 1; Or use the aggregate FILTER clause in Postgres 9.4 ...


1

You can use CROSS APPLY to get the MAX(date) for each Venue and then ORDER BY it: SELECT Event_number, Venue, [Date], Attendance, rn FROM dbo.SampleTable s CROSS APPLY ( SELECT md = MAX([Date]) FROM dbo.SampleTable WHERE s.Venue = Venue ) ca ORDER BY ca.md DESC, Venue, s.rn SQL Fiddle By the way, you can insert into the sample table with 1 ...


1

Query #1: You are missing indexes on sessions.ip and auth.session. The latter is really important because now during the join for each row in sessions (~2M) MySQL has to check all ~4M rows in the auth table instead of picking the one or few directly using the index. That means the query is maybe 1,000,000 times slower than it needs to be. You can make the ...


1

Yes, something like the below. SELECT * FROM DataTable WHERE (@SearchField = 'CreateDate' AND CreateDate BETWEEN @StartDate AND @EndDate) OR (@SearchField = 'ModifyDate' AND ModifyDate BETWEEN @StartDate AND @EndDate) Or, to use the example WHERE clauses you specified, how about the below? SELECT * FROM DataTable WHERE ( @ReportType = 1 ...


1

The question is all over the place but I think this is what you are looking for select a.name, u.name, b.note, b.date from table_B as B join table_A as u on u.ID = B.ID join table_A as a on a.ID = B.admin_id where B.ID = @userID you can create a line with select a.name + " took action on " + u.name ...


1

I think that this is a business-driven question that your client/management should decide. Most well-designed databases can scale a lot larger than you may think at first. It's good that you're thinking of an archiving strategy, but you should really only build this on an actual, outspoken business requirement. I'm almost certain that with a little index ...


1

You need to join these tables to get the result that you want. SELECT b.*, a.name FROM tableB AS b INNER JOIN tableA as A ON (b.id=a.id); This query will return everything from Table B and name from Table A where the ID from Table B is the same as the ID from Table A.


1

I doubt if there is a transaction id. But here are some "good practices": Turn off auto-reconnect. (This is a parameter on certain APIs when establishing the connection.) A disconnect terminates transactions and rolls them back. With auto-reconnect OFF, you will get an error on whatever happens next. Check for errors after ever SQL statement. Avoid ...



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