How to Substring from 12 char to 8 char itemid

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

General SQL Server Forums

New to SQL Server Programming

How to Substring from 12 char to 8 char itemid

Author

Topic

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-06-19 : 09:46:52

Hi,

alter PROCEDURE [dbo].[PPUpdateIWDetails]

(
@CompanyID NVARCHAR(36),
@DivisionID NVARCHAR(36),
@DepartmentID NVARCHAR(36),
@ItemID NVARCHAR(36),
@OrderNo NVARCHAR(36),
@LineNo NVARCHAR(36),
@TAllotedQty Numeric,
@EmployeeID NVARCHAR(36),
@Trndate datetime
)
AS
BEGIN
By default iam passing 12 char itemid as parameter...

Here iam selecting the itemid from InventoryLedger -if it is 8 char than this query should be executed
IF EXISTS(SELECT ItemID FROM InventoryLedger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID)

BEGIN
DECLARE @Qty INT

select @Qty =QUANTITY from inventoryledger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID
select qtyonhand=qtyonhand+@Qty from InventoryByWareHouse where ItemID=@ItemID
END

Here iam selecting the itemid from InventoryLedger -if it is 12 char than this query should be executed(both queries are same)
IF EXISTS(SELECT ItemID FROM InventoryLedger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID)

BEGIN
DECLARE @Qty INT

select @Qty =QUANTITY from inventoryledger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID
select qtyonhand=qtyonhand+@Qty from InventoryByWareHouse where ItemID=@ItemID
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 10:18:35

quote:
Originally posted by Anushka

Hi,

alter PROCEDURE [dbo].[PPUpdateIWDetails]

(
@CompanyID NVARCHAR(36),
@DivisionID NVARCHAR(36),
@DepartmentID NVARCHAR(36),
@ItemID NVARCHAR(36),
@OrderNo NVARCHAR(36),
@LineNo NVARCHAR(36),
@TAllotedQty Numeric,
@EmployeeID NVARCHAR(36),
@Trndate datetime
)
AS
BEGIN
By default iam passing 12 char itemid as parameter...

Here iam selecting the itemid from InventoryLedger -if it is 8 char than this query should be executed
IF EXISTS(SELECT ItemID FROM InventoryLedger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID)

BEGIN
DECLARE @Qty INT

select @Qty =QUANTITY from inventoryledger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID
select qtyonhand=qtyonhand+@Qty from InventoryByWareHouse where ItemID=@ItemID
END

Here iam selecting the itemid from InventoryLedger -if it is 12 char than this query should be executed(both queries are same)
IF EXISTS(SELECT ItemID FROM InventoryLedger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID)

BEGIN
DECLARE @Qty INT

select @Qty =QUANTITY from inventoryledger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID
select qtyonhand=qtyonhand+@Qty from InventoryByWareHouse where ItemID=@ItemID
END

Why do you write two similar blocks of code just to check if length is different?DIdnt understand you intention. Can you explain what you're trying to do? May be explain with some data

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-19 : 10:21:30

[code]ALTER PROCEDURE dbo.PPUpdateIWDetails
(
@CompanyID NVARCHAR(36),
@DivisionID NVARCHAR(36),
@DepartmentID NVARCHAR(36),
@ItemID NVARCHAR(36),
@OrderNo NVARCHAR(36),
@LineNo NVARCHAR(36),
@TAllotedQty Numeric,
@EmployeeID NVARCHAR(36),
@TrnDate datetime
)
AS

SET NOCOUNT ON

IF LEN(@ItemID) NOT IN (8, 12)
RETURN

DECLARE @Qty INT

SELECT @Qty = Quantity
FROM InventoryLedger
WHERE TransDate = @TrnDate
AND ItemID = @ItemID
AND ILLineNumber = @LineNo
AND TransNumber = @OrderNo
AND TransactionType = 'Production'
AND CompanyID = @CompanyID
AND DivisionID = @DivisionID
AND DepartmentID = @DepartmentID

SET @Qty = COALESCE(@Qty, 0)

SELECT QtyOnHand = QtyOnHand + @Qty
FROM InventoryByWareHouse
WHERE ItemID = @ItemID[/code]

E 12°55'05.25"
N 56°04'39.16"

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-06-19 : 11:17:47

Conversion failed when converting the nvarchar value 'BBBBBBBBBBBB' to data type int.
from front end iam passing nvarchar only...and itemid ='BBBBBBBBBBBB'

prm = cmd.Parameters.Add("@ItemID", SqlDbType.NVarChar, 36)
prm.Value = txtFabricID.Text
prm.Direction = ParameterDirection.Input

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 11:36:16

quote:
Originally posted by Anushka

Conversion failed when converting the nvarchar value 'BBBBBBBBBBBB' to data type int.
from front end iam passing nvarchar only...and itemid ='BBBBBBBBBBBB'

prm = cmd.Parameters.Add("@ItemID", SqlDbType.NVarChar, 36)
prm.Value = txtFabricID.Text
prm.Direction = ParameterDirection.Input

Try putting '' to values inside IN() statement.

b/w i still didnt understand what you're trying to do

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-19 : 11:40:51

What datatype is ItemID in table InventoryLedger?
What datatype is ItemID in table InventoryByWareHouse?

E 12°55'05.25"
N 56°04'39.16"

cvipin
Yak Posting Veteran

51 Posts

Posted - 2008-06-19 : 11:48:29

Try this:

ALTER PROCEDURE dbo.PPUpdateIWDetails
(
@CompanyID NVARCHAR(36),
@DivisionID NVARCHAR(36),
@DepartmentID NVARCHAR(36),
@ItemID NVARCHAR(36),
@OrderNo NVARCHAR(36),
@LineNo NVARCHAR(36),
@TAllotedQty Numeric,
@EmployeeID NVARCHAR(36),
@TrnDate datetime
)
AS

SET NOCOUNT ON

IF LEN(LTRIM(RTRIM(@ItemID))) NOT IN (8, 12)
RETURN

DECLARE @Qty INT

SELECT @Qty = Quantity
FROM InventoryLedger
WHERE TransDate = @TrnDate
AND ItemID = @ItemID
AND ILLineNumber = @LineNo
AND TransNumber = @OrderNo
AND TransactionType = 'Production'
AND CompanyID = @CompanyID
AND DivisionID = @DivisionID
AND DepartmentID = @DepartmentID

SET @Qty = COALESCE(@Qty, 0)

SELECT QtyOnHand = QtyOnHand + @Qty
FROM InventoryByWareHouse
WHERE ItemID = @ItemID

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-06-20 : 00:44:34

hi cvipin by using the sp u had given it is not showing any updation in my tables
InventoryLedger,InventoryByWareHouse

HI PESO IN BOTH THE TABLES IT IS NVARCHAR(36)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-20 : 00:49:56

There are no updates in the stored procedure!

E 12°55'05.25"
N 56°04'39.16"

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-06-20 : 00:58:31

uffffffffffff my mind is blocked.........

this is my need...but here updation is not gng on.....
i may have 8 char or 12 char itemid...
so based on that i have to update in inventory by warehouse

alter PROCEDURE [dbo].[PPUpdateIWDetails]

(
@CompanyID NVARCHAR(36),
@DivisionID NVARCHAR(36),
@DepartmentID NVARCHAR(36),
@ItemID NVARCHAR(36),
@OrderNo NVARCHAR(36),
@LineNo NVARCHAR(36),

@Trndate datetime
)

AS
begin
SET NOCOUNT ON

IF LEN(@ItemID) NOT IN (8, 12)
RETURN

DECLARE @Qty INT

SELECT @Qty = Quantity
FROM InventoryLedger
WHERE TransDate = @TrnDate
AND ItemID = @ItemID
AND ILLineNumber = @LineNo
AND TransNumber = @OrderNo
AND TransactionType = 'Production'
AND CompanyID = @CompanyID
AND DivisionID = @DivisionID
AND DepartmentID = @DepartmentID

SET @Qty = COALESCE(@Qty, 0)

UPDATE InventoryByWareHouse
set QtyOnHand = QtyOnHand + @Qty

WHERE ItemID = @ItemID

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-06-20 : 01:15:00

BY DNG LIKE THIS I GOT IT...........

alter PROCEDURE [dbo].[PPUpdateIWDetails]

(
@CompanyID NVARCHAR(36),
@DivisionID NVARCHAR(36),
@DepartmentID NVARCHAR(36),
@ItemID NVARCHAR(36), --12 CHAR ITEMID FROM FRONTEND
@OrderNo NVARCHAR(36),
@LineNo NVARCHAR(36),
@GreyItemQ NVARCHAR(36), --- 8 CHAR ITEMID FROM FRONTEND

@Trndate datetime
)
AS
BEGIN
IF EXISTS(SELECT ItemID FROM InventoryLedger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID)

BEGIN
DECLARE @Qty INT

select @Qty =QUANTITY from inventoryledger WHERE TransDate=@Trndate AND ItemID=@GreyItemQ AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID

UPDATE InventoryByWareHouse
SET QtyOnHand = QtyOnHand + @Qty
WHERE ItemID = @GreyItemQ

END

IF EXISTS(SELECT ItemID FROM InventoryLedger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID)

BEGIN
DECLARE @Qty1 INT

select @Qty1 =QUANTITY from inventoryledger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID
UPDATE InventoryByWareHouse
SET QtyOnHand = QtyOnHand + @Qty1
WHERE ItemID = @ItemID

END
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-20 : 02:06:45

1) Why do you use @ItemID in the EXISTS and then SELECT with @GreyItemID?
2) Why didn't you post your full code from the beginning?

aLTER PROCEDURE dbo.PPUpdateIWDetails
(
	@CompanyID NVARCHAR(36), 
	@DivisionID NVARCHAR(36), 
	@DepartmentID NVARCHAR(36),
	@ItemID NVARCHAR(36),
	@OrderNo NVARCHAR(36),
	@LineNo NVARCHAR(36),
	@GreyItemQ NVARCHAR(36),
	@TrnDate DATETIME
)
AS

SET NOCOUNT ON

IF LEN(@ItemID) NOT IN (8, 12)
	RETURN

DECLARE	@Qty8 INT,
	@Qty12 INT

SELECT	@Qty8 = MAX(CASE WHEN LEN(@GreyItemID) = 8 THEN Quantity ELSE 0 END),
	@Qty12 = MAX(CASE WHEN LEN(@ItemID) = 12 THEN Quantity ELSE 0 END)
FROM	InventoryLedger
WHERE	TransDate = @TrnDate
	AND ItemID IN (@ItemID, @GreyItemID)
	AND ILLineNumber = @LineNo
	AND TransNumber = @OrderNo
	AND TransactionType = 'Production'
	AND CompanyID = @CompanyID
	AND DivisionID = @DivisionID
	AND DepartmentID = @DepartmentID

SELECT	@Qty8 = COALESCE(@Qty8, 0),
	@Qty12 = COALESCE(@Qty12, 0)

UPDATE	InventoryByWareHouse
SET	QtyOnHand = QtyOnHand +	CASE
					WHEN ItemID = @GreyItemID THEN @Qty8
					WHEN ItemID = @ItemID THEN @Qty12
					ELSE 0
				END
WHERE	ItemID IN (@ItemID, @GreyItemID)

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources