Morning all Brother
can any one help me for edit query to work to search buy itemcode and and add maxcount items
Code:
USE SRO_VT_ACCOUNT
DECLARE @Serial bigint
declare @CID int
declare @AID int
declare @ItemCode varchar(50)
declare @loc varchar(50)
set @loc = 'inventory'
---------------------------------------------------
SET @Serial = YOUR_SERIAL_HERE
---------------------------------------------------
SET @CID = (select CharID from SRO_VT_SHARD.dbo._Inventory where ItemID = (select ID64 from SRO_VT_SHARD.dbo._Items where Serial64 like @Serial))
if @CID is null
begin
SET @AID = (select UserJID from SRO_VT_SHARD.dbo._Chest where ItemID = (select ID64 from SRO_VT_SHARD.dbo._Items where Serial64 like @Serial))
SET @loc = 'chest'
end
else if @AID is null
begin
SET @AID = (select UserJID from SRO_VT_SHARD.dbo._User where CharID = (select OwnerCharID from SRO_VT_SHARD.dbo._CharCOS where ID = (select COSID from SRO_VT_SHARD.dbo._InvCOS where ItemID = (select ID64 from SRO_VT_SHARD.dbo._Items where Serial64 like @Serial))))
SET @loc = 'pet'
end
else
begin
SET @AID = (select UserJID from SRO_VT_SHARD.dbo._User where CharID = @CID)
end
SET @ItemCode = (select CodeName128 from SRO_VT_SHARD.dbo._RefObjCommon where ID = (select RefItemID from SRO_VT_SHARD.dbo._Items where Serial64 like @Serial))
SELECT top 1 tbuser.StrUserID as AccID, chart.CharName16 as CharName, @Serial as Serial, @ItemCode as ItemCode, @loc as Location
FROM TB_User as tbuser
JOIN SRO_VT_SHARD.dbo._AccountJID as accjid on tbuser.JID = accjid.JID
JOIN SRO_VT_SHARD.dbo._User as usert on accjid.JID = usert.UserJID
JOIN SRO_VT_SHARD.dbo._Char as chart on usert.CharID = chart.CharID
WHERE usert.UserJID = @AID
we need to search items from invcos , inventory , Storage By char
and we need how much have char items like Gold,Silver coin
sorry my bad language English
i`know it but i want edit this query to search by itemsCode or Items ID
PHP Code:
USE SRO_VT_SHARD
GO
--//////////FindItemBySerial V1.3 By Syloxx\\\\\\\\\
DECLARE @RefID BIGINT = (SELECT ID FROM _RefObjCommon WHERE CodeName128='YourCodeHere'),
@ItemID INT,
@ItemCodeName VARCHAR(128)
SELECT @ItemCodeName = C.CodeName128 ,
@ItemID = I.ID64
FROM _RefObjCommon C
INNER JOIN _Items I
ON C.ID = I.RefItemID
WHERE @RefID = I.RefItemID
--###############################################
--##### CHECK IF SERIAL IS VALID #####
--###############################################
IF @ItemID IS NULL OR @ItemID = 0
BEGIN
PRINT 'No item with the specified Serial found!'
END
--###############################################
--##### CHECK IF ITEM IS IN INVENTORY #####
--###############################################
ELSE IF (@ItemID IN (SELECT ItemID from _Inventory))
BEGIN
SELECT TBU.JID AS 'UserJID',
TBU.StrUserID AS 'UserName',
C.CharID,
C.CharName16 AS 'CharName',
@ItemCodeName AS 'Item CodeName',
IT.OptLevel,
'Inventory' AS 'Storage',
INV.Slot
FROM _Items IT
INNER JOIN _Inventory INV
ON INV.ItemID = IT.ID64
INNER JOIN _Char C
ON C.CharID = INV.CharID
INNER JOIN _User U
ON U.CharID = C.CharID
INNER JOIN SRO_VT_ACCOUNT..TB_User TBU
ON TBU.JID = U.UserJID
WHERE IT.RefItemID = @RefID
END
--###############################################
--##### CHECK IF ITEM IS IN STORAGE #####
--###############################################
ELSE IF (@ItemID IN (SELECT ItemID FROM _Chest))
BEGIN
SELECT TBU.JID AS 'UserJID',
TBU.StrUserID AS 'UserName',
C.CharID,
C.CharName16 AS 'CharName',
@ItemCodeName AS 'Item CodeName',
IT.OptLevel,
'Storage' AS 'Storage',
SINV.Slot
FROM _Items IT
INNER JOIN _Chest SINV
ON SINV.ItemID = IT.ID64
INNER JOIN _User U
ON U.UserJID = SINV.UserJID
INNER JOIN _Char C
ON C.CharID = U.CharID
INNER JOIN SRO_VT_ACCOUNT..TB_User TBU
ON TBU.JID = U.UserJID
WHERE IT.RefItemID = @RefID
END
--###############################################
--##### CHECK IF ITEM IS IN PET #####
--###############################################
ELSE IF (@ItemID IN (SELECT ItemID FROM _InvCOS))
BEGIN
DECLARE @COSSerial BIGINT,
@COSItemID INT,
@COSCodeName VARCHAR(128)
SELECT CCOS.ID AS 'COSID',
ROC.CodeName128 AS 'COS CodeName',
CCOS.CharName AS 'COS Name',
@ItemCodeName AS 'Item CodeName',
IT.OptLevel,
'COS Inventory' AS 'Storage',
CINV.Slot
FROM _Items IT
INNER JOIN _InvCOS CINV
ON CINV.ItemID = IT.ID64
INNER JOIN _CharCOS CCOS
ON CCOS.ID = CINV.COSID
INNER JOIN _Items COSIT
ON COSIT.Data = CCOS.ID
INNER JOIN _RefObjCommon ROC
ON ROC.ID = COSIT.RefItemID
WHERE IT.RefItemID = @RefID
AND ROC.TypeID2=2
AND ROC.TypeID3=1
AND ROC.TypeID4=2
SELECT @COSSerial = COSIT.Serial64,
@COSItemID = COSIT.ID64,
@COSCodeName = ROC.CodeName128
FROM _Items IT
INNER JOIN _InvCOS CINV
ON CINV.ItemID = IT.ID64
INNER JOIN _CharCOS CCOS
ON CCOS.ID = CINV.COSID
INNER JOIN _Items COSIT
ON COSIT.Data = CCOS.ID
INNER JOIN _RefObjCommon ROC
ON ROC.ID = COSIT.RefItemID
WHERE IT.RefItemID = @RefID
AND ROC.TypeID2=2
AND ROC.TypeID3=1
AND ROC.TypeID4=2
--##############################################
--##### CHECK IF PET IS IN INVENTORY #####
--##############################################
IF (@COSItemID IN (SELECT ItemID from _Inventory))
BEGIN
SELECT TBU.JID AS 'UserJID',
TBU.StrUserID AS 'UserName',
C.CharID,
C.CharName16 AS 'CharName',
@COSCodeName AS 'COS CodeName',
IT.OptLevel,
'Inventory' AS 'Storage',
INV.Slot
FROM _Items IT
INNER JOIN _Inventory INV
ON INV.ItemID = IT.ID64
INNER JOIN _Char C
ON C.CharID = INV.CharID
INNER JOIN _User U
ON U.CharID = C.CharID
INNER JOIN SRO_VT_ACCOUNT..TB_User TBU
ON TBU.JID = U.UserJID
WHERE IT.Serial64 = @COSSerial
END
--###############################################
--##### CHECK IF ITEM IS IN STORAGE #####
--###############################################
ELSE IF (@COSItemID IN (SELECT ItemID FROM _Chest))
BEGIN
SELECT TBU.JID AS 'UserJID',
TBU.StrUserID AS 'UserName',
C.CharID,
C.CharName16 AS 'CharName',
@COSCodeName AS 'COS CodeName',
IT.OptLevel,
'Storage' AS 'Storage',
SINV.Slot
FROM _Items IT
INNER JOIN _Chest SINV
ON SINV.ItemID = IT.ID64
INNER JOIN _User U
ON U.UserJID = SINV.UserJID
INNER JOIN _Char C
ON C.CharID = U.CharID
INNER JOIN SRO_VT_ACCOUNT..TB_User TBU
ON TBU.JID = U.UserJID
WHERE IT.Serial64 = @COSSerial
END
--##############################################
--##### CHECK IF PET IS IN GUILD STORAGE #####
--##############################################
ELSE IF (@COSItemID IN (SELECT ItemID FROM _GuildChest))
BEGIN
SELECT TBU.JID AS 'UserJID',
TBU.StrUserID AS 'UserName',
GM.CharID,
GM.CharName AS 'GuildMaster',
G.ID AS 'GuildID',
G.Name AS 'GuildName',
@COSCodeName AS 'COS CodeName',
IT.OptLevel,
'Guild Storage' AS 'Storage',
GINV.Slot
FROM _Items IT
INNER JOIN _GuildChest GINV
ON GINV.ItemID = IT.ID64
INNER JOIN _Guild G
ON G.ID = GINV.GuildID
INNER JOIN _GuildMember GM
ON GM.GuildID = G.ID
INNER JOIN _User U
ON U.CharID = GM.CharID
INNER JOIN SRO_VT_ACCOUNT..TB_User TBU
ON TBU.JID = U.UserJID
WHERE IT.Serial64 = @COSSerial
AND GM.MemberClass = 0
END
END
--###############################################
--##### CHECK IF ITEM IS IN GUILD STORAGE #####
--###############################################
ELSE IF (@ItemID IN (SELECT ItemID FROM _GuildChest))
BEGIN
SELECT TBU.JID AS 'UserJID',
TBU.StrUserID AS 'UserName',
GM.CharID,
GM.CharName AS 'GuildMaster',
G.ID AS 'GuildID',
G.Name AS 'GuildName',
@ItemCodeName AS 'Item CodeName',
IT.OptLevel,
'Guild Storage' AS 'Storage',
GINV.Slot
FROM _Items IT
INNER JOIN _GuildChest GINV
ON GINV.ItemID = IT.ID64
INNER JOIN _Guild G
ON G.ID = GINV.GuildID
INNER JOIN _GuildMember GM
ON GM.GuildID = G.ID
INNER JOIN _User U
ON U.CharID = GM.CharID
INNER JOIN SRO_VT_ACCOUNT..TB_User TBU
ON TBU.JID = U.UserJID
WHERE IT.RefItemID = @RefID
AND GM.MemberClass = 0
END
[Query] to edit job type and job level for beginners! 08/04/2016 - SRO PServer Guides & Releases - 7 Replies /*### Query by Kiro ###*//*### Query by Kiro ###*/
use SRO_VT_SHARD /*### Query by Kiro ###*/
declare @jobtype int ;declare @joblvl int ; declare @Charname varchar(128)
set @joblvl = '10' -- Job level
set @jobtype = '1' -- (1 = Hunter) (2 = Thief) (3 = Hunter)
set @Charname = 'Kiro' -- Character Name !
update _CharTrijob set = @joblvl , = @jobtype where CharID = (select _char.CharID from _Char where CharName16 = @Charname) /*### Query by Kiro ###*/
Explain:How to edit any DB's Query 11/24/2012 - SRO Private Server - 1 Replies Hello everyone
I want an explain how to edit any db query
I want to view my clean db query and make some edits
How is it?
Sorry for my bad English :)
[Suche]Query für "Alteklinge, Reichsklinge" &' Stichdolche NUR QUERY! 08/18/2010 - Metin2 Private Server - 1 Replies Ich weiß ich werd nervig aber ich suche die Query's
für Alteklinge, Reichsklinge &' Strichdolche
Why?
Naya, weil ich in der DB i-wie nur Drachenmaulglocke hab
... not more -.-
Auf jeden Fall Icon's etc. etc. etc. hab ich ich brauche nur die Query's
Any Edit for This query
