Transact SQL :: How To Use GROUPING SETS
Jul 12, 2015
I was working on what I was told was SQL 2012 and it turns out it is SQL 2005. I wrote two procs that I need to convert to 2005. Here is the code:
SELECT
era_provider_name AS Provider,
RIGHT([era_upi], 5) AS 'ERA Upi',
[era_fy] AS 'ERA FY',
ProcGrp,
COUNT(DISTINCT UCI) AS 'Client Count',
[Code]....
I'm not finding an efficient way to do this. I cannot use GROUPING SETS with 2005. Here is the code for the second proc:
SELECT
CASE
WHEN GROUPING(era_provider_name) = 1 THEN 'TOTALS'
ELSE era_provider_name
END AS era_provider_name,
CASE WHEN GROUPING(era_fy) = 1 THEN 'TOTALFY'
[Code] ...
The results as in SQL 2012 are exactly as I would like them. I want to mimic those results in 2005.
View 6 Replies
ADVERTISEMENT
Jul 8, 2015
I needed to add in the Fiscal Year (FY) to group my results by FY. I changed my code to the following:
/*
EXEC ADAMHsp_BSS_GetNonMedicaidReportTotals
@pstrProviderName = 'FAM SER-WOOD'
*/
ALTER PROCEDURE [dbo].[ADAMHsp_BSS_GetNonMedicaidReportTotals]
@pstrProviderNameVARCHAR(100)
AS
BEGIN
[Code] ....
See my result set in the picture below. The rows with NULL in the 'ProcGrp' column have the totals of the groupings by FY that I am looking for - that's great. What I want to do now is have another row that contains the sums of the values from any row where 'ProcGrp' is null so that I have a totals row.
View 3 Replies
View Related
Jun 15, 2015
How do you incorporate a case statement so that you can add "sub total" and grand total" to each grouping set section? Trying to see how to incorporate case.
[URL] ....
SELECT
CustomerID,
SalesPersonID,
YEAR(OrderDate) AS 'OrderYear',
SUM(TotalDue) AS 'TotalDue'
FROM Sales.SalesOrderHeader
[Code] .....
View 4 Replies
View Related
Jun 19, 2013
I have been tasked with creating a report that shows sales of our products grouped into buckets of 5 each, DESC. I have a table that has the itemNo and revenue. The final report would be something like:
Top 5 Spreads$695,066
Next 5 Spreads$467,845
Next 5 Spreads$416,946
Next 5 Spreads$361,946
Next 5 Spreads$305,607
Next 5 Spreads$270,567
Bottom Spreads$15,954
My initial thinking was to use row_number() and partition to label the rows in groups of 5 like:
item 130,0001
item 229,0001
item 328,0001
item 427,0001
item 526,0001
item 625,0002
item 724,0002
item 823,0002
item 922,0002
item 1021,0002
item 1120,0003
item 1219,0003
item 1318,0003
And then Sum the revenue, grouping by this row_number. But I haven't been able to get it working right.
View 13 Replies
View Related
Jan 24, 2007
Hi folks. Hope all the gurus including Brett,Pat Phalen, RdjBarov, r937 are fine. ;)
been so long to ask stupid question. Here's the question and i really need help on this.
i have data that tracks patterns of bus stops from one point to another.
like point a, to point b, point b to point c forms one pattern.
point a to point c , point c to point b should be a different pattern.
create table #journeypatterns (patternid int ,points varchar(100))
go
insert #journeypatterns
select 1 ,'a' union all select 1 ,'b' union all select 1,'c'
union all select 2,'a' union all select 2,'c' union all select 2,'b'
union all select 3 ,'a' union all select 3 ,'b' union all select 3,'c'
select * from #journeypatterns
patternid points
1 a
1 b
1 c
2 a
2 c
2 b
3 a
3 b
3 c
what i want is to get unique pattern value of sequence of points by grouping on patternid. if the sequence of points change, i need a unique value against that pattern.
like for patternid 1, sequence of points a,bc for example should be abc.
for patternid 2, sequence of points a,c,b for example should be acb.
again patternid 3, sequence of points a,bc for example should be abc.
i tried CHECKSUM_AGG which brutally failed in production because the checksum values for each single point when summed produce SAME result for different patterns.
select checksum_agg(binary_checksum(points)) ,patternid
from #journeypatterns
group by patternid
961
962
963
here patternid 2 should be different because sequence is acb. i know checksum is not the right approach for what i need.
I NEED A GENERIC FUNCTION, that marks the pattern differences, my ultimate goal was to create a procedure, whom a patternid should be passed, and it would result the NEXT patternid in the table which has the SAME ORDER OF point sequences.
now folks, i can do this holding all data into a temp table and write a cursor to traverse through each patternid and concatenate the sequence of points.
BUT, using this approach is the ugliest, as it has slow down the process badly and boss is not happy with the performance. the table holds a lot of data.
I NEED a query rather than a cursor on the fly to resolve this.
Here's the query i am using to get the current sequence of a pattern and then i have to search all sequences similarly against it.
declare @patternid int
set @patternid =1
declare @sequence [varchar] (100)
declare @id varchar(10)
declare cr_sequence cursor fast_forward for select points from #journeypatterns where patternid=@patternid
open cr_sequence
fetch next from cr_sequence into @id
while @@fetch_status = 0
begin
select @sequence = isnull(@sequence,'')+@id
fetch next from cr_sequence into @id
end
print @sequence -- next i have code to find the similar sequence for another patternid.... which is not mentioned here but is similar
View 5 Replies
View Related
Sep 4, 2014
How to writing this query using rollup or grouping sets in two way?
data
shop time date sku discount% sales qty amount
A 13:00pm, 2014-feb-11 apple 30% 5 #20
A 13:00pm, 2014-feb-11 apple 30% 5 #20
A Shop Qty :10 Amt:40
B 23:00pm, 2014-feb-11 apple 30% 5 #20
B 23:00pm, 2014-feb-11 apple 30% 5 #20
B shop Qty :10 Amt $40
Grand total qty:20 , Amt $80
View 1 Replies
View Related
Aug 4, 2015
Periodically throughout the day a report is manually pulled from a SQL Server database. Is their a way w/o me adding a field to the database to have the result set return the "new" results? For example, lets say this is our DDL
Create Table OneTwoThree
(
id int
,date11 datetime
,firefly varchar(10)
)
Insert Into OneTwoThree Values (1, '08/03/2015 18:43:32.012', 'Hi'), (2, '08/03/2015 18:44:11.011', 'No'),
(3, '08/03/2015 19:36:33.011', 'Second'), (4, '08/03/2015 19:37:33.011', 'Alpha')
Prior I could use this syntax, but that was only with needing to generate 1 result set.
Select id, convert(varchar(10), date11, 101) As [Date], firefly from onetwothree
where CONVERT(varchar(10), date11, 101) < CONVERT(varchar(10), GetDate(), 101)
Looking at my datetime values, let's say the 1st was generated at 18:45, obviously the 1st two records in the table would be returned. And let's say a 2nd time I need to generate I want to exclude the 1st two entries as they have already been verified. How can I do such w/o adding a field to the table?
View 11 Replies
View Related
Oct 3, 2015
I have a table that have customer name and their bill amount
a) tblBilling
which shows records like below (Billing Dates are shown in Year-Month-Day format)
Invoice Customer BillingDate Amount Tax
--------------------------------------------------------------------------
1 ABC 20015-10-2 1000.00 500.00
2 DEF 20015-10-2 2000.00 1000.00
3 GHI 20015-10-2 1000.00 500.00
4 JKL 20015-10-3 5000.00 2500.00
5 MNO 20015-10-3 1500.00 750.00
6 PQR 20015-10-4 500.00 250.00
7 STU 20015-10-4 1000.00 500.00
8 VWX 20015-10-4 2500.00 1250.00
I want to perform a query that should SUM Amount and Tax Colums by date basis, so we could get the following result
BillingDate Amount Tax
--------------------------------------------------------------------------
20015-10-2 4000.00 2000.00
20015-10-3 6500.00 3250.00
20015-10-4 4000.00 2000.00
View 3 Replies
View Related
Nov 4, 2015
I have a table as below
ItemID ParentID
6 NULL
7 NULL
8 7
11 7
12 8
Here ParentID null means it does not have the parent its the master. I want result in below format.
ItemID ParentID
6 NULL
--No parent no child
7 NULL
--No parent but has child
8 7
--7 is the parent of 8
12 8
--8 is the parent
11 7
--7 is the parent
Basically it should be in parent id then child id after that child's childID , In a recursive way.
View 5 Replies
View Related
Jul 9, 2015
I have a table with couple of hundred thousand records, i have added a new column to the table named MD_Group.
Now i need this column populated, so that every 10,000 row set in that table gets a numeric (incremental number) assigned starting from 1.
So the first 10,000 rows will have MD_Group = 1, next 10,000 rows will have MD_Group = 2 , next 10K MD_Group = 3 etc.
For testing purpose here is a results table with total of 11 rows where we want data grouped every 3 rows.
MD_Group
CustomerNumber
AmountBilled
1
12
15243
1
1231234
15243
[code]....
-- Create Sample Table
Declare @GroupRelation_Test TABLE (
MD_Group [varchar](20),
CustomerNumber [varchar](20) ,
AmountBilled [varchar](20) ,
[code]....
-- Test data
INSERT INTO @GroupRelation_Test
( CustomerNumber, AmountBilled,MinAmounttBilled)
SELECT'12','15243','' UNION ALL
SELECT'1231234','15243','' UNION ALL
SELECT'463','15243','' UNION ALL
SELECT'442','15243','' UNION ALL
[code]....
View 22 Replies
View Related
Jun 23, 2015
I have a table with duration values for different machine states. I 'm trying have a sum of the duration value of each state ( the duration sum , was an earlier question).
declare
@tblDurations
TABLE
(StateName
nvarchar(30),Duration
nvarchar(30))
[code]...
I want the output to have sum of the duration ( from my function ), grouped by the state.So output should be :
Breaks 00:02:03:40
Meetings 00:00:01:50
Running 15:21:07:16
I think this can be done with windows functions, but how I don't know.
View 10 Replies
View Related
Nov 6, 2015
I have two tbles that have ItemName and their bill amount
a) tblLunch
which shows records like below
Invoice Item Amount
--------------------------------------------
1 COFFEE 1000.00
2 TEA 2000.00
3 ICE CREAM 1000.00
b) tblDinner
which shows records like below
Invoice Item Amount
------------------------------------------------------------
1 COFFEE 1000.00
2 TEA 2000.00
3 PASTA 1000.00
I want to perform a query that should SUM Amount Columns by Grouping the Item from both the tables, so we could get the following result
Item Amount
------------------------------
COFFEE 2000.00
TEA 4000.00
ICE CREAM 1000.00
PASTA 1000.00
View 3 Replies
View Related
Aug 9, 2015
In the below query, I can get the individual/single group by columns as well as multiple but I cannot control the order in which I would like to group by the data.
So lets say I want to group the data by OS->browser->browser_version(just one example) then I cannot achieve that as the order of OS column comes later in the query.
I know one option would be to write a dynamic SQL but i dont want to do that because of performance reasons. Any other way this can be achieved?
select
case when @include_browser =
1 then browser_name end as browser_name,
case when @include_browser_version
= 1 then browser_version end
as browser_version,
[Code] ....
View 4 Replies
View Related
Dec 5, 2015
I have a table like this.
AS-IS
Column1 Column2 count
a b 20
a b 25
c d 12
c d 22
And I need to update the same as below.
TO-BE
Column1 Column2 count
a b 45
c d 34
How to do it?
View 4 Replies
View Related
Jul 29, 2015
I am trying to generate XML path from a SQL Server Table. Table Structure and sample data as below
CREATE TABLE #OfferTransaction
( [OfferLoanAmount1] INT
,[offferid1ProgramName] VARCHAR(100)
,[Offer1LenderName] VARCHAR(100)
,[offerid1LenderNMLSID] INT
[code]....
what changes do I need in my query so that the XML looks like the one above ( DESIRED XML). Is it possible via query changes?
View 3 Replies
View Related
Nov 5, 2015
Below is the data I have in table name
TeamStatus
T 1 Complete or Escalate
T 2 Pick Up
T 2 Resolve Case
T 1 Pick Up
T 1 Complete or Escalate
T 1 Pick Up
T 1 Complete or Escalate
I want to get he group based of Resolve Case value in Status Column. Anything before Resolve case will be considered as Group 1 and after Resolve Case status should be considered as Group 2. Below is desired new Group column,
Group TeamStatus
Group 1 T 1Complete or Escalate
T 2 Pick Up
T 2 Resolve Case
Group 2 T 1Pick Up
T 1Complete or Escalate
T 1 Pick Up
T 1 Complete or Escalate
View 7 Replies
View Related
Sep 23, 2015
I've unpivoted some data and stored it in a temp table variable
idNumFreqDtFreqrn
16100120120101M2
16100120120101M3
16100120100101M4
16100120100101M5
16100120060101M6
16100120000929Q7
16100119990101A8
16100119970101M9
Using the above data, if two rows have the same FreqDt, I want to see the record with the lowest row number.
So it should look like the below
idNumFreqDtFreqrn
16100120120101M2
16100120100101M4
16100120060101M6
16100120000929Q7
16100119990101A8
16100119970101M9
I've used the below code to accomplish it
SELECT DISTINCT
CASE WHEN t2.idNum IS NULL THEN t1.idNum ELSE t2.idNum END,
CASE WHEN t2.FreqDt IS NULL THEN T1.FreqDt else t2.FreqDt END,
CASE WHEN t2.freq is null then t1.freq else t2.freq end
FROM @tmptbl as t1 LEFT JOIN @tmptbl as t2
ON t1.idNum = T2.idNum
AND t1.FreqDt = t2.FreqDt
AND t1.rn = (t2.rn-1)
After all this, I'm supposed to condense the result set to only include sequential frequency dates with unique frequencies.should look like below (this is where I'm stuck)
idNumFreqDtFreq
16100120060101M
16100120000929Q
16100119990101A
16100119970101M
answer is below:
SELECT T1.*
FROM @t as t1 LEFT JOIN @t as t2
ON t1.idnum = T2.idnum
AND t1.freq = t2.freq
AND t1.rn = (t2.rn-1)
WHERE t2.idnum IS NULL
View 5 Replies
View Related
Nov 18, 2015
I tried to ask a similar question yesterday and got shot down, so I'll try again in a different way. I have been looking online at the gaps and islands approach, and it seems to always be referencing a singular field, so i can't find anything which is clear to get my head around it.In the context of a hotel (people checking in and out) I would like to identify how long someone has been staying at the hotel (The Island?) regardless if they checked out and back in the following day.
Data example:
DECLARE @LengthOfStay TABLE
(
PersonVARCHAR(8) NOT NULL,
CheckInDATE NOT NULL,
CheckOutDATE NULL
[code]...
View 7 Replies
View Related
Nov 26, 2007
I'm really stumped on this one. I'm a self taught SQL guy, so there is probobly something I'm overlooking.
I'm trying to get information like this in to a report:
WO#
-WO Line #
--(Details)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Detail #etc
WO#
-WO Line #
--(Details)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Parts #etc
I'm unable to get the grouping right on this. Since the line details and line parts both are children of the line #, how do you do "parallel groups"?
There are 4 tables:
Work Order Header
Work Order Line
Work Order Line Details
Work Order Line Requisitions
The Header has a unique PK.
The Line uses the Header and a Line # as foreign keys that together are unique.
The Detail and requisition tables use the header and line #'s in addition to their own line number foreign keys. My queries ends up looking like this:
WO WOL WOLR WOLD
226952 10000 10000 10000
226952 10000 10000 20000
226952 10000 10000 30000
226952 10000 10000 40000
226952 10000 20000 10000
226952 10000 20000 20000
226952 10000 20000 30000
226952 10000 20000 40000
399999 10000 NULL 10000
375654 10000 10000 NULL
etc
Hierarchy:
WO > WOL > WOLD
WO > WOL > WOLR
It probobly isn't best practice, but I'm kinda new so I need some guidance. I'd really appreciate any help! Here's my query:
SELECT [Work Order Header].No_ AS WO_No, [Work Order Line].[Line No_] AS WOL_No,
[Work Order Requisition].[Line No_] AS WOLR_No, [Work Order Line Detail].[Line No_] AS WOLD_No
FROM [Work Order Header] LEFT OUTER JOIN
[Work Order Line] ON [Work Order Header].No_ = [Work Order Line].[Work Order No_] LEFT OUTER JOIN
[Work Order Line Detail] ON [Work Order Line].[Work Order No_] = [Work Order Line Detail].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Line Detail].[Work Order Line No_] LEFT OUTER JOIN
[Work Order Requisition] ON [Work Order Line].[Work Order No_] = [Work Order Requisition].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Requisition].[Work Order Line No_]
View 1 Replies
View Related
May 21, 2008
Hello,
I'm new to MDX and was hoping someone could answer this question. I'm working on an MDX query, a simplified version is below.
What I'm trying to do is to pull 2 sets, based on a date range. Based on those, I'd like to see the total "cost" measure for each set, and divide one by another.
What I'm finding is that when I run the query below, the cost is broken out by month. So, my select statement gives me the cost for January 2008 and February 2008 in the CurrentPeriod set, and for January, February, etc. 2007 in the PriorPeriodData set. What I want instead is to get the total "cost" measure for all the months in each set. I believe this is what I'd get if I put the date range in a subcube or a where clause - but in that case, I wouldn't be working with 2 distinct sets.
Is there some other way to write this, or to combine all the months in the set before the measures?
WITH
SET [CurrentPeriod] AS
'{[Activity Date].[Date].&[2008-01-01T00:00:00]:[Activity Date].[Date].&[2008-02-01T00:00:00]}'
SET [PriorPeriod] AS
'{[Activity Date].[Date].&[2007-01-01T00:00:00]:[Activity Date].[Date].&[2007-12-01T00:00:00]}'
MEMBER [Measures].[Cost Variance] AS
'([CurrentPeriod],[Measures].[Cost]) /
([PriorPeriodData],[Measures].[Cost])'
SELECT NON EMPTY {
([CurrentPeriod],[Measures].[Cost]),
([PriorPeriodData],[Measures].[Cost]),
([Measures].[Cost Variance])
} ON COLUMNS
FROM [Cube]
Thanks for your help!
View 3 Replies
View Related
Apr 21, 2003
PLEASE SEE my update (post #3) for a better simpler explanation!!!!!
Hi,
I need a query which is basically
(query 1)
Except
(query 2)
SQL server supports
(query 1)Union (query 2)
but I can't get the Except to work.
Alternatively,
I also tried to implement it by using the "NOT IN" but didnt work.
Background: 3 tables, accnts, Opty, Opty_postn
I want
the accounts who have 1 or more opty's at 100% (sold) before 1/1/2002 but zero opty's at 100% after 12/31/2001.
accnts has fields ID & name, ROW_ID
opty has fields acct_id, prob, ROW_ID
opty_postn has fields opty_id, close_dt
My query:
SELECT accnt.name
FROM accnt
WHERE
accnt.ROW_ID IN
(
SELECT acct_id
FROM opty
where prob = 100
AND opty.ROW_ID IN
(SELECT opty_id
FROM opty_postn
WHERE close_dt<1/1/2002)
)
AND accnt.ROW_ID NOT IN
(
SELECT acct_id
FROM opty
where prob = 100
AND opty.ROW_ID IN
(SELECT opty_id
FROM opty_postn
WHERE close_dt >12/31/2001)
)
AND accnt.ROW_ID = opty.accnt_ID
my intent was
select account.names
where id
is in set(prob = 100 & sale before 1/1/02)
but not in ( prob = 100 & sale after 21/31/01)
SO ,the accounts which have sales both before and after get counted. But I want only those accoutns which have sales only before 1/1/02.
I hope i explained this right.
Thanks in advance for your help.
Ash.
View 14 Replies
View Related
Dec 18, 2004
Hi,
Can someone briefly explain which this feature of Analysis services is and how canit be of use.
Also, besides BOL, I'll appreciate if someone can recommend a good reading on this.
Thanks.
View 2 Replies
View Related
Jan 31, 2005
Hello.
i have dimensions: month, country, customer, item.
measures: amount, price.
i would like to get such result :
for each customer from USA: sum (amount of item in June * price for the same
item in January).
is it possible?
thank you for your help, Mike.
View 2 Replies
View Related
Apr 24, 2007
I have the following table structure
CREATE TABLE [dbo].[QDisc](
[Id] [int] NOT NULL,
[MinVal] [int] NOT NULL,
[MaxVal] [int] NOT NULL,
[PerVal] [int] NOT NULL,
CONSTRAINT [PK_QDisc] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
I need to be able to select unique overlapping sets of data based on the minval and maxval.
Simple Example
minval maxval
5 15
16 25
10 20
query would produce
minval maxval
5 10
11 15
16 20
21 25
More Complex example
minval maxval
5 15
16 25
10 20
7 7
1 100
query would produce
minval maxval
1 5
6 6
7 7
8 10
11 15
16 20
21 25
26 100
Extra points if anyone can do this without SP and cursor but I'd be satisfied if someone supplied it that way.
View 9 Replies
View Related
Jul 20, 2005
Hi,I've tried without success to create a function where one of theparameters determines the final line of a select statement.The final line includes a 'where referencenumber in (@set)' line.. and@set is the parameter I wish to use which is a string.If the set is just one value, it works fine, however with anything morethan that I just get no records returned.. If I output the parameter,and copy and paste that into query analyser I do get the correct result,so it's nothing to do with string delimiters etc..Any ideas to fix this problem? Or am I going to have to dynamic sql thewhole thing?Cheers,Chris
View 2 Replies
View Related
Aug 17, 2006
I need to manipulate some sets, doing joins based on intersection, and looking for subsets. Is there any way to store an array in a single data field? And then do operations based on those array lists? e.g. -
{1,2,3} intersect {2,3,4} ==> {2,4} (would like to then be able to do joins based on intersection, so that I could say, return all cases where two sets have all but one member from each in common...)
View 3 Replies
View Related
Mar 20, 2008
Hi,
I'm currently trying to retrieve results from a large dataset, there are over 45000 records and I need to use them all to peform counts etc. I have set up views, but my page is still being returned slowly, is there anything I can do to speed this up?
Thanks
Gemma
View 2 Replies
View Related
May 7, 2008
I am trying to query one table and get two different timeperiods of data, I am summing monthly totals to provide a running year total, but I also need last month's total in a seperate column. This is what I have so far but the subquery makes me group it which provides duplicate grouping.DECLARE @LASTPD AS INT
SET @LASTPD = (SELECT MAX(LASTPERIOD) FROM TABLE)
SELECT NAME,
POST_PD AS [MONTH],SUM(CHARGE_AMOUNT) AS MONTHLY_$,
LASTMONTH.LAST_MONTH,(SELECT SUM(CHARGE_AMOUNT) AS LAST_MONTH
FROM TABLE INNER JOIN TABLE2
ON TABLE2.NAME = TABLE.NAME
WHERE POST_PD = @LASTPD
AND TABLE2.NUM= 539
GROUP BY NAME) AS LASTMONTH
INTO #TEMP_SAFROM TABLE
INNER JOIN TABLE2
ON TABLE2.NAME = TABLE.NAME,(SELECT SUM(CHARGE_AMOUNT) AS LAST_MONTH
FROM TABLEWHERE TABLE2.NUM = 539
GROUP BY NAME, POST_PDORDER BY NAME, POST_PD
SELECT NAME,
LAST_MONTH,
CAST(SUM(MONTHLY_$)AS DECIMAL(20,2)) AS YEARLY_$
FROM #TEMP_SA
GROUP BY NAME
ORDER BY NAME
View 13 Replies
View Related
Jul 31, 2000
I need to upgrade a 6.5 box to 7.0 but also change the character sets. Let me know how this sounds.
-Have a 2nd machine w/ 6.5 on it and same char sets.
-backup/restore data from prod box to 2nd machine.
-upgrade 2nd machine to 7.0 using the same char sets.
-uninstall prod machine and install 7.0 w/ new char set.
-use dts to move data from 2nd machine to new machine. (changing char sets from old to new)
View 4 Replies
View Related
Oct 28, 1999
We're constructing a three-tier application. We want the middle tier to
extract a recordset from the database, disconnect from the database,
then pass the recordset to the front tier. After changes have been made
by the front tier, it will pass the recordset back to the middle tier,
which will reconnect, and update the database.
The problem:
Using stored procedures, the recordset is no longer available
once the connection has been closed. Is there any way, using stored
procedures, to be able to keep the recordset available after the
connection has been closed, either by preserving it, copying it, or
otherwise?
View 1 Replies
View Related
Dec 11, 2007
Hi:
How can I combine 2 sets of group by together?
same criteria on group by Region, county
setA with where sum(timeSec) <= 30
while setB with where sum(timeSec) > 30
they are not necessary mutual exclusive. thus, union is not good.
However, when I use derived table method, I will have 'dups' on column of 'Region' and 'county', 2 total are fine.
thanks
David
View 2 Replies
View Related
Apr 8, 2008
Dear friend, the following is my query.
1.SELECT a.Avg_Binaryvalue as Avg1,a.Min_Binaryvalue as Min1 from MeasurementData a where Attribute_Flag ='5'
2.SELECT b.Avg_Binaryvalue as Avg2,b.Min_Binaryvalue as Min2 from MeasurementData b where Attribute_Flag ='6'
i need to join this two resultsets. please give me sample query to join this two result sets
View 10 Replies
View Related
Jun 3, 2014
i have these 2 queries with the included results sets...What commands could I use to take the TotalBlueCircle Column from the 2nd Results Set and have it included next to the TotalRugby column in the 1st results set??Do i need to do a UNION or use Sub Queries or something else?
View 5 Replies
View Related
