Home » SQL & PL/SQL » SQL & PL/SQL » nvl is not working properly
nvl is not working properly [message #449127] Sat, 27 March 2010 01:33 Go to next message
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.
Re: nvl is not working properly [message #449128 is a reply to message #449127] Sat, 27 March 2010 01:45 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
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 Go to previous messageGo to next message
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
Re: nvl is not working properly [message #449133 is a reply to message #449130] Sat, 27 March 2010 02:33 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is your client program?

Regards
Michel
Re: nvl is not working properly [message #449134 is a reply to message #449130] Sat, 27 March 2010 03:23 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
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
Re: nvl is not working properly [message #449137 is a reply to message #449127] Sat, 27 March 2010 04:02 Go to previous messageGo to next message
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
Re: nvl is not working properly [message #449138 is a reply to message #449137] Sat, 27 March 2010 04:27 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
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
Re: nvl is not working properly [message #449140 is a reply to message #449127] Sat, 27 March 2010 04:57 Go to previous messageGo to next message
dhinendra
Messages: 58
Registered: September 2009
Location: Gurgaon,India
Member
thanks michel it's working now....
Re: nvl is not working properly [message #449142 is a reply to message #449140] Sat, 27 March 2010 05:02 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
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
Re: nvl is not working properly [message #449504 is a reply to message #449127] Tue, 30 March 2010 01:25 Go to previous messageGo to next message
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
Re: nvl is not working properly [message #449506 is a reply to message #449504] Tue, 30 March 2010 01:43 Go to previous message
Michel Cadot
Messages: 63806
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
Previous Topic: Joining subsequent rows in a table
Next Topic: oracle query required
Goto Forum:
  


Current Time: Tue Sep 27 12:49:40 CDT 2016

Total time taken to generate the page: 0.09499 seconds