I am facing one problem regarding Oracle Sql queries in Scriptella.
My intention is - I have to fetch some column data from PRODUCT_PRICE table for each row (determined using where clause) fetched from PRODUCT table and then if the fetched data from PRODUCT_PRICE contains nothing or null, then I have to insert a new column for the price value corresponding to product & store id into the table or else if there exists a row corresponding to product & store id, then I have to update the price.
The following code should clear the logic which I am describing -
<query connection-id="db">
select PRODUCT_ID as "product_id1", STORE_ID as "store_id1" from PRODUCT
<query connection-id="db">
select REGULAR_PRICE, PRODUCT_ID as "product_id2", STORE_ID as "store_id2" from PRODUCT_PRICE where PRODUCT_ID=?product_id1 and STORE_ID=?store_id1
<script connection-id="db" if="rownum==0">
insert into PRODUCT_PRICE(REGULAR_PRICE, PRODUCT_ID, STORE_ID) values(?price, product_id1, store_id1)
</script>
<script connection-id="db" if="rownum gt 0">
update REGULAR_PRICE=?price where PRODUCT_ID=?product_id2 and STORE_ID=?store_id2
</script>
</query>
<query>
My question is - this is not working! As you can see I have to insert product_id, store_id as well as 'price' into the PRODUCT_PRICE table if there is no data present corresponding to the product & store id. Also I have to update only price if there exists a row of same product & store_id. But when the resulset fetched in the second query contains nothing, then the script with condition "rownum==0" is not working. SO how can achieve this in Oracle?
In MySQL or SyBase database, I know there is one syntax called "if exists(select ....) / then something / else something" but I could not find anything in the Oracle database. How to achieve this conditional insertion or updation based on select criterion in Oracle database because our project is based on Oracle??
P.S. - Here "price" variable is already found before the first query. I have put only the relevant part of the code.
You can implement it by using count() function:
<query connection-id="db">
select PRODUCT_ID as "product_id1", STORE_ID as "store_id1" from PRODUCT
<query connection-id="db">
select COUNT(*) as Price_Count from PRODUCT_PRICE where PRODUCT_ID=?product_id1 and STORE_ID=?store_id1
<script connection-id="db" if="Price_Count==0">
insert into PRODUCT_PRICE(REGULAR_PRICE, PRODUCT_ID, STORE_ID) values(?price, product_id1, store_id1)
</script>
<script connection-id="db" if="Price_Count gt 0">
update REGULAR_PRICE=?price where PRODUCT_ID=?product_id1 and STORE_ID=?store_id1
</script>
</query>
<query>
Although this solution should be working, you can leverage the power of SQL joins. The following solution needs only single query to accomplish this task. I will use left outer join. The idea is that you join 2 tables by product_id,store_id. When there is no match in product_price table, the corresponding price attribute will be null. Imagine you have the following input:
select * from product;
PRODUCT_ID STORE_ID
1 1
2 1
3 1
select * from product_price;
PRODUCT_ID STORE_ID PRICE
2 1 100
2 1 150
3 1 200
The left outer join of these 2 tables will look like:
select p.product_id, p.store_id , pp.price from product p LEFT OUTER JOIN product_price pp ON p.product_id =pp.product_id and p.store_id =pp.store_id;
PRODUCT_ID STORE_ID PRICE
1 1 null
2 1 100
2 1 150
3 1 200
Then you need to apply group by and count by price:
select p.product_id, p.store_id , count(pp.price) as Prices_Count from product p LEFT OUTER JOIN product_price pp ON p.product_id =pp.product_id and p.store_id =pp.store_id group by (p.product_id, p.store_id)
PRODUCT_ID STORE_ID Prices_Count
1 1 0
3 1 1
2 1 2
Then the XML becomes trivial and what is more important it should be much faster, because database joins are more efficient then ones made by any external client.
<query connection-id="db">
select p.product_id, p.store_id , count(pp.REGULAR_PRICE) as Prices_Count from product p LEFT OUTER JOIN product_price pp ON p.product_id =pp.product_id and p.store_id =pp.store_id group by (p.product_id, p.store_id)
<script connection-id="db" if="Prices_Count==0">
insert into PRODUCT_PRICE(REGULAR_PRICE, PRODUCT_ID, STORE_ID) values(?price, product_id, store_id)
</script>
<script connection-id="db" if="Prices_Count gt 0">
update REGULAR_PRICE=?price where PRODUCT_ID=?product_id and STORE_ID=?store_id
</script>
</query>
I think that the approach can be optimized even further by using MERGE INTO, so everything can be accomplished in a single Oracle statement, but I'll leave it for hungry minds.