How to use an expression in a join between two tables?

by 
SELECT L.*, T.*
  FROM (SELECT Supplier_Code,
               Local_Commodity_Code,
               SUBSTR(LOCAL_COMMODITY_CODE, 1, INSTR(LOCAL_COMMODITY_CODE,'~')-1)
                      LOCAL_COM_CODE
          FROM OYSTER_WEB3.TRANSACTION
       ) T
  JOIN Local_Feed_Commodity_Map L
    ON L.Local_Commodity_Code = T.Local_Com_Code

Oracle has an aversion to the SQL standard ‘AS’ keyword in some locations, so I’ve not used it anywhere to maximize the chances of the code working.

However, as I noted in a comment to the question, this is an appalling piece of schema design and should be fixed. It is ludicrous to pessimize all queries that have to work between these two tables by requiring the use of SUBSTR and INSTR like that. The Local_Commodity_Code in the Transaction table should be identical to the Local_Commodity_Code in the Local_Feed_Commodity_Map table so that both the primary key and the foreign key columns can be properly indexed (and referential integrity enforced).

More Related Contents:

  1. SQL function PIVOT with 2 column
  2. How to load a large number of strings to match with oracle database?
  3. How can I find which tables reference a given table in Oracle SQL Developer?
  4. Oracle SQL escape character (for a ‘&’)
  5. How to extract week number in sql
  6. How to use Timestamp_to_scn and Scn_to_timestamp in Oracle?
  7. Exporting a CLOB to a text file using Oracle SQL Developer
  8. COUNT MANY FIELDS IN ACCESS [closed]
  9. Max sal to print base on manager_id
  10. Managing and debugging SQL queries in MS Access
  11. Inserting multiple rows in a single SQL query? [duplicate]
  12. How do I query using fields inside the new PostgreSQL JSON datatype?
  13. How to combine date from one field with time from another field – MS SQL Server
  14. STRING_SPLIT in SQL Server 2012
  15. count(*) vs count(column-name) – which is more correct? [duplicate]
  16. How to store a list in a column of a database table
  17. Left Outer Join using + sign in Oracle 11g
  18. Return multiple fields as a record in PostgreSQL with PL/pgSQL
  19. How to deal with concurrent updates in databases?
  20. MySQL: What is a reverse version of LIKE?
  21. Facebook database design?
  22. Sequence vs identity
  23. What is the difference between Views and Materialized Views in Oracle?
  24. The backend version is not supported to design database diagrams or tables
  25. Cross Join without duplicate combinations
  26. How do I add a auto_increment primary key in SQL Server database?
  27. SQL: Alias Column Name for Use in CASE Statement
  28. How can I find Unicode/non-ASCII characters in an NTEXT field in a SQL Server 2005 table?
  29. Date serial in SQL?
  30. Parameterise table name in .NET/SQL?

Leave a Comment