Home » SQL & PL/SQL » SQL & PL/SQL » PickList query help needed Please
PickList query help needed Please [message #195761] Mon, 02 October 2006 04:06 Go to next message
MickD
Messages: 19
Registered: October 2006
Junior Member
Hi,
I'm pretty new to Oracle and SQL, I've been asked to write a query to get a picklist for all orders by customer by date, below is the code I've come up with, but it's not giving me what i need. What i need to get is order information for one or more customers by a ship date ( usually the same day but sometimes by the end of the week ) and display how many parts are in stock and their location, most of the time parts will be in more than one location, so i would like to show all locations for the parts but only show the order information once, I will upload a excel file with the info I'm getting at the moment and how i would like see it, and also the code i'm currently using.

Thanks in advance,
Mick.

  • Attachment: picklist.xls
    (Size: 25.50KB, Downloaded 296 times)

[Updated on: Mon, 02 October 2006 04:06]

Report message to a moderator

Re: PickList query help needed Please [message #195904 is a reply to message #195761] Tue, 03 October 2006 02:06 Go to previous messageGo to next message
MickD
Messages: 19
Registered: October 2006
Junior Member
Anyone Any idea's?

Thanks
Mick.
Re: PickList query help needed Please [message #195910 is a reply to message #195904] Tue, 03 October 2006 02:23 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I have observed that questions where all the details are tucked away in a file get noticably less attention than queries where we can actually see the details.

I notice you've provided a lot of data, and a big query, but no table creation or insert statements, which means that anyone who wants to help has a lot of work to do.
Re: PickList query help needed Please [message #195914 is a reply to message #195904] Tue, 03 October 2006 02:27 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Sorry, I (and many other members I suppose) can't download just any Excel file. Could you post the query (and desired output) in CODE tags? Ideally, one would add create table and insert statements so we can test.

MHE
Re: PickList query help needed Please [message #195949 is a reply to message #195914] Tue, 03 October 2006 05:03 Go to previous messageGo to next message
MickD
Messages: 19
Registered: October 2006
Junior Member
Hi,
Thanks for your reply, below is the code I'm using:
select (case when co.customer_no = Lag(co.customer_no) over (order by co.customer_no ) then null else co.customer_no end ) Cust_No, 
(case when IFSAPP.Cust_Ord_Customer_API.Get_Name(co.CUSTOMER_NO)= lag(IFSAPP.Cust_Ord_Customer_API.Get_Name(co.CUSTOMER_NO))over (order by co.customer_no ) then null else IFSAPP.Cust_Ord_Customer_API.Get_Name(co.CUSTOMER_NO) end) Customer_Name, 
(Case when co.SHIP_ADDR_NO = lag(co.SHIP_ADDR_NO) over (order by co.customer_no) then null else co.SHIP_ADDR_NO end) Address,
(case when col.order_no = lag(col.order_no) over (order by co.customer_no) then null else col.order_no end) Order_No,
(case when col.line_no = lag(col.line_no) over (order by co.customer_no) then null else col.line_no end) Line, 
(case when col.rel_no = lag(col.rel_no) over (order by co.customer_no) then null else col.rel_no end) Rel, 
(case when col.catalog_no = Lag(col.catalog_no) over (order by co.customer_no) then null else col.catalog_no end) Sales_Part, 
(case when col.customer_part_no = Lag(col.customer_part_no) over (order by co.customer_no) then null else col.customer_part_no end) Customer_Part_No, 
(case when to_char(col.PLANNED_SHIP_DATE,'DD/MM/YYYY') = lag(to_char(col.PLANNED_SHIP_DATE,'DD/MM/YYYY')) over (order by co.customer_no) then null else to_char(col.PLANNED_SHIP_DATE,'DD/MM/YYYY')end) Ship_Date,
col.buy_qty_due Cust_Demand,
(col.buy_qty_due - col.qty_shipped) Due_To_Be_Shipped,
loc.location_no,
loc.qty_onhand QTY,
loc.lot_batch_no Batch_No,
loc.availability_control_id Control_ID
from ifsapp.customer_order co, 
ifsapp.customer_order_line col,
ifsapp.inventory_part_in_stock loc
where col.state not in ('Cancelled', 'Delivered', 'Invoiced/Closed') and co.ORDER_ID not like 'TO' and col.catalog_no not like 'D%' and col.planned_ship_date <= nvl(trunc(to_date('05/10/2006', 'DD/MM/YYYY')),sysdate) and loc.part_no = col.part_no and col.order_no = co.order_no and 
(co.customer_no like nvl('10295','%')
or co.customer_no like nvl('10273','%')
or co.customer_no like nvl('','z')
or co.customer_no like nvl('','z')
or co.customer_no like nvl('','z')
or co.customer_no like nvl('','z')
or co.customer_no like nvl('','z')
or co.customer_no like nvl('','z')
or co.customer_no like nvl('','z')
or co.customer_no like nvl('102950','z')) and co.contract= 'CAS' and col.contract= 'CAS' and loc.qty_onhand > 0 and loc.location_no not in ('CASBOND', 'QRY1')
order by co.customer_no asc, col.catalog_no asc, col.planned_ship_date asc, loc.receipt_date asc


Below is the format I'm getting from the code above:

   	CUST_NO	CUSTOMER_NAME	ADDRESS	ORDER_NO	LINE	REL	SALES_PART	CUSTOMER_PART_NO	SHIP_DATE	CUST_DEMAND	DUE_TO_BE_SHIPPED	LOCATION_NO	QTY	BATCH_NO	CONTROL_ID
1	10273	R-Tek Limited	01	C402055	2	118	A4999-C	669009U20B	04/10/2006	7	7	CA31	9	Samples	BOND
2						119			05/10/2006	7	7	CA31	9	Samples	BOND
3				C402056		115	A4999-D	669019U20B	04/10/2006	7	7	CA31	9	Samples	BOND
4						116			05/10/2006	7	7	CA31	9	Samples	BOND
5				C402058		130	A5002-A	669019U30A		7	7	MB03	42	27032	
6				C402057		132	A5002-B	669009U30A		7	7	MB03	42	27033	
7				C400953		134	M4671-C	76981AV700	04/10/2006	250	250	PTRACK	189	24396	
8										250	250	NB18	250	24396	QRY
9										250	250	PTRACK	58	26248	
10										250	250	MB06	250	26248	
11						135			05/10/2006	250	250	PTRACK	189	24396	
12										250	250	NB18	250	24396	QRY
13										250	250	PTRACK	58	26248	
14										250	250	MB06	250	26248	
15				C400952		142	M4671-D	76980AV700	04/10/2006	250	250	PTRACK	848	24397	
16										250	250	PTRACK	66	26249	


And below is how i would like to see it:
   	CUST_NO	CUSTOMER_NAME	ADDRESS	ORDER_NO	LINE	REL	SALES_PART	CUSTOMER_PART_NO	SHIP_DATE	CUST_DEMAND	DUE_TO_BE_SHIPPED	LOCATION_NO	QTY	BATCH_NO	CONTROL_ID
1	10273	R-Tek Limited	01	C402055	2	118	A4999-C	669009U20B	04/10/2006	7	7	CA31	9	Samples	BOND
2						119			05/10/2006			CA31	9	Samples	BOND
3	10273	R-Tek Limited	01	C402056		115	A4999-D	669019U20B	04/10/2006	7	7	CA31	9	Samples	BOND
4						116			05/10/2006			CA31	9	Samples	BOND
5	10273	R-Tek Limited	01	C402058		130	A5002-A	669019U30A	05/10/2006	7	7	MB03	42	27032	
6	10273	R-Tek Limited	01	C402057		132	A5002-B	669009U30A	05/10/2006	7	7	MB03	42	27033	
7	10273	R-Tek Limited	01	C400953		134	M4671-C	76981AV700	04/10/2006	250	250	PTRACK	189	24396	
8												NB18	250	24396	QRY
9												PTRACK	58	26248	
10												MB06	250	26248	
11	10273	R-Tek Limited	01	C400953		135	M4671-C	76981AV700	05/10/2006	250	250	PTRACK	189	24396	
12												NB18	250	24396	QRY
13												PTRACK	58	26248	
14												MB06	250	26248	
15	10273	R-Tek Limited	01	C400952		142	M4671-D	76980AV700	04/10/2006	250	250	PTRACK	848	24397	
16												PTRACK	66	26249


I'm not sure what you mean by Create table and insert statements?
This is just a query i'm running.

Thanks in advance.

Mick.
Re: PickList query help needed Please [message #195984 is a reply to message #195949] Tue, 03 October 2006 08:23 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
MickD wrote on Tue, 03 October 2006 06:03



I'm not sure what you mean by Create table and insert statements?
This is just a query i'm running.

Thanks in advance.

Mick.


create table customer_order (foo1bar1 varchar2(10), foobar2 date, etc.);

insert into customer_order (foobar1, foobar2, etc.)
values ('ABC',sysdate, etc.);


so we don't have to do all the work and create our own tables and data.
Re: PickList query help needed Please [message #196112 is a reply to message #195984] Wed, 04 October 2006 03:15 Go to previous messageGo to next message
MickD
Messages: 19
Registered: October 2006
Junior Member
Thanks for the explanation, I think i have what you need ( appologies if not but i've never created tables before...)
Let me know if this is OK.
Thanks in advance,

Mick.

 create table customer_order (customer_no VARCHAR2(10), ship_addr_no VARCHAR2(2), order_id VARCHAR2(3), order_no VARCHAR2(12), contract VARCHAR2(5));

create table customer_order_line (order_no VARCHAR2(12), line_no VARCHAR2(4), rel_no VARCHAR2(4), catalog_no VARCHAR2(25), customer_part_no VARCHAR2(45), buy_qty_due NUMBER, state VARCHAR2(4), planned_ship_date DATE, qty_shipped NUMBER, contract VARCHAR2(5));

create table Inventory_part_in_stock (part_no VARCHAR2(25), location_no VARCHAR2(35), qty_onhand NUMBER);


insert into customer_order (customer_no, ship_addr_no, order_id, order_no, contract)
	Values ('10295', '01', 'AUT', '11748', 'CAS')
insert into customer_order (customer_no, ship_addr_no, order_id, order_no, contract)
	Values ('10295', '01', 'AUT', '11749', 'CAS')
insert into customer_order (customer_no, ship_addr_no, order_id, order_no, contract)
	Values ('10295', '01', 'AUT', '11750', 'CAS')
insert into customer_order (customer_no, ship_addr_no, order_id, order_no, contract)
	Values ('10295', '01', 'AUT', '11751', 'CAS')
insert into customer_order (customer_no, ship_addr_no, order_id, order_no, contract)
	Values ('10378', '02', 'AUT', '12001', 'CAS')
insert into customer_order (customer_no, ship_addr_no, order_id, order_no, contract)
	Values ('10378', '01', 'AUT', '12002', 'CAS')
insert into customer_order (customer_no, ship_addr_no, order_id, order_no, contract)
	Values ('10378', '01', 'AUT', '12003', 'CAS')


insert into customer_order_line (order_no, line_no, rel_no, catalog_no, customer_part_no, buy_qty_due, state, planned_ship_date, qty_shipped, contract)
	values ('11748', '10', '18', 'A1234-M', '100200300', '2000', 'RELEASED', '06/10/2006', '1000', 'CAS'

insert into customer_order_line (order_no, line_no, rel_no, catalog_no, customer_part_no, buy_qty_due, state, planned_ship_date, qty_shipped, contract)
	values ('11749', '10', '18', 'A1234-A', '100200299', '2500', 'RELEASED', '07/10/2006', '2000', 'CAS'

insert into customer_order_line (order_no, line_no, rel_no, catalog_no, customer_part_no, buy_qty_due, state, planned_ship_date, qty_shipped, contract)
	values ('11750', '02', '57', 'M5678-A', '100500200', '10000', 'RELEASED', '05/10/2006', '0', 'CAS'

insert into customer_order_line (order_no, line_no, rel_no, catalog_no, customer_part_no, buy_qty_due, state, planned_ship_date, qty_shipped, contract)
	values ('12001', '01', '180', 'A9999-B', '100000399', '1000', 'RELEASED', '06/10/2006', '50', 'CAS'

insert into customer_order_line (order_no, line_no, rel_no, catalog_no, customer_part_no, buy_qty_due, state, planned_ship_date, qty_shipped, contract)
	values ('12002', '03', '09', 'A1234-M', '100200300', '2000', 'RELEASED', '06/10/2006', '1000', 'CAS'

insert into customer_order_line (order_no, line_no, rel_no, catalog_no, customer_part_no, buy_qty_due, state, planned_ship_date, qty_shipped, contract)
	values ('12003', '01', '04', 'Z1111-Q', '100200890', '250', 'RELEASED', '07/10/2006', '10', 'CAS';


insert into Inventory_part_in_stock (part_no, location_no, qty_onhand)
	values ('A1234-M', 'A1A', '500')
insert into Inventory_part_in_stock (part_no, location_no, qty_onhand)
	values ('A1234-M', 'A1B', '600')
insert into Inventory_part_in_stock (part_no, location_no, qty_onhand)
	values ('A1234-M', 'A1C', '2000')
insert into Inventory_part_in_stock (part_no, location_no, qty_onhand)
	values ('A1234-A', 'B1A', '25')
insert into Inventory_part_in_stock (part_no, location_no, qty_onhand)
	values ('A1234-A', 'B1C', '500')
insert into Inventory_part_in_stock (part_no, location_no, qty_onhand)
	values ('M5678-A', 'D1A', '5000')
insert into Inventory_part_in_stock (part_no, location_no, qty_onhand)
	values ('M5678-A', 'D1B', '5000')
insert into Inventory_part_in_stock (part_no, location_no, qty_onhand)
	values ('A9999-B', 'F1A', '250')
insert into Inventory_part_in_stock (part_no, location_no, qty_onhand)
	values ('A9999-B', 'F1D', '780')
insert into Inventory_part_in_stock (part_no, location_no, qty_onhand)
	values ('A1234-M', 'A1Z', '2000')
insert into Inventory_part_in_stock (part_no, location_no, qty_onhand)
	values ('Z1111-Q', 'M1A', '290')

Re: PickList query help needed Please [message #196131 is a reply to message #196112] Wed, 04 October 2006 04:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I've frigged the statements so that I can get them to work (and added the Part_No column to the C_O_L table that the query needs.

alter session set nls_date_format = 'dd/mm/yyyy';

create table customer_order (customer_no VARCHAR2(10), ship_addr_no VARCHAR2(2), order_id VARCHAR2(3), order_no VARCHAR2(12), contract VARCHAR2(5));

create table customer_order_line (order_no VARCHAR2(12), line_no VARCHAR2(4), rel_no VARCHAR2(4), part_no VARCHAR2(25),catalog_no VARCHAR2(25), customer_part_no VARCHAR2(45), buy_qty_due NUMBER, state VARCHAR2(20), planned_ship_date DATE, qty_shipped NUMBER, contract VARCHAR2(5));

create table Inventory_part_in_stock (part_no VARCHAR2(25), location_no VARCHAR2(35), qty_onhand NUMBER);

insert into customer_order (customer_no, ship_addr_no, order_id, order_no, contract)	Values ('10295', '01', 'AUT', '11748', 'CAS');
insert into customer_order (customer_no, ship_addr_no, order_id, order_no, contract)	Values ('10295', '01', 'AUT', '11749', 'CAS');
insert into customer_order (customer_no, ship_addr_no, order_id, order_no, contract)	Values ('10295', '01', 'AUT', '11750', 'CAS');
insert into customer_order (customer_no, ship_addr_no, order_id, order_no, contract)	Values ('10295', '01', 'AUT', '11751', 'CAS');
insert into customer_order (customer_no, ship_addr_no, order_id, order_no, contract)	Values ('10378', '02', 'AUT', '12001', 'CAS');
insert into customer_order (customer_no, ship_addr_no, order_id, order_no, contract)	Values ('10378', '01', 'AUT', '12002', 'CAS');
insert into customer_order (customer_no, ship_addr_no, order_id, order_no, contract)	Values ('10378', '01', 'AUT', '12003', 'CAS');


insert into customer_order_line (order_no, line_no, rel_no, part_no, catalog_no, customer_part_no, buy_qty_due, state, planned_ship_date, qty_shipped, contract)	values ('11748', '10', '18', 'A1234-M', 'A1234-M', '100200300', '2000', 'RELEASED', '06/10/2006', '1000', 'CAS');
insert into customer_order_line (order_no, line_no, rel_no, part_no, catalog_no, customer_part_no, buy_qty_due, state, planned_ship_date, qty_shipped, contract)	values ('11749', '10', '18', 'A1234-M', 'A1234-A', '100200299', '2500', 'RELEASED', '07/10/2006', '2000', 'CAS');
insert into customer_order_line (order_no, line_no, rel_no, part_no, catalog_no, customer_part_no, buy_qty_due, state, planned_ship_date, qty_shipped, contract)	values ('11750', '02', '57', 'M5678-A', 'M5678-A', '100500200', '10000', 'RELEASED', '05/10/2006', '0', 'CAS');
insert into customer_order_line (order_no, line_no, rel_no, part_no, catalog_no, customer_part_no, buy_qty_due, state, planned_ship_date, qty_shipped, contract)	values ('12001', '01', '180', 'A9999-B', 'A9999-B', '100000399', '1000', 'RELEASED', '06/10/2006', '50', 'CAS');
insert into customer_order_line (order_no, line_no, rel_no, part_no, catalog_no, customer_part_no, buy_qty_due, state, planned_ship_date, qty_shipped, contract)	values ('12002', '03', '09', 'A1234-M', 'A1234-M', '100200300', '2000', 'RELEASED', '06/10/2006', '1000', 'CAS');
insert into customer_order_line (order_no, line_no, rel_no, part_no, catalog_no, customer_part_no, buy_qty_due, state, planned_ship_date, qty_shipped, contract)	values ('12003', '01', '04', 'Z1111-Q', 'Z1111-Q', '100200890', '250', 'RELEASED', '07/10/2006', '10', 'CAS');


insert into Inventory_part_in_stock (part_no, location_no, qty_onhand)	values ('A1234-M', 'A1A', '500');
insert into Inventory_part_in_stock (part_no, location_no, qty_onhand)  values ('A1234-M', 'A1B', '600');
insert into Inventory_part_in_stock (part_no, location_no, qty_onhand)	values ('A1234-M', 'A1C', '2000');
insert into Inventory_part_in_stock (part_no, location_no, qty_onhand)	values ('A1234-A', 'B1A', '25');
insert into Inventory_part_in_stock (part_no, location_no, qty_onhand)  values ('A1234-A', 'B1C', '500');
insert into Inventory_part_in_stock (part_no, location_no, qty_onhand)	values ('M5678-A', 'D1A', '5000');
insert into Inventory_part_in_stock (part_no, location_no, qty_onhand)	values ('M5678-A', 'D1B', '5000');
insert into Inventory_part_in_stock (part_no, location_no, qty_onhand)	values ('A9999-B', 'F1A', '250');
insert into Inventory_part_in_stock (part_no, location_no, qty_onhand)	values ('A9999-B', 'F1D', '780');
insert into Inventory_part_in_stock (part_no, location_no, qty_onhand)	values ('A1234-M', 'A1Z', '2000');
insert into Inventory_part_in_stock (part_no, location_no, qty_onhand)	values ('Z1111-Q', 'M1A', '290');


I can't get the query to give me results like you say you're getting.
I think you're missing a few terms out of the ORDER BY clauses in your analytic statements - you're just ordering by the Customer number, but I reckon that you should be ordering by customer number, order number and line number at the very least.

Can you post some data that will give us the results you get.

Alternatively, can you describe on what conditions you want trhe columns to be visible - the right hand end of your sample data is too badly mixed up for me to be able to make sense of it.
Re: PickList query help needed Please [message #196140 is a reply to message #196131] Wed, 04 October 2006 05:50 Go to previous messageGo to next message
MickD
Messages: 19
Registered: October 2006
Junior Member
Sorry about that, i thought i had added all the data needed for the query...

OK what i would like to get is
 
CUST_NO	CUSTOMER_NAME	ADDRESS	ORDER_NO	LINE	REL	SALES_PART	CUSTOMER_PART_NO	SHIP_DATE   CUST_DEMAND	 DUE_TO_BE_SHIPPED	LOCATION_NO	QTY	BATCH_NO	CONTROL_ID

10273	R-Tek Limited	01	C402055	         2	118	A4999-C	        669009U20B	        04/10/2006	7	     7	                   CA31	         9	Samples	             BOND
						        			                        			                           CA31	         9	Samples	             BOND
10273	R-Tek Limited	01	C402056		 2      115	A4999-D	        669019U20B	        04/10/2006	7	     7	                   CA31	         9	Samples	             BOND
						        			                        			                           CA31	         9	Samples	             BOND
10273	R-Tek Limited	01	C402058		 2      130	A5002-A	        669019U30A	        05/10/2006	7	     7	                   MB03	         42	27032	
10273	R-Tek Limited	01	C402057		 2      132	A5002-B	        669009U30A	        05/10/2006	7	     7	                   MB03	         42	27033	
10273	R-Tek Limited	01	C400953		 2      134	M4671-C	        76981AV700	        04/10/2006	250	    250	                 PTRACK	        189	24396	
												                                                           NB18	        250	24396	             QRY
												                                                         PTRACK	         58	26248	
												                                                           MB06	        250	26248	
10273	R-Tek Limited	01	C400953		 2      135	M4671-C	        76981AV700	        05/10/2006	250	    250	                 PTRACK	        189	24396	
												                                                           NB18	        250	24396	             QRY
												                                                         PTRACK	         58	26248	
												                                                           MB06	        250	26248	
10273	R-Tek Limited	01	C400952		 2      142	M4671-D	        76980AV700	        04/10/2006	250	    250	                 PTRACK	        848	24397	
												                                                         PTRACK	         66	26249


So all the seperate order lines are on a seperate line, but if the parts are in more than one location i want to show all locations, but to omit the order details and just show LOCATION_No, QTY, BATCH_No and CONTROL-ID, I hope this makes sense? my original query showed me all order information on every line, but the query that i posted using the CASE WHEN function narrowed it down a little, but i was not getting all the info i needed.

If you have any ideas I would appreciate them.

Thanks in advance,

Mick.



Re: PickList query help needed Please [message #196200 is a reply to message #196140] Wed, 04 October 2006 08:23 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Ok, I've cheated a bit to get the data, but I think this looks like a workable solution.

I've created a table called temp_cust which just holds one row for each row in your final results. You need to replace the 'FROM temp_cust' with the entire FROM clause in your query.

Based on the the data returned from TEMP_CUST, I populate a column called CHANGE. This simply determines if any of the fields that trigger printing new details have changed.
The trick here is to get the ORDER BY in the LAG functions and in the inner level query correct.

create table temp_cust (CUST_NO number,	CUSTOMER_NAME varchar2(30),ADDRESS number,ORDER_NO varchar2(10), LINE number, REL number,SALES_PART varchar2(10),CUSTOMER_PART_NO varchar2(20), SHIP_DATE date,
                        CUST_DEMAND number, DUE_TO_BE_SHIPPED number, LOCATION_NO varchar2(10), QTY number,	BATCH_NO varchar(20),CONTROL_ID varchar2(10));

insert into temp_cust values (10273     ,'R-Tek Limited',   01      ,'C402055',          2,      118,'A4999-C','669009U20B','04/10/2006',    7     ,       7    ,'CA31',       9    ,'Samples','BOND');
insert into temp_cust values (10273     ,'R-Tek Limited',   01      ,'C402055',          2,      118,'A4999-C','669009U20B','04/10/2006',    7     ,       7    ,'CA31',       9    ,'Samples','BOND');
insert into temp_cust values (10273     ,'R-Tek Limited',   01      ,'C402056',          2,      115,'A4999-D','669019U20B','04/10/2006',    7     ,       7    ,'CA31',       9    ,'Samples','BOND');
insert into temp_cust values (10273     ,'R-Tek Limited',   01      ,'C402056',          2,      115,'A4999-D','669019U20B','04/10/2006',    7     ,       7    ,'CA31',       9    ,'Samples','BOND');
insert into temp_cust values (10273     ,'R-Tek Limited',   01      ,'C402058',          2,      130,'A5002-A','669019U30A','05/10/2006',    7     ,       7    ,'MB03',       42   ,'27032','');
insert into temp_cust values (10273     ,'R-Tek Limited',   01      ,'C402057',          2,      132,'A5002-B','669009U30A','05/10/2006',    7     ,       7    ,'MB03',       42   ,'27033','');
insert into temp_cust values (10273     ,'R-Tek Limited',   01      ,'C400953',          2,      134,'M4671-C','76981AV700','04/10/2006',    250   ,      250   ,'PTRACK',      189   ,'24396','');
insert into temp_cust values (10273     ,'R-Tek Limited',   01      ,'C400953',          2,      134,'M4671-C','76981AV700','04/10/2006',    250   ,      250   ,'NB18',      250   ,'24396','QRY');
insert into temp_cust values (10273     ,'R-Tek Limited',   01      ,'C400953',          2,      134,'M4671-C','76981AV700','04/10/2006',    250   ,      250   ,'PTRACK',       58   ,'26248','');
insert into temp_cust values (10273     ,'R-Tek Limited',   01      ,'C400953',          2,      134,'M4671-C','76981AV700','04/10/2006',    250   ,      250   ,'MB06',      250   ,'26248','');
insert into temp_cust values (10273     ,'R-Tek Limited',   01      ,'C400953',          2,      135,'M4671-C','76981AV700','05/10/2006',    250   ,      250   ,'PTRACK',      189   ,'24396','');
insert into temp_cust values (10273     ,'R-Tek Limited',   01      ,'C400953',          2,      135,'M4671-C','76981AV700','05/10/2006',    250   ,      250   ,'NB18',      250   ,'24396','QRY');
insert into temp_cust values (10273     ,'R-Tek Limited',   01      ,'C400953',          2,      135,'M4671-C','76981AV700','05/10/2006',    250   ,      250   ,'PTRACK',       58   ,'26248','');
insert into temp_cust values (10273     ,'R-Tek Limited',   01      ,'C400953',          2,      135,'M4671-C','76981AV700','05/10/2006',    250   ,      250   ,'MB06',      250   ,'26248','');
insert into temp_cust values (10273     ,'R-Tek Limited',   01      ,'C400952',          2,      142,'M4671-D','76980AV700','04/10/2006',    250   ,      250   ,'PTRACK',      848   ,'24397','');

SELECT  case when change = 1 then CUST_NO           else null end     CUST_NO 
       ,case when change = 1 then CUSTOMER_NAME     else null end     CUSTOMER_NAME    
       ,case when change = 1 then ADDRESS           else null end     ADDRESS  
       ,case when change = 1 then ORDER_NO          else null end     ORDER_NO 
       ,case when change = 1 then LINE              else null end     LINE     
       ,case when change = 1 then REL               else null end     REL      
       ,case when change = 1 then SALES_PART        else null end     SALES_PART       
       ,case when change = 1 then CUSTOMER_PART_NO  else null end     CUSTOMER_PART_NO 
       ,case when change = 1 then SHIP_DATE         else null end     SHIP_DATE   
       ,case when change = 1 then CUST_DEMAND       else null end     CUST_DEMAND      
       ,case when change = 1 then DUE_TO_BE_SHIPPED else null end     DUE_TO_BE_SHIPPED  
       ,LOCATION_NO            LOCATION_NO      
       ,QTY                    QTY      
       ,BATCH_NO               BATCH_NO 
       ,CONTROL_ID             CONTROL_ID
from  (select case when cust_no    != lag(cust_no)    over (order by cust_no,rel) then 1
                   when order_no   != lag(order_no)   over (order by cust_no,rel) then 1
                   when line       != lag(line)       over (order by cust_no,rel) then 1
                   when rel        != lag(rel)        over (order by cust_no,rel) then 1
                   when sales_part != lag(sales_part) over (order by cust_no,rel) then 1
                   when customer_part_no != lag(customer_part_no) over (order by cust_no,order_no,line,rel) then 1
                   when ship_date  != lag(ship_date)  over (order by cust_no,order_no,line,rel) then 1
                   else 0 end change
             ,CUST_NO 
             ,CUSTOMER_NAME    
             ,ADDRESS  
             ,ORDER_NO 
             ,LINE     
             ,REL      
             ,SALES_PART       
             ,CUSTOMER_PART_NO 
             ,SHIP_DATE   
             ,CUST_DEMAND      
             ,DUE_TO_BE_SHIPPED
             ,LOCATION_NO
             ,QTY
             ,BATCH_NO
             ,CONTROL_ID
       from temp_cust 
       order by cust_no,rel);

   CUST_NO CUSTOMER_NAME                     ADDRESS ORDER_NO         LINE        REL SALES_PART CUSTOMER_PART_NO     SHIP_DATE CUST_DEMAND DUE_TO_BE_SHIPPED LOCATION_N        QTY BATCH_NO             CONTROL_ID
---------- ------------------------------ ---------- ---------- ---------- ---------- ---------- -------------------- --------- ----------- ----------------- ---------- ---------- -------------------- ----------
     10273 R-Tek Limited                           1 C402056             2        115 A4999-D    669019U20B           04-OCT-06           7                 7 CA31                9 Samples              BOND
                                                                                                                                                              CA31                9 Samples              BOND
     10273 R-Tek Limited                           1 C402055             2        118 A4999-C    669009U20B           04-OCT-06           7                 7 CA31                9 Samples              BOND
                                                                                                                                                              CA31                9 Samples              BOND
     10273 R-Tek Limited                           1 C402058             2        130 A5002-A    669019U30A           05-OCT-06           7                 7 MB03               42 27032
     10273 R-Tek Limited                           1 C402057             2        132 A5002-B    669009U30A           05-OCT-06           7                 7 MB03               42 27033
     10273 R-Tek Limited                           1 C400953             2        134 M4671-C    76981AV700           04-OCT-06         250               250 PTRACK            189 24396
                                                                                                                                                              NB18              250 24396                QRY
                                                                                                                                                              PTRACK             58 26248
                                                                                                                                                              MB06              250 26248
     10273 R-Tek Limited                           1 C400953             2        135 M4671-C    76981AV700           05-OCT-06         250               250 PTRACK            189 24396
                                                                                                                                                              MB06              250 26248
                                                                                                                                                              NB18              250 24396                QRY
                                                                                                                                                              PTRACK             58 26248
     10273 R-Tek Limited                           1 C400952             2        142 M4671-D    76980AV700           04-OCT-06         250               250 PTRACK            848 24397


Is this any good?
Re: PickList query help needed Please [message #196218 is a reply to message #196200] Wed, 04 October 2006 09:43 Go to previous messageGo to next message
MickD
Messages: 19
Registered: October 2006
Junior Member
Hi,

That's what i need thanks very much, the only thing I'm confused about is where i need to declare the aliases? is it the first select, the second or can't i use aliases? I 've tried a couple of different things but i get 'FROM keyword not found where expected'
Any ideas?

Mick.
Re: PickList query help needed Please [message #196222 is a reply to message #196218] Wed, 04 October 2006 09:49 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Not quite sure what you mean - let's have a look at the new query.
Re: PickList query help needed Please [message #196225 is a reply to message #196222] Wed, 04 October 2006 09:58 Go to previous messageGo to next message
MickD
Messages: 19
Registered: October 2006
Junior Member
This is what i have so far ( It's not finished yet ) but i get the error on the first line, but the alias is declared in the FROM, Have i done something wrong?

Mick.

SELECT  case when change = 1 then CO.CUSTOMER_NO                                        else null end     CO.CUSTOMER_NO 
       ,case when change = 1 then IFSAPP.CUST_ORD_CUSTOMER_API.Get_Name(Co.CUSTOMER_NO) else null end     IFSAPP.CUST_ORD_CUSTOMER_API.Get_Name(Co.CUSTOMER_NO)    
       ,case when change = 1 then CO.SHIP_ADDR_NO                                       else null end   CO.SHIP_ADDR_NO  
       ,case when change = 1 then COL.ORDER_NO                                          else null end     COL.ORDER_NO 
       ,case when change = 1 then COL.LINE_NO                                           else null end     COL.LINE_NO     
       ,case when change = 1 then COL.REL_NO                                            else null end     COL.REL_NO      
       ,case when change = 1 then COL.CATALOG_NO                                        else null end     COL.CATALOG_NO       
       ,case when change = 1 then COL.CUSTOMER_PART_NO                                  else null end     COL.CUSTOMER_PART_NO 
       ,case when change = 1 then TO_CHAR(COL.PLANNED_SHIP_DATE,'DD/MM/YYYY')           else null end     TO_CHAR(COL.PLANNED_SHIP_DATE,'DD/MM/YYYY')   
       ,case when change = 1 then COL.buy_qty_due                                       else null end     COL.buy_qty_due      
       ,case when change = 1 then (COL.buy_qty_due - COL.qty_shipped)                   else null end     (COL.buy_qty_due - COL.qty_shipped)  
       ,LOCATION_NO            LOCATION_NO      
       ,QTY                    QTY      
       ,BATCH_NO               BATCH_NO 
       ,CONTROL_ID             CONTROL_ID
from  (select case when co.customer_no    != lag(cust_no)    over (order by cust_no,rel) then 1
                   when order_no   != lag(order_no)   over (order by cust_no,rel) then 1
                   when line       != lag(line)       over (order by cust_no,rel) then 1
                   when rel        != lag(rel)        over (order by cust_no,rel) then 1
                   when sales_part != lag(sales_part) over (order by cust_no,rel) then 1
                   when customer_part_no != lag(customer_part_no) over (order by cust_no,order_no,line,rel) then 1
                   when ship_date  != lag(ship_date)  over (order by cust_no,order_no,line,rel) then 1
                   else 0 end change
              CUST_NO 
             ,CUSTOMER_NAME    
             ,ADDRESS  
             ,ORDER_NO 
             ,LINE     
             ,REL      
             ,SALES_PART       
             ,CUSTOMER_PART_NO 
             ,SHIP_DATE   
             ,CUST_DEMAND      
             ,DUE_TO_BE_SHIPPED
             ,LOCATION_NO
             ,QTY
             ,BATCH_NO
             ,CONTROL_ID
       from IFSAPP.CUSTOMER_ORDER CO,
            IFSAPP.CUSTOMER_ORDER_LINE COL,
            IFSAPP.INVENTORY_PART_IN_STOCK loc
       order by co.customer_no, col.rel_no)
Re: PickList query help needed Please [message #196231 is a reply to message #196225] Wed, 04 October 2006 10:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I see what you were saying.

Table Aliases are only valid in the SELECT that they're declared in.
All the rows that come out of a FROM Clause Query use the alias declared for that query

Rewrite the query as
SELECT  case when change = 1 then ORD.CUSTOMER_NO                                        else null end     .CUSTOMER_NO 
       ,case when change = 1 then IFSAPP.CUST_ORD_CUSTOMER_API.Get_Name(ORD.CUSTOMER_NO) else null end     Customer_name    
       ,case when change = 1 then ORD.SHIP_ADDR_NO                                       else null end    SHIP_ADDR_NO  
       ,case when change = 1 then ORD.ORDER_NO                                          else null end     ORDER_NO 
       ,case when change = 1 then ORD.LINE_NO                                           else null end     LINE_NO     
       ,case when change = 1 then ORD.REL_NO                                            else null end     REL_NO      
       ,case when change = 1 then ORD.CATALOG_NO                                        else null end     CATALOG_NO       
       ,case when change = 1 then ORD.CUSTOMER_PART_NO                                  else null end     CUSTOMER_PART_NO 
       ,case when change = 1 then TO_CHAR(ORD.PLANNED_SHIP_DATE,'DD/MM/YYYY')           else null end     PLANNED_SHIP_DATE
       ,case when change = 1 then ORD.buy_qty_due                                       else null end     buy_qty_due      
       ,case when change = 1 then (ORD.buy_qty_due - ORD.qty_shipped)                   else null end     qty_left
       ,LOCATION_NO            LOCATION_NO      
       ,QTY                    QTY      
       ,BATCH_NO               BATCH_NO 
       ,CONTROL_ID             CONTROL_ID
from  (select case when co.customer_no    != lag(cust_no)    over (order by cust_no,rel) then 1
                   when order_no   != lag(order_no)   over (order by cust_no,rel) then 1
                   when line       != lag(line)       over (order by cust_no,rel) then 1
                   when rel        != lag(rel)        over (order by cust_no,rel) then 1
                   when sales_part != lag(sales_part) over (order by cust_no,rel) then 1
                   when customer_part_no != lag(customer_part_no) over (order by cust_no,order_no,line,rel) then 1
                   when ship_date  != lag(ship_date)  over (order by cust_no,order_no,line,rel) then 1
                   else 0 end change
              CUST_NO 
             ,CUSTOMER_NAME    
             ,ADDRESS  
             ,ORDER_NO 
             ,LINE     
             ,REL      
             ,SALES_PART       
             ,CUSTOMER_PART_NO 
             ,SHIP_DATE   
             ,CUST_DEMAND      
             ,DUE_TO_BE_SHIPPED
             ,LOCATION_NO
             ,QTY
             ,BATCH_NO
             ,CONTROL_ID
       from IFSAPP.CUSTOMER_ORDER CO,
            IFSAPP.CUSTOMER_ORDER_LINE COL,
            IFSAPP.INVENTORY_PART_IN_STOCK loc
       order by co.customer_no, col.rel_no) ord
Re: PickList query help needed Please [message #196238 is a reply to message #196231] Wed, 04 October 2006 10:35 Go to previous messageGo to next message
MickD
Messages: 19
Registered: October 2006
Junior Member
OK Getting a little further now, On your query i was getting the error 'FROM keyword not found where expected' at CUST_NO underneath end change, so i changed the query as follows, but it still gives the error in the same place. I'll keep looking and get back tomorrow if you'll be around.

Thanks,

Mick.

SELECT  case when change = 1 then ORD.CUSTOMER_NO                                        else null end     CUSTOMER_NO 
       ,case when change = 1 then IFSAPP.CUST_ORD_CUSTOMER_API.Get_Name(ORD.CUSTOMER_NO) else null end     Customer_name    
       ,case when change = 1 then ORD.SHIP_ADDR_NO                                       else null end    SHIP_ADDR_NO  
       ,case when change = 1 then ORD.ORDER_NO                                          else null end     ORDER_NO 
       ,case when change = 1 then ORD.LINE_NO                                           else null end     LINE_NO     
       ,case when change = 1 then ORD.REL_NO                                            else null end     REL_NO      
       ,case when change = 1 then ORD.CATALOG_NO                                        else null end     CATALOG_NO       
       ,case when change = 1 then ORD.CUSTOMER_PART_NO                                  else null end     CUSTOMER_PART_NO 
       ,case when change = 1 then TO_CHAR(ORD.PLANNED_SHIP_DATE,'DD/MM/YYYY')           else null end     PLANNED_SHIP_DATE
       ,case when change = 1 then ORD.buy_qty_due                                       else null end     buy_qty_due      
       ,case when change = 1 then (ORD.buy_qty_due - ORD.qty_shipped)                   else null end     due_to_be_shipped
       ,LOCATION_NO            LOCATION_NO      
       ,QTY                    QTY      
       ,BATCH_NO               BATCH_NO 
       ,CONTROL_ID             CONTROL_ID
from  (select case when co.customer_no    != lag(co.customer_no)    over (order by co.customer_no,rel) then 1
                   when order_no   != lag(order_no)   over (order by co.customer_no,rel) then 1
                   when line       != lag(line)       over (order by co.customer_no,rel) then 1
                   when rel        != lag(rel)        over (order by co.customer_no,rel) then 1
                   when sales_part != lag(sales_part) over (order by co.customer_no,rel) then 1
                   when customer_part_no != lag(customer_part_no) over (order by co.customer_no,order_no,line,rel) then 1
                   when ship_date  != lag(ship_date)  over (order by cust_no,order_no,line,rel) then 1
                   else 0 end change
              CO.customer_no 
             ,IFSAPP.CUST_ORD_CUSTOMER_API.Get_Name(ORD.CUSTOMER_NO) Customer_Name   
             ,co.ship_addr_no  
             ,col.ORDER_NO 
             ,col.line_no LINE     
             ,col.rel_no REL      
             ,col.catalog_no SALES_PART       
             ,col.CUSTOMER_PART_NO 
             ,SHIP_DATE   
             ,CUST_DEMAND      
             ,DUE_TO_BE_SHIPPED
             ,LOCATION_NO
             ,QTY
             ,BATCH_NO
             ,CONTROL_ID
       from IFSAPP.CUSTOMER_ORDER CO,
            IFSAPP.CUSTOMER_ORDER_LINE COL,
            IFSAPP.INVENTORY_PART_IN_STOCK loc
       order by co.customer_no, col.rel_no) ord
Re: PickList query help needed Please [message #196358 is a reply to message #196238] Thu, 05 October 2006 02:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There's a comma missing between the field CHANGE, and the field CO.customer_number
Re: PickList query help needed Please [message #196371 is a reply to message #196358] Thu, 05 October 2006 02:41 Go to previous messageGo to next message
MickD
Messages: 19
Registered: October 2006
Junior Member
Hi Mr Rowbottom ( sorry I don't know your first name )

Thanks for your reply, i did find the missing comma last night, then it gave me lots of errors with unknown fields etc... but im stuck on a bit now that i can't get past its:
",case when change = 1 then (ORD.buy_qty_due - ORD.qty_shipped)"

and is pointing to ORD.qty_shipped

Below is the code that i have tried:

SELECT  case when change = 1 then ORD.CUSTOMER_NO                                        else null end     CUSTOMER_NO 
       ,case when change = 1 then ORD.IFSAPP.CUST_ORD_CUSTOMER_API.Get_Name(ORD.CUSTOMER_NO) else null end     Customer_name    
       ,case when change = 1 then ORD.SHIP_ADDR_NO                                       else null end    SHIP_ADDR_NO  
       ,case when change = 1 then ORD.ORDER_NO                                          else null end     ORDER_NO 
       ,case when change = 1 then ORD.LINE_NO                                           else null end     LINE_NO     
       ,case when change = 1 then ORD.REL_NO                                            else null end     REL_NO      
       ,case when change = 1 then ORD.CATALOG_NO                                        else null end     CATALOG_NO       
       ,case when change = 1 then ORD.CUSTOMER_PART_NO                                  else null end     CUSTOMER_PART_NO 
       ,case when change = 1 then TO_CHAR(ORD.PLANNED_SHIP_DATE,'DD/MM/YYYY')           else null end     PLANNED_SHIP_DATE
       ,case when change = 1 then ORD.buy_qty_due                                       else null end     buy_qty_due      
       ,case when change = 1 then (ORD.buy_qty_due - ORD.qty_shipped)                   else null end     due_to_be_shipped
       ,LOCATION_NO            LOCATION_NO      
       ,QTY_onhand                   QTY      
       ,lot_BATCH_NO               BATCH_NO 
       ,availability_CONTROL_ID             CONTROL_ID
from  (select case when co.customer_no    != lag(co.customer_no)    over (order by co.customer_no,col.rel_no) then 1
                   when col.order_no   != lag(col.order_no)   over (order by co.customer_no,col.rel_no) then 1
                   when line_no       != lag(line_no)       over (order by co.customer_no,col.rel_no) then 1
                   when rel_no        != lag(rel_no)        over (order by co.customer_no,col.rel_no) then 1
                   when catalog_no != lag(catalog_no) over (order by co.customer_no,col.rel_no) then 1
                   when customer_part_no != lag(customer_part_no) over (order by co.customer_no,col.order_no,col.line_no,col.rel_no) then 1
                   when to_char(col.PLANNED_SHIP_DATE,'DD/MM/YYYY')  != lag(to_char(col.PLANNED_SHIP_DATE,'DD/MM/YYYY'))  over (order by co.customer_no,col.order_no,col.line_no,col.rel_no) then 1
                   else 0 end change
              ,CO.customer_no 
             ,IFSAPP.CUST_ORD_CUSTOMER_API.Get_Name(co.CUSTOMER_NO) Customer_Name   
             ,co.ship_addr_no  
             ,col.ORDER_NO 
             ,col.line_no LINE     
             ,col.rel_no REL      
             ,col.catalog_no SALES_PART       
             ,col.CUSTOMER_PART_NO 
             ,to_char(col.PLANNED_SHIP_DATE,'DD/MM/YYYY')  Ship_Date   
             ,col.buy_qty_due CUST_DEMAND      
             ,(col.buy_qty_due - col.qty_shipped) DUE_TO_BE_SHIPPED
             ,loc.LOCATION_NO
             ,QTY_onhand
             ,lot_BATCH_NO
             ,availability_CONTROL_ID
       from IFSAPP.CUSTOMER_ORDER CO,
            IFSAPP.CUSTOMER_ORDER_LINE COL,
            IFSAPP.INVENTORY_PART_IN_STOCK loc
       order by co.customer_no, col.rel_no) ord


Thanks in advance,

Mick.
Re: PickList query help needed Please [message #196372 is a reply to message #196371] Thu, 05 October 2006 02:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's objecting because it can't find a column called QTY_SHIPPED in the list of columns provided by the query aliased ORD.
If you look at the list of columns in the innermost SELECT (which is the one called ORD) you'll see that COL.QTY_SHIPPED isn't being selected.
It will also be objecting becuase there isn't a column ORD.BUY_QTY_DUE - you've renamed BUY_QTY_DUE to CUST_DEMAND in the ORD select.
Re: PickList query help needed Please [message #196383 is a reply to message #196372] Thu, 05 October 2006 03:31 Go to previous messageGo to next message
MickD
Messages: 19
Registered: October 2006
Junior Member
Hi,

I think all the column names etc are sorted now as the query runs, I've added a where clause in because i need to be able to select customer number and ship dates, but now i have a couple of problems,

firstly when i do a particular query i get two lines, which is correct because there should be one order for the customer and parts are in two locations, but all i can see is 'location_no', 'qty', 'batch_no', 'control_id' none of the other fields are filled in ie, customer_no, Customer_name, etc..
If i remove the case when change statement then i get something ( but obviously it's on two lines)

Secondly in the where clause i have put a date in there for the ship date, on my original query this works fine and i can put in any date and it will show all orders to be shipped by that date, now if I put in a date in the future i get 'INVALID NUMBER' error, i can only run the query for todays date or in the past....

Any idea's on these two problems? Below is the code:

Thanks in advance,

Mick.

SELECT  case when change = 1 then ORD.CUSTOMER_NO                                       else null end     CUSTOMER_NO 
       ,case when change = 1 then ORD.Customer_Name                                     else null end     Customer_name    
       ,case when change = 1 then ORD.SHIP_ADDR_NO                                      else null end    SHIP_ADDR_NO  
       ,case when change = 1 then ORD.ORDER_NO                                          else null end     ORDER_NO 
       ,case when change = 1 then ORD.LINE                                              else null end     LINE_NO     
       ,case when change = 1 then ORD.REL                                               else null end     REL_NO      
       ,case when change = 1 then ORD.SALES_PART                                        else null end     Sales_part       
       ,case when change = 1 then ORD.CUSTOMER_PART_NO                                  else null end     CUSTOMER_PART_NO 
       ,case when change = 1 then TO_CHAR(ORD.PLANNED_SHIP_DATE,'DD/MM/YYYY')           else null end     PLANNED_SHIP_DATE
       ,case when change = 1 then ORD.buy_qty_due                                       else null end     buy_qty_due      
       ,case when change = 1 then (ORD.buy_qty_due - ORD.qty_shipped)                   else null end    due_to_be_shipped
       ,LOCATION_NO            LOCATION_NO      
       ,QTY_onhand                   QTY      
       ,lot_BATCH_NO               BATCH_NO 
       ,availability_CONTROL_ID             CONTROL_ID
from  (select case when co.customer_no    != lag(co.customer_no)    over (order by co.customer_no,col.rel_no) then 1
                   when col.order_no   != lag(col.order_no)   over (order by co.customer_no,col.rel_no) then 1
                   when line_no       != lag(line_no)       over (order by co.customer_no,col.rel_no) then 1
                   when rel_no        != lag(rel_no)        over (order by co.customer_no,col.rel_no) then 1
                   when col.catalog_no != lag(col.catalog_no) over (order by co.customer_no,col.rel_no) then 1
                   when customer_part_no != lag(customer_part_no) over (order by co.customer_no,col.order_no,col.line_no,col.rel_no) then 1
                   when to_char(col.PLANNED_SHIP_DATE,'DD/MM/YYYY')  != lag(to_char(col.PLANNED_SHIP_DATE,'DD/MM/YYYY'))  over (order by co.customer_no,col.order_no,col.line_no,col.rel_no) then 1
                   else 0 end change
              ,CO.customer_no 
             ,IFSAPP.CUST_ORD_CUSTOMER_API.Get_Name(co.CUSTOMER_NO) Customer_Name   
             ,co.ship_addr_no  
             ,col.ORDER_NO 
             ,col.line_no LINE     
             ,col.rel_no REL      
             ,col.catalog_no SALES_PART     
             ,col.CUSTOMER_PART_NO 
             ,to_char(col.PLANNED_SHIP_DATE,'DD/MM/YYYY')  planned_Ship_Date   
             ,col.buy_qty_due       
             ,(col.buy_qty_due - col.qty_shipped) DUE_TO_BE_SHIPPED
             ,loc.LOCATION_NO
             ,QTY_onhand
             ,qty_shipped
             ,lot_BATCH_NO
             ,availability_CONTROL_ID
       from IFSAPP.CUSTOMER_ORDER CO,
            IFSAPP.CUSTOMER_ORDER_LINE COL,
            IFSAPP.INVENTORY_PART_IN_STOCK loc
Where col.state not in ('Cancelled', 'Delivered', 'Invoiced/Closed') and co.ORDER_ID not like 'TO' and col.catalog_no not like 'D%' and col.planned_ship_date <= nvl(trunc(to_date('04/10/2006', 'DD/MM/YYYY')),sysdate) and loc.part_no = col.part_no and col.order_no = co.order_no and 
      (co.customer_no like nvl('10295','%')
      or co.customer_no like nvl('','z')
      or co.customer_no like nvl('','z')
      or co.customer_no like nvl('','z')
      or co.customer_no like nvl('','z')
      or co.customer_no like nvl('','z')
      or co.customer_no like nvl('','z')
      or co.customer_no like nvl('','z')
      or co.customer_no like nvl('','z')
      or co.customer_no like nvl('102950','z')) and co.contract= 'CAS' and col.contract= 'CAS' and loc.qty_onhand > 0 and loc.location_no not in ('CASBOND','QRY1')
       order by co.customer_no, col.rel_no) ord
Re: PickList query help needed Please [message #196404 is a reply to message #196383] Thu, 05 October 2006 04:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I can't see a problem with the Date logic, as long as the column PLANNED_SHIP_DATE has a type of DATE.
The error is one you'd get if you're joining a column of type NUMBER to a column of type VARCHAR, and the optimiser is doing a to_number on the varchar.
Try reformatting that query so that each condition in the WHERE clause is on a seperate line - that might make it easier to spot the error.

To work out why rows aren't showing all the details, just run the inner query, and have a look at the value of the change column.
Re: PickList query help needed Please [message #196422 is a reply to message #196404] Thu, 05 October 2006 05:27 Go to previous messageGo to next message
MickD
Messages: 19
Registered: October 2006
Junior Member
Hi,

I'm not quite sure what you mean by inner query? could you explain which part you meant/ I'm pretty new to sql so please bear with me. And also I'm using pl/sql and i thought the cursor went to the place the error was, but it doesn't seem to be doing that now, should it or was i imagining it before? I've put all the lines on the where clause on seperate lines, but it's not showing me anything.

Mick.

OK I've run the inner query, and that runs fine, there are rows with 1's and 0's in the change column so that looks good, not sure what the problem is?

And also the DATE works fine, I can put any date i need in.

[Updated on: Thu, 05 October 2006 05:43]

Report message to a moderator

Re: PickList query help needed Please [message #196446 is a reply to message #196422] Thu, 05 October 2006 06:38 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
By Inner Query, I mean the SELECT statement that's aliased ORD, the one that's selecting from Customer_Order, Customer_Order_Line and Inventory.

In SQL*Plus, it will highlight the line in the query that it has a problem with.
In Pl/Sql, it will generally highlight the statement that the error is in, and one entire sql statement like yours counts as one statement.
I'd run SQL in SQL*Plus or TOAD or SQL Developer as a piece of SQL, and get it working before trying to use it in Pl/Sql.

Are the 1s and 0s that you're seeing when you run the inner query correct - ie you get 1 1 on rows that you'd expect to see all the data on, and a 0 on rows that you'd expect to only see the last few columns on?

Re: PickList query help needed Please [message #196448 is a reply to message #196422] Thu, 05 October 2006 06:49 Go to previous messageGo to next message
MickD
Messages: 19
Registered: October 2006
Junior Member
Hi,
Thanks for the explaination,

I think i've found the problem, it's the PLANNED_SHIP_DATE statement's, I removed all entries and the query works perfect just as i want it to ( but without the planned ship date) So i need to find another way of getting this in as the stores people will need this.

Thanks very much for all your help on this.
Re: PickList query help needed Please [message #196451 is a reply to message #196448] Thu, 05 October 2006 06:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I see the problem.

In the ORD query, you are selecting
to_char(col.PLANNED_SHIP_DATE,'DD/MM/YYYY')  planned_Ship_Date   

When you get to the part of the query where you are doing a CASE on CHANGE and seeing if you need to display the Planned_Ship_Date, you are doing
,case when change = 1 then TO_CHAR(ORD.PLANNED_SHIP_DATE,'DD/MM/YYYY')           else null end


At this point, Planned_Ship_Date is already a Varchar column, and I bet that this is the problem.

Replace the line
to_char(col.PLANNED_SHIP_DATE,'DD/MM/YYYY')  planned_Ship_Date   
in the ORD select with a simple
Planned_Ship_Date
and hopefully things will work.
Re: PickList query help needed Please [message #196458 is a reply to message #196451] Thu, 05 October 2006 07:36 Go to previous message
MickD
Messages: 19
Registered: October 2006
Junior Member
Yes, That's it, it works great.
Thanks very much for your help and patience.

Mick.
Previous Topic: will this query work in oracle10g
Next Topic: Update query Help
Goto Forum:
  


Current Time: Wed Dec 07 06:40:54 CST 2016

Total time taken to generate the page: 0.10956 seconds