replacing nulls

I have a string like this
select @.feed5 = @.name+', '+@.work_name +', '+isnull( @.workedyearfrom,
'')+' '+ isnull(@.workedyearto, '')+', '+ isnull(@.height,
'')+'x'+isnull(@.width, '')+'x'+isnull(@.depth, '')+' '+ @.measuretype+'
'+'Editions: '+' ' +@.edition+.+
Here some of the values will be null. Say width is null so my result
would be 10 x x 12
or if edition is null i will get in the result Editions: .
if the @.workedyearto is null then i will get 1980, ,
height.........
how do i write a string to replace the , or x or editions: with '
'(space).Not sure how many height x width x depth combinations are possible or
acceptable, however one try may be:
select @.feed5 = @.name+', '+@.work_name +', '+isnull( @.workedyearfrom,
'')+' '+ isnull(@.workedyearto, '')+', '+
COALESCE
(
@.height+'x'+@.width+'x'+@.depth,
@.height+'x'+@.width,
''
)
+' '+ @.measuretype+'
'+COALESCE('Editions: ' +@.edition, '') + ...
Or better yet, just return the data to the client/presentation tier and let
it handle NULLs and formatting.|||Not sure how many height x width x depth combinations are possible or
acceptable, however one try may be:
select @.feed5 = @.name+', '+@.work_name +', '+isnull( @.workedyearfrom,
'')+' '+ isnull(@.workedyearto, '')+', '+
COALESCE
(
@.height+'x'+@.width+'x'+@.depth,
@.height+'x'+@.width,
''
)
+' '+ @.measuretype+'
'+COALESCE('Editions: ' +@.edition, '') + ...
Or better yet, just return the data to the client/presentation tier and let
it handle NULLs and formatting.|||thanks but with '+COALESCE('Editions: ' +@.edition, '') since
'Editions' is hard coded even if @.edition is null
i get the Editions in the output
how do i make that null if @.edition is null as well|||My guess is that @.edition is '' which is not the same as NULL.
"VJ" <vishal.sql@.gmail.com> wrote in message
news:1147881891.316429.181680@.y43g2000cwc.googlegroups.com...
> thanks but with '+COALESCE('Editions: ' +@.edition, '') since
> 'Editions' is hard coded even if @.edition is null
> i get the Editions in the output
> how do i make that null if @.edition is null as well
>|||Actually no. A NULL value concatenated with any string will result in NULL.
So 'Editions: ' + NULL will yield NULL, instead of 'Editions: '.
The following example shows the result:
declare @.edition varchar(30)
set @.edition = null
select COALESCE('Editions: ' +@.edition, '') -- returns empty string ''
set @.edition = 'foo'
select COALESCE('Editions: ' +@.edition, '') -- returns "Editions: foo"
"VJ" wrote:

> thanks but with '+COALESCE('Editions: ' +@.edition, '') since
> 'Editions' is hard coded even if @.edition is null
> i get the Editions in the output
> how do i make that null if @.edition is null as well
>

replacing nulls

I have a string like this
select @.feed5 = @.name+', '+@.work_name +', '+isnull( @.workedyearfrom,
'')+' '+ isnull(@.workedyearto, '')+', '+ isnull(@.height,
'')+'x'+isnull(@.width, '')+'x'+isnull(@.depth, '')+' '+ @.measuretype+'
'+'Editions: '+' ' +@.edition+.+
Here some of the values will be null. Say width is null so my result
would be 10 x x 12
or if edition is null i will get in the result Editions: .
if the @.workedyearto is null then i will get 1980, ,
height.........
how do i write a string to replace the , or x or editions: with '
'(space).Try this:
SET CONTACT_NULL_YIELDS_NULL ON
ISNULL(@.name + ', ') + ISNULL(@.work_name + ', ') etc
"VJ" <vishal.sql@.gmail.com> wrote in message
news:1147878293.952880.53040@.j33g2000cwa.googlegroups.com...
>I have a string like this
>
> select @.feed5 = @.name+', '+@.work_name +', '+isnull( @.workedyearfrom,
> '')+' '+ isnull(@.workedyearto, '')+', '+ isnull(@.height,
> '')+'x'+isnull(@.width, '')+'x'+isnull(@.depth, '')+' '+ @.measuretype+'
> '+'Editions: '+' ' +@.edition+.+
>
> Here some of the values will be null. Say width is null so my result
> would be 10 x x 12
> or if edition is null i will get in the result Editions: .
> if the @.workedyearto is null then i will get 1980, ,
> height.........
>
> how do i write a string to replace the , or x or editions: with '
> '(space).
>|||You can use CASE statements for something like that:
SELECT @.feed5 = CASE WHEN @.width IS NULL THEN ' ' ELSE 'x' + @.width + 'x'
END +
CASE WHEN @.edition IS NULL THEN ' ' ELSE 'Edition: ' + @.edition + ',' END
Or you can use COALESCE:
SELECT @.feed5 = COALESCE('x' + @.width + 'x', ' ') +
COALESCE('Edition: ' + @.edition + ',', ' ')
COALESCE will work since NULL plus anything is NULL. So 'x' + @.width + 'x'
where @.width is NULL, returns NULL.
"VJ" wrote:

> I have a string like this
>
> select @.feed5 = @.name+', '+@.work_name +', '+isnull( @.workedyearfrom,
> '')+' '+ isnull(@.workedyearto, '')+', '+ isnull(@.height,
> '')+'x'+isnull(@.width, '')+'x'+isnull(@.depth, '')+' '+ @.measuretype+'
> '+'Editions: '+' ' +@.edition+.+
>
> Here some of the values will be null. Say width is null so my result
> would be 10 x x 12
> or if edition is null i will get in the result Editions: .
> if the @.workedyearto is null then i will get 1980, ,
> height.........
>
> how do i write a string to replace the , or x or editions: with '
> '(space).
>|||thanks michael but this does not works for COALESCE('Edition: ' +
@.edition + ',', ' ') is @.edition is null it still selects the Edition:
how do i get rid of the hardcoded 'Edition:'|||Apparently you do not have SET CONCAT_NULL_YIELDS_NULL ON, which is the
ANSI-defined behavior for NULL concatenation. So you can either SET
CONCAT_NULL_YIELDS_NULL ON or you can use the CASE statement provided in
addition to the COALESCE example. I would recommend turning the ANSI
Standard-defined behavior back ON, but it's your choice.
"VJ" <vishal.sql@.gmail.com> wrote in message
news:1147882316.278165.220540@.y43g2000cwc.googlegroups.com...
> thanks michael but this does not works for COALESCE('Edition: ' +
> @.edition + ',', ' ') is @.edition is null it still selects the Edition:
> how do i get rid of the hardcoded 'Edition:'
>

replacing nulls

I have a string like this
select @.feed5 = @.name+', '+@.work_name +', '+isnull( @.workedyearfrom,
'')+' '+ isnull(@.workedyearto, '')+', '+ isnull(@.height,
'')+'x'+isnull(@.width, '')+'x'+isnull(@.depth, '')+' '+ @.measuretype+'
'+'Editions: '+' ' +@.edition+.+
Here some of the values will be null. Say width is null so my result
would be 10 x x 12
or if edition is null i will get in the result Editions: .
if the @.workedyearto is null then i will get 1980, ,
height.........
how do i write a string to replace the , or x or editions: with '
'(space).Try this:
SET CONTACT_NULL_YIELDS_NULL ON
ISNULL(@.name + ', ') + ISNULL(@.work_name + ', ') etc
"VJ" <vishal.sql@.gmail.com> wrote in message
news:1147878293.952880.53040@.j33g2000cwa.googlegroups.com...
>I have a string like this
>
> select @.feed5 = @.name+', '+@.work_name +', '+isnull( @.workedyearfrom,
> '')+' '+ isnull(@.workedyearto, '')+', '+ isnull(@.height,
> '')+'x'+isnull(@.width, '')+'x'+isnull(@.depth, '')+' '+ @.measuretype+'
> '+'Editions: '+' ' +@.edition+.+
>
> Here some of the values will be null. Say width is null so my result
> would be 10 x x 12
> or if edition is null i will get in the result Editions: .
> if the @.workedyearto is null then i will get 1980, ,
> height.........
>
> how do i write a string to replace the , or x or editions: with '
> '(space).
>|||You can use CASE statements for something like that:
SELECT @.feed5 = CASE WHEN @.width IS NULL THEN ' ' ELSE 'x' + @.width + 'x'
END +
CASE WHEN @.edition IS NULL THEN ' ' ELSE 'Edition: ' + @.edition + ',' END
Or you can use COALESCE:
SELECT @.feed5 = COALESCE('x' + @.width + 'x', ' ') +
COALESCE('Edition: ' + @.edition + ',', ' ')
COALESCE will work since NULL plus anything is NULL. So 'x' + @.width + 'x'
where @.width is NULL, returns NULL.
"VJ" wrote:
> I have a string like this
>
> select @.feed5 = @.name+', '+@.work_name +', '+isnull( @.workedyearfrom,
> '')+' '+ isnull(@.workedyearto, '')+', '+ isnull(@.height,
> '')+'x'+isnull(@.width, '')+'x'+isnull(@.depth, '')+' '+ @.measuretype+'
> '+'Editions: '+' ' +@.edition+.+
>
> Here some of the values will be null. Say width is null so my result
> would be 10 x x 12
> or if edition is null i will get in the result Editions: .
> if the @.workedyearto is null then i will get 1980, ,
> height.........
>
> how do i write a string to replace the , or x or editions: with '
> '(space).
>|||thanks michael but this does not works for COALESCE('Edition: ' +
@.edition + ',', ' ') is @.edition is null it still selects the Edition:
how do i get rid of the hardcoded 'Edition:'|||Apparently you do not have SET CONCAT_NULL_YIELDS_NULL ON, which is the
ANSI-defined behavior for NULL concatenation. So you can either SET
CONCAT_NULL_YIELDS_NULL ON or you can use the CASE statement provided in
addition to the COALESCE example. I would recommend turning the ANSI
Standard-defined behavior back ON, but it's your choice.
"VJ" <vishal.sql@.gmail.com> wrote in message
news:1147882316.278165.220540@.y43g2000cwc.googlegroups.com...
> thanks michael but this does not works for COALESCE('Edition: ' +
> @.edition + ',', ' ') is @.edition is null it still selects the Edition:
> how do i get rid of the hardcoded 'Edition:'
>

Replace negative values

hi,
In the result of a function in my query, there are negative numbers.
How do I replace them with a 0 or is there a function like ISNULL that replaces the values that are negative?
thanks,
maartenYou can modify select statement to something like this-

select item, qty, value=
case
when value < 0 then '0' else value
end
from test

Roshmi Choudhurysql

Repeated SubQuery

Hi,
Check this query:
SELECT @.ItemCodeID
,@.ItemCategory
,LPD.LabelBatchContainerID
,LPD.ContainerNumber
,@.TableID
,(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
+ (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
LPD1.LabelBatchRecID = @.ItemCodeID) AS ItemOpenBal
-- Opening Balance for Item
,0 -- Opening Balance for Container
,LPD.Qty -- Quantity Change
,@.ActionType -- ActionType for Label Creation
-- This query needs to be optimized...
(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
+ (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
LPD1.LabelBatchRecID = @.ItemCodeID + LPD.Qty) AS ItemBalQty
-- Balance Quantity for Item
,LPD.Qty -- Balance Quantity for Container
,LP.LabelBatchStatus -- Item Status
,NULL -- Container Status
,@.ItemStatusTaskID
,@.ContainerStatusTaskID
,@.ActionStatus
,@.ActionBy
,getdate()
FROM tbl_Inventory AS I
RIGHT OUTER JOIN tbl_LabelProduction AS LP
ON LP.LabelBatchRecID = I.ItemCodeID AND I.ItemCategory = 3
INNER JOIN tbl_LabelProductionDetail AS LPD
ON LPD.LabelBatchRecID = LP.LabelBatchRecID
WHERE LP.LabelBatchRecID = @.ItemCodeID
GROUP BY I.ItemCodeID
,I.ItemCategory
,LPD.LabelBatchRecID
,LPD.LabelBatchContainerID
,LPD.ContainerNumber
,LPD.Qty
,LP.LabelBatchStatus
In the above query following part is repeated twice:
(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
+ (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
LPD1.LabelBatchRecID = @.ItemCodeID)
Is there any way to execute it only once and use it at both places in
the query.
Regards,
Shah AdarshOn 30 Mar 2006 23:05:08 -0800, Adarsh wrote:
>Hi,
>Check this query:
(snip)
>In the above query following part is repeated twice:
>(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
>I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
> + (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
>AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
>LPD1.LabelBatchRecID = @.ItemCodeID)
>Is there any way to execute it only once and use it at both places in
>the query.
Hi Adarsh,
The query is a bit too long to give you a complete solution, but I'll
give you an example that you can use.
Instead of writing
SELECT a, b, c, (a + b) AS d, (a + b) * c AS e
FROM SomeTable
You can write:
SELECT a, b, c, d, d * c AS e
FROM (SELECT a, b, c, (a + b) AS d
FROM SomeTable) AS Derived
--
Hugo Kornelis, SQL Server MVP

Repeated SubQuery

Hi,
Check this query:
SELECT @.ItemCodeID
,@.ItemCategory
,LPD.LabelBatchContainerID
,LPD.ContainerNumber
,@.TableID
,(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
+ (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
LPD1.LabelBatchRecID = @.ItemCodeID) AS ItemOpenBal
-- Opening Balance for Item
,0-- Opening Balance for Container
,LPD.Qty-- Quantity Change
,@.ActionType-- ActionType for Label Creation
-- This query needs to be optimized...
(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
+ (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
LPD1.LabelBatchRecID = @.ItemCodeID + LPD.Qty) AS ItemBalQty
-- Balance Quantity for Item
,LPD.Qty-- Balance Quantity for Container
,LP.LabelBatchStatus-- Item Status
,NULL-- Container Status
,@.ItemStatusTaskID
,@.ContainerStatusTaskID
,@.ActionStatus
,@.ActionBy
,getdate()
FROM tbl_Inventory AS I
RIGHT OUTER JOIN tbl_LabelProduction AS LP
ON LP.LabelBatchRecID = I.ItemCodeID AND I.ItemCategory = 3
INNER JOIN tbl_LabelProductionDetail AS LPD
ON LPD.LabelBatchRecID = LP.LabelBatchRecID
WHERE LP.LabelBatchRecID = @.ItemCodeID
GROUP BY I.ItemCodeID
,I.ItemCategory
,LPD.LabelBatchRecID
,LPD.LabelBatchContainerID
,LPD.ContainerNumber
,LPD.Qty
,LP.LabelBatchStatus
In the above query following part is repeated twice:
(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
+ (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
LPD1.LabelBatchRecID = @.ItemCodeID)
Is there any way to execute it only once and use it at both places in
the query.
Regards,
Shah Adarsh
On 30 Mar 2006 23:05:08 -0800, Adarsh wrote:

>Hi,
>Check this query:
(snip)
>In the above query following part is repeated twice:
>(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
>I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
>+ (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
>AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
>LPD1.LabelBatchRecID = @.ItemCodeID)
>Is there any way to execute it only once and use it at both places in
>the query.
Hi Adarsh,
The query is a bit too long to give you a complete solution, but I'll
give you an example that you can use.
Instead of writing
SELECT a, b, c, (a + b) AS d, (a + b) * c AS e
FROM SomeTable
You can write:
SELECT a, b, c, d, d * c AS e
FROM (SELECT a, b, c, (a + b) AS d
FROM SomeTable) AS Derived
Hugo Kornelis, SQL Server MVP

Repeated SubQuery

Hi,
Check this query:
SELECT @.ItemCodeID
,@.ItemCategory
,LPD.LabelBatchContainerID
,LPD.ContainerNumber
,@.TableID
,(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
+ (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
LPD1.LabelBatchRecID = @.ItemCodeID) AS ItemOpenBal
-- Opening Balance for Item
,0 -- Opening Balance for Container
,LPD.Qty -- Quantity Change
,@.ActionType -- ActionType for Label Creation
-- This query needs to be optimized...
(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
+ (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
LPD1.LabelBatchRecID = @.ItemCodeID + LPD.Qty) AS ItemBalQty
-- Balance Quantity for Item
,LPD.Qty -- Balance Quantity for Container
,LP.LabelBatchStatus -- Item Status
,NULL -- Container Status
,@.ItemStatusTaskID
,@.ContainerStatusTaskID
,@.ActionStatus
,@.ActionBy
,getdate()
FROM tbl_Inventory AS I
RIGHT OUTER JOIN tbl_LabelProduction AS LP
ON LP.LabelBatchRecID = I.ItemCodeID AND I.ItemCategory = 3
INNER JOIN tbl_LabelProductionDetail AS LPD
ON LPD.LabelBatchRecID = LP.LabelBatchRecID
WHERE LP.LabelBatchRecID = @.ItemCodeID
GROUP BY I.ItemCodeID
,I.ItemCategory
,LPD.LabelBatchRecID
,LPD.LabelBatchContainerID
,LPD.ContainerNumber
,LPD.Qty
,LP.LabelBatchStatus
In the above query following part is repeated twice:
(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
+ (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
LPD1.LabelBatchRecID = @.ItemCodeID)
Is there any way to execute it only once and use it at both places in
the query.
Regards,
Shah AdarshOn 30 Mar 2006 23:05:08 -0800, Adarsh wrote:

>Hi,
>Check this query:
(snip)
>In the above query following part is repeated twice:
>(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
>I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
> + (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
>AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
>LPD1.LabelBatchRecID = @.ItemCodeID)
>Is there any way to execute it only once and use it at both places in
>the query.
Hi Adarsh,
The query is a bit too long to give you a complete solution, but I'll
give you an example that you can use.
Instead of writing
SELECT a, b, c, (a + b) AS d, (a + b) * c AS e
FROM SomeTable
You can write:
SELECT a, b, c, d, d * c AS e
FROM (SELECT a, b, c, (a + b) AS d
FROM SomeTable) AS Derived
Hugo Kornelis, SQL Server MVP