SQL Compatability *= syntax

I have some old sql code that contains some *= statements.

What is the replacement syntax?

                        where
                        clm.client_location_id = @client_location_id and
                        clm.carrier_id = @carrier_id and
                        clm.manifest_number = @manifest_number and
                        clm.manifest_number = m.value
                        and tnum.itn = m.itn
                        and m.itn *= haz.itn
                        and m.itn *= ctag.itn
                        and m.itn *= aod.itn
                        and m.itn *= cod.itn
                        and m.itn *= handle.itn
                        and m.itn *= dvamt.itn
                        and m.itn *= dvcharge.itn
                        and m.itn *= dvunits.itn
                        and m.itn = op.itn
                        and m.itn *= osize.itn
                        and m.itn *= aodtype.itn
                        and m.itn *= sat.itn
                        and m.itn *= ac.itn
                        and m.itn *= dc.itn
                        and m.itn = muid.itn
                        and muid.value <> ' '
jdr0606Asked:
Who is Participating?
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Having put that away, Pawan's translation is not correct, because we have a mix of inner and outer joins here. This should be correct:
SELECT  m.value, /*@Pickup_Nbr = */
        clm.close_date, /*@Manifest_Date = */
        tnum.value, 
        op.shipment_number    , /*@Order_ID =  */
        isnull(haz.value      ,'00000000'    ), /*@Hazardous_Charge = */
        isnull(ctag.value     ,'00000000'    ), /*@Call_Tag_Charge =  */
        isnull(aod.value      ,'00000000'    ), /*@AOD_Charge =  */
        isnull(cod.value      ,'000000000000'), /*@COD_Charge =  */
        isnull(handle.value   ,'00000000'    ), /*@Handling_Charge =  */
        isnull(dvamt.value    ,'000000000000'), /*@Declared_Value_Amount =  */
        isnull(dvcharge.value ,'000000000000'), /*@Declared_Value_Charge =  */
        isnull(dvunits.value  ,'000000000000'), /*@Declared_Value_Units =  */
      --isnull(op.scale_weight,'00000000000' ), /*@Actual_Weight =  */
        right('00000000000' + trim(isnull(str(convert(integer, op.scale_weight*10000),11),'')), 11),
        isnull(osize.value,' '), /*@Over_Size = */
        isnull(dc.value,'00000000000'), /*@Discounted_Freight_Charge =  */
        op.create_date, /*@Ship_Date =  */
        right('00000000000' + trim(isnull(str(convert(integer, ac.totalcharges*10000),11),'')), 11),
        isnull(muid.value,' ') /*@MU_ID =  */

from      vpackage_attributes_manifest_number   m
     join client_location_manifests             clm      ON clm.manifest_number = m.value
     join vPackage_Attributes_MU_ID             muid     ON muid.itn     = m.itn
     join vPackage_Attributes_Tracking_Number   tnum     ON tnum.itn     = m.itn
     join order_packages                        op       ON op.itn       = m.itn
left join vPackage_Charges_Hazardous            haz      ON haz.itn      = m.itn
left join vPackage_Charges_Ctag                 ctag     ON ctag.itn     = m.itn
left join vPackage_Charges_AOD                  aod      ON aod.itn      = m.itn  
left join vPackage_Charges_COD                  cod      ON cod.itn      = m.itn  
left join vPackage_Charges_Handle_Charge        handle   ON handle.itn   = m.itn  
left join vPackage_Attributes_DV_Amount         DVamt    ON dvamt.itn    = m.itn  
left join vPackage_Charges_DV_Charge            DVcharge ON dvcharge.itn = m.itn  
left join vPackage_Attributes_DV_Units          DVunits  ON dvunits.itn  = m.itn  
left join vPackage_Attributes_Oversize          OSize    ON osize.itn    = m.itn  
left join vPackage_Attributes_AOD               AODtype  ON aodtype.itn  = m.itn  
left join vPackage_Charges_total_charges        AC       ON sat.itn      = m.itn  
left join vPackage_Charges_Discount             DC       ON ac.itn       = m.itn  
left join vPackage_Attributes_Saturday_Delivery Sat      ON dc.itn       = m.itn  

where muid.value             <> ' ' 
  and clm.client_location_id = @client_location_id
  and clm.carrier_id         = @carrier_id
  and clm.manifest_number    = @manifest_number

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Please try this..

Remove *...

Which database are you using? Any sample data and expected output.

where 
                        clm.client_location_id = @client_location_id and 
                        clm.carrier_id = @carrier_id and 
                        clm.manifest_number = @manifest_number and
                        clm.manifest_number = m.value 
                        and tnum.itn = m.itn
                        and m.itn= haz.itn
                        and m.itn= ctag.itn
                        and m.itn= aod.itn
                        and m.itn= cod.itn
                        and m.itn= handle.itn
                        and m.itn= dvamt.itn
                        and m.itn= dvcharge.itn
                        and m.itn= dvunits.itn
                        and m.itn op.itn
                        and m.itn= osize.itn
                        and m.itn= aodtype.itn
                        and m.itn= sat.itn 
                        and m.itn= ac.itn
                        and m.itn= dc.itn
                        and m.itn = muid.itn
                        and muid.value <> ' '

Open in new window

0
 
Pallavi GodseSr. Digital Marketing ExecutiveCommented:
The solution below might be yor answer as I have replaced *= operator  with LEFT OUTER JOIN keyword.

clm.client_location_id = @client_location_id and
                        clm.carrier_id = @carrier_id and
                        clm.manifest_number = @manifest_number and
                        clm.manifest_number = m.value
                        and tnum.itn = m.itn
                        and m.itn LEFT OUTER JOIN jobs   haz.itn
                        and m.itn LEFT OUTER JOIN jobs   ctag.itn
                        and m.itn LEFT OUTER JOIN jobs   aod.itn
                        and m.itn LEFT OUTER JOIN jobs   cod.itn
                        and m.itn LEFT OUTER JOIN jobs   handle.itn
                        and m.itn LEFT OUTER JOIN jobs   dvamt.itn
                        and m.itn LEFT OUTER JOIN jobs   dvcharge.itn
                        and m.itn LEFT OUTER JOIN jobs   dvunits.itn
                        and m.itn = op.itn
                        and m.itn LEFT OUTER JOIN jobs   osize.itn
                        and m.itn LEFT OUTER JOIN jobs   aodtype.itn
                        and m.itn LEFT OUTER JOIN jobs   sat.itn
                        and m.itn LEFT OUTER JOIN jobs   ac.itn
                        and m.itn LEFT OUTER JOIN jobs   dc.itn
                        and m.itn = muid.itn
                        and muid.value <> ' '
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Pawan, you really should know better. Your suggestion builds a inner join from an outer join. That cannotbe correct.

Pallavi, that is nonsense. The OUTER JOIN clause belongs into FROM, not WHERE, and has a different syntax.

jdr0606, we need to see more from the original query - at least starting from FROM, as that part needs modification too.
0
 
Pawan KumarDatabase ExpertCommented:
@Author - Please provide complete query. Also which DB are you using?
We can rewrite your query if required.
0
 
jdr0606Author Commented:
select       m.value, /*@Pickup_Nbr = */
                  clm.close_date, /*@Manifest_Date = */
                  tnum.value,
                  op.shipment_number, /*@Order_ID =  */
                  isnull(haz.value,'00000000'), /*@Hazardous_Charge = */
                  isnull(ctag.value,'00000000'), /*@Call_Tag_Charge =  */
                  isnull(aod.value,'00000000'), /*@AOD_Charge =  */
                  isnull(cod.value,'000000000000'), /*@COD_Charge =  */
                  isnull(handle.value,'00000000'), /*@Handling_Charge =  */
                  isnull(dvamt.value,'000000000000'), /*@Declared_Value_Amount =  */
                  isnull(dvcharge.value,'000000000000'), /*@Declared_Value_Charge =  */
                  isnull(dvunits.value,'000000000000'), /*@Declared_Value_Units =  */
                  --isnull(op.scale_weight,'00000000000'), /*@Actual_Weight =  */
                  case
                      when len(convert(integer,isnull(op.scale_weight,'00000000000') * 10000)) = 1 then '0000000000' + str(convert(integer,isnull(op.scale_weight,'00000000000') * 10000),1)
                      when len(convert(integer,isnull(op.scale_weight,'00000000000') * 10000)) = 2 then '000000000' + str(convert(integer,isnull(op.scale_weight,'00000000000') * 10000),2)
                      when len(convert(integer,isnull(op.scale_weight,'00000000000') * 10000)) = 3 then '00000000' + str(convert(integer,isnull(op.scale_weight,'00000000000') * 10000),3)
                      when len(convert(integer,isnull(op.scale_weight,'00000000000') * 10000)) = 4 then '0000000' + str(convert(integer,isnull(op.scale_weight,'00000000000') * 10000),4)
                      when len(convert(integer,isnull(op.scale_weight,'00000000000') * 10000)) = 5 then '000000' + str(convert(integer,isnull(op.scale_weight,'00000000000') * 10000),5)
                      when len(convert(integer,isnull(op.scale_weight,'00000000000') * 10000)) = 6 then '00000' + str(convert(integer,isnull(op.scale_weight,'00000000000') * 10000),6)
                      when len(convert(integer,isnull(op.scale_weight,'00000000000') * 10000)) = 7 then '0000' + str(convert(integer,isnull(op.scale_weight,'00000000000') * 10000),7)
                      when len(convert(integer,isnull(op.scale_weight,'00000000000') * 10000)) = 8 then '000' + str(convert(integer,isnull(op.scale_weight,'00000000000') * 10000),8)
                      when len(convert(integer,isnull(op.scale_weight,'00000000000') * 10000)) = 9 then '00' + str(convert(integer,isnull(op.scale_weight,'00000000000') * 10000),9)
                      when len(convert(integer,isnull(op.scale_weight,'00000000000') * 10000)) = 10 then '0' + str(convert(integer,isnull(op.scale_weight,'00000000000') * 10000),10)
                      when len(convert(integer,isnull(op.scale_weight,'00000000000') * 10000)) = 11 then str(convert(integer,isnull(op.scale_weight,'00000000000') * 10000),11)
                  end ,
                  isnull(osize.value,' '), /*@Over_Size = */
                  isnull(dc.value,'00000000000'), /*@Discounted_Freight_Charge =  */
                  op.create_date, /*@Ship_Date =  */
                  case
                      when len(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000)) = 1 then '0000000000' + str(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000),1)
                      when len(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000)) = 2 then '000000000' + str(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000),2)
                      when len(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000)) = 3 then '00000000' + str(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000),3)
                      when len(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000)) = 4 then '0000000' + str(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000),4)
                      when len(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000)) = 5 then '000000' + str(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000),5)
                      when len(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000)) = 6 then '00000' + str(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000),6)
                      when len(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000)) = 7 then '0000' + str(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000),7)
                      when len(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000)) = 8 then '000' + str(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000),8)
                      when len(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000)) = 9 then '00' + str(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000),9)
                      when len(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000)) = 10 then '0' + str(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000),10)
                      when len(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000)) = 11 then str(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000),11)
                  end ,
                  isnull(muid.value,' ') /*@MU_ID =  */

                  from
                        vpackage_attributes_manifest_number m,
                        client_location_manifests clm,
                        vPackage_Attributes_Tracking_Number tnum,
                        vPackage_Charges_Hazardous haz,
                        vPackage_Charges_Ctag ctag,
                        vPackage_Charges_AOD aod,
                        vPackage_Charges_COD cod,
                        vPackage_Charges_Handle_Charge handle,
                        vPackage_Attributes_DV_Amount DVamt,
                        vPackage_Charges_DV_Charge DVcharge,
                        vPackage_Attributes_DV_Units DVunits,
                        order_packages op,
                        vPackage_Attributes_Oversize OSize,
                        vPackage_Attributes_AOD AODtype,
                        vPackage_Charges_total_charges AC,
                        vPackage_Charges_Discount DC,
                        vPackage_Attributes_Saturday_Delivery Sat,
                        vPackage_Attributes_MU_ID MUID
                        where
                        clm.client_location_id = @client_location_id and
                        clm.carrier_id = @carrier_id and
                        clm.manifest_number = @manifest_number and
                        clm.manifest_number = m.value
                        and tnum.itn = m.itn
                        and m.itn *= haz.itn
                        and m.itn *= ctag.itn
                        and m.itn *= aod.itn
                        and m.itn *= cod.itn
                        and m.itn *= handle.itn
                        and m.itn *= dvamt.itn
                        and m.itn *= dvcharge.itn
                        and m.itn *= dvunits.itn
                        and m.itn = op.itn
                        and m.itn *= osize.itn
                        and m.itn *= aodtype.itn
                        and m.itn *= sat.itn
                        and m.itn *= ac.itn
                        and m.itn *= dc.itn
                        and m.itn = muid.itn
                        and muid.value <> ' '
0
 
Pawan KumarDatabase ExpertCommented:
Please find the details below -

Meaning

*= -> LEFT OUTER JOIN
=* -> RIGHT OUTER JOIN

They are non-ANSI syntax for outer joins (*= and =*). They are deprecated.

Read this for more details ( read Corrective Action from )  -> https://msdn.microsoft.com/en-us/library/ee240720(v=sql.120).aspx

Updated code for you -

SELECT       
	m.value, /*@Pickup_Nbr = */
	clm.close_date, /*@Manifest_Date = */
	tnum.value, 
	op.shipment_number, /*@Order_ID =  */
	isnull(haz.value,'00000000'), /*@Hazardous_Charge = */
	isnull(ctag.value,'00000000'), /*@Call_Tag_Charge =  */
	isnull(aod.value,'00000000'), /*@AOD_Charge =  */
	isnull(cod.value,'000000000000'), /*@COD_Charge =  */
	isnull(handle.value,'00000000'), /*@Handling_Charge =  */
	isnull(dvamt.value,'000000000000'), /*@Declared_Value_Amount =  */
	isnull(dvcharge.value,'000000000000'), /*@Declared_Value_Charge =  */
	isnull(dvunits.value,'000000000000'), /*@Declared_Value_Units =  */
	--isnull(op.scale_weight,'00000000000'), /*@Actual_Weight =  */
	case
		when len(convert(integer,isnull(op.scale_weight,'00000000000') * 10000)) = 1 then '0000000000' + str(convert(integer,isnull(op.scale_weight,'00000000000') * 10000),1)
		when len(convert(integer,isnull(op.scale_weight,'00000000000') * 10000)) = 2 then '000000000' + str(convert(integer,isnull(op.scale_weight,'00000000000') * 10000),2)
		when len(convert(integer,isnull(op.scale_weight,'00000000000') * 10000)) = 3 then '00000000' + str(convert(integer,isnull(op.scale_weight,'00000000000') * 10000),3)
		when len(convert(integer,isnull(op.scale_weight,'00000000000') * 10000)) = 4 then '0000000' + str(convert(integer,isnull(op.scale_weight,'00000000000') * 10000),4)
		when len(convert(integer,isnull(op.scale_weight,'00000000000') * 10000)) = 5 then '000000' + str(convert(integer,isnull(op.scale_weight,'00000000000') * 10000),5)
		when len(convert(integer,isnull(op.scale_weight,'00000000000') * 10000)) = 6 then '00000' + str(convert(integer,isnull(op.scale_weight,'00000000000') * 10000),6)
		when len(convert(integer,isnull(op.scale_weight,'00000000000') * 10000)) = 7 then '0000' + str(convert(integer,isnull(op.scale_weight,'00000000000') * 10000),7)
		when len(convert(integer,isnull(op.scale_weight,'00000000000') * 10000)) = 8 then '000' + str(convert(integer,isnull(op.scale_weight,'00000000000') * 10000),8)
		when len(convert(integer,isnull(op.scale_weight,'00000000000') * 10000)) = 9 then '00' + str(convert(integer,isnull(op.scale_weight,'00000000000') * 10000),9)
		when len(convert(integer,isnull(op.scale_weight,'00000000000') * 10000)) = 10 then '0' + str(convert(integer,isnull(op.scale_weight,'00000000000') * 10000),10)
		when len(convert(integer,isnull(op.scale_weight,'00000000000') * 10000)) = 11 then str(convert(integer,isnull(op.scale_weight,'00000000000') * 10000),11)
	end ,
	isnull(osize.value,' '), /*@Over_Size = */
	isnull(dc.value,'00000000000'), /*@Discounted_Freight_Charge =  */
	op.create_date, /*@Ship_Date =  */
	case
		when len(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000)) = 1 then '0000000000' + str(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000),1)
		when len(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000)) = 2 then '000000000' + str(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000),2)
		when len(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000)) = 3 then '00000000' + str(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000),3)
		when len(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000)) = 4 then '0000000' + str(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000),4)
		when len(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000)) = 5 then '000000' + str(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000),5)
		when len(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000)) = 6 then '00000' + str(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000),6)
		when len(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000)) = 7 then '0000' + str(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000),7)
		when len(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000)) = 8 then '000' + str(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000),8)
		when len(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000)) = 9 then '00' + str(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000),9)
		when len(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000)) = 10 then '0' + str(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000),10)
		when len(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000)) = 11 then str(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000),11)
	end ,
	isnull(muid.value,' ') /*@MU_ID =  */
from 
vpackage_attributes_manifest_number m  
LEFT JOIN client_location_manifests clm ON 
									clm.client_location_id = @client_location_id and 
									clm.carrier_id = @carrier_id and 
									clm.manifest_number = @manifest_number and
									clm.manifest_number = m.value
LEFT JOIN vPackage_Attributes_Tracking_Number tnum ON tnum.itn = m.itn
LEFT JOIN vPackage_Charges_Hazardous haz ON m.itn = haz.itn
LEFT JOIN vPackage_Charges_Ctag ctag ON m.itn = ctag.itn
LEFT JOIN vPackage_Charges_AOD aod ON m.itn = aod.itn
LEFT JOIN vPackage_Charges_COD cod ON m.itn = cod.itn
LEFT JOIN vPackage_Charges_Handle_Charge handle ON m.itn = handle.itn
LEFT JOIN vPackage_Attributes_DV_Amount DVamt ON m.itn = dvamt.itn
LEFT JOIN vPackage_Charges_DV_Charge DVcharge ON m.itn = dvcharge.itn
LEFT JOIN vPackage_Attributes_DV_Units DVunits ON m.itn = dvunits.itn
LEFT JOIN order_packages op ON m.itn = op.itn
LEFT JOIN vPackage_Attributes_Oversize OSize ON m.itn = osize.itn
LEFT JOIN vPackage_Attributes_AOD AODtype ON m.itn = aodtype.itn
LEFT JOIN vPackage_Charges_total_charges AC ON m.itn = sat.itn 
LEFT JOIN vPackage_Charges_Discount DC ON m.itn = ac.itn
LEFT JOIN vPackage_Attributes_Saturday_Delivery Sat ON m.itn = dc.itn
LEFT JOIN vPackage_Attributes_MU_ID MUID ON m.itn = muid.itn
WHERE muid.value <> ' '

Open in new window

0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
First, please let us format and simplify your select clause:
SELECT  m.value, /*@Pickup_Nbr = */
        clm.close_date, /*@Manifest_Date = */
        tnum.value, 
        op.shipment_number    , /*@Order_ID =  */
        isnull(haz.value      ,'00000000'    ), /*@Hazardous_Charge = */
        isnull(ctag.value     ,'00000000'    ), /*@Call_Tag_Charge =  */
        isnull(aod.value      ,'00000000'    ), /*@AOD_Charge =  */
        isnull(cod.value      ,'000000000000'), /*@COD_Charge =  */
        isnull(handle.value   ,'00000000'    ), /*@Handling_Charge =  */
        isnull(dvamt.value    ,'000000000000'), /*@Declared_Value_Amount =  */
        isnull(dvcharge.value ,'000000000000'), /*@Declared_Value_Charge =  */
        isnull(dvunits.value  ,'000000000000'), /*@Declared_Value_Units =  */
      --isnull(op.scale_weight,'00000000000' ), /*@Actual_Weight =  */
        case when len(convert(integer,isnull(op.scale_weight,'00000000000') * 10000))
          when  1 then '0000000000' + str(convert(integer,isnull(op.scale_weight,'00000000000') * 10000), 1)
        	when  2 then  '000000000' + str(convert(integer,isnull(op.scale_weight,'00000000000') * 10000), 2)
        	when  3 then   '00000000' + str(convert(integer,isnull(op.scale_weight,'00000000000') * 10000), 3)
        	when  4 then    '0000000' + str(convert(integer,isnull(op.scale_weight,'00000000000') * 10000), 4)
        	when  5 then     '000000' + str(convert(integer,isnull(op.scale_weight,'00000000000') * 10000), 5)
        	when  6 then      '00000' + str(convert(integer,isnull(op.scale_weight,'00000000000') * 10000), 6)
        	when  7 then       '0000' + str(convert(integer,isnull(op.scale_weight,'00000000000') * 10000), 7)
        	when  8 then        '000' + str(convert(integer,isnull(op.scale_weight,'00000000000') * 10000), 8)
        	when  9 then         '00' + str(convert(integer,isnull(op.scale_weight,'00000000000') * 10000), 9)
        	when 10 then          '0' + str(convert(integer,isnull(op.scale_weight,'00000000000') * 10000),10)
        	when 11 then                str(convert(integer,isnull(op.scale_weight,'00000000000') * 10000),11)
        end ,
        isnull(osize.value,' '), /*@Over_Size = */
        isnull(dc.value,'00000000000'), /*@Discounted_Freight_Charge =  */
        op.create_date, /*@Ship_Date =  */
        case len(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000)) 
          when  1 then '0000000000' + str(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000), 1)
          when  2 then  '000000000' + str(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000), 2)
          when  3 then   '00000000' + str(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000), 3)
          when  4 then    '0000000' + str(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000), 4)
          when  5 then     '000000' + str(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000), 5)
          when  6 then      '00000' + str(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000), 6)
          when  7 then       '0000' + str(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000), 7)
          when  8 then        '000' + str(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000), 8)
          when  9 then         '00' + str(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000), 9)
          when 10 then          '0' + str(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000),10)
          when 11 then                str(convert(integer,isnull(ac.totalcharges,'00000000000') * 10000),11)
        end ,
        isnull(muid.value,' ') /*@MU_ID =  */

Open in new window

This is still not optimal because of a lot of repetition of expressions, so:
SELECT  m.value, /*@Pickup_Nbr = */
        clm.close_date, /*@Manifest_Date = */
        tnum.value, 
        op.shipment_number    , /*@Order_ID =  */
        isnull(haz.value      ,'00000000'    ), /*@Hazardous_Charge = */
        isnull(ctag.value     ,'00000000'    ), /*@Call_Tag_Charge =  */
        isnull(aod.value      ,'00000000'    ), /*@AOD_Charge =  */
        isnull(cod.value      ,'000000000000'), /*@COD_Charge =  */
        isnull(handle.value   ,'00000000'    ), /*@Handling_Charge =  */
        isnull(dvamt.value    ,'000000000000'), /*@Declared_Value_Amount =  */
        isnull(dvcharge.value ,'000000000000'), /*@Declared_Value_Charge =  */
        isnull(dvunits.value  ,'000000000000'), /*@Declared_Value_Units =  */
      --isnull(op.scale_weight,'00000000000' ), /*@Actual_Weight =  */
        right('00000000000' + trim(isnull(str(convert(integer, op.scale_weight*10000),11),'')), 11),
        isnull(osize.value,' '), /*@Over_Size = */
        isnull(dc.value,'00000000000'), /*@Discounted_Freight_Charge =  */
        op.create_date, /*@Ship_Date =  */
        right('00000000000' + trim(isnull(str(convert(integer, ac.totalcharges*10000),11),'')), 11),
        isnull(muid.value,' ') /*@MU_ID =  */

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.