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