Weird Join Question

[Anonymous]

hi

this is a 3 section question and i think fixing any of the 3 section will fix my issue

so below i have this table

basically its color and item code then a hashcode then image location 

the issue is i have multiple location and i need to try to narrow it down to one..

there are 3 ways -- 2 of them in qv and not sure how to do it

1 the formula used to pull the location should be

=firstsortedvalue(swlocation,

- aggr(

  sum(

  {$<DTL.Flag_OrderStatus={1}>}DTL.Qty*DTL.Flag_OrderStatus)

  ,PD.Style,swlocation))

which should work because of the first sorted.. but it doesnt seem to work not sure why..

the other way is the join where we got this table..  i am joining 2 tables to get this one..

if somehow i can do max or something

select distinct i.itemCode, color =col.dimensionLong,

     bcItemCode =reverse(substring(reverse(i.itemcode),1,4)),

     itemDesc = i.itemDesc,

     BCColorCode =isnull(bc_col.color_code,''),

     Swatch =isnull(img.ImageUrl,'')

from dbo.VItem i

join .VItemVariant iv

     on i.itemId = iv.itemId

join VDimension col

     on col.dimensionId =iv.colorId

left join.dbo.zzxcolrr bc_col

     on bc_col.color_group + bc_col.nrf_color =col.dimensionCode+col.nrf collate Chinese_PRC_CI_AS

left join .VImage img

     on img.classId =iv.itemVariantId

     and img.ObjectType ='ItemVariant'

     and img.imageType ='Standard Image'

where i.Category ='Fabric'

or i.Category = 'Yarn'

and i.SeasonId > 429542

;

join(FabImages)

load

bcItemCode as BCItemCode,

BCColorCode as BCColorcode,

Swatch as swlocation,

hash128(bcItemCode,BCColorCode) as fabcolor

resident temp14;

not sure whats the easiest

thanks