Mgiyuk

Create Index equivalent from Oracle to SQL Server I have a current index that I have pulled from Oracle and I am trying to add to SQL Server. As far as I can tell, I will have to remove the double quotes and use WITH(... However, I cannot find the Maxtrans and other keywords equivalent in SQL Server, especially the M_ROW$$ , which I was thinking it would be the ROW_NUMBER . WITH(... M_ROW$$ ROW_NUMBER CREATE UNIQUE INDEX ""MYDB"".""I_SNAP$MYTABLE"" ON ""MYDB"".""MYTABLE"" (""M_ROW$$"") PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE ""IL_MIS_INDX"" Any help will be greatly appreciated. Thank you! M_ROW$$ is the name of a column o...

Finding Duplicate and Missing (null) Values in Oracle Table I am looking for errors in a table and want to report both duplicates and missing values. I am unsure of the best way to do this and am looking for advice on a better way to accomplish this. This is in Oracle 12c. This appears to achieve the desired result: SELECT a.id, a.mainfield, a.location, b.counter FROM maintable a INNER JOIN ( SELECT mainfield, Count(*) counter FROM maintable GROUP BY mainfield HAVING Count(mainfield) > 1 OR mainfield IS NULL ) b ON a.mainfield = b.mainfield OR ( a.mainfield IS NULL AND b.mainfield IS NULL ) ORDER BY a.mainfield; This works and gives me the ID, the potentially null MAINFIELD, the location and a count of either the duplicate MAINFIELD values or the null MAINFIELD values. Is there something simpler or potenti...

Select all substrings that are contained in a string I have a string that contains multiple substrings that are separated by a delimiter character. substr1#substr2#substr3...#substrN substr1#substr2#substr3...#substrN I want to query for all the values in a column that are also in this string. What I have so far is: SELECT substring_col FROM table WHERE id IN SUBSTR(str_to_search,INSTR(str_to_search,substring_col),INSTR(str_to_search,'#',INSTR(str_to_search,substring_col))-1) However, this only returns the first substring that is found. How can I make it return all substrings that are found? Could you share an example? This sounds easy but somehow I want to confirm if I am getting what you are asking. – trollster Jun 29 at 14:33 Probably duplicate of stackoverflow.com/questions/51045592/extract-nth-subst...

Get last value from a certain group (Oracle) I have something like this Date Group ID 11/01 'A' 1 12/01 'A' 2 13/01 'B' 3 14/01 'B' 4 What i basically want is to get for example the latest from group 'A' Date Group ID LatestID_from_GROUP_A_ordered_by_recent_date 11/01 'A' 1 2 12/01 'A' 2 2 13/01 'B' 3 2 14/01 'B' 4 2 or at least something like this Date Group ID LatestID_from_GROUP_A_ordered_by_recent_date 11/01 'A' 1 null 12/01 'A' 2 null 13/01 'B' 3 2 14/01 'B' 4 2 Where does Latest_from_A come from? – user8035311 Jun 29 at 15:49 Sorry it's basically the Latest ID from group A which is 2 (it doesn't neccesar...

Nth max salary in Oracle To find out the Nth max sal in oracle i'm using below query SELECT DISTINCE sal FROM emp a WHERE ( SELECT COUNT(DISTINCE sal) FROM emp b WHERE a.sal<=b.sal)=&n; But According to me by using the above query it will take more time to execute if table size is big. i'm trying to use the below query SELECT sal FROM ( SELECT DISTINCE sal FROM emp ORDER BY sal DESC ) WHERE rownum=3; but not getting output.. any suggetions please .. Please share any link on how to optimise queries and decrease the time for a query to execute. what's this DISTINCE sal or is it DISTINCT ?? – TeamDataViz. Nov 20 '13 at 9:09 DISTINCE possible duplicate of Find out the nth-highest salary from table – bummi Nov 28 ...

finding highest total price and converting So I have to use a query where I list the trade id stock id and the total price converted to us dollars where it is the highest price total. SELECT tr.trade_id, tr.stock_id, round(tr.price_total * con.exchange_rate,2) as "US Dollars" from trade tr JOIN stock_exchange se on se.STOCK_EX_ID = tr.STOCK_EX_ID JOIN currency curr on curr.CURRENCY_ID = se.currency_id JOIN conversion con on con.from_CURRENCY_ID = curr.CURRENCY_ID WHERE (tr.PRICE_TOTAL) = (Select Max(price_total) from trade) and curr.name = 'Dollar' and tr.stock_ex_id is not NULL group by tr.trade_id, tr.stock_id, round(tr.price_total), tr.price_total, round(tr.price_total * con.exchange_rate,2); Trade (trade_id PK, stock_id FK2, transaction_time, shares, stock_ex_id FK1,price_total) Stock-exchange( stock_ex_id PK, name, symbol, currency_id FK1) conversion( from_currency_id PK, to_currency_id) currency ( currency_id PK, name, symbol expected output should be - trade_...