sql

Question: After migrating to Oracle 18c Enterprise Edition, functional index (FBI) creation fails. This is how the DDL for creating an index looks like: CREATE INDEX my_index ON my_table( UPPER (REGEXP_REPLACE (“DEPT_NUM”, ‘[^[:alnum:]]’, NULL, 1, 0))); I am getting the following error: ORA-01743: only pure functions can be indexed 01743. …

sql – What is the alternative to REGEXP_REPLACE to create a functional index? Read More »

Question: I want to make a query that in theory (and wrong for practical purposes) would be something like this select A,MAX(COUNT(B)) from Tabla group by A; However I know this doesn't work. For MAX(count(op)) to work, you would have to put it alone, that is, it would be something …

sql – how to make a query with MAX(count) and another attribute Read More »

Question: How to get information about all database sessions that have open transactions from data dictionary views? As far as I understand, you need to access v$session and v$transaction and make a selection of v$session.sid and v$session.username . For example, through the console and SQL*Plus as two columns – session …

sql – How to get information about database sessions that have open transactions? Read More »

Question: There is a certain table with dates and other infoy. it is necessary to group information only by months. Here is what I tried Select наименование, month(дата) FROM таблица1 Group by month(дата), наименование This code groups, but only if the date exactly matches. It is necessary for me that …

SQL Date Month Grouping Read More »

Question: Requests are in resources in the form of *.sql files, and in the place of the call there are instructions of the following form: Properties.Resources.Query When hovering over a Query, the query text is highlighted, but there is no syntax highlighting. Are there any plugins that fix this issue? …

sql – Extension for VISUAL STUDIO that tints query keywords Read More »

Question: I make the following query in t-sql: SELECT *, CASE WHEN Sum_ IS NOT NULL THEN Sum_ ELSE COALESCE(Sum_, 0) END AS first_Non_Null FROM dbo.VIEW_1; GO Where is the processing of the Sum_ field, if it is empty, then we replace it with 0, otherwise we leave it unchanged. …

sql – Replacing NULL with the value of the previous value Read More »

Question: This SQL query works: SELECT NEXT_DAY (sysdate, 7) “NEXT DAY” FROM DUAL; NEXT DAY ——————- 2021-08-15 00:31:12 But in PL/SQL, the same function doesn't work: declare nextday date := NEXT_DAY (sysdate, 7); begin null; end; / ORA-01846: not a valid day of the week Wouldn't want to hardcode the …

Why does NEXT_DAY work differently in PL/SQL than in SQL? ORA-01846: not a valid day of the week Read More »

Question: Is there a sql command that can inform the server and the database in which the select is being executed? I'm available for more details Answer: Yes. you can use the following select . select @@SERVERNAME, DB_NAME()

Question: Consider that we have models A , B , C and D , each one referring to the previous one, that is, A has many B s, B has many C s and C has many D s: no redundancy A | id | … | ———— | … …

sql – Is it a good practice to keep redundant foreign key columns? Read More »

Question: I want to display the current month and year in a SQL query, I want it to look like this: april/2020 I tried this: SELECT DATENAME(MONTH,GETDATE()) + ‘ / ‘ + YEAR(GETDATE()) But, I get the following error: Error de conversión al convertir el valor nvarchar ‘Abril / ‘ …

Correct conversion in SQL date Read More »