Invalid column Name Hcode

Question

Post reply

Invalid column Name Hcode

jagjitsingh

Ten Centuries

Points: 1059
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

Steve Collins

SSCrazy

Points: 2069

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246705 More actions · Answer 1

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?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

jagjitsingh Ten Centuries Points: 1059 More actions · Answer 2

jagjitsingh

Ten Centuries

Points: 1059

December 15, 2021 at 11:15 am

#3963845

Hi

Error is coming Invalid column HCode

Thanks

Phil Parkin SSC Guru Points: 246705 More actions · Answer 3

jagjitsingh wrote:

Hi
Error is coming Invalid column HCode
Thanks

Still no question.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.