I have the below data in a table. I am trying to fetch all the “Modem” users, who do not have an associated telephone service. UserID DeviceNumber DeviceType DeviceRole 1 A …
Tag: oracle
Replace characters with padding zeros
I have a table with values: and I need to replace the first dash (-) with zeros, so that the total length is exactly 18 characters, and insert it back into the database: If that is not possible, then maybe do the same using grep Answer I need to replace the first dash (-) with zeros, so that the total
concatenate with leading zeros for months – not a valid month error
I want to concatenate string with year in oracle. The string is month. Query is This query wrks fine however if the string is ’01’ or anything starting with zero i am getting not a valid month error. Answer Let’s run it without the to_date(…, …) wrapper: Do you see the problem? (…
Select from group by based on case statement
Based on a table like this I want to group by PRODID, then select a row based on a case statement. Case statement being if IND1 = 1 select row, else if IND2 = 1 select row, else select row with max DATE. If multiple indicators within a group are 1 select row with the max date from the group
Case statement based on the time
I have the following table Based on the transaction_DT: if the transaction between 04:00:00 PM to 08:00:00 AM + next day get “After Hour” Between 08:00:00 AM to 04:00:00 PM within same day get “Working Hour” Using the case statement it is not works! Answer Hmmm . . . how about somethin…
Why this select selects no rows
I am trying to select a highest earner in ‘IT’ department, but I keep selecting no rows. Salary and names are in table called employees while department_name is in a table named departments. Can anyone please explain why does this select no rows and how should I do it? Answer Why this select selec…
ORA-00937 not a single-group group function when using CASE Condition
I’ve been searching for a few hours now, on how to resolve an ORA-00937: not a single-group group function and none of them seem to explain why I am getting the error. My understanding is, when applying an aggregate function in the select statement on one or more columns, than you should include the col…
How to get the root record in Oracle database
Starting from the generic node (or leaf) how to get the root node? How to get the root msg_id? Answer You were on the right path, but you are missing two essential ingredients. First, to indicate the starting point, you need to use the start with clause. Obviously, it will be something like start with id = &l…
Oracle SQL regexp_substr number extraction behavior
In a sense I’ve answered my own question, but I’m trying to understand the answer better: When using regexp_substr (in oracle) to extract the first occurrence of a number (either single or multi digits), how/why do the modifiers * and + impact the results? Why does + provide the behavior I’m…
Calculate Week Numbers based on the initial given date to end date
I have below scenario that Business want to calculate Week Number based on Given Start Date to End Date. For Ex: Start Date = 8/24/2020 End Date = 12/31/2020 ( These Start date & end date are not constant they may change from year to year ) Expected Output below: I need Oracle Query to calculate Week Numb…