Home » SQL & PL/SQL » SQL & PL/SQL » small problem with this query
small problem with this query [message #303529] Fri, 29 February 2008 13:25 Go to next message
skumar.dba
Messages: 82
Registered: October 2007
Member
Hi experts,

i had one problem with this query. can any one help to fix this problem.
SELECT 
--MIC.INVENTORY_ITEM_ID "INVENTORY_ITEM_ID",
SUBSTR( MSB.DESCRIPTION ,1,30)/* EXP_MTL_SYSTEM_B.OUTGRP1.ITEM_DESC */ "DESCRIPTION",
MSB.ORGANIZATION_ID "ORGANIZATION_ID",
MSB.ITEM_TYPE "ITEM_TYPE",
MSB.SALES_ACCOUNT "SALES_ACCOUNT",
MSB.EXPENSE_ACCOUNT "EXPENSE_ACCOUNT",
SUBSTR( MSB.ATTRIBUTE4,1,30)/* EXP_MTL_SYSTEM_B.OUTGRP1.ADDITIONAL_DESC */ "ADDITIONAL_DESC",
--SUBSTR( MST.LONG_DESCRIPTION,1,255)/* EXP_MTL_SYSTEM_ITEMS_TL.OUTGRP1.LONG_DESC */ "LONG_DESC",
SUBSTR( MSB.ATTRIBUTE1,1,1)/* EXP_MTL_SYSTEM_B.OUTGRP1.SUSPECT_ITEM_FLAG */ "SUSPECT_ITEM_FLAG",
SUBSTR( MSB.ATTRIBUTE2,1,30)/* EXP_MTL_SYSTEM_B.OUTGRP1.TRAFFIC_CODE */ "TRAFFIC_CODE",
SUBSTR( MSB.ATTRIBUTE3,1,30)/* EXP_MTL_SYSTEM_B.OUTGRP1.PICKING_ATTRIBUTE */ "PICKING_ATTRIBUTE",
SUBSTR( MSB.ATTRIBUTE5,1,30)/* EXP_MTL_SYSTEM_B.OUTGRP1.CONTINUITY_PROG_CODE */ "CONTINUITY_PROG_CODE",
SUBSTR( MSB.ATTRIBUTE6,1,30)/* EXP_MTL_SYSTEM_B.OUTGRP1.NOTE_TO_CSR */ "NOTE_TO_CSR",
SUBSTR( MSB.ATTRIBUTE7,1,1)/* EXP_MTL_SYSTEM_B.OUTGRP1.PRINT_PACK_SLIP_PACK */ "PRINT_PACK_SLIP_PACK",
SUBSTR( MSB.ATTRIBUTE8,1,1)/* EXP_MTL_SYSTEM_B.OUTGRP1.LOT_CONTROLED_FLAG */ "LOT_CONTROLED_FLAG",
SUBSTR( MSB.ATTRIBUTE9,1,30)/* EXP_MTL_SYSTEM_B.OUTGRP1.COUNTRY_OF_ORIGIN */ "COUNTRY_OF_ORIGIN",
SUBSTR( MSB.ATTRIBUTE11,1,30)/* EXP_MTL_SYSTEM_B.OUTGRP1.MDB_CALCLE_NOTE */ "MDB_CALCLE_NOTE",
MSB.PRIMARY_UOM_CODE "PRIMARY_UOM_CODE",
MSB.WEIGHT_UOM_CODE "WEIGHT_UOM_CODE",
MSB.UNIT_WEIGHT "UNIT_WEIGHT",
MSB.DIMENSION_UOM_CODE "DIMENSION_UOM_CODE",
MSB.UNIT_LENGTH "UNIT_LENGTH",
MSB.UNIT_HEIGHT "UNIT_HEIGHT",
MSB.UNIT_WIDTH "UNIT_WIDTH",
MSB.INVENTORY_ITEM_FLAG "INVENTORY_ITEM_FLAG",
MSB.PURCHASING_ITEM_FLAG "PURCHASING_ITEM_FLAG",
MSB.OUTSIDE_OPERATION_FLAG "OUTSIDE_OPERATION_FLAG",
MSB.TAXABLE_FLAG "TAXABLE_FLAG",
MSB.HAZARD_CLASS_ID "HAZARD_CLASS_ID",
MSB.PREPROCESSING_LEAD_TIME "PREPROCESSING_LEAD_TIME",
MSB.POSTPROCESSING_LEAD_TIME "POSTPROCESSING_LEAD_TIME",
MSB.BUILD_IN_WIP_FLAG "BUILD_IN_WIP_FLAG",
MSB.WIP_SUPPLY_TYPE "WIP_SUPPLY_TYPE",
MSB.WIP_SUPPLY_SUBINVENTORY "WIP_SUPPLY_SUBINVENTORY",
MSB.CUSTOMER_ORDER_FLAG "CUSTOMER_ORDER_FLAG",
MSB.CUSTOMER_ORDER_ENABLED_FLAG "CUSTOMER_ORDER_ENABLED_FLAG",
MSB.SHIPPABLE_ITEM_FLAG "SHIPPABLE_ITEM_FLAG",
MSB.PICK_COMPONENTS_FLAG "PICK_COMPONENTS_FLAG",
MSB.RETURNABLE_FLAG "RETURNABLE_FLAG",
MSB.INVOICEABLE_ITEM_FLAG "INVOICEABLE_ITEM_FLAG",
MSB.INVOICE_ENABLED_FLAG "INVOICE_ENABLED_FLAG",
(select mcb.segment1 "categroy item type" from MTL_CATEGORIES_B MCB where mcb.structure_id = 101 )

FROM
MTL_SYSTEM_ITEMS_B MSB,
MTL_CATEGORIES_B MCB,
MTL_SYSTEM_ITEMS_TL MST,
MTL_ITEM_CATEGORIES MIC

WHERE
MCB.CATEGORY_ID = MIC.CATEGORY_ID
AND
MST.INVENTORY_ITEM_ID = MSB.INVENTORY_ITEM_ID
AND
MST.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
AND
MSB.LAST_UPDATE_DATE > MSB.LAST_UPDATE_DATE - 1


in this query i need to select segment1 with structure id =101
along with other columns.
i am trying to write subquery it is returning 57 rows with structure id 101

how do i write this query

regards,
kumar
Re: small problem with this query [message #303533 is a reply to message #303529] Fri, 29 February 2008 13:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you want 1 row or 57 rows?

Regards
Michel
Re: small problem with this query [message #303537 is a reply to message #303533] Fri, 29 February 2008 13:39 Go to previous messageGo to next message
skumar.dba
Messages: 82
Registered: October 2007
Member
Michel,

thanks for your reply

all the rows from that table. structure id has 5 distinct values.
any way i need all the rows effected by where condition (sturcture id =101)

[Updated on: Fri, 29 February 2008 13:41]

Report message to a moderator

Re: small problem with this query [message #303540 is a reply to message #303537] Fri, 29 February 2008 13:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
All rows in one row or all rows with other columns duplicated?

Regards
Michel
Re: small problem with this query [message #303547 is a reply to message #303540] Fri, 29 February 2008 13:57 Go to previous messageGo to next message
skumar.dba
Messages: 82
Registered: October 2007
Member
all the rows in one row
Re: small problem with this query [message #303551 is a reply to message #303547] Fri, 29 February 2008 14:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can either use CURSOR(select ...) and handle the cursor in your program, or use a function like stragg or concat_all to concat all values from the 57 rows.

Regards
Michel
Re: small problem with this query [message #303558 is a reply to message #303551] Fri, 29 February 2008 14:35 Go to previous messageGo to next message
skumar.dba
Messages: 82
Registered: October 2007
Member
what i have to do if i want to have duplicated values.(rows)
Re: small problem with this query [message #303562 is a reply to message #303558] Fri, 29 February 2008 15:15 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Put the view in the from clause.

Regards
Michel
Previous Topic: eliminate duplicates in a table
Next Topic: How to interpret explain plan
Goto Forum:
  


Current Time: Fri Dec 02 20:58:18 CST 2016

Total time taken to generate the page: 0.05828 seconds