Only One Record

[lrod]

I need some help maybe somebody can help me with this. I'm tyring to run this query, however the out result is only one Record but if i run the select statement without declare I get a bunch, can someone see what i'm doing wrong please. Thanks in advanced.
LROD

declare @quotedate 	                datetime
declare @AccName 	                varchar(80)
declare @AccRept 	                varchar(80)
declare @ContractAMT	varchar(20) 
declare @QuotedRate	varchar(20) 
declare @Booked_YN	varchar(4)
declare @Status		varchar(50)
declare @Booked_DT 	datetime


select 
@quotedate	= QD.QuoteDate,
@AccName	= ac2.contact_nm, 	
@AccRept	                = ac1.contact_nm,	
@ContractAMT	= OD1.AMOUNT_NR ,	
@QuotedRate	= CASE  WHEN OD2.AMOUNT_NR <0 THEN (od1.amount_nr - (OD2.AMOUNT_NR *-1) ) END,
@Booked_YN	= case when o.order_status_cd in (995,994) then 'N'
		           when o.order_status_cd in (997,996) then 'Y' end,  
@Status		= o.order_status_cd,
@Booked_DT	= BD.BookedDate

from t_order o
left outer join (
		SELECT ORDER_ID,SUM(AMOUNT_NR)AS 'AMOUNT_NR'
		FROM t_detail 
		WHERE detail_type_cd <> 600
		GROUP BY ORDER_ID
		)OD1 ON O.ORDER_ID = OD1.ORDER_ID

left outer join (
		select order_id,new_value_ds, min(updated_dt)'BookedDate'
		from t_history 
		where field_nm = 'Status_CD'and new_value_ds = '500'
		group by order_id,new_value_ds
		)BD on o.order_id = BD.order_id

left outer join (
		select min(updated_dt)'QuoteDate'
		from t_history 
		)QD on o.order_id = QD.order_id


left outer join t_detail od2 on o.order_id = od2.order_id and od2.order_detail_type_cd =582 	 	  
left outer join t_contact oc1 (nolock)on o.order_id = oc1.order_id and oc1.contact_role_type_cd=300  
left outer join t_contact1 ac1 (nolock)on oc1.contact_id = ac1.contact_id
left outer join t_contact2 oc2 (nolock)on o.order_id = oc2.order_id and oc2.contact_role_type_cd=220 
left outer join t_contact3 ac2 (nolock)on oc2.contact_id = ac2.contact_id

where 
o.order_cd = 1111

and QD.QuoteDate >= '3/23/09' 
and QD.QuoteDate <= '3/24/09' 
order by o.order_id desc



select 
@quotedate	'QuoteDate', 
@AccName	'AccountName', 
@AccRept	                'AccountRept', 
@ContractAMT	'Contract_AMT', 
@QuotedRate 	'Quoted Rated', 
@Booked_YN	'Booked Y/N', 
@Status		'order_status_cd', 
@Booked_DT	'BookedDate'

[ck9663]

Because your query will run and will store the value of the last record to your variable.

Read more info from here


-- CK

[lrod]

i'm a newbie at this CK, I really not sure what should i do, do you think i should use SET on my variables then...can you help me out? Thanks in advanced

[ck9663]

Of you need the process each record you might need to use CURSOR. Depends actually on what you're trying to do.


-- CK

[lrod]

Well here is the whole code. If i run this code it only gives me one record during the time range, however if i run the main sql query i get about 90 records and that's what i should get when i run the whole code, same quantity.

declare @string 	varchar(500)
declare @quotedate 	datetime --varchar(100)
declare @AccName 	varchar(80)
declare @AccRept 	varchar(80)
declare @AMT	        varchar(20) 
declare @QuotedRate	varchar(20) 
declare @Booked_YN	varchar(4)
declare @Status		varchar(50)
declare @Booked_DT	datetime --varchar(100)

set @string = 'ds'

-----------------------------------------------START MAIN QUERY--------------------------------------
select 

@string		= o.ds,
@quotedate	= QD.QuoteDate,
@AccName	= ac2.contact_nm, 	 
@AccRept	= ac1.contact_nm, 
@AMT		= OD1.AMOUNT_NR, 
@QuotedRate 	= CASE  WHEN OD2.AMOUNT_NR <0 THEN (od1.amount_nr - (OD2.AMOUNT_NR *-1) ) END, 
@Booked_YN	= case 	when o.status_cd in (100,200,300,400,500)then 'N'
		        when o.status_cd in (110,210,310,410,510,610,710,810,910,920) then 'Y' end , 
@Status		= o.status_cd,
@Booked_DT	= BD.BookedDate

from t_order o
left outer join (
		SELECT ID,SUM(AMOUNT_NR)AS 'AMOUNT_NR'
		FROM t_detail 
		WHERE detail_type_cd <> 980
		GROUP BY ID
		)OD1 ON O.ID = OD1.ID

left outer join (
		select id,new_value_ds, min(updated_dt)'BookedDate'
		from t_history 
		where field_nm = 'Status_CD'and value_ds = '810'
		group by id,_value_ds
		)BD on o.id = BD.id

left outer join (
		select id,min(updated_dt)'QuoteDate'
		from t_history 
		group by id
		)QD on o.id = QD.id

left outer join t_detail od2 on o.id = od2.id and od2.detail_type_cd =180 	 	  

left outer join t_contact oc1 (nolock)on o.id = oc1.id and oc1.contact_type_cd=291      
left outer join t_contact1 ac1 (nolock)on oc1.contact_id = ac1.contact_id

left outer join t_contact oc2 (nolock)on o.id = oc2.id and oc2.contact_type_cd=292 	 
left outer join t_contact1 ac2 (nolock)on oc2.id = ac2.id

where 
QD.QuoteDate >= '3/23/09' and 
QD.QuoteDate <= '3/24/09' 

-----------------------------------------------END MAIN QUERY--------------------------------------


declare @pos int
declare @piece varchar(500)
declare @variableCount int
declare @var1 varchar(25),@var2 varchar(25), @var3 varchar(25), @var4 varchar(25)
,@var5 varchar(25), @var6 varchar(25)

set @variableCount = 1

if right(rtrim(@string),1) <> '/'
 set @string = @string  + '/'
set @pos =  patindex('%/%' , @string)
while @pos <> 0 
begin
 set @piece = left(@string, @pos - 1)
 

 if @variableCount = 1 
 begin
 set @var1=@piece
 end
 else if @variableCount = 2
 begin
 set @var2=@piece
 end
 else if @variableCount = 3
 begin
 set @var3=@piece
 end
 else if @variableCount = 4
 begin
 set @var4=@piece
 end
 else if @variableCount = 5
 begin
 set @var5=@piece
 end
 else if @variableCount = 6
 begin
 set @var6=@piece
 end

 set @string = stuff(@string, 1, @pos, '')
 set @pos =  patindex('%/%' , @string)
 set @variableCount = @variableCount + 1
end


print @quotedate  
print @AccName 
print @AccRept 
print @AMT
print @QuotedRate
print @Booked_YN
print @Status	
print @Booked_DT
print @var1
print @var2
print @var3
print @var4
print @var5
print @var6

select
@quotedate   'QuoteDate'
,@AccName    'AccountName'
,@AccRept    'AccountRept'
,@AMT        'AMT'
,@QuotedRate 'QuotedRated'
,@Booked_YN  'Booked_YN'
,@Status     'order_status_cd'
,@Booked_DT  'BookedDate'
,@var1       'OrigZip'
,@var2       'OrigST'
,@var3       'OrigCity'
,@var4       'DestZip'
,@var5       'DestState'
,@var6       'DestCity'

[ck9663]

I'm not sure why you need to print them or display it. Looks like CURSOR is your option. If you need this to be displayed in your GUI side, you might just want to control the returned result as result set.

--- CK

[lrod]

Thank you CK, I'll look into it and let you know what happened.

LROD