2

The following is a very shortened version of what I'm trying to do.

I know that SplitString2 works because I tested it with hard coding the string and using ID = 1.

Can someone please try to explain why the last line does not work? 

select 
    xx.ParseThis,
    p1.ID, p1.Value,
    p2.ID, p2.Value  
from 
    (select 
         cast('Term1=2018FA&Term2=2019SP&EndDate=04/02/2019&Major=NURN' as nvarchar(max)) 'ParseThis') xx  
left join 
    dbo.SplitString2('Term1=2018FA&Term2=2019SP&EndDate=04/02/2019&Major=NURN', '&') [p1] on p1.ID = 1  
left join 
    dbo.SplitString2(xx.ParseThis, '&') [p2] on p2.ID = 2

I get this error:

Msg 4104, Level 16, State 1, Line 8
The multi-part identifier "xx.ParseThis" could not be bound.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
cmg
  • 23
  • 2

3 Answers3

1

You are trying to join onto the expression dbo.SplitString2(xx.ParseThis, '&')

The contents of the table returned by that will vary row by row dependant on the value of xx.ParseThis.

The definition of tables involved in a join can not be correlated based on values from other tables (and it does not matter that in this case your example only has one row.)

For that you would need APPLY.

SELECT xx.ParseThis,
       p1.ID,
       p1.Value,
       p2.ID,
       p2.Value
FROM   (SELECT cast('Term1=2018FA&Term2=2019SP&EndDate=04/02/2019&Major=NURN' AS NVARCHAR(max)) AS ParseThis) xx
       LEFT JOIN dbo.SplitString2('Term1=2018FA&Term2=2019SP&EndDate=04/02/2019&Major=NURN', '&') [p1]
              ON p1.ID = 1
       OUTER APPLY (SELECT *
                    FROM   dbo.SplitString2(xx.ParseThis, '&') [p2]
                    WHERE  p2.ID = 2) [p2]
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
-1

I believe its because your query can not find "xx.ParseThis" inside your "From" selection / destination

Ranidu
  • 19
  • 2
-1

Put a second literal string as like this

   NURN' as nvarchar(max)) as 'ParseThis'

Try with and without quotes around the ParseThis FieldName

Sql Surfer
  • 1,344
  • 1
  • 10
  • 25