Invalid column Name Hcode

• jagjitsingh

  Ten Centuries

  Points: 1072

  More actions

  December 15, 2021 at 10:58 am

  Go to Answer

  #3963836

  Hi

   

  (Select Case (select count(*) from tbl1 T where T.num = T0.num ) when 0 then

  HCode=STUFF

  (

  (

  SELECT ', '+ CAST(A0.[aCode] AS VARCHAR(MAX))

  FROM O A0

  INNER JOIN I A1 ON A0.[AEntry] = A1.[SEntry]

  FOR XMl PATH('')

  ),1,1,''

  ) else '2' end)

   

  from tbl5 t0

   

  Thanks

• Phil Parkin

  SSC Guru

  Points: 247137

  More actions

  December 15, 2021 at 11:14 am

  #3963844

  jagjitsingh wrote:

  Hi

  (Select Case (select count(*) from tbl1 T where T.num = T0.num ) when 0 then HCode=STUFF ( ( SELECT ', '+ CAST(A0.[aCode] AS VARCHAR(MAX)) FROM O A0 INNER JOIN I A1 ON A0.[AEntry] = A1.[SEntry] FOR XMl PATH('') ),1,1,'' ) else '2' end)

  from tbl5 t0

  Thanks

  What is your question?

  ---

• jagjitsingh

  Ten Centuries

  Points: 1072

  More actions

  December 15, 2021 at 11:15 am

  #3963845

  Hi

  Error is coming  Invalid column HCode

  Thanks

• Phil Parkin

  SSC Guru

  Points: 247137

  More actions

  December 15, 2021 at 11:22 am

  #3963846

  jagjitsingh wrote:

  Hi

  Error is coming  Invalid column HCode

  Thanks

  Still no question.

  ---

• Steve Collins

  SSCrazy

  Points: 2192

  More actions

  December 15, 2021 at 1:43 pm

  Answer

  #3963901

  Maybe extract the constant and put the correlated subquery in the FROM clause as a CROSS APPLY

  declare @vmax_var            varchar(max)=
  (SELECT STUFF((SELECT ', ' + CAST(A0.[aCode] AS VARCHAR(MAX)) 
                 FROM O A0
                      JOIN I A1 ON A0.[AEntry] = A1.[SEntry]
                 FOR XMl PATH('')),1,1,''));
  
  Select HCode=case when t.t_count=0 then @vmax_var else '2' end
  from tbl5 t0
       cross apply (select count(*) 
                    from tbl1 T 
                    where T.num = T0.num) t(t_count);
  

  Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können