• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 174
  • Last Modified:

oracle query

0
anumoses
Asked:
anumoses
  • 6
  • 4
1 Solution
 
anumosesAuthor Commented:
I had to give the right table creation scripts.
query-to-be-sent.sql
0
 
PortletPaulCommented:
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
 
anumosesAuthor Commented:
No not correct data. Please run for

Edward Hines,Jr VA Hospital

see attached. This is what I want.
correct-data.xls
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
anumosesAuthor Commented:
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
 
anumosesAuthor Commented:
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
 
PortletPaulCommented:
>>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
 
anumosesAuthor Commented:
I am getting the correct data for EDW-CS0003'  Edward Hines,Jr VA Hospital
but not others.
0
 
PortletPaulCommented:
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
 
anumosesAuthor Commented:
thanks
0
 
PortletPaulCommented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now