By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,266 Members | 1,312 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,266 IT Pros & Developers. It's quick & easy.

Only One Record

P: 7
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. declare @quotedate                     datetime
  3. declare @AccName                     varchar(80)
  4. declare @AccRept                     varchar(80)
  5. declare @ContractAMT    varchar(20) 
  6. declare @QuotedRate    varchar(20) 
  7. declare @Booked_YN    varchar(4)
  8. declare @Status        varchar(50)
  9. declare @Booked_DT     datetime
  10.  
  11.  
  12. select 
  13. @quotedate    = QD.QuoteDate,
  14. @AccName    = ac2.contact_nm,     
  15. @AccRept                    = ac1.contact_nm,    
  16. @ContractAMT    = OD1.AMOUNT_NR ,    
  17. @QuotedRate    = CASE  WHEN OD2.AMOUNT_NR <0 THEN (od1.amount_nr - (OD2.AMOUNT_NR *-1) ) END,
  18. @Booked_YN    = case when o.order_status_cd in (995,994) then 'N'
  19.                    when o.order_status_cd in (997,996) then 'Y' end,  
  20. @Status        = o.order_status_cd,
  21. @Booked_DT    = BD.BookedDate
  22.  
  23. from t_order o
  24. left outer join (
  25.         SELECT ORDER_ID,SUM(AMOUNT_NR)AS 'AMOUNT_NR'
  26.         FROM t_detail 
  27.         WHERE detail_type_cd <> 600
  28.         GROUP BY ORDER_ID
  29.         )OD1 ON O.ORDER_ID = OD1.ORDER_ID
  30.  
  31. left outer join (
  32.         select order_id,new_value_ds, min(updated_dt)'BookedDate'
  33.         from t_history 
  34.         where field_nm = 'Status_CD'and new_value_ds = '500'
  35.         group by order_id,new_value_ds
  36.         )BD on o.order_id = BD.order_id
  37.  
  38. left outer join (
  39.         select min(updated_dt)'QuoteDate'
  40.         from t_history 
  41.         )QD on o.order_id = QD.order_id
  42.  
  43.  
  44. left outer join t_detail od2 on o.order_id = od2.order_id and od2.order_detail_type_cd =582            
  45. left outer join t_contact oc1 (nolock)on o.order_id = oc1.order_id and oc1.contact_role_type_cd=300  
  46. left outer join t_contact1 ac1 (nolock)on oc1.contact_id = ac1.contact_id
  47. left outer join t_contact2 oc2 (nolock)on o.order_id = oc2.order_id and oc2.contact_role_type_cd=220 
  48. left outer join t_contact3 ac2 (nolock)on oc2.contact_id = ac2.contact_id
  49.  
  50. where 
  51. o.order_cd = 1111
  52.  
  53. and QD.QuoteDate >= '3/23/09' 
  54. and QD.QuoteDate <= '3/24/09' 
  55. order by o.order_id desc
  56.  
  57.  
  58.  
  59. select 
  60. @quotedate    'QuoteDate', 
  61. @AccName    'AccountName', 
  62. @AccRept                    'AccountRept', 
  63. @ContractAMT    'Contract_AMT', 
  64. @QuotedRate     'Quoted Rated', 
  65. @Booked_YN    'Booked Y/N', 
  66. @Status        'order_status_cd', 
  67. @Booked_DT    'BookedDate'
  68.  
  69.  
Mar 25 '09 #1
Share this Question
Share on Google+
6 Replies


ck9663
Expert 2.5K+
P: 2,878
Because your query will run and will store the value of the last record to your variable.

Read more info from here


-- CK
Mar 25 '09 #2

P: 7
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
Mar 25 '09 #3

ck9663
Expert 2.5K+
P: 2,878
Of you need the process each record you might need to use CURSOR. Depends actually on what you're trying to do.


-- CK
Mar 26 '09 #4

P: 7
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. declare @string     varchar(500)
  3. declare @quotedate     datetime --varchar(100)
  4. declare @AccName     varchar(80)
  5. declare @AccRept     varchar(80)
  6. declare @AMT            varchar(20) 
  7. declare @QuotedRate    varchar(20) 
  8. declare @Booked_YN    varchar(4)
  9. declare @Status        varchar(50)
  10. declare @Booked_DT    datetime --varchar(100)
  11.  
  12. set @string = 'ds'
  13.  
  14. -----------------------------------------------START MAIN QUERY--------------------------------------
  15. select 
  16.  
  17. @string        = o.ds,
  18. @quotedate    = QD.QuoteDate,
  19. @AccName    = ac2.contact_nm,      
  20. @AccRept    = ac1.contact_nm, 
  21. @AMT        = OD1.AMOUNT_NR, 
  22. @QuotedRate     = CASE  WHEN OD2.AMOUNT_NR <0 THEN (od1.amount_nr - (OD2.AMOUNT_NR *-1) ) END, 
  23. @Booked_YN    = case     when o.status_cd in (100,200,300,400,500)then 'N'
  24.                 when o.status_cd in (110,210,310,410,510,610,710,810,910,920) then 'Y' end , 
  25. @Status        = o.status_cd,
  26. @Booked_DT    = BD.BookedDate
  27.  
  28. from t_order o
  29. left outer join (
  30.         SELECT ID,SUM(AMOUNT_NR)AS 'AMOUNT_NR'
  31.         FROM t_detail 
  32.         WHERE detail_type_cd <> 980
  33.         GROUP BY ID
  34.         )OD1 ON O.ID = OD1.ID
  35.  
  36. left outer join (
  37.         select id,new_value_ds, min(updated_dt)'BookedDate'
  38.         from t_history 
  39.         where field_nm = 'Status_CD'and value_ds = '810'
  40.         group by id,_value_ds
  41.         )BD on o.id = BD.id
  42.  
  43. left outer join (
  44.         select id,min(updated_dt)'QuoteDate'
  45.         from t_history 
  46.         group by id
  47.         )QD on o.id = QD.id
  48.  
  49. left outer join t_detail od2 on o.id = od2.id and od2.detail_type_cd =180            
  50.  
  51. left outer join t_contact oc1 (nolock)on o.id = oc1.id and oc1.contact_type_cd=291      
  52. left outer join t_contact1 ac1 (nolock)on oc1.contact_id = ac1.contact_id
  53.  
  54. left outer join t_contact oc2 (nolock)on o.id = oc2.id and oc2.contact_type_cd=292      
  55. left outer join t_contact1 ac2 (nolock)on oc2.id = ac2.id
  56.  
  57. where 
  58. QD.QuoteDate >= '3/23/09' and 
  59. QD.QuoteDate <= '3/24/09' 
  60.  
  61. -----------------------------------------------END MAIN QUERY--------------------------------------
  62.  
  63.  
  64. declare @pos int
  65. declare @piece varchar(500)
  66. declare @variableCount int
  67. declare @var1 varchar(25),@var2 varchar(25), @var3 varchar(25), @var4 varchar(25)
  68. ,@var5 varchar(25), @var6 varchar(25)
  69.  
  70. set @variableCount = 1
  71.  
  72. if right(rtrim(@string),1) <> '/'
  73.  set @string = @string  + '/'
  74. set @pos =  patindex('%/%' , @string)
  75. while @pos <> 0 
  76. begin
  77.  set @piece = left(@string, @pos - 1)
  78.  
  79.  
  80.  if @variableCount = 1 
  81.  begin
  82.  set @var1=@piece
  83.  end
  84.  else if @variableCount = 2
  85.  begin
  86.  set @var2=@piece
  87.  end
  88.  else if @variableCount = 3
  89.  begin
  90.  set @var3=@piece
  91.  end
  92.  else if @variableCount = 4
  93.  begin
  94.  set @var4=@piece
  95.  end
  96.  else if @variableCount = 5
  97.  begin
  98.  set @var5=@piece
  99.  end
  100.  else if @variableCount = 6
  101.  begin
  102.  set @var6=@piece
  103.  end
  104.  
  105.  set @string = stuff(@string, 1, @pos, '')
  106.  set @pos =  patindex('%/%' , @string)
  107.  set @variableCount = @variableCount + 1
  108. end
  109.  
  110.  
  111. print @quotedate  
  112. print @AccName 
  113. print @AccRept 
  114. print @AMT
  115. print @QuotedRate
  116. print @Booked_YN
  117. print @Status    
  118. print @Booked_DT
  119. print @var1
  120. print @var2
  121. print @var3
  122. print @var4
  123. print @var5
  124. print @var6
  125.  
  126. select
  127. @quotedate   'QuoteDate'
  128. ,@AccName    'AccountName'
  129. ,@AccRept    'AccountRept'
  130. ,@AMT        'AMT'
  131. ,@QuotedRate 'QuotedRated'
  132. ,@Booked_YN  'Booked_YN'
  133. ,@Status     'order_status_cd'
  134. ,@Booked_DT  'BookedDate'
  135. ,@var1       'OrigZip'
  136. ,@var2       'OrigST'
  137. ,@var3       'OrigCity'
  138. ,@var4       'DestZip'
  139. ,@var5       'DestState'
  140. ,@var6       'DestCity'
  141.  
  142.  
Mar 26 '09 #5

ck9663
Expert 2.5K+
P: 2,878
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
Mar 26 '09 #6

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

LROD
Mar 26 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.