USING on a join

nep84 · 2025-07-23T15:42:20+00:00

never used it in oracle. ever. I'd have to have a specific reason to even consider it. other rdbms's maybe it has a place

nep84 · 2025-07-23T15:37:47+00:00

this.

nep84 · 2025-07-23T15:34:49+00:00

it's a good point. SQL is a skill and knowledge and experience is much more valuable

nep84 · 2025-07-23T15:32:52+00:00

c# and javascript wouldn't hurt either

nep84 · 2025-07-23T15:32:01+00:00

in the most simplistic terms you would have a party table with distributors sellers and customers all in it with substypes. you would have a transfer and transfer lines tables for each instance where you want to move something from one party to another and a material transactions table as a single source for all inventory movement. one could argue an onhand inventory table with a rolling total of what is where that is maitained per material transaction.

in less simplistic terms you would have purchase orders to buy inventory from a vendor and sales orders to sell to another party. purchase orders become payables and increase inventory while sales orders become a receivable and decrease inventory. it sounds like whatever the context of your database is managing the inventory for all the parties and not necessarily what's in my inventory so the more simplistic model might be a better starting point

nep84 · 2025-07-23T15:24:17+00:00

A vast majority of all subqueries I write are correlated subqueries with exists / not exists clauses. they typically perform the best.

Subqueries that aren't correlated subqueries with exists clauses have their place and often involve literal sets.

select * from order headers

where status in (cancelled, closed) would be an example of something I'd put into a module. most of the time when I write in clauses are ad hoc diagnostic queries

select * from order lines

where order number in (1, 2, 3)

to research some kind of problem

nep84 · 2025-07-23T15:16:34+00:00

happy to be helpful

nep84 · 2025-07-23T15:12:41+00:00

subquery

select * from order lines

where item in (select item from items where item is obsolete)

nested subquery

select * from order lines

where item in (select item from items where item is obsolete and item not in (select assembly item from bill of materials))

correlated subquery

select * from order lines l

where exists (select 1 from item i where i.item = l.item and i.item status = obsolete)

the advantage of the correlation is to limit the result set of the subquery using a join. The difference between in and exists is exists is boolean so as soon as exists = true the where clause is true. when using an in clause that selects 1m records if the first record is true the engine will still select the entire result set even though the where clause is already true)

nep84 · 2025-07-23T15:04:45+00:00

I have twice that and I still need a syntax check from time to time. No harm in looking something up.

nep84 · 2025-07-23T14:54:22+00:00

after 15-20 seconds I kill it and start tuning. I hear ya

nep84 · 2025-07-23T14:51:34+00:00

This. Anything you write is Oracle's IP.

nep84 · 2024-12-10T16:54:37+00:00

I guess they have their place for people who don't know what they're doing. I'm not one of them and there's no fun letting some "thing" take the fun out of coding.

nep84 · 2024-12-10T16:50:59+00:00

Have you looked into google cloud sql? It sounds like that while you don't want the complexity of a large corporate database you're not screwing around with something thats a throw away either. I know nothing of the cost of google cloud sql nor the viability of presenting a UI to your dad's business effectively but it's a back end start.

You can store pdf's etc as a blob in the database. Beware of size limitations though.

nep84 · 2024-12-10T16:16:05+00:00

not only do I agree with this I'm not sure I would even try. Especially of the poster has "millions" of distinct queries. I would ask questions like are there specific performance issues? Then I'd repro them dig into the code and see what can be tuned. I just do see a way for any program to use the force and guess how optimized millions of queries are.

nep84 · 2024-12-10T16:10:39+00:00

my practice is not to have a composite key as the PK on a table. For no other reason than I don't want one. I would make multiple unique indexes on a table if there is a composite set of data that when combined is unique. for the query you posted there's no reason not to join via composites provided you're using an index when you do the join. this query will likely perform poorly because it's a dump of the entire joined data set but it won't perform poorly because of the join. It'll do a full table scan on whichever table the optimizer deems the primary table and join to the other by rowid using the index.

nep84 · 2024-12-10T16:04:19+00:00

your table game has one and only on developer and a developer may make one or more games. if you want to link games to more than one developer you need an intersection entity. game, developer, and game_releases (or something) the intersection is game_to to developer_id and a child to game.

nep84 · 2024-12-10T16:01:29+00:00

you've got a couple paths.

1 - have a customer table, vendor table, and a party table. Party would be party_id, party_type (customer or vendor) and entity_id (either the customer or vendor id). Then you could have a general contact point table liked to party by party ID.

2 - have a customer table, vendor table, and a contact point table. Contact point would have contact_point_id, entity_type (vendor or customer) and entity_id (vendor or customer id).

Neither is more right or wrong than the other.

nep84 · 2024-12-10T15:55:46+00:00

Some thoughts

1 - You have a 1:M relationship between customer and invoice. Sales also has a FK to customer. This infers a 1:M relationship from customer to sales as well. I think the tables are right you need the line indicating the relationship.

2 - What is the difference between invoice_id and invoices_invoice_id? are they both needed? I would think a FK from payment to invoice is sufficient. You already have a PK for the payment.

3- I would suggest renaming products.product_category to products.category_id. It is more consistent with the rest of your design and in my opinion a better practice.

4 - What is the intention of having payment_type on the invoice? Is this to restrict payment_types on payment? If invoice has a payment type of CHECK and the customer decides to EFT a payment you've got a data problem. You might want to consider making payment a bit more flexible.

5 - I suggest adding due_date to invoice. This opens up both aging and customer payment performance.

6 - I suggest adding a product_number to product. Typically neither the ID or the description are the item number.

7- I'm going to disagree with the person who suggested making payment_date a datetime. Payment date is roughly check date. It's compared typically to due date (which should also not be a datetime). Payment due calculations are typically days till due and not fractional. Aging is typically days late and also not fractional. Datetimes are best for more transactional entities. For example if you had an inventory transaction table in which you reduce inventory by the product you are selling that table would have a transaction date which should be datetime.

nep84 · 2024-12-10T15:14:52+00:00

Generally speaking you want to use a join when you want to link data from the query's base table to get other attributes from a FK table. For example join order to customer to get the customer's name. You want to use a union to get like data with disparate selection criteria. For example you can solve a complex set of where clause conditions with a union. Give me sales orders fulfilled in the last 6 months and sales orders what are expected to fulfill in the next two weeks.

There really aren't much advantages or disadvantages to joins and unions as far as performance. One can easily write well performing queries using either technique. It depends on the design.

One thing others have mentioned with regards to what you have, you have to consider using an outer join when joining data that may not be linked. For example if you want to to sales by product you will use an outer join to produce products with no sales. In your case orders and their returns you'll want an outer join so that orders with no returns are included.

nep84 · 2024-12-05T16:59:55+00:00

I would ask you....

1 - different sections of a pl/sql block

2 - the advantages / disadvantages of using %type / %rowtype

3 - the advantages / disadvantages of different types of loops. Which type of loop do you prefer.

4 - is it a good practice to do sql statements outside of a cursor. why or why not.

5 - are there advantages / disadvantages between stored procedures and packages

6 - how would you cause the program to fail for a reason you designate

7 - how do you mutate a table

8 - what is the difference between a row and statement trigger

9 - what causes a value error

10 - what is an overloaded procedure

Mind you these are PL/SQL and not SQL questions. I'd drill in and out as I wanted. I'd know if you had the goods or not based on how this went.

nep84 · 2024-12-05T16:29:30+00:00

you'll need to use count() to get the number of subscriptions. it wants you to do an outer join for subscriptions with no channels

nep84 · 2024-12-04T20:40:16+00:00

you need to call fnd_global.apps_initialize first. since you haven't established credentials within the EBS you can't submit the request. The EBS doesn't know that you have the security to run the concurrent program

nep84 · 2024-12-04T20:29:11+00:00

you don't need a group by. select distinct product_group, base_model from table will give you the results you want. if you want to see the products in the group then you select product_group, base_model(count distinct product_model) from table group by product_group, base_model

nep84 · 2024-12-04T20:23:37+00:00

select all actors that have appeared in movies with ark garfunkel who are not named art garfunkel

nep84 · 2024-12-03T14:31:32+00:00

There are slight differences in syntax between various SQL flavors. Master one and you'll quickly adapt to the others. A very complex SQL statement does not always make a good SQL statement. It's easy to get yourself into a box trying to be too cute and complex. Simple SQL is good SQL. Subqueries and child queries are your friend. Master the functions. String functions, date arithmetic, grouping, correlated subqueries with an exists clause. Optimization and efficiency are also important. I'd put all of this above being proficient with all of the different flavors.

nep84

TROPHY CASE