Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Error when in Select Statement

Author  Topic 

ryoka12
Starting Member

9 Posts

Posted - 2014-12-16 : 06:18:42
Hi
can anyone help with the error i received.
"subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

thanks for the help.

Using my below query in T-sql

All with the comment is where i got my error.

select (select ifm_port from OneOpus) as CustomOfficeCode
,(select IFM_REGNO from OneOpus) as Regno
,IFM_BL as BillofLadingNo
,'0' as BLSublineNo
,'4' as BLStatus
,'HBL' as BLType
,IFM_NATCOD as BLNatureCode
,IFM_SHIPR as ExporterName
,IFM_SADD1 as ExporterAdd1
,IFM_SADD2 as ExporterAdd2
,IFM_SADD3 as ExporterAdd3
,IFM_SADD4 as ExporterAdd4
,IFM_CNSGN as ConsigneeName
,IFM_ADD1 as ConsigneeName1
,IFM_ADD2 as ConsigneeName2
,IFM_ADD3 as ConsigneeName3
,IFM_ADD4 as ConsigneeName4
,IFM_NOTIFY as NotifyName
,IFM_NADD1 as NotifyAdd1
,IFM_NADD2 as NotifyAdd2
,IFM_NADD3 as NotifyAdd3
,IFM_NADD4 as NotifyAdd4
,IFM_ORG as PlaceofDeparture
,IFM_DES as PlacceofDestination
,IFM_NOCNTR as NumberofContainers
,'NE' as PackagingCode
--,(select IFM_NOPCKG from FiveOpus) as NumberofPackges
,IFM_WEIGHT as GrossMass
,(select IFM_DESC from SixOpus) as ShippingMarks1
,(select IFM_DESC from SixOpus) as ShippingMarks2
,(select IFM_DESC from SixOpus) as ShippingMarks3
,(select IFM_DESC from SixOpus) as ShippingMarks4
,(select IFM_DESC from SixOpus) as ShippingMarks5
,(select IFM_DESC from SixOpus) as ShippingMarks6
,(select IFM_DESC from SixOpus) as ShippingMarks7
,(select IFM_DESC from SixOpus) as ShippingMarks8
,(select IFM_DESC from SixOpus) as ShippingMarks9
,(select IFM_DESC from SixOpus) as ShippingMarks0
--,(select IFM_DESC from FiveOpus) as GoodsDescription1
--,(select IFM_DESC from FiveOpus) as GoodsDescription2
--,(select IFM_DESC from FiveOpus) as GoodsDescription3
--,(select IFM_DESC from FiveOpus) as GoodsDescription4
--,(select IFM_DESC from FiveOpus) as GoodsDescription5
,(select IFM_SMODE From FourOpus) as DelivveryMode
from TwoOpus


--select * from FourOpus

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-16 : 12:29:17
It's because it returns more than one row. Is there a column in common between the two tables, so that you could join to it instead?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

ryoka12
Starting Member

9 Posts

Posted - 2014-12-17 : 01:26:41
Hi Thanks for the the prompt reply tkizer.

Yes IFM_REGNO for both table can you advice me how can i use the join statement.

Thanks.
Go to Top of Page

ryoka12
Starting Member

9 Posts

Posted - 2014-12-17 : 01:44:56
i have edited the column NumberofPackges to
(select IFM_NOPCKG from FiveOpus F inner join TwoOpus on f.IFM_REGNO=TwoOpus.IFM_REGNO group by f.IFM_NOPCKG) as NumberofPackges
but in GoodsDescription1 i have use below code but same error appear.
(select IFM_DESC from FiveOpus F inner join TwoOpus on f.IFM_REGNO=TwoOpus.IFM_REGNO group by f.IFM_DESC) as GoodsDescription1
Go to Top of Page

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-12-17 : 04:46:04
assuming IFM_REGNO is common in all the tables,you may try this query
select op.ifm_port as CustomOfficeCode
, op.IFM_REGNO Regno
,tp.IFM_BL as BillofLadingNo
,'0' as BLSublineNo
,'4' as BLStatus
,'HBL' as BLType
,tp.IFM_NATCOD as BLNatureCode
,tp.IFM_SHIPR as ExporterName
,tp.IFM_SADD1 as ExporterAdd1
,tp.IFM_SADD2 as ExporterAdd2
,tp.IFM_SADD3 as ExporterAdd3
,tp.IFM_SADD4 as ExporterAdd4
,tp.IFM_CNSGN as ConsigneeName
,tp.IFM_ADD1 as ConsigneeName1
,tp.IFM_ADD2 as ConsigneeName2
,tp.IFM_ADD3 as ConsigneeName3
,tp.IFM_ADD4 as ConsigneeName4
,tp.IFM_NOTIFY as NotifyName
,tp.IFM_NADD1 as NotifyAdd1
,tp.IFM_NADD2 as NotifyAdd2
,tp.IFM_NADD3 as NotifyAdd3
,tp.IFM_NADD4 as NotifyAdd4
,tp.IFM_ORG as PlaceofDeparture
,tp.IFM_DES as PlacceofDestination
,tp.IFM_NOCNTR as NumberofContainers
,'NE' as PackagingCode
, fp.IFM_NOPCKG as NumberofPackges
,tp.IFM_WEIGHT as GrossMass
,sp.IFM_DESC as ShippingMarks1
,sp.IFM_DESC as ShippingMarks2
,sp.IFM_DESC as ShippingMarks3
,sp.IFM_DESC as ShippingMarks4
,sp.IFM_DESC as ShippingMarks5
,sp.IFM_DESC as ShippingMarks6
,sp.IFM_DESC as ShippingMarks7
,sp.IFM_DESC as ShippingMarks8
,sp.IFM_DESC as ShippingMarks9
,sp.IFM_DESC as ShippingMarks0
, fp.IFM_DESC as GoodsDescription1
, fp.IFM_DESC as GoodsDescription2
, fp.IFM_DESC as GoodsDescription3
, fp.IFM_DESC as GoodsDescription4
, fp.IFM_DESC as GoodsDescription5
, fo.IFM_SMODE as DelivveryMode
from OneOpus op
inner join TwoOpus tp
on op.IFM_REGNO=tp.IFM_REGNO
inner join FourOpus fo
on fo.IFM_REGNO=tp.IFM_REGNO
inner join FiveOpus fp
on tp.IFM_REGNO=fp.IFM_REGNO
inner join SixOpus sp
on sp.IFM_REGNO=fp.IFM_REGNO

Javeed Ahmed
https://www.linkedin.com/pub/javeed-ahmed/25/5b/95
Go to Top of Page
   

- Advertisement -