Home » Developer & Programmer » Forms » Populate data block (Forms 6i)
Populate data block [message #593157] Wed, 14 August 2013 12:45 Go to next message
m.abdulhaq
Messages: 105
Registered: April 2013
Location: Ajman
Senior Member
i have created on query block , upon pressing the button there is a wehre clause in the block which will filter the records based on all alike items from two tables but my problem is , in one table there is no information of struct and in other there is struct information or data, what i want is even if i pass a parameter in the where clause all the records should be filtered.


CREATE TABLE OT_ACTUAL_ACT (ITEM_CODE VARCHAR2(30),ITEM_NAME VARCHAR2(30),WO VARCHAR2(12),STRUCT VARCHAR2(12))

INSERT INTO OT_ACTUAL_ACT(ITEM_CODE,ITEM_NAME,WO,STRUCT) VALUES ('1001','HEA100X10','300',null);

INSERT INTO OT_ACTUAL_ACT(ITEM_CODE,ITEM_NAME,WO,STRUCT) VALUES ('1003','HEA100','300',null);

INSERT INTO OT_ACTUAL_ACT(ITEM_CODE,ITEM_NAME,WO,STRUCT) VALUES ('1004','HEA200','300',null);


CREATE TABLE OT_ML_ACT (MI_ITEM VARCHAR2(30),MI_ITEM_NAME VARCHAR2(30),MI_WO VARCHAR2(12),MI_STRUCT VARCHAR2(12))

INSERT INTO OT_ML_ACT (MI_ITEM,MI_ITEM_NAME,MI_WO,MI_STRUCT) VALUES ('1002','HEA100X22','300','30010');


CREATE OR REPLACE VIEW SIMPROF
AS 
SELECT ITEM_CODE,ITEM_NAME,WO,STRUCT 
FROM
OT_ACTUAL_ACT
UNION
SELECT MI_ITEM,MI_ITEM_NAME,MI_WO,MI_STRUCT
FROM OT_ML_ACT


SELECT * FROM SIMPROF WHERE UPPER(ITEM_NAME) LIKE 'HEA100%' AND NVL(STRUCT,'X') = '30010'

I am getting this

ITEM_CODE	ITEM_NAME	WO	STRUCT
1002	        HEA100X22	300	30010

i want all the records


ITEM_CODE	ITEM_NAME	WO	STRUCT
1002	        HEA100X22	300	30010
1003            HEA100          300     
1001            HEA100X10       300     



Re: Populate data block [message #593184 is a reply to message #593157] Wed, 14 August 2013 15:24 Go to previous message
joy_division
Messages: 4490
Registered: February 2005
Location: East Coast USA
Senior Member
m.abdulhaq wrote on Wed, 14 August 2013 13:45
SELECT * FROM SIMPROF WHERE UPPER(ITEM_NAME) LIKE 'HEA100%' AND NVL(STRUCT,'X') = '30010'



How about
SELECT * FROM SIMPROF WHERE UPPER(ITEM_NAME) LIKE 'HEA100%' AND (NVL(STRUCT,'X') = '30010' or struct is null)
Previous Topic: Master Detail form storing multiple records
Next Topic: Next-Item trigger
Goto Forum:
  


Current Time: Tue Jul 29 21:32:44 CDT 2014

Total time taken to generate the page: 0.09345 seconds