0 
select 
    itoh.header_number Transfer_Order, 
    itol.SHIPPED_QTY , 
    itol.REQUESTED_QTY, 
    itol.RECEIVED_QTY,
    itol.DELIVERED_QTY,
    itol.SOURCE_SUBINVENTORY_CODE Transfer_subiinventory 
    (select DISTINCT (esi.ITEM_NUMBER and esi.item_type)
     from egp_system_items_b esi 
     where INVENTORY_ITEM_ID = itol.INVENTORY_ITEM_ID) 
From 
    inv_transfer_order_headers itoh, inv_transfer_order_lines itol 
where 
    itoh.HEADER_ID = itol.HEADER_ID
Improve this question
6
  • and what's your question? Commented May 25, 2021 at 20:03
  • What do you expect select DISTINCT ( esi.ITEM_NUMBER and esi.item_type) to do? A subquery in the select list can only return a single value (since it only produces a single column in the result set). Perhaps you want to do a string concatenation of the two columns (select distinct esi.item_number || esi.item_type)? But that's a wild guess without a more detailed description of the problem. Commented May 25, 2021 at 20:14
  • Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (almost 30 years ago) and its use is discouraged Commented May 25, 2021 at 20:14
  • How I can use the two columns In subquery what is the exact solution. Will anyone help me sir with using of the code ,if any one can means please correct my code Commented May 25, 2021 at 20:20
  • You can't; subquery can return only one value. Why didn't you JOIN that table (EGP_SYSTEM_ITEMS_B) with others? If you insist on subquery, then create two of them, one for each column. Commented May 25, 2021 at 20:35

2 Answers 2

Reset to default
1

It is always helpful to provide some sort of DDL and sample data so that the problem can be demo'ed. It's also useful to provide your full version number, some features are only available in recent versions, I will assume you are using at least version 12.2 (as that is the current latest supported version)

This is only necessary if you do not want to distinct against all of the columns in your result set. You can use a lateral join to join to a correlated subquery:

select 
    itoh.header_number Transfer_Order, 
    itol.SHIPPED_QTY , 
    itol.REQUESTED_QTY, 
    itol.RECEIVED_QTY,
    itol.DELIVERED_QTY,
    itol.SOURCE_SUBINVENTORY_CODE Transfer_subiinventory,
    esi_subq.ITEM_NUMBER,
    esi_subq.item_type
From 
    inv_transfer_order_headers itoh, inv_transfer_order_lines itol 
  , lateral
    (select DISTINCT esi.ITEM_NUMBER , esi.item_type
     from egp_system_items_b esi 
     where INVENTORY_ITEM_ID = itol.INVENTORY_ITEM_ID
    ) esi_subq
where 
    itoh.HEADER_ID = itol.HEADER_ID

I've made a small dbfiddle to demo

You are also free to use the newer join syntax but you'll need to use different keywords.

select 
    itoh.header_number Transfer_Order, 
    itol.SHIPPED_QTY , 
    itol.REQUESTED_QTY, 
    itol.RECEIVED_QTY,
    itol.DELIVERED_QTY,
    itol.SOURCE_SUBINVENTORY_CODE Transfer_subiinventory,
    esi_subq.ITEM_NUMBER,
    esi_subq.item_type
From 
     inv_transfer_order_headers itoh
join inv_transfer_order_lines itol 
  on itoh.HEADER_ID = itol.HEADER_ID
outer apply 
    (select DISTINCT esi.ITEM_NUMBER , esi.item_type
     from egp_system_items_b esi 
     where INVENTORY_ITEM_ID = itol.INVENTORY_ITEM_ID
    ) esi_subq
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

for a start :

SQL Queries and Subqueries

Using Subqueries

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.