Hi,
I have a sql table, over 30 milion recs, with the following fields:
(id1 int, id2 int, itemsCollection varchar(100), myText TEXT)
I have also sql table, with the following fields: (item varchar(10), rate int)
I need to write a query that returns the following info: id1, id2, itemsCollection, item, rate, myText
The output need to be ordered as:
- Get id1 & id2 with the bigest rate
- output all the recs for the id1 & 2, ordered by rate (sub order)
eg
Main table:
id1 id2 itemsCollection myText
1 1 'a,b' 'count-11-a,B - max = 15 additional txt'
1 1 'a,b' 'count-11-a-B - max = 15'
1 1 '' 'count-11'
1 1 'a,c' 'count-11-a,C - max = 20'
2 8 'c,d' 'count-28-C-d - max = 20 additional txt'
2 8 'c,d' 'count-28-C-d - max = 20'
2 8 'd' 'count-28-D - max = 5'
3 2 'a,d' 'count-32-A-d - max = 10'
3 2 '' 'count-32'
Rates table:
item rate
a 10
b 15
c 20
d 5
'' 0
RequestedOutput:
itemsCollection item rate id1 id2 myText
a,c c 20 1 1 count-11-a,C - max = 20
a,b b 15 1 1 count-11-a,B - max = 15 additional txy
a,b b 15 1 1 count-11-a,B - max = 15
0 1 1 count-11
c,d c 20 2 8 count-28-C-d - max = 20 additional txt
c,d c 20 2 8 count-28-C-d - max = 20
d d 5 2 8 count-28-D - max = 5
a,d a 10 3 2 count-32-A-d - max = 10
0 3 2 count-32
Thanks!you need an application program for that logic
you can sort the results of a join (and in your case the join will be monstrously inefficient, because of the comma-separated list of ids) by descending order of rate, but you can not "take a side trip" and sort all the a/b rows up under the a/b row with the largest rate|||r937, thanks for reply.
No app available - I'm trying to do it using cursor & temp tables, because
Thanks anyway|||okay, then transact-sql is your application programming language
good luck
Showing posts with label varchar. Show all posts
Showing posts with label varchar. Show all posts
Monday, March 19, 2012
Monday, March 12, 2012
help needed on XML
using the BOL and looking through some of the posts on here ive managed to
come up with the following code in T-SQl
DECLARE @.TESTXML varchar(8000)
SET @.TESTXML = '<?xml version="1.0" encoding="ISO-8859-1"?>
<BDocument>,
<Data>
<InputReport>
<Header reportType="REFT2013" reportNumber="999999" batchNumber="026"
reportSequenceNumber="000121" userNumber="123456">
<ProducedOn time="19:21:22" date="2004-09-30"/>
<ProcessingDate date="2004-10-01"/>
</Header>
</InputReport>
</Data>
</BDocument>'
DECLARE @.hDoc int
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
SELECT *
FROM
OPENXML(@.hDoc, '/BDocument')
EXEC sp_xml_removedocument @.hDoc
I need help on expanding this.
1. Would i be right in assuming that to load the contants of a XML file
into the variable @.TESTXML, i would need to use something like actixex in a
DTS.
2. how can i in this case just do a select on a specific field ie
'reportnumber'
3. some of the reports i will be recieving will have the same field names in
different sections, for example
- <AccountTotals>
- <DebitEntry>
<AcceptedRecords numberOf="1" valueOf="0.00" currency="GBP" />
<RejectedRecords numberOf="0" valueOf="0.00" currency="GBP" />
<TotalsRecords numberOf="1" valueOf="0.00" currency="GBP" />
</DebitEntry>
</AccountTotal>
- <CreditEntry>
<AcceptedRecords numberOf="0" valueOf="0.00" currency="GBP" />
<RejectedRecords numberOf="0" valueOf="0.00" currency="GBP" />
<UserTrailerTotals numberOf="0" valueOf="0.00" currency="GBP" />
<AdjustmentRecords numberOf="0" valueOf="0.00" currency="GBP" />
</CreditEntry>
As you can see the field 'numberOf' is used several times, how can i
differanciate between each one in each section
See below.
Best regards
Michael
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:3A396C18-0271-4DD4-B2B5-C74505CE1156@.microsoft.com...
> using the BOL and looking through some of the posts on here ive managed to
> come up with the following code in T-SQl
> DECLARE @.TESTXML varchar(8000)
> SET @.TESTXML = '<?xml version="1.0" encoding="ISO-8859-1"?>
> <BDocument>,
> <Data>
> <InputReport>
> <Header reportType="REFT2013" reportNumber="999999" batchNumber="026"
> reportSequenceNumber="000121" userNumber="123456">
> <ProducedOn time="19:21:22" date="2004-09-30"/>
> <ProcessingDate date="2004-10-01"/>
> </Header>
> </InputReport>
> </Data>
> </BDocument>'
> DECLARE @.hDoc int
> EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
> SELECT *
> FROM
> OPENXML(@.hDoc, '/BDocument')
> EXEC sp_xml_removedocument @.hDoc
> I need help on expanding this.
> 1. Would i be right in assuming that to load the contants of a XML file
> into the variable @.TESTXML, i would need to use something like actixex in
> a
> DTS.
Not necessarily. Any client side API that allows you to pass a parameter to
a stored proc should work. Just copy the file content over as parameter
value.
> 2. how can i in this case just do a select on a specific field ie
> 'reportnumber'
The OpenXML above results in an edge table.
To get the reportNumber for every header, you would replace your select
with:
select *
from OpenXML(@.hDoc, '/BDocument/Data/InputReport/Header') WITH (rno int
'@.reportNumber')
> 3. some of the reports i will be recieving will have the same field names
> in
> different sections, for example
> - <AccountTotals>
> - <DebitEntry>
> <AcceptedRecords numberOf="1" valueOf="0.00" currency="GBP" />
> <RejectedRecords numberOf="0" valueOf="0.00" currency="GBP" />
> <TotalsRecords numberOf="1" valueOf="0.00" currency="GBP" />
> </DebitEntry>
> </AccountTotal>
> - <CreditEntry>
> <AcceptedRecords numberOf="0" valueOf="0.00" currency="GBP" />
> <RejectedRecords numberOf="0" valueOf="0.00" currency="GBP" />
> <UserTrailerTotals numberOf="0" valueOf="0.00" currency="GBP" />
> <AdjustmentRecords numberOf="0" valueOf="0.00" currency="GBP" />
> </CreditEntry>
>
> As you can see the field 'numberOf' is used several times, how can i
> differanciate between each one in each section
The following will give you only AcceptedRecords:
select * from OpenXML(@.hDoc, '//AcceptedRecords') with
(numberOf int, valueOf real, currency nvarchar(5))
The following will give you numberOf and the name of its element:
select * from OpenXML(@.hDoc, '//DebitEntry/*') with
(recname nvarchar(40) '@.mp:localname', numberOf int)
HTH
Michael
come up with the following code in T-SQl
DECLARE @.TESTXML varchar(8000)
SET @.TESTXML = '<?xml version="1.0" encoding="ISO-8859-1"?>
<BDocument>,
<Data>
<InputReport>
<Header reportType="REFT2013" reportNumber="999999" batchNumber="026"
reportSequenceNumber="000121" userNumber="123456">
<ProducedOn time="19:21:22" date="2004-09-30"/>
<ProcessingDate date="2004-10-01"/>
</Header>
</InputReport>
</Data>
</BDocument>'
DECLARE @.hDoc int
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
SELECT *
FROM
OPENXML(@.hDoc, '/BDocument')
EXEC sp_xml_removedocument @.hDoc
I need help on expanding this.
1. Would i be right in assuming that to load the contants of a XML file
into the variable @.TESTXML, i would need to use something like actixex in a
DTS.
2. how can i in this case just do a select on a specific field ie
'reportnumber'
3. some of the reports i will be recieving will have the same field names in
different sections, for example
- <AccountTotals>
- <DebitEntry>
<AcceptedRecords numberOf="1" valueOf="0.00" currency="GBP" />
<RejectedRecords numberOf="0" valueOf="0.00" currency="GBP" />
<TotalsRecords numberOf="1" valueOf="0.00" currency="GBP" />
</DebitEntry>
</AccountTotal>
- <CreditEntry>
<AcceptedRecords numberOf="0" valueOf="0.00" currency="GBP" />
<RejectedRecords numberOf="0" valueOf="0.00" currency="GBP" />
<UserTrailerTotals numberOf="0" valueOf="0.00" currency="GBP" />
<AdjustmentRecords numberOf="0" valueOf="0.00" currency="GBP" />
</CreditEntry>
As you can see the field 'numberOf' is used several times, how can i
differanciate between each one in each section
See below.
Best regards
Michael
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:3A396C18-0271-4DD4-B2B5-C74505CE1156@.microsoft.com...
> using the BOL and looking through some of the posts on here ive managed to
> come up with the following code in T-SQl
> DECLARE @.TESTXML varchar(8000)
> SET @.TESTXML = '<?xml version="1.0" encoding="ISO-8859-1"?>
> <BDocument>,
> <Data>
> <InputReport>
> <Header reportType="REFT2013" reportNumber="999999" batchNumber="026"
> reportSequenceNumber="000121" userNumber="123456">
> <ProducedOn time="19:21:22" date="2004-09-30"/>
> <ProcessingDate date="2004-10-01"/>
> </Header>
> </InputReport>
> </Data>
> </BDocument>'
> DECLARE @.hDoc int
> EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
> SELECT *
> FROM
> OPENXML(@.hDoc, '/BDocument')
> EXEC sp_xml_removedocument @.hDoc
> I need help on expanding this.
> 1. Would i be right in assuming that to load the contants of a XML file
> into the variable @.TESTXML, i would need to use something like actixex in
> a
> DTS.
Not necessarily. Any client side API that allows you to pass a parameter to
a stored proc should work. Just copy the file content over as parameter
value.
> 2. how can i in this case just do a select on a specific field ie
> 'reportnumber'
The OpenXML above results in an edge table.
To get the reportNumber for every header, you would replace your select
with:
select *
from OpenXML(@.hDoc, '/BDocument/Data/InputReport/Header') WITH (rno int
'@.reportNumber')
> 3. some of the reports i will be recieving will have the same field names
> in
> different sections, for example
> - <AccountTotals>
> - <DebitEntry>
> <AcceptedRecords numberOf="1" valueOf="0.00" currency="GBP" />
> <RejectedRecords numberOf="0" valueOf="0.00" currency="GBP" />
> <TotalsRecords numberOf="1" valueOf="0.00" currency="GBP" />
> </DebitEntry>
> </AccountTotal>
> - <CreditEntry>
> <AcceptedRecords numberOf="0" valueOf="0.00" currency="GBP" />
> <RejectedRecords numberOf="0" valueOf="0.00" currency="GBP" />
> <UserTrailerTotals numberOf="0" valueOf="0.00" currency="GBP" />
> <AdjustmentRecords numberOf="0" valueOf="0.00" currency="GBP" />
> </CreditEntry>
>
> As you can see the field 'numberOf' is used several times, how can i
> differanciate between each one in each section
The following will give you only AcceptedRecords:
select * from OpenXML(@.hDoc, '//AcceptedRecords') with
(numberOf int, valueOf real, currency nvarchar(5))
The following will give you numberOf and the name of its element:
select * from OpenXML(@.hDoc, '//DebitEntry/*') with
(recname nvarchar(40) '@.mp:localname', numberOf int)
HTH
Michael
Monday, February 27, 2012
Help me with this SELECT FROM OPENXML
The following script doesnt return any records. Why ?
----
declare @.t varchar(1000)
DECLARE @.XMLDocPointer1 INT
EXEC sp_xml_preparedocument @.XMLDocPointer1 OUTPUT, @.t
set @.t = '<EMP><EE EID=''1'' NAME=''ANAND'' /><EE EID=''2''
NAME=''SAGAR'' /></EMP>'
select @.t
BEGIN TRANSACTION
select * FROM
OPENXML(@.XMLDocPointer1,'/EMP/EE')
WITH (EID VARCHAR(2), ENAME VARCHAR(30))
EXEC sp_xml_removedocument @.XMLDocPointer1
COMMIT
----You must assign value to @.t variable before sp_xml_preparedocument
call.
On Jul 16, 1:04 pm, Sagar <anandsa...@.gmail.com> wrote:
> The following script doesnt return any records. Why ?
> ----
> declare @.t varchar(1000)
> DECLARE @.XMLDocPointer1 INT
> EXEC sp_xml_preparedocument @.XMLDocPointer1 OUTPUT, @.t
> set @.t = '<EMP><EE EID=''1'' NAME=''ANAND'' /><EE EID=''2''
> NAME=''SAGAR'' /></EMP>'
> select @.t
> BEGIN TRANSACTION
> select * FROM
> OPENXML(@.XMLDocPointer1,'/EMP/EE')
> WITH (EID VARCHAR(2), ENAME VARCHAR(30))
> EXEC sp_xml_removedocument @.XMLDocPointer1
> COMMIT
> ----|||Hello gapokrif@.gmail.com,
Well spotted
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
> You must assign value to @.t variable before sp_xml_preparedocument
> call.
> On Jul 16, 1:04 pm, Sagar <anandsa...@.gmail.com> wrote:
>
----
declare @.t varchar(1000)
DECLARE @.XMLDocPointer1 INT
EXEC sp_xml_preparedocument @.XMLDocPointer1 OUTPUT, @.t
set @.t = '<EMP><EE EID=''1'' NAME=''ANAND'' /><EE EID=''2''
NAME=''SAGAR'' /></EMP>'
select @.t
BEGIN TRANSACTION
select * FROM
OPENXML(@.XMLDocPointer1,'/EMP/EE')
WITH (EID VARCHAR(2), ENAME VARCHAR(30))
EXEC sp_xml_removedocument @.XMLDocPointer1
COMMIT
----You must assign value to @.t variable before sp_xml_preparedocument
call.
On Jul 16, 1:04 pm, Sagar <anandsa...@.gmail.com> wrote:
> The following script doesnt return any records. Why ?
> ----
> declare @.t varchar(1000)
> DECLARE @.XMLDocPointer1 INT
> EXEC sp_xml_preparedocument @.XMLDocPointer1 OUTPUT, @.t
> set @.t = '<EMP><EE EID=''1'' NAME=''ANAND'' /><EE EID=''2''
> NAME=''SAGAR'' /></EMP>'
> select @.t
> BEGIN TRANSACTION
> select * FROM
> OPENXML(@.XMLDocPointer1,'/EMP/EE')
> WITH (EID VARCHAR(2), ENAME VARCHAR(30))
> EXEC sp_xml_removedocument @.XMLDocPointer1
> COMMIT
> ----|||Hello gapokrif@.gmail.com,
Well spotted
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
> You must assign value to @.t variable before sp_xml_preparedocument
> call.
> On Jul 16, 1:04 pm, Sagar <anandsa...@.gmail.com> wrote:
>
Help me with this SELECT FROM OPENXML
The following script doesnt return any records. Why ?
declare @.t varchar(1000)
DECLARE @.XMLDocPointer1 INT
EXEC sp_xml_preparedocument @.XMLDocPointer1 OUTPUT, @.t
set @.t = '<EMP><EE EID=''1'' NAME=''ANAND'' /><EE EID=''2''
NAME=''SAGAR'' /></EMP>'
select @.t
BEGIN TRANSACTION
select * FROM
OPENXML(@.XMLDocPointer1,'/EMP/EE')
WITH (EID VARCHAR(2), ENAME VARCHAR(30))
EXEC sp_xml_removedocument @.XMLDocPointer1
COMMIT
You must assign value to @.t variable before sp_xml_preparedocument
call.
On Jul 16, 1:04 pm, Sagar <anandsa...@.gmail.com> wrote:
> The following script doesnt return any records. Why ?
> ----
> declare @.t varchar(1000)
> DECLARE @.XMLDocPointer1 INT
> EXEC sp_xml_preparedocument @.XMLDocPointer1 OUTPUT, @.t
> set @.t = '<EMP><EE EID=''1'' NAME=''ANAND'' /><EE EID=''2''
> NAME=''SAGAR'' /></EMP>'
> select @.t
> BEGIN TRANSACTION
> select * FROM
> OPENXML(@.XMLDocPointer1,'/EMP/EE')
> WITH (EID VARCHAR(2), ENAME VARCHAR(30))
> EXEC sp_xml_removedocument @.XMLDocPointer1
> COMMIT
> ----
|||Hello gapokrif@.gmail.com,
Well spotted
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
[vbcol=seagreen]
> You must assign value to @.t variable before sp_xml_preparedocument
> call.
> On Jul 16, 1:04 pm, Sagar <anandsa...@.gmail.com> wrote:
declare @.t varchar(1000)
DECLARE @.XMLDocPointer1 INT
EXEC sp_xml_preparedocument @.XMLDocPointer1 OUTPUT, @.t
set @.t = '<EMP><EE EID=''1'' NAME=''ANAND'' /><EE EID=''2''
NAME=''SAGAR'' /></EMP>'
select @.t
BEGIN TRANSACTION
select * FROM
OPENXML(@.XMLDocPointer1,'/EMP/EE')
WITH (EID VARCHAR(2), ENAME VARCHAR(30))
EXEC sp_xml_removedocument @.XMLDocPointer1
COMMIT
You must assign value to @.t variable before sp_xml_preparedocument
call.
On Jul 16, 1:04 pm, Sagar <anandsa...@.gmail.com> wrote:
> The following script doesnt return any records. Why ?
> ----
> declare @.t varchar(1000)
> DECLARE @.XMLDocPointer1 INT
> EXEC sp_xml_preparedocument @.XMLDocPointer1 OUTPUT, @.t
> set @.t = '<EMP><EE EID=''1'' NAME=''ANAND'' /><EE EID=''2''
> NAME=''SAGAR'' /></EMP>'
> select @.t
> BEGIN TRANSACTION
> select * FROM
> OPENXML(@.XMLDocPointer1,'/EMP/EE')
> WITH (EID VARCHAR(2), ENAME VARCHAR(30))
> EXEC sp_xml_removedocument @.XMLDocPointer1
> COMMIT
> ----
|||Hello gapokrif@.gmail.com,
Well spotted
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
[vbcol=seagreen]
> You must assign value to @.t variable before sp_xml_preparedocument
> call.
> On Jul 16, 1:04 pm, Sagar <anandsa...@.gmail.com> wrote:
Friday, February 24, 2012
Help me to understand this SQL sentence
Set @.mSQL = 'SELECT Max([AccountID] + [ItemID] + [StorehouseID] + [BINID] + [LotItemID] + Convert(varchar(10),[BalDate],111)) AS [KEY],
AccountID, ItemID, StorehouseID, BINID, LotItemID INTO [xIV_tblStockSumLastDate' + ']
FROM IV_tblIVMaster
WHERE (BalDate<= 'Exec(@.mSQL + '''' + @.mtxtDate + '''' + ')
GROUP BY ItemID, AccountID, StorehouseID, BINID, LotItemID')what is that sql sentence supposed to be doing?
it looks like it's trying to be recursive|||If we are trying to get some value into the variable , then this query is no good.
"Set @.mSQL = 'SELECT Max([AccountID] "
should actuallu read
"SELECT @.mSQL = Max([AccountID] "
Hope this helps...|||First we must identify the subject, then the verb, and if they exist, the direct object and the indirect object....Oh, sorry.
Any ideas what the value of @.mSQL was before this assignment? Maybe the original programmer was trying to reduce the number of variables he had? (OK, I am reaching, there)|||It looks to me like the code is creating a "Superkey", a concatenation of multiple natural values to fabricate a single unique semi-surrogate key.
Superkeys are database abominations frequently found in legacy systems or in applications created by noob developers.|||Hi all,
The original procedure as follow
--Repaired 03/11/2005
CREATE Procedure IV_spStockReportSummary
(
@.mName Varchar(50),
@.mtxtDate DateTime,
@.moptName TinyInt,
@.mchkReport Bit
)
As
Declare @.mSQL Varchar(3000)
Set @.mSQL = 'SELECT Max([AccountID] + [ItemID] + [StorehouseID] + [BINID] + [LotItemID] + Convert(varchar(10),[BalDate],111)) AS [KEY],
AccountID, ItemID, StorehouseID, BINID, LotItemID INTO [xIV_tblStockSumLastDate' + @.mName + ']
FROM IV_tblIVMaster
WHERE (BalDate<= '
Exec(@.mSQL + '''' + @.mtxtDate + '''' + ')
GROUP BY ItemID, AccountID, StorehouseID, BINID, LotItemID')
If @.mchkReport=0
Begin
Set @.mSQL='SELECT LD.AccountID, (Case When '
Exec (@.mSQL + '' + @.moptName + '' + '=1 Then C.AccountName Else C.AccountName_Secn End) AS AccountName,
LD.StorehouseID, (Case When ' + '' + @.moptName + '' + '=1 Then S.StoreHouseName Else S.StoreHouseName_Secn End) AS StoreHouseName,
I.CategoryID, (Case When ' + '' + @.moptName + '' + '=1 Then CI.CategoryName Else CI.CategoryName_Secn End) AS CategoryName,
LD.ItemID, (Case When ' + '' + @.moptName + '' + '=1 Then I.ItemName Else I.ItemName_Secn End) AS ItemName,
(Case When ' + '' + @.moptName + '' + '=1 Then U.UMName Else U.UMName_Secn End) AS Unit, L.LotNo, L.ExpireDate, SUM(MC.BeginUnit) AS OnHand, SUM(MC.BeginTotal) AS Amount, Convert(Varchar(10), Null) AS txtGrp INTO [xIV_tblStockSummaryTmp' + @.mName + ']
FROM IV_tblItemList I INNER JOIN CF_tblChartAcct C INNER JOIN [xIV_tblStockSumLastDate' + @.mName + '] LD INNER JOIN
IV_viewIVMasterCalc MC ON LD.[KEY] = MC.[Key] AND LD.AccountID = MC.AssetAcctID AND LD.ItemID = MC.ItemID AND
LD.StorehouseID = MC.StorehouseID AND LD.BINID = MC.BINID AND LD.LotItemID = MC.LotItemID ON C.AccountID = LD.AccountID ON I.ItemID = LD.ItemID INNER JOIN IV_tblUnitOfMeasureList U ON
I.InvUnitOfMeasr = U.UMID INNER JOIN IV_tblCategoryList CI ON I.CategoryID = CI.CategoryID INNER JOIN IV_tblStoreHouseList S ON
LD.StorehouseID = S.StoreHouseID LEFT JOIN IV_tblLotNumbers L ON LD.LotItemID = L.LotItemID
GROUP BY LD.AccountID, (Case When ' + '' + @.moptName + '' + '=1 Then C.AccountName Else C.AccountName_Secn End), LD.StorehouseID, (Case When ' + '' + @.moptName + '' + '=1 Then S.StoreHouseName Else S.StoreHouseName_Secn End),
I.CategoryID, (Case When ' + '' + @.moptName + '' + '=1 Then CI.CategoryName Else CI.CategoryName_Secn End),
LD.ItemID, (Case When ' + '' + @.moptName + '' + '=1 Then I.ItemName Else I.ItemName_Secn End), (Case When ' + '' + @.moptName + '' + '=1 Then U.UMName Else U.UMName_Secn End), L.LotNo, L.ExpireDate
HAVING (SUM(MC.BeginUnit) <> 0) OR (SUM(MC.BeginTotal) <> 0)')
End
Else
Begin
Set @.mSQL='SELECT LD.AccountID, (Case When '
Exec (@.mSQL + '' + @.moptName + '' + '=1 Then C.AccountName Else C.AccountName_Secn End) AS AccountName,
LD.StorehouseID, (Case When ' + '' + @.moptName + '' + '=1 Then S.StoreHouseName Else S.StoreHouseName_Secn End) AS StoreHouseName,
LD.ItemID, L.LotNo, L.ExpireDate, SUM(MC.BeginUnit) AS OnHand, SUM(MC.BeginTotal) AS Amount INTO [xIV_tblStockSumTmp' + @.mName + ']
FROM CF_tblChartAcct C INNER JOIN [xIV_tblStockSumLastDate' + @.mName + '] LD INNER JOIN IV_viewIVMasterCalc MC ON LD.[KEY] = MC.[Key] AND
LD.AccountID = MC.AssetAcctID AND LD.ItemID = MC.ItemID AND LD.StorehouseID = MC.StorehouseID AND LD.BINID = MC.BINID AND LD.LotItemID = MC.LotItemID ON
C.AccountID = LD.AccountID INNER JOIN IV_tblStoreHouseList S ON LD.StorehouseID = S.StoreHouseID LEFT JOIN IV_tblLotNumbers L ON LD.LotItemID = L.LotItemID
GROUP BY LD.AccountID, (Case When ' + '' + @.moptName + '' + '=1 Then C.AccountName Else C.AccountName_Secn End),
LD.StorehouseID, (Case When ' + '' + @.moptName + '' + '=1 Then S.StoreHouseName Else S.StoreHouseName_Secn End),
LD.ItemID, L.LotNo, L.ExpireDate
HAVING (SUM(MC.BeginUnit) <> 0) OR (SUM(MC.BeginTotal) <> 0)')
Set @.mSQL = 'SELECT S.AccountID, S.AccountName, S.StorehouseID, S.StoreHouseName, S.ItemID, (Case When '
Exec (@.mSQL + '' + @.moptName + '' + '=1 Then I.ItemName Else I.ItemName_Secn End) AS ItemName,
(Case When ' + '' + @.moptName + '' + '=1 Then U1.UMName Else U1.UMName_Secn End) AS Unit,
I.CategoryID, (Case When ' + '' + @.moptName + '' + '=1 Then C.CategoryName Else C.CategoryName_Secn End) AS CategoryName, S.LotNo, S.ExpireDate,
(S.OnHand * (Case When V.ConvFactor IS Null Then 1 Else V.ConvFactor End)) AS OnHand, S.Amount, Convert(Varchar(10), Null) AS txtGrp INTO [xIV_tblStockSummaryTmp' + @.mName + ']
FROM IV_tblUnitOfMeasureList U1 LEFT JOIN IV_tblUMConversion V ON U1.UMID = V.UMToID RIGHT JOIN IV_tblUnitOfMeasureList U ON
V.UMFromID = U.UMID RIGHT JOIN IV_tblItemList I ON U1.UMID = I.PrintUnitOfMeasr AND U.UMID = I.InvUnitOfMeasr LEFT JOIN
IV_tblCategoryList C ON I.CategoryID = C.CategoryID RIGHT JOIN [xIV_tblStockSumTmp' + @.mName + '] S ON I.ItemID = S.ItemID')
End
Return
GO|||i can't believe that runs
and the guy that wrote it should be shot
it no longer looks like it's trying to be recursive
but there's a dangling ) after the first Exec, just before If @.mchkReport=0|||i can't believe that runs
...
but there's a dangling ) after the first Exec, just before If @.mchkReport=0Nor me. There look to be a lot of dangly things.
dangquanghai - are you saying that this actually works?
--Repaired 03/11/2005
Doesn't look like it from here|||I m sure It run smoothly.|||I can give you the examble from SQL server book online
C. Use EXECUTE 'tsql_string' with a variable
This example shows how EXECUTE handles dynamically built strings containing variables. This example creates the tables_cursor cursor to hold a list of all user-defined tables (type = U).
Note This example is shown for illustrative purposes only.
DECLARE tables_cursor CURSOR
FOR
SELECT name FROM sysobjects WHERE type = 'U'
OPEN tables_cursor
DECLARE @.tablename sysname
FETCH NEXT FROM tables_cursor INTO @.tablename
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
/* A @.@.FETCH_STATUS of -2 means that the row has been deleted.
There is no need to test for this because this loop drops all
user-defined tables. */.
EXEC ('DROP TABLE ' + @.tablename)
FETCH NEXT FROM tables_cursor INTO @.tablename
END
PRINT 'All user-defined tables have been dropped from the database.'
DEALLOCATE tables_cursor|||Uh-Oh... you said the forbidden word... "cursor"...|||Oh sorry friends !!!
this sentence
Set @.mSQL = 'SELECT Max([AccountID] + [ItemID] + [StorehouseID] + [BINID] + [LotItemID] + Convert(varchar(10),[BalDate],111)) AS [KEY],
AccountID, ItemID, StorehouseID, BINID, LotItemID INTO [xIV_tblStockSumLastDate' + ']
FROM IV_tblIVMaster
WHERE (BalDate<= 'Exec(@.mSQL + '''' + @.mtxtDate + '''' + ')
GROUP BY ItemID, AccountID, StorehouseID, BINID, LotItemID')
contain two sentences
1. Set @.mSQL = 'SELECT Max([AccountID] + [ItemID] + [StorehouseID] + [BINID] + [LotItemID] + Convert(varchar(10),[BalDate],111)) AS [KEY],
AccountID, ItemID, StorehouseID, BINID, LotItemID INTO [xIV_tblStockSumLastDate' + ']
FROM IV_tblIVMaster
WHERE (BalDate<= '
2.'Exec(@.mSQL + '''' + @.mtxtDate + '''' + ')
GROUP BY ItemID, AccountID, StorehouseID, BINID, LotItemID')
The coder typed it at the same row so it make me confuse
Now, It is so clear
Thank for your consideration|||Uh-Oh... you said the forbidden word... "cursor"...I think that was to explain to us what EXEC does :)|||Ah. So that is what EXEC does. I had no idea. Apparently it is a convenient method for f***ing up an application. The posted code demonstrates it clearly.
AccountID, ItemID, StorehouseID, BINID, LotItemID INTO [xIV_tblStockSumLastDate' + ']
FROM IV_tblIVMaster
WHERE (BalDate<= 'Exec(@.mSQL + '''' + @.mtxtDate + '''' + ')
GROUP BY ItemID, AccountID, StorehouseID, BINID, LotItemID')what is that sql sentence supposed to be doing?
it looks like it's trying to be recursive|||If we are trying to get some value into the variable , then this query is no good.
"Set @.mSQL = 'SELECT Max([AccountID] "
should actuallu read
"SELECT @.mSQL = Max([AccountID] "
Hope this helps...|||First we must identify the subject, then the verb, and if they exist, the direct object and the indirect object....Oh, sorry.
Any ideas what the value of @.mSQL was before this assignment? Maybe the original programmer was trying to reduce the number of variables he had? (OK, I am reaching, there)|||It looks to me like the code is creating a "Superkey", a concatenation of multiple natural values to fabricate a single unique semi-surrogate key.
Superkeys are database abominations frequently found in legacy systems or in applications created by noob developers.|||Hi all,
The original procedure as follow
--Repaired 03/11/2005
CREATE Procedure IV_spStockReportSummary
(
@.mName Varchar(50),
@.mtxtDate DateTime,
@.moptName TinyInt,
@.mchkReport Bit
)
As
Declare @.mSQL Varchar(3000)
Set @.mSQL = 'SELECT Max([AccountID] + [ItemID] + [StorehouseID] + [BINID] + [LotItemID] + Convert(varchar(10),[BalDate],111)) AS [KEY],
AccountID, ItemID, StorehouseID, BINID, LotItemID INTO [xIV_tblStockSumLastDate' + @.mName + ']
FROM IV_tblIVMaster
WHERE (BalDate<= '
Exec(@.mSQL + '''' + @.mtxtDate + '''' + ')
GROUP BY ItemID, AccountID, StorehouseID, BINID, LotItemID')
If @.mchkReport=0
Begin
Set @.mSQL='SELECT LD.AccountID, (Case When '
Exec (@.mSQL + '' + @.moptName + '' + '=1 Then C.AccountName Else C.AccountName_Secn End) AS AccountName,
LD.StorehouseID, (Case When ' + '' + @.moptName + '' + '=1 Then S.StoreHouseName Else S.StoreHouseName_Secn End) AS StoreHouseName,
I.CategoryID, (Case When ' + '' + @.moptName + '' + '=1 Then CI.CategoryName Else CI.CategoryName_Secn End) AS CategoryName,
LD.ItemID, (Case When ' + '' + @.moptName + '' + '=1 Then I.ItemName Else I.ItemName_Secn End) AS ItemName,
(Case When ' + '' + @.moptName + '' + '=1 Then U.UMName Else U.UMName_Secn End) AS Unit, L.LotNo, L.ExpireDate, SUM(MC.BeginUnit) AS OnHand, SUM(MC.BeginTotal) AS Amount, Convert(Varchar(10), Null) AS txtGrp INTO [xIV_tblStockSummaryTmp' + @.mName + ']
FROM IV_tblItemList I INNER JOIN CF_tblChartAcct C INNER JOIN [xIV_tblStockSumLastDate' + @.mName + '] LD INNER JOIN
IV_viewIVMasterCalc MC ON LD.[KEY] = MC.[Key] AND LD.AccountID = MC.AssetAcctID AND LD.ItemID = MC.ItemID AND
LD.StorehouseID = MC.StorehouseID AND LD.BINID = MC.BINID AND LD.LotItemID = MC.LotItemID ON C.AccountID = LD.AccountID ON I.ItemID = LD.ItemID INNER JOIN IV_tblUnitOfMeasureList U ON
I.InvUnitOfMeasr = U.UMID INNER JOIN IV_tblCategoryList CI ON I.CategoryID = CI.CategoryID INNER JOIN IV_tblStoreHouseList S ON
LD.StorehouseID = S.StoreHouseID LEFT JOIN IV_tblLotNumbers L ON LD.LotItemID = L.LotItemID
GROUP BY LD.AccountID, (Case When ' + '' + @.moptName + '' + '=1 Then C.AccountName Else C.AccountName_Secn End), LD.StorehouseID, (Case When ' + '' + @.moptName + '' + '=1 Then S.StoreHouseName Else S.StoreHouseName_Secn End),
I.CategoryID, (Case When ' + '' + @.moptName + '' + '=1 Then CI.CategoryName Else CI.CategoryName_Secn End),
LD.ItemID, (Case When ' + '' + @.moptName + '' + '=1 Then I.ItemName Else I.ItemName_Secn End), (Case When ' + '' + @.moptName + '' + '=1 Then U.UMName Else U.UMName_Secn End), L.LotNo, L.ExpireDate
HAVING (SUM(MC.BeginUnit) <> 0) OR (SUM(MC.BeginTotal) <> 0)')
End
Else
Begin
Set @.mSQL='SELECT LD.AccountID, (Case When '
Exec (@.mSQL + '' + @.moptName + '' + '=1 Then C.AccountName Else C.AccountName_Secn End) AS AccountName,
LD.StorehouseID, (Case When ' + '' + @.moptName + '' + '=1 Then S.StoreHouseName Else S.StoreHouseName_Secn End) AS StoreHouseName,
LD.ItemID, L.LotNo, L.ExpireDate, SUM(MC.BeginUnit) AS OnHand, SUM(MC.BeginTotal) AS Amount INTO [xIV_tblStockSumTmp' + @.mName + ']
FROM CF_tblChartAcct C INNER JOIN [xIV_tblStockSumLastDate' + @.mName + '] LD INNER JOIN IV_viewIVMasterCalc MC ON LD.[KEY] = MC.[Key] AND
LD.AccountID = MC.AssetAcctID AND LD.ItemID = MC.ItemID AND LD.StorehouseID = MC.StorehouseID AND LD.BINID = MC.BINID AND LD.LotItemID = MC.LotItemID ON
C.AccountID = LD.AccountID INNER JOIN IV_tblStoreHouseList S ON LD.StorehouseID = S.StoreHouseID LEFT JOIN IV_tblLotNumbers L ON LD.LotItemID = L.LotItemID
GROUP BY LD.AccountID, (Case When ' + '' + @.moptName + '' + '=1 Then C.AccountName Else C.AccountName_Secn End),
LD.StorehouseID, (Case When ' + '' + @.moptName + '' + '=1 Then S.StoreHouseName Else S.StoreHouseName_Secn End),
LD.ItemID, L.LotNo, L.ExpireDate
HAVING (SUM(MC.BeginUnit) <> 0) OR (SUM(MC.BeginTotal) <> 0)')
Set @.mSQL = 'SELECT S.AccountID, S.AccountName, S.StorehouseID, S.StoreHouseName, S.ItemID, (Case When '
Exec (@.mSQL + '' + @.moptName + '' + '=1 Then I.ItemName Else I.ItemName_Secn End) AS ItemName,
(Case When ' + '' + @.moptName + '' + '=1 Then U1.UMName Else U1.UMName_Secn End) AS Unit,
I.CategoryID, (Case When ' + '' + @.moptName + '' + '=1 Then C.CategoryName Else C.CategoryName_Secn End) AS CategoryName, S.LotNo, S.ExpireDate,
(S.OnHand * (Case When V.ConvFactor IS Null Then 1 Else V.ConvFactor End)) AS OnHand, S.Amount, Convert(Varchar(10), Null) AS txtGrp INTO [xIV_tblStockSummaryTmp' + @.mName + ']
FROM IV_tblUnitOfMeasureList U1 LEFT JOIN IV_tblUMConversion V ON U1.UMID = V.UMToID RIGHT JOIN IV_tblUnitOfMeasureList U ON
V.UMFromID = U.UMID RIGHT JOIN IV_tblItemList I ON U1.UMID = I.PrintUnitOfMeasr AND U.UMID = I.InvUnitOfMeasr LEFT JOIN
IV_tblCategoryList C ON I.CategoryID = C.CategoryID RIGHT JOIN [xIV_tblStockSumTmp' + @.mName + '] S ON I.ItemID = S.ItemID')
End
Return
GO|||i can't believe that runs
and the guy that wrote it should be shot
it no longer looks like it's trying to be recursive
but there's a dangling ) after the first Exec, just before If @.mchkReport=0|||i can't believe that runs
...
but there's a dangling ) after the first Exec, just before If @.mchkReport=0Nor me. There look to be a lot of dangly things.
dangquanghai - are you saying that this actually works?
--Repaired 03/11/2005
Doesn't look like it from here|||I m sure It run smoothly.|||I can give you the examble from SQL server book online
C. Use EXECUTE 'tsql_string' with a variable
This example shows how EXECUTE handles dynamically built strings containing variables. This example creates the tables_cursor cursor to hold a list of all user-defined tables (type = U).
Note This example is shown for illustrative purposes only.
DECLARE tables_cursor CURSOR
FOR
SELECT name FROM sysobjects WHERE type = 'U'
OPEN tables_cursor
DECLARE @.tablename sysname
FETCH NEXT FROM tables_cursor INTO @.tablename
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
/* A @.@.FETCH_STATUS of -2 means that the row has been deleted.
There is no need to test for this because this loop drops all
user-defined tables. */.
EXEC ('DROP TABLE ' + @.tablename)
FETCH NEXT FROM tables_cursor INTO @.tablename
END
PRINT 'All user-defined tables have been dropped from the database.'
DEALLOCATE tables_cursor|||Uh-Oh... you said the forbidden word... "cursor"...|||Oh sorry friends !!!
this sentence
Set @.mSQL = 'SELECT Max([AccountID] + [ItemID] + [StorehouseID] + [BINID] + [LotItemID] + Convert(varchar(10),[BalDate],111)) AS [KEY],
AccountID, ItemID, StorehouseID, BINID, LotItemID INTO [xIV_tblStockSumLastDate' + ']
FROM IV_tblIVMaster
WHERE (BalDate<= 'Exec(@.mSQL + '''' + @.mtxtDate + '''' + ')
GROUP BY ItemID, AccountID, StorehouseID, BINID, LotItemID')
contain two sentences
1. Set @.mSQL = 'SELECT Max([AccountID] + [ItemID] + [StorehouseID] + [BINID] + [LotItemID] + Convert(varchar(10),[BalDate],111)) AS [KEY],
AccountID, ItemID, StorehouseID, BINID, LotItemID INTO [xIV_tblStockSumLastDate' + ']
FROM IV_tblIVMaster
WHERE (BalDate<= '
2.'Exec(@.mSQL + '''' + @.mtxtDate + '''' + ')
GROUP BY ItemID, AccountID, StorehouseID, BINID, LotItemID')
The coder typed it at the same row so it make me confuse
Now, It is so clear
Thank for your consideration|||Uh-Oh... you said the forbidden word... "cursor"...I think that was to explain to us what EXEC does :)|||Ah. So that is what EXEC does. I had no idea. Apparently it is a convenient method for f***ing up an application. The posted code demonstrates it clearly.
Sunday, February 19, 2012
help me out for storing more data
CREATE TABLE [dbo].[blogs] (
[blog_title] [nvarchar] (500) ,
[blog_desc_full] [varchar] (8000) ,
[blogger_name] [nvarchar] (100) ,
[mailid] [nvarchar] (100) ,
[blogid] [numeric](10, 0) IDENTITY (1, 1) NOT NULL ,
[blog_desc] [nvarchar] (125) ,
[cat_name] [varchar] (100) ,
[b_url] [varchar] (250) ,
[b_date] [datetime] NULL ,
[author] [nvarchar] (250) ,
[approval] [char] (1)
) ON [PRIMARY]
GO
using this script i have created my blog table.
and a procedure given below. i am using to insert data in it.
CREATE PROCEDURE SP_BlogAdd
@.blog_title nvarchar(500),
@.blog_desc_full varchar(8000),
@.blogger_name nvarchar(100),
@.mailid nvarchar(100),
@.blog_desc nvarchar(125),
@.cat_name varchar(100),
@.b_url varchar(250),
@.author nvarchar(250)
AS
INSERT INTO blogs(blog_title, blog_desc_full, blogger_name, mailid, blog_desc, cat_name, b_url,author)
VALUES(@.blog_title ,@.blog_desc_full ,@.blogger_name,@.mailid ,@.blog_desc ,@.cat_name ,@.b_url,@.author)
GO
now, the problem i m facing is.
i am using varchar datatype for [blog_desc_full] [varchar] (8000).
i want more than this size to store data in it.
please give me some detailed code.
i tried text datatype but i didnt succeed. how to use text datatype.
i replaced with text datatype.but in length i couldnt type. it shows only 16.
Please help me out.
regards,
ASIFhttp://www.dbforums.com/showthread.php?t=1605270
[blog_title] [nvarchar] (500) ,
[blog_desc_full] [varchar] (8000) ,
[blogger_name] [nvarchar] (100) ,
[mailid] [nvarchar] (100) ,
[blogid] [numeric](10, 0) IDENTITY (1, 1) NOT NULL ,
[blog_desc] [nvarchar] (125) ,
[cat_name] [varchar] (100) ,
[b_url] [varchar] (250) ,
[b_date] [datetime] NULL ,
[author] [nvarchar] (250) ,
[approval] [char] (1)
) ON [PRIMARY]
GO
using this script i have created my blog table.
and a procedure given below. i am using to insert data in it.
CREATE PROCEDURE SP_BlogAdd
@.blog_title nvarchar(500),
@.blog_desc_full varchar(8000),
@.blogger_name nvarchar(100),
@.mailid nvarchar(100),
@.blog_desc nvarchar(125),
@.cat_name varchar(100),
@.b_url varchar(250),
@.author nvarchar(250)
AS
INSERT INTO blogs(blog_title, blog_desc_full, blogger_name, mailid, blog_desc, cat_name, b_url,author)
VALUES(@.blog_title ,@.blog_desc_full ,@.blogger_name,@.mailid ,@.blog_desc ,@.cat_name ,@.b_url,@.author)
GO
now, the problem i m facing is.
i am using varchar datatype for [blog_desc_full] [varchar] (8000).
i want more than this size to store data in it.
please give me some detailed code.
i tried text datatype but i didnt succeed. how to use text datatype.
i replaced with text datatype.but in length i couldnt type. it shows only 16.
Please help me out.
regards,
ASIFhttp://www.dbforums.com/showthread.php?t=1605270
help me out for storing more data
CREATE TABLE [dbo].[blogs] (
[blog_title] [nvarchar] (500) ,
[blog_desc_full] [varchar] (8000) ,
[blogger_name] [nvarchar] (100) ,
[mailid] [nvarchar] (100) ,
[blogid] [numeric](10, 0) IDENTITY (1, 1) NOT NULL ,
[blog_desc] [nvarchar] (125) ,
[cat_name] [varchar] (100) ,
[b_url] [varchar] (250) ,
[b_date] [datetime] NULL ,
[author] [nvarchar] (250) ,
[approval] [char] (1)
) ON [PRIMARY]
GO
[blog_title] [nvarchar] (500) ,
[blog_desc_full] [varchar] (8000) ,
[blogger_name] [nvarchar] (100) ,
[mailid] [nvarchar] (100) ,
[blogid] [numeric](10, 0) IDENTITY (1, 1) NOT NULL ,
[blog_desc] [nvarchar] (125) ,
[cat_name] [varchar] (100) ,
[b_url] [varchar] (250) ,
[b_date] [datetime] NULL ,
[author] [nvarchar] (250) ,
[approval] [char] (1)
) ON [PRIMARY]
GO
using this script i have created my blog table.
and a procedure given below. i am using to insert data in it.
CREATE PROCEDURE SP_BlogAdd
@.blog_title nvarchar(500),
@.blog_desc_full varchar(8000),
@.blogger_name nvarchar(100),
@.mailid nvarchar(100),
@.blog_desc nvarchar(125),
@.cat_name varchar(100),
@.b_url varchar(250),
@.author nvarchar(250)
AS
INSERT INTO blogs(blog_title, blog_desc_full, blogger_name, mailid, blog_desc, cat_name, b_url,author)
VALUES(@.blog_title ,@.blog_desc_full ,@.blogger_name,@.mailid ,@.blog_desc ,@.cat_name ,@.b_url,@.author)
GO
now, the problem i m facing is.
i am using varchar datatype for [blog_desc_full] [varchar] (8000).
i want more than this size to store data in it.
please give me some detailed code.
i tried text datatype but i didnt succeed. how to use text datatype.
i replaced with text datatype.but in length i couldnt type. it shows only 16.
Please help me out.
regards,
ASIF
if you are on SQL Server 2005, you can use the VARCHAR(MAX) Type, which will allow you to store up to 2GB of data and use all string and aggregation operations on it, other like the TEXT datatype. Anyway, if you are on SQL Server 200 you have to keep in mind, that you cannot store more than 8096 in one row, therefore you can′t create the table above without having pro′blems while inserting more than 8046 bytes of data. Most people either scaled tables out for this cases in order to split up the data to more than one table referencing them with a 1:1 relation OR, like in your cases used the TEXT type for the columns. You can′t modify the length column, because the length of the TEXT column is 2GB having a pointer stored in the tables with the size of 16Bytes. So anything you put in, can be up to the size of 2GB.
Subscribe to:
Posts (Atom)