144 questions
- Bountied 0
- Unanswered
- Frequent
- Score
- Trending
- Week
- Month
- Unanswered (my tags)
1
vote
1
answer
38
views
jooq iif or nvl2 in request
I can’t figure out what I’m specifying incorrectly, null values from the STUDENT.CURRENT_CURS_ID column are not processed by iif or nvl2 and in general this student with a null value is not included ...
1
vote
1
answer
58
views
Modify sql macro with nvl()
I have customers. Who can have 1 or customer accounts. In each customer account there can be debits or credits in a transaction table.
I am converting a function to a MACRO in order to do performance ...
2
votes
3
answers
322
views
Adding If-else condition in view query in Oracle
My oracle database has 3 tables
Order (id, order_number, external_id(FK to other app),version, description, create_tS)
Partner (id, order_id(FK to orders),partner_code, partner_name, identifier, type,...
0
votes
2
answers
786
views
Using NVL to Pass Zero's for NULL Values Dynamically
I have the following Oracle SQL:
with dat as
(
SELECT
(trunc(sysdate) - level + 1)AS ISSUE_DATE,
'Tesla' AS MAKE
FROM
DUAL
CONNECT BY LEVEL <= (to_date(sysdate+59,...
1
vote
4
answers
125
views
Return a default value in SQL even if the query return no result
For a very specific need for my application, in the best scenario, I must always retrieve an additional record preceding the date filter that the user selected.
Suppose in my database I have orders ...
0
votes
1
answer
448
views
SQL NVL alternative in WHERE clause
I do have that silly job at work,in which I need to analyse and improve the performance on some stupid 20 years old SQL statements.
One statement uses NVL in the WHERE clause (no wonder why it's slow) ...
0
votes
1
answer
113
views
Error in execution case when nvl in oracle
SELECT cast(ID as number) AS ID,
cast(MARKETER_ID as NUMBER) AS MARKETER_ID,
CAST(NEW_MARKETING_BANKS_ID AS NUMBER) AS NEW_MARKETING_BANKS_ID_ASSIGNE,
To_char(To_date(REQUEST_DATE,'YYYY/MM/...
-1
votes
1
answer
85
views
Set the same value to columns if column is null and another value if column is not null using just nvl, decode or coalesce in Oracle
I need write select script that checks if column is null then shows "is null" value, when column is not null then shows "is not null" value in output. But I should use only nvl, ...
0
votes
2
answers
723
views
Select substring in Oracle SQL up to a specific character
Let's consider the following example. Say I have a table column called N.Note and it contains the string:
Veg: Lettuce 200 ¶ Fruit: Oranges 200 ¶ Dairy: Milk 300 ¶
This string is not constant and ...
0
votes
0
answers
65
views
How to replace a particular column element with 0 if NULL is found in another column in the same tuple?
My database schema is as follows:
Students(sid, firstname, lastname, status, gpa, email)
Courses(dept_code, course#, title)
Course_credit(course#, credits)
Classes(classid, dept_code, course#, sect#, ...
2
votes
2
answers
83
views
Is there any way to set 'varchar' in number type?
When I write:
SELECT
last_name, NVL(commission_pct, 0)
FROM
Hr.employees;
it works fine. When commission percentage is null, then it is set to 0.
But I want to set it to 'NO COMMISSION' ...
0
votes
1
answer
123
views
Oracle - Replace null values for pivot columns with join from another table
I've a pivot table output and now I want to check for the values from the pivot columns and replace the values if null from another column from another table.
Invoice_No
Column
value
111
A
One
111
B
...
1
vote
0
answers
603
views
JPAQueryFactory NVL function
I am using this JPAQueryFactory .select to get the data
queryFactory.select(
Projections.bean(detailDTO.class,
qEmpcate.id,
qEmcate.date.as("...
0
votes
1
answer
75
views
Why put NVL around WHEN in SQL?
I was reading a query and came upon the following code:
nvl(case when month>start then null else 0 end, 1)
and it seemed to me there was no reason for the nvl.
Why not do this:
case when month>...
1
vote
2
answers
120
views
combine nvl into single statement
There are two different tables
Table 1:
select nvl(a.column1,'dummy_employee') from table1 a;
Table 2:
select nvl(b.column1,a.column1) from table1 a
left join table2 b
on a.key_col=b.key_col
Logic:
...