View Suresh Vaishya's profile
Now there are 3 ways to create distinct values value set
1) Write distinct statement in the field where table name is entered, but this has a limitation to the number of characters that can be entered. so if query is long then you cannot make use of this feature
2) Create a view based on your query and use that view in the value set
3) Write your query in such a way that it gives you a distinct value. You can make use of ROWID to meet the requirement.
ex. SELECT ooh.order_number
FROM oe_order_lines_all ool, oe_order_headers_all ooh
WHERE ool.header_id = ooh.header_id
AND ool.ROWID = (SELECT MAX(ROWID)
FROM oe_order_lines_all oo11
WHERE oo11.header_id = ooh.header_id)
Note that the above query is just an example and needs to be changed as per your need. In the above query I purposely joined lines and header to display duplicate order_numbers and then supressed it by using MAX(ROWID).
3 Comments:
I want to create a value set using distinct as well as $flex$ values, i tried creating view as well but it does not work. here is complete scenario:-
I want to fetch distinct test cases for items in some range. Query which works fine otherwise is here:-
SELECT test_desc,A.test_id,test_code FROM
(select distinct test_desc,test_id,test_code,ROW_ID
FROM gmd_qc_tests_vl )A,gmd_specifications gm_spec, gmd_spec_tests_b spec_test
WHERE gm_spec.spec_id = spec_test.spec_id
AND a.test_id = spec_test.test_id
AND GM_SPEC.INVENTORY_ITEM_ID BETWEEN 30000 AND 99999.
I dont know how to do this using value set.
If you have already created a view then why dont you just refer that view in your value set.
Also the way you have written your query does not seem to be write, you have distinct and then the rowid in the same column list, that will never give you distinct values. Also I dont see any need to write inline query and then join it with other tables. I think this can easily be written as follows to get distinct records
SELECT test_desc,A.test_id,test_code
FROM gmd_qc_tests_vl A,gmd_specifications gm_spec, gmd_spec_tests_b spec_test
WHERE gm_spec.spec_id = spec_test.spec_id
AND a.test_id = spec_test.test_id
AND GM_SPEC.INVENTORY_ITEM_ID BETWEEN 30000 AND 99999
AND a.rowid = (SELECT MAX(rowid) FROM gmd_qc_tests_vl b WHERE a.test_id = b.test_id)
Hi suresh, below is my query and the rowid condition is not working for me :
SELECT cii.instance_number
FROM csi_item_instances cii, wip_discrete_jobs wdj
WHERE cii.instance_id =
DECODE (wdj.maintenance_object_type,
3, wdj.maintenance_object_id,
NULL
)
AND TRUNC (wdj.date_closed) BETWEEN TRUNC (:p_from_date)
AND TRUNC (:p_to_date)
AND wdj.organization_id = :p_org_id
AND cii.ROWID = (SELECT MAX (ROWID)
FROM csi_item_instances cii2
WHERE cii2.instance_number = cii.instance_number)
ORDER BY cii.instance_number
Post a Comment