1

Topic: Sampling of a nonzero field

Hello!
I get acquainted with SQL. There was a simple task. It is necessary to make SELECT to two fields, but to return only the nonzero.
I do while so. I write on PL SQL for Si, therefore I ask not to pay attention to syntax singularities
SELECT VAR1, VAR2
INTO:var1
:var2
FROM TABLE
WHERE KEY =:key;
That is now I fill both Si-variables var1 and var2, I analyze them in  the code on NULL further. I want to understand, how at level SQL to write a condition that if one field NULL to select the second.
In advance thanks for the help!

2

Re: Sampling of a nonzero field

RTFM CASE Expressions (FAQ)

3

Re: Sampling of a nonzero field

SELECT nvl (VAR1, VAR2)

4

Re: Sampling of a nonzero field

violektra;
COALESCE or NVL

5

Re: Sampling of a nonzero field

wrote:

SELECT nvl (VAR1, VAR2)

Correctly thought?
SELECT nvl (VAR1, VAR2)
INTO:var
FROM TABLE
WHERE KEY =:key;
That is if VAR1 zero we take VAR2? For it also searched

6

Re: Sampling of a nonzero field

At once thoughts that happens, if both fields NULL or both are filled. A nonsense, but it is necessary to consider. So through IF THEN it will be more safe, I so understand.

7

Re: Sampling of a nonzero field

Whether if at both of us fields NULL returns SELECT error status code or in a variable var registers NULL?

8

Re: Sampling of a nonzero field

And into the documentation feeblly to come?
NVL lets you replace null (returned as a blank) with a string in the results of a query. If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.

9

Re: Sampling of a nonzero field

violektra;
[CSV =;] var1; var2; nvl (var1, var2); nvl (var2, var1)
null; null; null; null
1; null; 1; 1
null; 1; 1; 1
1; 2; 1; 2

10

Re: Sampling of a nonzero field

lLocust;
Thanks huge!!
And generally to all who responded.
I ask not to rush in me manuals since the sphere new and turns out to investigate not at once into the English documentation.
Would like still to ask to prompt the most simple and convenient tool where it would be possible  a test small label and to check up request as made it lLocust.
At me costs Toad SQL where the fighting circuit of a DB is loaded. That is empty. And generally it would not be desirable to climb there that nothing to break
Only I understand with it. Therefore while all I do instinctively.
How is better to check up quickly SQL request? Can eat online services?
Many thanks for responsiveness!

11

Re: Sampling of a nonzero field

violektra wrote:

Can eat online services?

livesql.oracle.com registration, but it free and fast is required

12

Re: Sampling of a nonzero field

violektra wrote:

lLocust;
Would like still to ask to prompt the most simple and convenient tool where it would be possible  a test small label and to check up request as made it lLocust.

Ask to tear to you a sandbox in a DBMS, see oracle database sample chemas

13

Re: Sampling of a nonzero field

AnSi_Sr wrote:

oracle database sample s chemas

14

Re: Sampling of a nonzero field

Can at you there is  Scott, it just with a test dial-up.