Home » Developer & Programmer » Reports & Discoverer » Can anyone help me with this Query?
Can anyone help me with this Query? [message #324862] Wed, 04 June 2008 06:31 Go to next message
Basit
Messages: 38
Registered: April 2007
Member


Hi,

kindly help me to solve this problem i m try it but didn't
understand what is the fault.

CREATE OR REPLACE VIEW STOCK_TODAY
(ITEM_NAME,QUANTITY, WEIGHT)
AS
SELECT ITEM_NAME, SUM(NVL(QUANTITY,0)) QUANTITY, SUM(NVL(WEIGHT,0)) WEIGHT
FROM PRODUCTION A , ITEM B
WHERE A. ITEM_CODE(+)=B.ITEM_CODE
AND TO_CHAR(P_DATE,'DD-MON-YYYY')=(SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY') FROM DUAL)
GROUP BY B.ITEM_NAME

when i run this query it creates a view named STOCK_TODAY
but doesn't show the data from the base tables,
NO ROWS SELECTED! i m using this View for reports, and there also i don't get the output. so kindly help me out!. and this is somekind of discouraging moment for me. please
Re: Can anyone help me with this Query? [message #324866 is a reply to message #324862] Wed, 04 June 2008 06:40 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
AND TO_CHAR(P_DATE,'DD-MON-YYYY')=(SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY') FROM DUAL)

What is "p_date"? Where does it belong to? What is this line supposed to do?

If it is a DATE column, you could put it as
AND trunc(p_date) = trunc(sysdate)
It, however, means that records with "p_date = 04.06.2008" (dd.mm.yyyy) must exist in a table which contains the "p_date" column.

As you've said that there are no records there, obviously, WHERE clause conditions prevent SELECT statement to fetch anything.

Perhaps you should first make sure that view's SELECT statement works correctly, then create a view. Because, the fact that the view got created means nothing but it was correctly written (regarding CREATE VIEW statement syntax).
Re: Can anyone help me with this Query? [message #324882 is a reply to message #324866] Wed, 04 June 2008 07:24 Go to previous messageGo to next message
Basit
Messages: 38
Registered: April 2007
Member



the problem still exist,
i will send you and export file .DMP and i will send you what i m trying to do, if i had no power failure.
Re: Can anyone help me with this Query? [message #324913 is a reply to message #324882] Wed, 04 June 2008 08:40 Go to previous messageGo to next message
Basit
Messages: 38
Registered: April 2007
Member

I FORUM DOESN'T SUPPORT THE .DMP FILE
BUT SENDING YOU A COPY OF A REPORT . YOU MIGHT UNDERSTAND
WHAT I REALLY WANT TO DO . I WOULD HIGHLY APPRECIATE YOU.

REGARDS
BASIT
Re: Can anyone help me with this Query? [message #325580 is a reply to message #324913] Fri, 06 June 2008 10:49 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
Hi Basit,

Can you send the rdf also... u have to see if the tables you are accessing have the data or NOT and second thing check with the condition you are using.

Bye
Ashu
Re: Can anyone help me with this Query? [message #326132 is a reply to message #325580] Tue, 10 June 2008 05:08 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
What does the single statement give as a result? So, just run in SQL*Plus:

SELECT item_name
      ,SUM(nvl(quantity
              ,0)) quantity
      ,SUM(nvl(weight
              ,0)) weight
FROM   production a
      ,item       b
WHERE  a. item_code(+) = b.item_code
AND    to_char(p_date
              ,'DD-MON-YYYY') = (SELECT to_char(SYSDATE
                                                ,'DD-MON-YYYY')
                                  FROM   dual)
GROUP  BY b.item_name

(formatted your code a bit)

Furthermore, sysdate doesn't need to be selected from dual, you can just use it in your statement. And I would recommend to compares dates to dates instead of varchar2's. What you seem to try to do is to compare dates without the time component, right? An alternative that might be less "messy" could be:
SELECT item_name
      ,SUM(nvl(quantity
              ,0)) quantity
      ,SUM(nvl(weight
              ,0)) weight
FROM   production a
      ,item       b
WHERE  a. item_code(+) = b.item_code
AND    trunc(p_date) = trunc(sysdate)
GROUP  BY b.item_name


And now for a possible solution for the issue (no records): in what table is p_date? Could that be table production? If so, then you're "undoing" the outer join by stating that p_date should equal sysdate (a row that does not exist, can not have a column equal to anything, right?).
Perhaps, you need something like:
SELECT b.item_name
      ,SUM(nvl(a.quantity
              ,0)) quantity
      ,SUM(nvl(a.weight
              ,0)) weight
FROM   (SELECT *
        FROM   production aa
        WHERE  trunc(aa.p_date) = trunc(SYSDATE)) a
      ,item b
WHERE  b.item_code = a. item_code(+)
GROUP  BY b.item_name

This is assuming that you want all items with their own production numbers for today.

(it would be very convenient if next time, you (a) state what you want to accomplish along with what you tried and (b) gave us the create table and insert scripts in order for us to test what we say... Wink)
Previous Topic: Arabic language in Reports 10g
Next Topic: please need help with bar graph
Goto Forum:
  


Current Time: Fri Dec 09 04:13:40 CST 2016

Total time taken to generate the page: 0.10853 seconds