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  |
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  |
 |
Littlefoot
Messages: 21809
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 asAND trunc(p_date) = trunc(sysdate)
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 #326132 is a reply to message #325580] |
Tue, 10 June 2008 05:08 |
skooman
Messages: 913
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...  )
|
|
|
|
Goto Forum:
Current Time: Wed May 15 15:19:19 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
