Home » SQL & PL/SQL » SQL & PL/SQL » steps to create dynamic join query in oracle sql
steps to create dynamic join query in oracle sql [message #668714] Mon, 12 March 2018 07:02 Go to next message
asheolik
Messages: 1
Registered: March 2018
Junior Member
Hi SQL GURUS

I am doing this query for creating testing set of data.
Need your help :

select distinct fixed_atribute_element_id from products p
inner join product_attributes using(product_id)
left join
( select attribute_element_id as
fixed_atribute_element_id,product_attribute_id from attribute_fixed_texts
where attribute_element_id = 510
)using (product_attribute_id)

where attribute_type_id = 159


i will need this one for sample testing data . But issue is table can have multiple fixed text data that may come from another table.
which in turn should look like this :


select distinct fixed_atribute_element_id,fixed_atribute_element_id1 from
products p
inner join product_attributes using(product_id)
left join
( select attribute_element_id as
fixed_atribute_element_id,product_attribute_id from attribute_fixed_texts
where attribute_element_id = 510
)using (product_attribute_id)
left join
(
select attribute_element_id as
fixed_atribute_element_id1,product_attribute_id from attribute_free_texts
where attribute_element_id = 509
) using(product_attribute_id )


where attribute_type_id = 159
I thought this can be done by generating dynamic join query.
Can you please give guidance .How can i achieve this.This is just sample.
Please let me know i can clarify further.
Re: steps to create dynamic join query in oracle sql [message #668716 is a reply to message #668714] Mon, 12 March 2018 08:04 Go to previous message
John Watson
Messages: 8348
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read the OraFAQ Forum Guide and How to use code tags and make your code easier to read

I've formatted your first query for you:
SELECT DISTINCT fixed_atribute_element_id
FROM   products p
       inner join product_attributes USING(product_id)
       left join (SELECT attribute_element_id AS fixed_atribute_element_id,
                         product_attribute_id
                  FROM   attribute_fixed_texts
                  WHERE  attribute_element_id = 510)USING (product_attribute_id)
WHERE  attribute_type_id = 159 
It would help if you used table aliases correctly. As it is, one has to guess where each column comes from. Do you actually need to include PRODUCTS in the query?
Previous Topic: Get Current & Previous Quarter Data from table
Next Topic: DBMS_CRYPTO package
Goto Forum:
  


Current Time: Mon Sep 28 23:31:02 CDT 2020