Convert string - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Convert string

Author

Topic

Imco20030
Starting Member

10 Posts

Posted - 2009-10-07 : 16:58:43

Hi All,

I have a statement in the query that does not return the expected result.
I probably have to convert one line to be in compliance with other ones.

Statement:

case when o.ordnum = (select min(o3.ordnum) from ord o3 left join shipment_line sl3 on (o3.ordnum = sl3.ordnum) where sl3.ship_id = sl.ship_id)
then 'a'
else 'tog with ' + (select min(o3.reffld_5) from ord o3 left join shipment_line sl3 on (o3.ordnum = sl3.ordnum) where sl3.ship_id = sl.ship_id) end,

When I launched this, I get:
'a' if the condition is met
if not, I get 'tog with' + value

Now I want to replace the value 'a' by a calculation:
(select round(sum(tc7.chrg_amt),2) from tm_chrg tc7 where tc7.chrg_role = 'PRICE' and tc7.chargetype = 'ACCESSORIAL' and tc7.ship_id = sl.ship_id and isnull(tc7.cb_invoice_id,'null') = isnull(tc3.cb_invoice_id, 'null') )

the result of this is a numeric amount.

When I replace the value 'a':

case when o.ordnum = (select min(o3.ordnum) from ord o3 left join shipment_line sl3 on (o3.ordnum = sl3.ordnum) where sl3.ship_id = sl.ship_id)
then (select round(sum(tc7.chrg_amt),2) from tm_chrg tc7 where tc7.chrg_role = 'PRICE' and tc7.chargetype = 'ACCESSORIAL' and tc7.ship_id = sl.ship_id and isnull(tc7.cb_invoice_id,'null') = isnull(tc3.cb_invoice_id, 'null') )
else 'tog with ' + (select min(o3.reffld_5) from ord o3 left join shipment_line sl3 on (o3.ordnum = sl3.ordnum) where sl3.ship_id = sl.ship_id) end,

I got the wrong result.

I know that it is something to do with th string but I don't know how to insert the 'convert' function.

Could you please help on this?

Much appreciated

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-10-07 : 18:15:14

OK, Does the string you want to insert work on its own (minus the begin and end parens?

What is the result you get standalone? What is the result you get when inserted in place of 'a'?

John

Imco20030
Starting Member

10 Posts

Posted - 2009-10-07 : 18:29:39

When using the 'a' value, it works ok.

if the condition is met, then it shows the 'a' value. Otherwise, it shows the 'tog with' + the reference reffld_5

example:
Column1

row 1:a
row 2: a
row 3: a
row 4: tog with + reffld_5

Now if insert the calculation select round(sum(tc7.chrg_amt)etc...
I get only the calculation but when the condition is not met anymore, then it stops.

example:

Column1

row 1:50 (sum 25 and 25)
row 2: 65 (sum 40 and 25)
row 3: 10 (sum 4 and 6)

then, it stops because the condition is more met. In case, the 'tog with' + reffld_5 should be shown in row 4

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources