SQL: How to rewrite left join with unknown parameter in view -

SQL: How to rewrite left join with unknown parameter in view -

this meant kind of simplified / more point question of:

nhibernate / queryover: how left bring together parameter

the core problem have next query:

1)

select v.*, ... someview v left bring together sometable t on v.foreignkey = t.foreignkey , t.someothervalue = @myparam

where @myparam parameter.

i want utilize query within view since don´t know @myparam when creating view don´t know way attach query used straight within join. can version of query this:

2)

select v.*, ... (someview v left bring together sometable t on v.foreignkey = t.foreignkey) someothervalue = 123

wich in view this:

3)

create view myview select v.*, t.someothervalue, ... someview v left bring together sometable t on v.foreignkey = t.foreignkey

and say:

select * myview someothervalue = @myparam

in both cases (2 , 3) @myparam gets applied after left bring together happened, result set different (and in case incorrect).

so searching way rewrite 1) in way can utilize within view (with similar syntax in 2 , 3)

note: using table valued function @myparam parameter work 1 time again can´t utilize model nhibernate or queryover, not option.

on db2, accomplished you're trying 1 time by:

making scalar udf function referenced in place of @myparam. the scalar udf function retrieved parameter value session (temporary table). prior referencing view @ runtime, code created or recreated , populated session temporary table single row parameter value (giving scalar udf feed off of). at runtime, sql view reference phone call scalar udf function, access temp table, , homecoming parameter value view, , voila, worked.

on db2, table udf function perform in similar manner. in general, table functions more flexible scalar functions, , improve choice.

db2 supports late binding when using functions in manner.

i don't know database you're using, might have luck trying similar.

sql join left-join