Error when in Select Statement

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/

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.

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources