is there any way to write Oracle Traditional JOIN query instead of INNER JOIN?

Question

I have used below INNER JOIN Oracle query for join tables in NetSuite REST API. Right now, NetSuite REST API in beta version.

Here is my query, below query worked fine before ,but now below query does not work with INNER JOIN, so I wanna use traditional join query without using INNER JOIN. I know old traditional join query is outdated ,but I do not have any choice right now.

SELECT
Itemprice.price
as
actual_selling_price,Itemprice.pricelevelname,Item.pricinggroup,
Item.itemid
as
sku,Item.isserialitem,Item.ID
as
iteminternalid,Item.custitem_f3_category_type,Item.description,TransactionLine.Transaction
AS
TransactionID,Transaction.employee
as
salesrep,TransactionLine.custcol_f3_document_number
as
documentnumber,Transaction.TranDate,Transaction.custbody_es_customer_inv_number,Transaction.Type,
Transaction.TranID,Transaction.Entity
as
customer,TransactionLine.Rate,TransactionLine.quantity,
TransactionLine.NetAmount
FROM
TransactionLine
INNER JOIN
Transaction
ON
(
Transaction.ID
=
TransactionLine.Transaction
AND
Transaction.Entity=16249
AND
(transaction.recordtype='invoice' OR transaction.recordtype='cashsale')
AND
TransactionLine.memo!='CostofSales' AND TransactionLine.itemtype!='ShipItem')
INNER JOIN
Itemprice
ON(TransactionLine.Item
=
Itemprice.item
)
INNER JOIN
Item
ON
(
Item.ID
=
TransactionLine.Item
AND
(LOWER(Item.description)
LIKE
LOWER('%44753BLK%')
or
UPPER(Item.description)
LIKE
UPPER('%44753BLK%')
OR
Item.itemid
=
'44753BLK'
OR
Transaction.TranID
=
'44753BLK'
OR
Transaction.custbody_es_customer_inv_number
=
'44753BLK'))
ORDER
BY
trandate
DESC

I wanna convert above query into join query without using INNER JOIN,JOIN keywords. Is there other way to join tables without using INNER JOIN,JOIN keywords in oracle ?

Why do you want to convert the query? You can use the old-school join syntax but that is generally frowned upon because it makes queries harder to read. — Justin Cave, Apr 19 '21 at 20:30
@JustinCave Convert means I do not want to use INNER JOIN or JOIN keyword in oracle query,so without these keywords join query possible ? — Niraj Patel, Apr 19 '21 at 20:33
Inner join is just a filtering of cartesian product of relations, so you can enumerate them in `from` clause, separated by comma. But it is discouraged to write queries in this way. What is the reason to do this? — astentx, Apr 19 '21 at 20:44
What is the reasoning for wanting to remove the `JOIN`s? Is the query not returning correct results? If it is a performance issue, indexing could help but would not be a reason for removing joins. — EJ Egyed, Apr 19 '21 at 20:46
@astentx in netsuite REST API SuiteQL supports oracle query so I did inner join query but recently it throws error "invalid search query", before my posted INNER JOIN query was wroking fine when I debug my above query so I realised that this parameter TransactionLine.itemtype != 'ShipItem' arised issue If I remove this parameter then wokring fine but This parameter is mandatory according the client requirement so When I used below answer then it working fine. I think it recently arise bugs in netsuite SuiteQL api where TransactionLine.itemtype != 'ShipItem' parameter not work in inner join — Niraj Patel, Apr 20 '21 at 15:38
@EJEgyed Netsuite REST API SuiteQL supports oracle query so I did inner join query but recently it throws error "invalid search query", before my posted INNER JOIN query was wroking fine when I debug my above query so I realised that this parameter TransactionLine.itemtype != 'ShipItem' arised issue If I remove this parameter then wokring fine but This parameter is mandatory according the client requirement so When I used below answer then it working fine. I think it recently arise bugs in netsuite SuiteQL api where TransactionLine.itemtype != 'ShipItem' parameter not work in inner join — Niraj Patel, Apr 20 '21 at 15:39
Please clarify via edits, not comments. Please format code reasonably per its parse/syntax. Please research before considering posting a question. [ask] [Help] — philipxy, Dec 09 '21 at 21:27

score 1 · Accepted Answer · answered Apr 19 '21 at 21:27

You asked whether there's a way to do that. Yes, it is. Nowadays, there are rare occasions where you'd want to use such a syntax (for example, in older Oracle Reports Builder versions), but - if you insist, here's how:

SELECT itemprice.price                               AS actual_selling_price,
       itemprice.pricelevelname,
       item.pricinggroup,
       item.itemid                                   AS sku,
       item.isserialitem,
       item.id                                       AS iteminternalid,
       item.custitem_f3_category_type,
       item.description,
       transactionline.transaction                   AS transactionid,
       transaction.employee                          AS salesrep,
       transactionline.custcol_f3_document_number    AS documentnumber,
       transaction.trandate,
       transaction.custbody_es_customer_inv_number,
       transaction.type,
       transaction.tranid,
       transaction.entity                            AS customer,
       transactionline.rate,
       transactionline.quantity,
       transactionline.netamount
FROM transactionline,
     transaction,
     itemprice,
     item
WHERE transaction.id = transactionline.transaction
      AND transaction.entity = 16249
      AND(   transaction.recordtype = 'invoice'
          OR transaction.recordtype = 'cashsale'
         )
      AND transactionline.memo != 'Cost of Sales'
      AND transactionline.itemtype != 'ShipItem'
      AND transactionline.item = itemprice.item
      AND item.id = transactionline.item
      AND (   lower(item.description) LIKE lower('%44753BLK%')
           OR upper(item.description) LIKE upper('%44753BLK%')
           OR item.itemid = '44753BLK'
           OR transaction.tranid = '44753BLK'
           OR transaction.custbody_es_customer_inv_number = '44753BLK'
          )
ORDER BY trandate DESC;

is there any way to write Oracle Traditional JOIN query instead of INNER JOIN?

1 Answers1