OraFAQ Forum: SQL & PL/SQL » nvl is not working properly

Home » SQL & PL/SQL » SQL & PL/SQL » nvl is not working properly

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

nvl is not working properly [message #449127]

Sat, 27 March 2010 01:33

dhinendra
Messages: 58
Registered: September 2009
Location: Gurgaon,India

Member

hi to all
SELECT nvl(F.STRATE,0)
FROM GATEENTRY A,POITEMS B,GATEENTRYITEMS C,ITEMMST D,PARTYMST E,STAXMST F,PODETAILS G
WHERE
B.POCODE=G.POCODE
AND G.SALESCODE_M=F.STCODE
AND C.ITEMCODE=B.ITEMCODE
AND A.PARTYCODE=E.PARTYCODE
AND C.ITEMCODE=D.ITEMCODE
AND A.GATEENTRYCODE=C.GATEENTRYCODE
AND A.GE_POAMENDMENTNO=B.POAMENDMENTNO
AND A.GE_POCODE=B.POCODE
AND E.PARTYNAME LIKE :NAME
AND A.GATEENTRYDATE BETWEEN :FRMDT AND :TODT
AND A.UNITCODE='UC1'
AND A.FYCODE='FY-03';

in the above query when i execute it,it returns null value rather then the 0 if there is no value.the red one condition is the main condition as there are many conditions in which G.SALESCODE value is null.will any body pls tell me the solution.

Report message to a moderator

Re: nvl is not working properly [message #449128 is a reply to message #449127]

Sat, 27 March 2010 01:45

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

1/ It does not return NULL, it returns no row
2/ It depends on the meaning of your query and what it intends to return, an outer join may fit your needs altough that as you don't return any value from this table I wonder why you put it in the query in this case
3/ Put in a subquery (IN ...) all tables that do not return any value in SELECT (that is all but F)

Regards
Michel

[Updated on: Sat, 27 March 2010 01:46]

Report message to a moderator

Re: nvl is not working properly [message #449130 is a reply to message #449127]

Sat, 27 March 2010 02:12

dhinendra
Messages: 58
Registered: September 2009
Location: Gurgaon,India

Member

Thanks for reply michel but the problem is that i am using this query in my report like-
SELECT DISTINCT(nvl(F.STRATE,0)) INTO N4

and further i am returning value after doing some arithmetic operations.so if it only returns the alert \-no row return
then it is giving error at the report level so that's why i am asking for a alternate to this problem.

Thanks
Dhinendra

Report message to a moderator

Re: nvl is not working properly [message #449133 is a reply to message #449130]

Sat, 27 March 2010 02:33

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What is your client program?

Regards
Michel

Report message to a moderator

Re: nvl is not working properly [message #449134 is a reply to message #449130]

Sat, 27 March 2010 03:23

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Not sure it is the best thing for your case (as you didn't answer my previous question) but you can do something like that:

SQL> select nvl((/* here's your query instead */ select dummy from dual where dummy='&x'),
  2             'No dummy') result
  3  from dual
  4  /
RESULT
--------
X

1 row selected.

SQL> def x='Y'
SQL> /
RESULT
--------
No dummy

1 row selected.

Regards
Michel

Report message to a moderator

Re: nvl is not working properly [message #449137 is a reply to message #449127]

Sat, 27 March 2010 04:02

dhinendra
Messages: 58
Registered: September 2009
Location: Gurgaon,India

Member

hello michel
sorry for not replying but i don't understand the query you have send to me it will not work according to me because it is not fetching the data exactly i want.
actually what i want from my query is-

SELECT DISTINCT(NVL(F.STRATE,0)) into N1
FROM GATEENTRY A,POITEMS B,GATEENTRYITEMS C,ITEMMST D,PARTYMST E,STAXMST F,PODETAILS G
WHERE
B.POCODE=G.POCODE
AND G.SALESCODE_M(+)=F.STCODE
AND C.ITEMCODE=B.ITEMCODE
AND A.PARTYCODE=E.PARTYCODE
AND C.ITEMCODE=D.ITEMCODE
AND A.GATEENTRYCODE=C.GATEENTRYCODE
AND A.GE_POAMENDMENTNO=B.POAMENDMENTNO
AND A.GE_POCODE=B.POCODE
AND E.PARTYNAME LIKE :NAME
AND A.GATEENTRYDATE BETWEEN :FRMDT AND :TODT
AND A.UNITCODE='UC1'
AND A.FYCODE='FY-03';

and i am using this query in my formula column in oracle reports where AFTER getting this value in n1 and then i do some arithmatic operation such as-
RETURN n1+:amount;
in my returning value using RETURN.
i am using Oracle developer 6i.

Thanks
Dhinendra

Report message to a moderator

Re: nvl is not working properly [message #449138 is a reply to message #449137]

Sat, 27 March 2010 04:27

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

i am using Oracle developer 6i.

So you are able to know if the query returns nothing and then set your variable to 0.

Quote:

it will not work according to me because it is not fetching the data exactly i want.

Which query are you talking about? My question or my SQL query?

If it is my SQL query then put your query where I indicate it (removing my "select dummy from dual where dummy='&x'" of course) and change 'nodummy' by the value you want in case of no row, 0 for instance.
But as you use Developer 6i this is NOT the correct way to do it. The correct way is the one I mentioned above.

Regards
Michel

Report message to a moderator

Re: nvl is not working properly [message #449140 is a reply to message #449127]

Sat, 27 March 2010 04:57

dhinendra
Messages: 58
Registered: September 2009
Location: Gurgaon,India

Member

thanks michel it's working now....

Report message to a moderator

Re: nvl is not working properly [message #449142 is a reply to message #449140]

Sat, 27 March 2010 05:02

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Please post the solution you eventually find.

Regards
Michel

[Updated on: Sat, 27 March 2010 05:03]

Report message to a moderator

Re: nvl is not working properly [message #449486 is a reply to message #449127]

Mon, 29 March 2010 22:56

dhinendra
Messages: 58
Registered: September 2009
Location: Gurgaon,India

Member

SELECT DISTINCT (Nvl((SELECT DISTINCT (f.strate)
                      FROM   gateentry a,
                             poitems b,
                             gateentryitems c,
                             itemmst d,
                             partymst e,
                             staxmst f,
                             podetails g
                      WHERE  b.pocode = g.pocode
                             AND g.salescode_m = f.stcode
                             AND c.itemcode = b.itemcode
                             AND a.partycode = e.partycode
                             AND c.itemcode = d.itemcode
                             AND a.gateentrycode = c.gateentrycode
                             AND a.ge_poamendmentno = b.poamendmentno
                             AND a.ge_pocode = b.pocode
                             AND e.partyname LIKE :NAME
                             AND a.gateentrydate BETWEEN :FRMDT AND :TODT
                             AND a.unitcode = 'UC1'
                             AND a.fycode = 'FY-03'),0))
FROM   staxmst f

Thanks
Dhinendra Panwar

Formatted by BlackSwan

[Updated on: Mon, 29 March 2010 22:58] by Moderator

Report message to a moderator

Re: nvl is not working properly [message #449490 is a reply to message #449486]

Mon, 29 March 2010 23:54

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You don't need the outer DISTINCT as there is only 1 row returned.

Regards
Michel

Report message to a moderator

Re: nvl is not working properly [message #449504 is a reply to message #449127]

Tue, 30 March 2010 01:25

dhinendra
Messages: 58
Registered: September 2009
Location: Gurgaon,India

Member

no michel actually it is returning more then one records equals to the the number of records in database so i used it outside also.

Thanks
Dhinendra Panwar

Report message to a moderator

Re: nvl is not working properly [message #449506 is a reply to message #449504]

Tue, 30 March 2010 01:43

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Doh! I didn't see you replace "dual" by your table, silly me.

Regards
Michel

Report message to a moderator

Previous Topic:	Joining subsequent rows in a table
Next Topic:	oracle query required

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 19:32:20 CDT 2024