Solved

oracle query

Posted on 2013-06-06
10
166 Views
Last Modified: 2013-06-12
0
Comment
Question by:anumoses
  • 6
  • 4
10 Comments
 
LVL 6

Author Comment

by:anumoses
ID: 39225538
I had to give the right table creation scripts.
query-to-be-sent.sql
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39225929
could you please try this? it's from an earlier question please see http://sqlfiddle.com/#!4/12a49/35
I haven't yet had a chance to load all your test data and may not be able to right now (its v.late for me) - but it may help others
SELECT
        CASE 
		WHEN id = prev_id
			THEN NULL
		ELSE (
				SELECT 'EDWARD HINES' from dual
				--FROM consignee_id c
				--WHERE c.consignee_id = id
				)
		END NAME
	, wkday
    , qty
	, CASE 
		WHEN id = prev_id
			AND wkday = prev_wkday
			AND prd_typ = prev_prdtyp
			THEN NULL
		ELSE prd_typ
		END prd_typ
	, CASE 
		WHEN id = prev_id
			AND wkday = prev_wkday
			AND prd_typ = prev_prdtyp
			THEN NULL
		ELSE attrib_grp
		END attrib_grp
	, CASE 
		WHEN id = prev_id
			AND wkday = prev_wkday
			AND prd_typ = prev_prdtyp
			THEN NULL
		ELSE attrib_cod
		END attrib_cod
FROM (
      SELECT
            b.consignee_id id
          , b.week_day wkday
          , b.product_type prd_typ
          , a.attrib_group attrib_grp
          , a.attrib_code attrib_cod
          , b.qty
          , LAG(b.consignee_id) OVER (ORDER BY b.consignee_id, b.week_day) prev_id
          , LAG(b.week_day)     OVER (ORDER BY b.consignee_id, b.week_day) prev_wkday
          , LAG(b.product_type) OVER (ORDER BY b.consignee_id, b.week_day, b.product_type) prev_prdtyp
      FROM tab1 a
      RIGHT JOIN tab2 b ON a.consignee_id = b.consignee_id
          --AND a.item_no = b.item_no
          AND a.week_day = b.week_day
	)
ORDER BY
  prev_id desc
, id
, wkday
, prd_typ desc
;

Open in new window

0
 
LVL 6

Author Comment

by:anumoses
ID: 39225975
No not correct data. Please run for

Edward Hines,Jr VA Hospital

see attached. This is what I want.
correct-data.xls
0
 
LVL 6

Author Comment

by:anumoses
ID: 39226001
The earlier query I posted works fine when  attrib group and attrib code is there. Some lines do not have the group and code, but we have to show the line with week day qty and product type. That is what we are not getting to.
0
 
LVL 6

Author Comment

by:anumoses
ID: 39226139
sdstuber gave me the query that is working fine when we have attrib code and atrib group but does not pull the data when we do not have the group and code. Can someone help me please?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 39226201
>>No not correct data. Please run for
anumoses, if you look at my proposal you will see it has a small piece commented out

the assumption is you would uncomment that - it's not a great deal to do see lines 6,7,8

remove "from dual" and remove the 2 "--" comments

>>I haven't yet had a chance to load all your test data
as previously stated
0
 
LVL 6

Author Comment

by:anumoses
ID: 39226248
I am getting the correct data for EDW-CS0003'  Edward Hines,Jr VA Hospital
but not others.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39228265
anumoses, and I still have not been able to load all your data and have made no advance - hopefully others will be able to help - sorry.
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 39241937
thanks
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39242889
thanks anumoses - I thought there was more needed still but had been unable to progress it myself - hope you have what you needed.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle Finace 3 67
VB.Net - CSV to Oracle table 4 52
Oracle Next Available Number 2 32
Create table from select - oracle 6 24
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now