The multi-part identifier L.REAS_CD could not be b

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)

The multi-part identifier L.REAS_CD could not be b

Author

Topic

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-11-05 : 20:06:01

Thanks

 declare       @PeriodType      varchar(255),
        @SpecificPeriod        varchar(255),
		@RegID               varchar(50)

begin
   	declare @@rc int                   /* return code for called stored procedures */
	declare @sqlRC int
	declare @errMsg varchar(255)
	declare @qry varchar(1000)
declare @RegIDTemp varchar(50)
declare @primSort varchar(255)
declare @ascDesc varchar(50)
declare @groupBy varchar(255)
declare @filterBy varchar(255)
declare @period varchar(10)
declare @BeginDate varchar(50)
declare @EndDate varchar(50)

	set nocount on
	set arithabort off
	set arithignore on

create table #TmpJnlData(
        EMP_ID					varchar(50),
        LAST_NAME				varchar(50),
        REASON_TYPE             varchar(50),
        SYSTEM_TOTAL            varchar(50),
		TENDER_TOTAL			varchar(50),
		DISCREPENCY				varchar(50),
		REASON					varchar(50),
		TENDER_TYPE				varchar(50)
)
select @RegID='all'
select @RegID = upper(@RegID)
select @RegIDTemp = @RegID
select @sqlRC = @@error


/********* get period from the period function based on date **********************/

set @PeriodType = 'Today'
declare @tempdate varchar(50)

   if @PeriodType = 'Today' 
	BEGIN
	  select @tempdate = CONVERT(CHAR(9), GETDATE(), 6)
	  select @BeginDate = STRT_DT from GetBBYPeriod(@tempdate, 7)
	  select @EndDate = END_DT from GetBBYPeriod(@tempdate, 7)
	END
   
/********* query the database and populate the report data table ******************/

	  select @tempdate = CONVERT(CHAR(9), GETDATE(), 6)
	  select @BeginDate = STRT_DT from GetBBYPeriod(@tempdate, 7)
	  select @EndDate = END_DT from GetBBYPeriod(@tempdate, 7)

if @RegIDTemp = 'ALL'
BEGIN
 insert #TmpJnlData
select  (select top 1 EMP_ID from LOAN_PICKUP L where T.TILL_ID = L.TILL_ID) as EMP_ID, 
(select top 1 LST_NM from LOAN_PICKUP L left join EMPLOYEE E on E.EMP_ID = L.EMP_ID where T.TILL_ID = L.TILL_ID) as LAST_NAME,
REASON_TYPE= CASE L.REAS_CD
WHEN '14' THEN 'F'
WHEN '27' THEN 'F'
WHEN '11' THEN 'S'
WHEN '24' THEN 'S'
END, 
T.AMT as SYSTEM_TOTAL,
(select sum(L.AMT) from LOAN_PICKUP L where T.TILL_ID = L.TILL_ID) as TENDER_TOTAL, 
(T.AMT - (select sum(L.AMT) from LOAN_PICKUP L where T.TILL_ID = L.TILL_ID)) as DISCREPENCY, ' ' as REASON , 
(select top 1 TT.TND_DESCR  from TENDER TT where T.TND_CD = TT.TND_CD) as TENDER_TYPE  from TENDER_ON_HAND T 

END
     else
BEGIN
insert #TmpJnlData
select  (select top 1 EMP_ID from LOAN_PICKUP L where T.TILL_ID = L.TILL_ID and EMP_ID=@RegIDTemp) as EMP_ID, 
(select top 1 LST_NM from LOAN_PICKUP L left join EMPLOYEE E on E.EMP_ID = L.EMP_ID where T.TILL_ID = L.TILL_ID) as LAST_NAME,
REASON_TYPE= CASE L.REAS_CD
WHEN '14' THEN 'F'
WHEN '27' THEN 'F'
WHEN '11' THEN 'S'
WHEN '24' THEN 'S'
END, 
T.AMT as SYSTEM_TOTAL,
(select sum(L.AMT) from LOAN_PICKUP L where T.TILL_ID = L.TILL_ID) as TENDER_TOTAL, 
(T.AMT - (select sum(L.AMT) from LOAN_PICKUP L where T.TILL_ID = L.TILL_ID)) as DISCREPENCY, ' ' as REASON , 
(select top 1 TT.TND_DESCR  from TENDER TT where T.TND_CD = TT.TND_CD) as TENDER_TYPE  from TENDER_ON_HAND T 

END
end
select * from #TmpJnlData

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-11-05 : 20:08:08

What is your question?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-11-05 : 20:13:17

The multi-part identifier L.REAS_CD could not be bound.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-05 : 22:50:53

can you explain what you're trying to achieve in above query with help of some sample data?

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-11-06 : 08:59:49

It may look like

EMP_ID	Name	 	REASON_TYPE	System Total	Transfer Total	Discrepancy	Reason
							
123456	Jack		F	$2,537.90	-$2,537.55	$0.35	Unexplained Shortage
456789	Jill			$250.00	$0.00	$250.00	
789123	Mark		S	$1,790.47	-$1,200.00	$590.47	
234567	Scott		F	$0.00	-$145.70	-$145.70	Offset with Ruth
345678	Carry		F	$498.62	-$398.62	$100.00	Took G/C as cash
456789	Cindy		F	$56.00	-$56.00	$0.00	
135790	Amy		S	$1,590.42	-$1,500.00	$90.42	
246810	Ruth			$145.70	$0.00	$145.70

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-06 : 09:09:49

see below to understand how to post data

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-11-06 : 09:27:48

[code]EMP_ID Name REASON System Transfer Discrepancy
123456 Jack F $2,537.90 -$2,537.55 $0.35
456789 Jill $250.00 $0.00 $250.00
789123 Mark S $1,790.47 -$1,200.00 590.47[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-06 : 09:35:46

what represents sample data and what output you want.please state clearly

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-11-06 : 09:40:20

The above is the output data.
Sample data comes from differnt tables which are hard to present them herein.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-06 : 09:42:21

then atleast explian how you think you get this output from your tables.

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-11-06 : 10:21:43

Well, I think data source is not a key issue.

Perhaps wrong here.

REASON_TYPE= CASE L.REAS_CD
WHEN '14' THEN 'F'
WHEN '27' THEN 'F'
WHEN '11' THEN 'S'
WHEN '24' THEN 'S'
END,

The multi-part identifier L.REAS_CD could not be bound.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-06 : 10:28:06

you're selecting from TENDER_ON_HAND T so cant use field L.REAS_CD which is in another table and is available only inside subquery. Actually i really feel your query can written much better, but without enough info I dont think i can. that why i asked for sample data and output.


select  (select top 1 EMP_ID from LOAN_PICKUP L where T.TILL_ID = L.TILL_ID and EMP_ID=@RegIDTemp) as EMP_ID, 
(select top 1 LST_NM from LOAN_PICKUP L left join EMPLOYEE E on E.EMP_ID = L.EMP_ID where T.TILL_ID = L.TILL_ID) as LAST_NAME,
REASON_TYPE= CASE L.REAS_CD
WHEN '14' THEN 'F'
WHEN '27' THEN 'F'
WHEN '11' THEN 'S'
WHEN '24' THEN 'S'
END, 
T.AMT as SYSTEM_TOTAL,
(select sum(L.AMT) from LOAN_PICKUP L where T.TILL_ID = L.TILL_ID) as TENDER_TOTAL, 
(T.AMT - (select sum(L.AMT) from LOAN_PICKUP L where T.TILL_ID = L.TILL_ID)) as DISCREPENCY, ' ' as REASON , 
(select top 1 TT.TND_DESCR  from TENDER TT where T.TND_CD = TT.TND_CD) as TENDER_TYPE  from TENDER_ON_HAND T

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-11-06 : 10:43:21

Yes. You are right.
I should add something.

(SELECT L.REAS_CD CASE
WHEN '14' THEN 'F'
WHEN '27' THEN 'F'
WHEN '11' THEN 'S'
WHEN '24' THEN 'S'
END  FROM LOAN_PICKUP L) AS REASON_TYPE

Correct or wrong?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources