Empty Recordsets And Artificial Records

Jul 5, 2006

I'm currently running the following statement that is used in a Crystal
Report. Basically, a record is returned when the T_PAYMENT.amount
has a record in the database based on the value of the T_MULTILIST.code
field. Currently, if there is no record returned, there is no listing
in the report for the given T_MULTILIST.code.


The user now wants a record to be displayed on the report when there is
no record in the database - she wants it to display a value of '$0'
for the given T_MULTILIST.code record. I tried to explain the fact that
is not possible the way things stand at the moment. Basically I need
some type
of case statement that says 'if there is no record returned, create a
single record and set T_PAYMENT.amount = 0' AFTER each query has been
executed.
Anyone have any idea how to accomplish this?



SELECT DISTINCT
'English Language Arts, Grade 1' as Rec_Type, 'English Language Arts
(Consumable)' as Super_Type, '' as Other_Type, 'Continuing Contracts'
as Proc_Type,
T_MULTILIST_GRADE.grade, T_MULTILIST.description, T_MULTILIST.code,
T_PAYMENT.amount
FROM (T_MULTILIST T_MULTILIST INNER JOIN (T_PAYMENT T_PAYMENT
INNER JOIN T_SHIPPING_DETAIL
T_SHIPPING_DETAIL ON
T_PAYMENT.transaction_id=T_SHIPPING_DETAIL.transac tion_id)
ON T_MULTILIST.code=T_SHIPPING_DETAIL.multilist_code) INNER JOIN
T_MULTILIST_GRADE T_MULTILIST_GRADE ON
T_MULTILIST.code=T_MULTILIST_GRADE.multilist_code,
T_ORDER, T_REQUISITION, T_REQUISITION_DETAIL
WHERE
T_ORDER.id = T_SHIPPING_DETAIL.order_id AND
T_REQUISITION.id = T_ORDER.requisition_id AND
T_REQUISITION_DETAIL.requisition_id = T_REQUISITION.id AND
T_REQUISITION_DETAIL.latest_record_flag = 1 AND
T_REQUISITION.latest_record_flag = 1 AND
T_ORDER.latest_record_flag = 1
AND (T_MULTILIST.code='1040')
AND (T_MULTILIST.expiration_year >= '2006' )
AND (T_REQUISITION.requested_shipment_date >= '2006' + '0601'
AND T_REQUISITION.requested_shipment_date < dateadd(YY, 1,
'2006' + '0601' ) )


UNION
SELECT DISTINCT
'English Language Arts, Kindergarten' as Rec_Type, 'English
Language Arts (Consumable)' as Super_Type,
'' as Other_Type, 'Continuing Contracts' as Proc_Type,
T_MULTILIST_GRADE.grade, T_MULTILIST.description,
T_MULTILIST.code, T_PAYMENT.amount
FROM (T_MULTILIST T_MULTILIST INNER JOIN (T_PAYMENT T_PAYMENT
INNER JOIN T_SHIPPING_DETAIL T_SHIPPING_DETAIL
ON T_PAYMENT.transaction_id=T_SHIPPING_DETAIL.transac tion_id)
ON T_MULTILIST.code=T_SHIPPING_DETAIL.multilist_code) INNER JOIN
T_MULTILIST_GRADE T_MULTILIST_GRADE ON
T_MULTILIST.code=T_MULTILIST_GRADE.multilist_code,
T_ORDER, T_REQUISITION, T_REQUISITION_DETAIL
WHERE
T_ORDER.id = T_SHIPPING_DETAIL.order_id AND
T_REQUISITION.id = T_ORDER.requisition_id AND
T_REQUISITION_DETAIL.requisition_id = T_REQUISITION.id AND
T_REQUISITION_DETAIL.latest_record_flag = 1 AND
T_REQUISITION.latest_record_flag = 1 AND
T_ORDER.latest_record_flag = 1
AND (T_MULTILIST.code='0040')
and (T_MULTILIST.expiration_year >= '2006' )
AND (T_REQUISITION.requested_shipment_date >= '2006' + '0601'
AND T_REQUISITION.requested_shipment_date < dateadd(YY, 1,
'2006' + '0601' ) )

Up to 40 more UNION statements follow the above 2.

View 3 Replies


ADVERTISEMENT

Dynamic # Of Records In An Artificial Grouping

May 12, 2008



I have a report that requires SubTotals for each page.

To do this successfully, I had to introduce the following grouping:


Code Snippet=Int((RowNumber(Nothing)-1)/5




This groups every 5 records.

Unfortunately, the length of data in my records is not uniform. For example, certain records have long(over 4 lines) comment field while others are short(1 line).


This means that even though I have an artificial grouping, the group may still be split up over two or more pages.

Is there a way to Group records PER PAGE dynamically?

View 5 Replies View Related

HOW To Select A Matrix (cross Join) With Empty Records To Retrieve The Same Amount Of Records For Each Cell

Nov 2, 2006

Hello

Im searching for a solution to set all matrix row or cell the same height.
it schoud looks like this example:

This is a simple matrix


test a

text b








text c








text d

text e

text f








text g










This is a matrix with all the same row-height.



test a

text b

.








text c

.
.









text d

text e

text f








text g

.

.









Thx you a lot

View 3 Replies View Related

Query Help, What Columns For All Records Are Empty

Aug 30, 2004

I am wanting to make a query to find out if there are any columns in a table that are null for all records.

So far I am using a cursor that holds all the column names of that table and then a stored procedure that would use a loop to cycle through all the column names in a query to essentially count the number of nulls for each column and then an If statement to check to see if the #of nulls = to the number of records.

What isn't working is my query to count the number of nulls...Here is my query

select count(*)
from XYZ
where @cName is null

Ive tried declaring cName as a sysname, and varchar, neither work.

Also, I've tried using COL_NAME(OBJECT_ID('XYZ'), someColumnIndx)...I think this would be the better choice just because I wouldn't need to use a cursor, but both aren't working.
I am a bit new to MSSQL and don't know my way around the system tables. Is there a sys table that would give me this info so I wouldn't need to perform a query?

Any info would be great.

View 4 Replies View Related

Empty A Table Before Using DTS To Append Records?

Jul 20, 2005

In Access I have a macro that, each night, takes a table with aprimary key defined in it, and deletes all the rows. Then itimports/appends records from a fixed width text file. In this way,since the table is not deleted and recreated, the primary key is keptintact.What would be the equivalent SQL method for doing this in an automatedway? I've tried letting DTS import the table from Access, but theprimary key is lost. Is there some way to "empty" a table instead ofdropping it, and then append new records so that the table will end uphaving the primary key I want it to have?Thanks.Larry- - - - - - - - - - - - - - - - - -"Forget it, Jake. It's Chinatown."

View 4 Replies View Related

MDX Query To Display No Records For Empty Rows

Jan 9, 2007

Hi,

I am facing with the following problem.

I am using bar chart to display my report.

My MDX query is as follows:

SELECT
NON EMPTY { [Measures].[SUM_COUNT] } ON COLUMNS,
TopCount ( Filter ( {[DIM].[NAME].[NAME]}, [Measures].[SUM_COUNT] <> 0 ) , 10, [Measures].[SUM_COUNT] ) ON ROWS
FROM
[USAGE]

where <criteria>

I want to show the topmost 10 records. For some criteria I get the results in the chart.

But for some criteria or say for wrong criteria, there are no records. In such a case the X-axis contains all values for {[DIM].[NAME].[NAME] and value for the Y-axis is all 0. Its kind of blank report it will restrict to 10 records.

In such a scenario I want to show a message to the user saying "No records found", which I have set in the No Rows property of the chart.

If I remove the TopCount clause then I get the above message, which is obvious.

So how do I acheive the same message but at the same time limiting the records to 10?

How can I acheive this in my scenario? Can something be done at the query end?

any help is appreciated.

Thanks in advance!

View 3 Replies View Related

How To Pull Null (empty) Records From SQL Database

Dec 5, 2007


How to pull null (empty) records from SQL database,

what query am I suppose to use to get result

help me

thank you
maxs

View 4 Replies View Related

IS NULL Returns Empty Records (using TEXT Type)

Mar 3, 2008

Hi all I am having some issues in selecting items from my database where the record is NOT NULL. I have the code below however although some fields do contain soem data in it, others are blank which I believe are empty spaces. How do I do a SELECT command which ignores empty spaces and NULLS?





Code Snippet

SELECT CustomSearch FROM OfficesTable WHERE CustomSearch IS NOT NULL
Thanks, Onam.

View 10 Replies View Related

Normal Vs Surrogate/artificial Key?

Jun 10, 2008

 Hey All, I'm trying to decide what's the 'best' to use.  I've been designing and creating database for a while and have pretty much always used a surrogate key and not a normal one.  I've finally had some free time to start studying more so in my spare time and read up and come accross a lot of guides, articles and stories that tout that normal keys should be used whenever possible as they're a better identifier and that surrogate keys should only be used when there is not a readily available normal key.  Now perhaps I'd be open to accepting that but absolutely every database I come across tends to only use surrogate keys.  For example I'm doing an authentication system from scratch and am looking at the User table.  Now of course the user name has to be unique, should that be the primary key or should I have a seperate column with a guid or an incrementing int or the like as the primary key? I can certainly see that username could be used.  I can also see how it may be easier when looking through the data tables to identify who/what a table is refering to with a surrogate key.  However it still seems sort of sloppy, for lack of a better word, to me.  Where now I could have somebody's username (or any other piece of data used for this purpose) spread accross a lot of other tables.  And while writting this I just thought of the scenario that perhaps somebody needs their username changed, with this method now the ids need to be changed on all the related rows of all the other tables whereas with a surrogate key it wouldn't matter. Anyways I'm mostly looking for opinions on which way to go (not just with the user sample, but more in general).Thanks.

View 2 Replies View Related

SQL Server 2012 :: Variable Declared For Inserting Records Is Setting To Empty From Second Execution Onward?

Jul 9, 2015

I am facing a strange problem in executing stored procedure. Basically my sproc will take a values from Java application and create a Insert statement. see stored procedure below.Just to give some more background- I am re writing the procedure which was written in oracle already.

Problem I am facing now is with the statement below . When I execute the procedure for first time it works fine however when I execute for second time onwards it is setting to empty. Not sure what is the problem with my declaration and setting up with values. For reference I have pasted my complete stored procedure code below.

select @L_STMT= 'INSERT INTO '+ @l_table_name + '(' + LTRIM(RTRIM((substring (@L_INS_STMT,2,len(@L_INS_STMT))))) + ') VALUES (' + LTRIM(RTRIM((substring (@L_INS_STMT1,2,len(@L_INS_STMT1))))) +')';
ALTER PROCEDURE [dbo].[PKG_OBJ_API$CREATE_OBJ]
(
@P_TYPE VARCHAR(4000),
@P_SCOPE VARCHAR(4000),
@Arrlist varchar(max),

[code]....

View 3 Replies View Related

Artificial Mind - Part One - Basic Architecture And Cognitive Structure Is Now Available

Oct 5, 2006

It's a dream of human beings to build machines that can think andbehave like human beings. The most important part of of such a machineis an artificial mind that can emulate the cognitive processing ofhuman mind.This book, "Next Generation Artificial Intelligence, Artificial Mind -Part One - Basic Architecture and Cognitive Structure" introduces abasic artificial mind architecture and computational model forcognitive processing. Inside the book, three important cognitiveprocess modeling components, mental objects network (MON),associative-learning mechanisms and a concept formation principle areintroduced. Based on the architecture and the computational model, onecan develop his own model of artificial mind according to his ownspecific requirements.The first edition of Artificial Mind - Part One is now available forpurchase from the author's personal web site. The price of the e-bookis USD7.00 (seven US dollars). An evaluation edition of this e-book isalso available for download from the web site.The author's personal web site:http://www.geocities.com/tomwingmak/

View 1 Replies View Related

Why Recordsets

Aug 8, 2005

why should one use Recordsets and not SQL itself in database work. Isn't the latter muvch faster, more portable, and needs to be learnt only once?

View 1 Replies View Related

How To Concatenate Recordsets

Aug 31, 2007

is it possible to concatenate 2 recordsets ? i have 2 select, and i need then to be displayed one after other.using union all the results are gathered, ordered like only one select.thanks for all 

View 5 Replies View Related

Multiple Recordsets

Mar 16, 2001

Is it possible to return multiple recordsets from SQL to ASP in a single query?

TIA
Pilot

View 5 Replies View Related

Returning One Of Three Recordsets

Jun 21, 2007

Hi all,

I've written a query which needs to return different records depending on its argument.

I've used an IF statment to test which of the three SELECT statments should be returned. However, when I try to access the data from VBA in Access I find that the recordset is empty, whereas the query returns thr correct data if I run it in the SQL environment. WHy is this? More importantly, how do I make it return records?


Code:


ALTER PROC [dbo].[spFilteredContractListByUser]

@UserID varchar(7) --The user's 'T' number

AS

PRINT 'User ID is' + @UserID

--Get this user's filter type from tbl

DECLARE @FilterType int

SELECT @FilterType = FilterID
FROM tblFindContractUserFilter
WHERE UserID = @UserID

-- Standard filter
-- Returning all queries matching the username
IF @FilterType = 1
BEGIN
SELECT DISTINCT [Acc No], Business, Type, [Log Date], [Logged By], [Ack. Date], [Ack. By], [Late?]
FROM qryQueriesWorkList
WHERE (suser_sname = @UserID)
END
ELSE IF @FilterType = 2
-- Return only queries acknowledged by @UserID
BEGIN
SELECT DISTINCT q.[Acc No], q.Business, q.Type, q.[Log Date], q.[Logged By], q.[Ack. Date], q.[Ack. By], q.[Late?]
FROM qryQueriesWorkList q
WHERE (q.[Ack. By] = dbo.getEmployeeName(@UserID))
END
ELSE IF @FilterType = 3
-- Return all the queries which have not been acknowledged
BEGIN
SELECT DISTINCT q.[Acc No], q.Business, q.Type, q.[Log Date], q.[Logged By], q.[Ack. Date], q.[Ack. By], q.[Late?]
FROM qryQueriesWorkList q
WHERE (q.[Ack. By] IS NULL)
END



The VBA code is:


Code:


rst.Open ("EXEC spFilteredContractListByUser '" & Me.cmbUserFilter.Value & "'")



Me.cmbUserFilter.Value contains the argument.

Thanks all

Dave

View 2 Replies View Related

Nested Recordsets

Nov 19, 2004

I need to decrease the amount of time it takes to return a set of data of the following format:

State[Provided vai user intput]->All Counties in State->All Cities in County->All Zones in City[usually < 50 or 75 per city]

current behavior:
1. currently user selects state all counties are returned from db
2. foreach county cities are returned
3. foreach city streets are returned
4. objects hydrated with data & display drawn

As you can see this gets expensive. I know there has to be a better way to do this. Any suggestions? We use sql server/c#.net.

View 8 Replies View Related

How To Concatenate 2 Recordsets

Aug 31, 2007

is it possible to concatenate 2 recordsets ?

i have 2 selects and i want to display all results from the first
select and then the results from the second select query
with paging.

if i use union, all the results will be mixed.

thanks for the help!

View 7 Replies View Related

Passing Recordsets

Sep 26, 2007

Greetings,

I'm having SSIS block right now and thought I'd see if anyone had a simple solution to this.

What I have is SSN data coming in from several different tables, which needs to be inserted into a SurrogateSSN table which just uses an Identity field for the surrogate.

I normally build a seperate package for each detination table, so in each package that has SSN data in the source, I need to insert into the SurrogateSSN table (then I can use the surrogate values later in my package).

What I'm looking for is a simple solution if possible (preferrable using existing non-script tasks, or very short script), where I can pass in the SSN. Is there way to create a package that will accept a recordset (of SSN's) for this insert, that I can call from several different packages, and pass in the recordset of SSN's?

Thanks for your ideas

View 7 Replies View Related

How Can I Use The Last Recordset In A SP That Return 3 Recordsets ?

Mar 9, 2005

Hello,

I have a SP1 that is calling another SP2,
SP2 is using select and returning records

So in SP1 I get 3 different record sets
(as I can see it on Query Analyzer - one after the other)

When I'm trying using the ASP page I get this error:
"Item cannot be found in the collection corresponding to the requested name or ordinal."


How can I access the last recordset ?

View 3 Replies View Related

Browser Updateable Recordsets ?

Mar 26, 2000

I think it whould be simpler to explain what I am trying to accomplish.
I wish to pull a query to return multiple records from a SQL 7.0 database.
The query is a customers standing order.
I wish to make edits to multiple records then return those records for update.
The HTML pages that I have seen all seem to display tables on a singular record format.
Is it possible to display records in a 'Table' format and allow it to be updated.
Can any one answer this or direct me to a site with the relevent info.
Thanks Stuart
Indtec@eircom.net

View 2 Replies View Related

How To Output Multiple Recordsets On One Row Using SQL?

Nov 8, 2001

Hello Folks,

Using just SQL (within SQL Server 2000), I'm looking to output 2 (or more) recordsets from a one-to-many join between table all on one row. Help! Can someone please show me how to do this if it can be done.

Below is an example of the situation.

Thanks a lot folks, - Jerry


************* Tables *******************

** ITEMS **
ItemID
=======
10
11
12

** ITEMSTOPARTS **
ItemID ---- PartID ---- Funktion
=================================
10 -------- 100 ------- headlight
10 -------- 120 ------- taillight

** PARTS **
PARTID ---- Manf ---- ProductName
=================================
100 ------- M1 ------ Halogens
120 ------- M2 ------ Red Lights


************* Queries *******************

-- QUERY 1 --
==============
(dashes preserve indentation)

SELECT
- i.itemID AS ItemID,
- p1.partID AS HLPartID,
- p1.manf AS HLManf,
- p1.name AS HLName,
- p2.partID AS TLPartID,
- p2.manf AS TLManf,
- p2.name AS TLName


FROM - ZItems i
- - - - INNER JOIN ZItemsToParts ip
- - - - - - ON i.itemID = ip.itemID AND
- - - - - - (ip.funktion = 'headlight' OR
- - - - - - ip.funktion = 'taillight') AND
- - - - - - i.itemID = 10
- - - - LEFT OUTER JOIN ZParts p1
- - - - - - ON ip.partID = p1.partID AND
- - - - - - ip.funktion = 'headlight'
- - - - LEFT OUTER JOIN ZParts p2
- - - - - - ON ip.partID = p2.partID AND
- - - - - - ip.funktion = 'taillight'


************* Output *******************

Using "Query 1" I get this:
(Note: HL = Headlight; TL = Taillight)

ItemID - HLPartID - HLManf - HLName --- TLPartID - TL Manf - TL Name
================================================== ==============
10 ----- 100 ------ M1 ----- Halogens - NULL ----- NULL ---- NULL
10 ----- NULL ----- NULL --- NULL ----- 120 ------ M2 ------ Red Lights


I wish to output the proceeding two record set in one row
Any ideas how this can be done with SQL Server 2000
(with JOINS in the FROM clause)?
- DESIRED OUTPUT:

ItemID - HLPartID - HLManf - HLName --- TLPartID - TL Manf - TL Name
================================================== =============
10 ----- 100 ------ M1 ----- Halogens - 120 ------ M2 ------ Red Lights



-= End-O-Message =-

View 2 Replies View Related

Paging Large Recordsets

Oct 21, 2007

Here's a question you'll never quit hearing: is there a convenient way to page through large recordsets in SQL Server 2000?

I'm writing some software which, for all intents and purposes, works like a messageboard: users can create threads, leave replies, and so on.

I have about a half million records in a few of my tables, and some of my queries return 1000s of results. I'd prefer not to return 1000s of records all at once, so I don't want to page my records in code; I'd rather page them in SQL Server. Naturally, I want to page replies. However, I don't know of a convenient way to page records in SQL Server 2000.

View 1 Replies View Related

Opening Mulitple Recordsets

Jul 20, 2005

I have a single .asp page that opens a connection and then sequentiallyopens and closes 14 recordsets from stored procedures to obtain variousproduct information before closing the connection.Is it common practice to do something like this? Or is opening 14recordsets going to become a real problem when the page goes live and startsgetting high web traffic?Thank you in advance for any information you might provide.Dave

View 2 Replies View Related

Asp / Sql Server Dates In Recordsets

Jul 20, 2005

Hey,I've got an Classic ASP app pulling some records out of a DB, whichworked fine when it was Access. When we upped to SQL Server, all of asudden, when the Recordsets came back, all the dates were empty, eventhough the data was showing up in the DB.About a year ago (only code asp apps once and a while), I rememberseeing this and finding something that just had something to do with theorder in which something was being referenced, but I just can't for thelife of me remember what it was.The Dates are set as datetime, length of 8.Anyone know why this happens and a solution?many thanks,rob

View 1 Replies View Related

Closed Recordsets From Stored Procedures

Jun 18, 2001

Hello all,

I'm writing some stored procedures that first do an Insert or an Update, and then also do a Select to return a Recordset back to an ADO client. However an Insert or Update causes a closed recordset to be produced in ADO. I can skip over the closed recordset with the NextRecordset method.

So the question is, is there any way of stopping the closed recordset being returned? I don't want to have to call the NextRecordset method from ADO as this would mean that the client would need to know about the implementation of the stored procedure.

Has anyone got any ideas on how to get round this?

Thanks
Dave Sykes

View 2 Replies View Related

@@IDENTITY Problem With Multiple Recordsets

Apr 24, 2008

Can anyone help with this problem.

I am doing some mods to an existing db web app in asp. I am providing a facility to copy a subset of related records in a few tables. I am using one recordset to read in the selected records and then copying the data into another recordset creating a new record in the same table. I have to then pick up the @@IDENTITY of the new record so that I can update linked records in other tables.

The @@IDENTITY value returned is null unless I close the selection recordset.

I have created a simplified version of the code to demonstrate this.

The underlying table is called 'test' and it has 2 fields, 'test_id', integer, identity and 'test_desc' varchar(50)

ASP Code


<%
dim objConn, objRS1, objRS2, objRS3

sub OpenDatabase()
'open database object here instead of in page
Set objConn = Server.CreateObject("ADODB.Connection")
'Connect object to database through global.asa application variable
objConn.Open = Application("test_ConnectionString")
'Create recordset object to use
Set objRS1 = Server.CreateObject("ADODB.Recordset")
Set objRS2 = Server.CreateObject("ADODB.Recordset")
end sub

sub CloseDatabase
Set objRS1 = Nothing
Set objRS2 = Nothing
objConn.Close
Set objConn = Nothing
end sub

function UnNull(numval)
if isnull(numval) then
UnNull="NULL"
else
UnNull=cstr(numval)
end if
end function


OpenDatabase

strSQLQuery = "select * from test where test_id in (1,2) order by test_id"
objRS1.Open strSQLQuery, objConn, 1, 3

do while not objRS1.eof
objRS2.Open "test", objConn, 1, 3
objRS2.AddNew
old_test_id = objRS1("test_id")

objRS2("test_desc") = objRS1("test_desc")
objRS2.Update
objRS2.Close

' *1 objRS1.Close

strSQLQuery = "select @@IDENTITY as new_id"
objRS2.Open strSQLQuery, objConn
new_test_id=objRS2("new_id")
objRS2.close

response.write("Old ID = " & UnNull(old_test_id) & ", New ID = " & UnNull(new_test_id))
response.write("<p>")

' *2 response.end

objRS1.movenext
loop

objRS1.close

CloseDatabase

%>



Output

Old ID = 1, New ID = NULL
Old ID = 2, New ID = NULL

If I unrem the lines *1 and *2 then the output is

Old ID = 1, New ID = 11

I realise that there are other ways of doing this but this is only a simplified example whereas the actual application is far more complicated.

Thanks is advance

Kennedy

View 3 Replies View Related

Delete Recordsets With Same Date And Line

Jul 23, 2005

Hi All!I need help with a Statement!I am working with an Access2000 DB.I have the following Problem.ChNrLinieDatum Code 39 Stückzahl BHL1 BHL2 BMRH582-064L2.1008.03.2005 02:30:00FCAA14821701582-064L2.1008.03.2005 02:30:00FCAA14871701582-114L2.1208.03.2005 01:00:00FAC827501240582-114L2.1208.03.2005 01:00:00FAC827441240582-114L2.1208.03.2005 01:00:00FAC827501240582-094L2.707.03.2005 19:45:00FAE74323481582-094L2.707.03.2005 19:45:00FAE74489481582-094L2.707.03.2005 19:45:00FAE74489481581-294L2.807.03.2005 18:20:00FA8V2658221581-294L2.807.03.2005 18:20:00FA8V2652221581-294L2.807.03.2005 18:20:00FA8V2658221582-114L2.1207.03.2005 17:45:00FAAR20721236As you can see I have a few recordsets that are double. The Thing is, thereis an ID that makes them different.I need a Statement that deletes the surplus records where 'Datum' and'Linie' are identical to another record. 1 record has to remain of course.I thought of something like this.DELETE FROM tbAuswertWHERE EXISTS(SELECT *FROM tbAuswertWHERE (Linie AND Datum)IN (SELECT Linie AND Datum AS SuchkritFROM tbAuswertGROUP BY SuchkritHAVING ((Count(Suchkrit)>1)))But I get an error:You wanted to execute a Query that did not have the following expression'Not Linie = 0 And Not Datum = 0' in its AggregatefunctionPerhaps you ccan help me.ThanksJulia--Message posted via http://www.sqlmonster.com

View 3 Replies View Related

Rotating Information From Recordsets To Columns.

Jul 20, 2005

Hello,This problem perplexes me and I hope that someone has done somethingefficient.Take for example the data in the MASTER..SYSPERFINFO:SELECTCAST(RTRIM(INSTANCE_NAME) AS VARCHAR(15)),CAST(RTRIM(COUNTER_NAME) AS VARCHAR(31)),CAST(RTRIM(CNTR_VALUE) AS VARCHAR(10))FROM MASTER..SYSPERFINFOWHERE INSTANCE_NAME = N'TEMPDB'tempdb Data File(s) Size (KB) 51200tempdb Log File(s) Size (KB) 1272tempdb Log File(s) Used Size (KB) 738tempdb Percent Log Used 58tempdb Active Transactions 0tempdb Transactions/sec 186281tempdb Repl. Pending Xacts 0tempdb Repl. Trans. Rate 0tempdb Log Cache Reads/sec 0tempdb Log Cache Hit Ratio 0tempdb Log Cache Hit Ratio Base 0tempdb Bulk Copy Rows/sec 0tempdb Bulk Copy Throughput/sec 0tempdb Backup/Restore Throughput/sec 0tempdb DBCC Logical Scan Bytes/sec 0tempdb Shrink Data Movement Bytes/sec 0tempdb Log Flushes/sec 1578tempdb Log Bytes Flushed/sec 67882496tempdb Log Flush Waits/sec 226tempdb Log Flush Wait Time 47tempdb Log Truncations 248tempdb Log Growths 3tempdb Log Shrinks 0<I did the CAST and LTRIM so that it looks better when displayed in abrowser>I would like to keep statistics in a table with the following columns:INSTANCE_NAME,DATA_FILE_SIZE,LOG_FILE_FIZE,ACTIVE_TRANS,TRANS_PER_SECSo, instead of having a table with three columns and 23 rows(only 4 ofwhich I want), I would have a single row with 4 columns(plus theInstance_Name).Visualy, I want to call this a 90 degree rotation. Here's what theselect statement would then look like:SELECT *FROM SYSPERFINFO_ARCHIVEWHERE INSTANCE_NAME = N'TEMPDB'Here's the result set:tempdb 51200 1272 0 185198Is it possible to 'rotate' a recordset into columns?How would it be done?Gracias.

View 2 Replies View Related

Unable To Merge Two Stand-alone Recordsets

Jul 4, 2007



I have two stand-alone recordsets. They have the exactly the same fields definitions. They come from different ADODB connections (Microsoft Jet). And I cannot merge them into one recordset.



I was trying to merge the second recordset into the first, but instead of adding the records, the first recordset becomes populated with infinite copies of the first record of the second recordset. This seems to me a VB6 bug.



Any help on how to go around this problem will be really appreciated. Thanks a lot. Frank



This is the code:



(Please note, the INVENTORY table contains different data but has the same structure in both databases.)




Code Snippet

Dim S1ADO As New ADODB.Connection

Dim S2ADO As New ADODB.Connection



S1ADO.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Z: [...]"

S2ADO.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Y: [...]"



RS1.Open INVENTORY, S1ADO, adOpenDynamic, adLockOptimistic
RS2.Open INVENTORY, S2ADO, adOpenDynamic, adLockOptimistic


RS2.MoveFirst

While Not RS2.EOF

RS1.AddNew

For A=0 to RS1.Fields(A).Count - 1

RS1.Fields(A).Value = RS2.Fields(A).Value

Next

RS1.Update

RS2.MoveNext

Wend







View 1 Replies View Related

After Moving To SQL2005, Disconnected Recordsets Are Ready-only

Feb 16, 2006

We're using ADO disconnected recordsets. On SQL 2000, we could update these on the client (without propagating the changes to the server) even if the underlying view or table was non-updatable.

When running our apps against SQL 2005 (using the same client-side environment), we can no longer change any attributes of those disconnected recordsets, that connect to a non-updatable database object (the rest of the app runs fine, we can update all updatable database objects through disconnected recordsets) . Does SQL 2005 respond to such calls differently from SQL 2000, so that ADO recordsets are built in a new way (which makes them read-only in our setting)?

Thanks for any suggestions.

Rene

View 1 Replies View Related

Report From Prcedure That Return Multiple Recordsets

Feb 11, 2007

Hi there,




I am creating a report from a stored procedure that returns
multiples record sets. For an example, my stored procedure is as follows








CREATE PROCEDURE MYPROCEDURE
AS









BEGIN
SELECT * FROM TABLE1
SELECT * FROM TABLE2
SELECT * FROM TABLE3
END












Now lets say I want to create a report that will display the
result of the query no 2 (which is SELECT * FROM TABLE2 in this example). How
can I do this? Is there any way to bind the second record set to the report
dataset with out changing the stored procedure?
I will appreciate any kind of suggestions on this
Thanks
Moim

View 8 Replies View Related

SQL7 + Stored Proc&#39;s = Files [Persistant Recordsets]

Oct 10, 2000

Hi,

I was wondering, is there any way to have a stored procedure write to a file its resultant recordset?

i.e. I have this SP which fires a query, this in turn returns a single row recordset that I need to have written to a file. I realise this could easily be facilitated using an ADO client, however, can it be done by the backend i.e. the SQL7 & SP itself, directly?

I hope you can help me here! thanks for your time!
Cheers
Gaurav

View 2 Replies View Related

Inserting Few Recordsets Into Temp Table Containing NULL Values

Jul 20, 2005

HiI need some help on achieving the following:I wrote a querie which collects Data out of three Tables, the Resultlooks like this:SET NOCOUNT ONDECLARE @ROWINTDECLARE @CURPTNO CURSORSET @CURPTNO = CURSORFORSELECT * FROM TEMPOPEN @CURPTNOFETCH NEXT FROM @CURPTNOINTO @ROWWHILE (@@FETCH_STATUS = 0)BEGINSELECT ONE.CYNO, ONE.ROLE, ONE.ROL_BEZ, ONE.PTNO, ONE.NOCY, TWO.TEXTAS NOCY_TEXTFROM(SELECT CY.CYNO,RE.CYNO AS RECYNO, RTRIM(RE.ROLE) AS ROLE, RTRIM(V_ROLE.TEXT) ASROL_BEZ,RE.PTNO AS PTNO, RTRIM(RE.NAME) AS SACHBEARBEITER, RE.NOCYFROM CYRIGHT OUTER JOINRE ON RE.CYNO = CY.CYNOINNER JOINV_ROLE ON RE.ROLE = V_ROLE.CODEWHERE (RE.PTNO IN(SELECT PT.PTNOFROM PTWHERE PT.PTNO IN(@ROW)AND V_ROLE.LANGUAGE = PT.CLANG))) AS ONELEFT JOIN(SELECT V_NOCY.CODE, V_NOCY.TEXTFROM V_NOCYINNER JOINPT ON PT.CLANG = V_NOCY.LANGUAGEAND PT.PTNO IN (SELECT PT.PTNOFROM PTWHERE PT.PTNO IN(@ROW))) AS TWOON ONE.NOCY = TWO.CODEFETCH NEXT FROM @CURPTNOINTO @ROWENDCLOSE @CURPTNODEALLOCATE @CURPTNOThe Result looks like this:RS1:6313,1300,Architekt,99737505,NULL,NULL2392762,100,Bauherr,99737505,NULL,NULLRS2:2693265,100,Bauherr,99756900,NULL,NULLNULL,1,Planer,99756900,2,Bauherr macht Pläne selberRS3:2691919,100,Bauherr,99755058,NULL,NULL2691962,6000,Kontakt,99755058,NULL,NULLMy Problem is, that I need to have all the Resultsets in one Table atthe end.So my further undertaking was to create a Temp Table with theexpectation to receive all the resultsets in one Step.The TSQL for this looks like that:SET NOCOUNT ONCREATE TABLE #CYRE_TEMP(CYNOINT NULL,ROLEINT NULL,ROL_BEZVARCHAR (60) NULL,PTNOINT NULL ,NOCYINT NULL,TEXTVARCHAR (60) NULL)GODECLARE @CYNOINT,@ROLEINT,@ROL_BEZVARCHAR (60),@PTNOINT,@NOCYINT,@TEXTVARCHAR (60),@ROWINT,@CURPTNOCURSORSET @CURPTNO = CURSOR FORSELECT PTNO FROM TEMPOPEN @CURPTNOFETCH NEXT FROM @CURPTNOINTO @ROWWHILE (@@FETCH_STATUS = 0)BEGININSERT INTO #CYRE_TEMP (CYNO, ROLE, ROL_BEZ, PTNO, NOCY, TEXT)VALUES(@CYNO,@ROLE ,@ROL_BEZ ,@PTNO ,@NOCY ,@TEXT)SELECT @CYNO = ONE.CYNO,@ROLE = ONE.ROLE,@ROL_BEZ = ONE.ROL_BEZ,@PTNO = ONE.PTNO,@NOCY = ONE.NOCY,@TEXT = TWO.TEXTFROM(SELECT CY.CYNO, RTRIM(RE.ROLE) AS ROLE, RTRIM(V_ROLE.TEXT) ASROL_BEZ,RE.PTNO AS PTNO, RTRIM(RE.NAME) AS SACHBEARBEITER, RE.NOCYFROM CYRIGHT OUTER JOINRE ON RE.CYNO = CY.CYNOINNER JOINV_ROLE ON RE.ROLE = V_ROLE.CODEWHERE (RE.PTNO IN(SELECT PT.PTNOFROM PTWHERE PT.PTNO =@ROWAND V_ROLE.LANGUAGE = PT.CLANG))) AS ONELEFT JOIN(SELECT V_NOCY.CODE, V_NOCY.TEXTFROM V_NOCYINNER JOINPT ON PT.CLANG = V_NOCY.LANGUAGEAND PT.PTNO IN (SELECT PT.PTNOFROM PTWHERE PT.PTNO =@ROW)) AS TWOON ONE.NOCY = TWO.CODEFETCH NEXT FROM @CURPTNOINTO @ROWENDCLOSE @CURPTNODEALLOCATE @CURPTNOSELECT * FROM #CYRE_TEMPDROP TABLE #CYRE_TEMPGOAnd the Output looks like this now:Q1:NULL,NULL,NULL,NULL,NULL,NULL2392762,100,Bauherr,99737505,NULL,NULLNULL,1,Planer,99756900,2,Bauherr macht Pläne selberCan someone help me getting all the 6 Rows into one Table as Output?I appreciate any available Help on this..Ssscha

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved