Big Queries Return Empty Result Set

Apr 22, 2004

Hello guys,

MS SQL server 2000 behavies strange with big queries that involves relatively large number of tables:
If I just enumerate columns I want to receive, the result set is empty. Adding * to the column list without making any change to where clause seems to solve the problem but I guess it's not the best practice.
The most amazing is that this behavior is not stable, so I suppose there's something to deal with server itself, not the application.
Has anybody suffered this problem and what solution was adopted?
Thanks for any information you can provide.

View 5 Replies


ADVERTISEMENT

What To Do With An Empty Result Set

Mar 13, 2008



I'm querying my database with a datetime constraint, only picking up records newer than the last ETL. Although I doubt it would ever happen I was going to put a check in the data flow to trap the case when NO rows were read due to inactivity in the OLAP. I set up a row count and tried to trap it. Apparently if you use an OLE DB source to get a result set and that result set is empty, the program flow bypasses everything and keeps running until it hits something causing an error, like an SQL Insert or Update command.

Is there any way to trap this condition? Currently it is going to cause the program to fail which will cause a snapshot restore (after kicking everyone out of the database).

Thanks.

View 8 Replies View Related

Spt_datatype_info Result Is Empty

Mar 23, 2007

Enviroment
O.S. = Windows Small Business 2003
DB = SQL 2000 Server with SP3a
No windows or third party firewalls that are set up.

Problem:
When performing a Link Table in MS Access 2003 it runs into this error message "The Microsoft Jet Database engine could not find the object 'zstblSetup' (the name of our table). Make sure the object exists and that you spell its name and path name correctly.

But when we try to another server but at this time the SQL 2000 is patch with SP4, the link table works fine in Ms Access 2003. So we decided to use SQL Profiler to trace the cause of the problem. It so happen when sp_columns N'zstblSetup' is called no result is returned. Going through the code in sp_columns, it calls spt_datatype_info. When calling spt_datatype_info, again no record is returned. We suspect that spt_datatype_info is the culprit of the problem that's why Link Table in MS Access 2003 doesn't work.

Calling the sp_columns and spt_datatype_info into the working server that I mentioned aboved can return results. The way I understand it spt_datatype_info holds the SQL data type info.

My question is if we apply SP4 in the server that is problematic will this problem be resolved? Or are there any possible reason to solve this problem?

View 4 Replies View Related

Return Subquery Result For Only First Row In Result

Apr 7, 2015

I'm using a subquery to return a delivery charge line as a column in the result set. I want to see this delivery charge only on the first line of the results for each contract. Code and results are below.

declare @start smalldatetime
declare @end smalldatetime
set @start = '2015-03-22 00:00' -- this should be a Sunday
set @end = '2015-03-28 23:59' -- this should be the following Saturday

select di.dticket [Contract], di.ddate [Delivered], di.item [Fleet_No], di.descr [Description], dd.min_chg [Delivery_Chg], dd.last_invc_date [Delivery_Invoiced],

[code]....

In this example, I only want to see the delivery charge of 125.00 for the first line of contract HU004377. For simplicity I have only shown the lines for 1 contract here, but there would normally be many different contracts with varying numbers of lines, and I only want to see the delivery charge once for each contract.

View 6 Replies View Related

Return Value Of Empty List

Aug 15, 2006

Hi,

I'm trying check the records which satisfies the following condn.

select @p1 = su1 from RER where (no=10 and atp ='N')

It returns empty list . bcos no match found.that's ok.

But i need to write the logic if record is not there.

i tried to do as follows

if ((@p1 =null) or (p1=' '))

...some logic

else

.. other logic

It's always executing else case. How do i catch empty value using sql query.

Is there any command there like null?

I tried in SQL query analyzer. Using SQL 2000.

Thank You

View 6 Replies View Related

How To Return A List Of Empty Tables

Mar 19, 2003

I want to return a list of user tables from a database where the rowcount is 0. This will be a 3 step process: (1) truncate all 'New%' tables, (2) load data via ODBC/DTS into 'New%' tables, (3) list all 'New%' tables with zero rows (i.e. those that didn't get loaded, as all tables in the ODBC data source contain data).

I've tried:
select left(s2.name,32) as TableName, max(s1.rows) as Records
from sysindexes s1
inner join sysobjects s2 on s1.id=s2.id
where type = 'U' and s2.name like 'New%'
group by s2.name
HAVING max(rows) = 0
ORDER BY TableName
but of course there are multiple rows in sysindexes and the routine does not reliably return the correct list; for example the data in sysobjects & sysindexes, without the max and group by, might look like:
TableNameRows
NewARTxn0
NewARTxn1214800
NewARTxn1214800
NewARTxn1214800
NewARTxn1214800
NewARTxn1214800
NewARTxn1214800
I was hoping to come up with a single T-SQL statement that I could use in an xp_sendmail situation to email me the results.

Thanks for the suggestions.

Al

View 3 Replies View Related

SQL Case Statement To Return 0 For Empty Row

Jun 11, 2008

Hi,

I have some sql and i expect a row back with some information even if there is nothing. What i want to happen is when it returns an empty row then give me a 0 so at least i get something back I am filling a dataset here and it isnt populating the fields when an empty row is returned.

SELECT 3 row_id,
'2003' year,
'Mar' period,
(SELECT count(*) FROM news WHERE news.id IN (
SELECT news.id
FROM news
WHERE news.announced_date >= '2003-03-01' AND ........

So ive tried variations of the below in a CASE statement WHEN NUll or WHEN 0 but i still get nothing back. I would like the information to return 3 2003 Mar 0 if its empty.

SELECT 3 row_id,
'2003' year,
'Mar' period,
(SELECT CASE count(*) WHEN NULL THEN 0 ELSE count(*) END FROM news WHERE news.id IN (
SELECT news.id
FROM news
WHERE news.announced_date >= '2003-03-01' AND ............

Can anyone help me please??

View 8 Replies View Related

Return Primary Key Even When Foreign Key Is Empty

Feb 20, 2015

I have a query that returns contacts connected to a client, but the problem is that it's only returning a result when a contact is associated with a client, even if the client does exist in the db. i want it to still return the client if the client exists.

SELECT * FROM clients, addressbook where clients.clientid = addressbook.clientid AND (clients.clientname LIKE '" . strtoupper($_GET['txtfname'])."%')

View 1 Replies View Related

To Catch The Event When There Is Empty Dataset As A Result Of A Query In Sqldatasource

Aug 1, 2007

hi,
i want to execute a finctionX() based on the returned resultset of SQLDataSource. For example, if the SELECT command returns nothing based on a specific search criteria then I want to execute functionX(). Currently, I have a string that displays "No Result" in the GridView in such case.
How to catch the  resultset of SQLDataSource?
-nero

View 1 Replies View Related

Check For Null Or Empty Values And Return 0

Mar 13, 2014

I am using the below query to calculate column values. But I need to return zero when a column values is empty or null.

select [Funding] [Fundings],
[Original] AS [Originals],
[Variance] = SUM([Previous_Year]-[Current_Year]),
[SumValue] = SUM([CurrentYear]/4),
[ActualValue] = SUM([Variance] * 0.75),
[FinanceYear],
[New Value] = SUM([Previous_Year]+[Current_Year])
from Finance
GROUP BY [Original], [FinanceYear]

View 1 Replies View Related

SQL Server 2012 :: SSIS - Stop DataFlow Task If Result Set Is Empty?

Oct 7, 2014

I am trying to create a SSIS package that will create a csv of a dataset for daily events in the database. However there will be days that there was no activity and thus an empty dataset. The package still runs fine but I want to stop the package if the dataset is empty.

FLOW:

DATA FLOW task: get daily data and put in CSV file

FTP TASK: upload the file to FTP server

MAIL/Copy file task: Move the file and then send a confirmation mail on task completion status.

Pretty simple and it all works great, I do have a few complexities in there. What I would like to add and I am at a loss is at the beginning, if the OLE DB Task resultset is empty then move to Mail Task otherwise process normally. I have tried conditional split, derived columns, the only thing I haven't tried in Script task and am not sure about that yet.

View 4 Replies View Related

Different Result Same Sql Queries

Sep 26, 2006

I wrote 2 sql queries in two different ways.
The only difference in both queries are One using '=' and other one using 'in'.
All the other data is same.In my sense '=' and 'in' same
Both queries should give same result.
But one query giving different result and other query giving different result.
Is this 2 queries different??


SELECT sum(SALES_ITEM.NET_PRICE)
FROM PRODUCT_GROUP_LINK,
PRODUCT,
SALES_ITEM
WHERE (PRODUCT_GROUP_LINK.PRODUCT_CODE=PRODUCT.PRODUCT_CODE)
AND (PRODUCT_GROUP_LINK.PRODUCT_CODE=SALES_ITEM.ORIG_PRODUCT)
AND PRODUCT.STYLE_CODE='MC001'
AND (SALES_ITEM.PRICE_LIST='1FAC'
OR SALES_ITEM.PRICE_LIST='1FAW' )
AND CLASS_CODE='TYPE'
group by STYLE_CODE


SELECT sum(sales_item.net_price)
from PRODUCT,
product_group_link,
SALES_ITEM
WHERE (PRODUCT_GROUP_LINK.PRODUCT_CODE=PRODUCT.PRODUCT_CODE)
AND (PRODUCT_GROUP_LINK.PRODUCT_CODE=SALES_ITEM.ORIG_PRODUCT)
AND PRODUCT.STYLE_CODE='MC001'
AND sales_item.PRICE_LIST IN ('1FAC','1FAW')
AND CLASS_CODE='TYPE'
group by style_code

Any idea?
Thanks

View 5 Replies View Related

Copy Table Scripts Results In Empty Return From Sysobjexts?

May 22, 2001

Hi, I wanted to create an exact database in another server, so I generated script of all tables,views, store procedures and ran the script on the new server. I was able to have all objects in the new server.
but when I run the following sql from sql query analyser, I get nothing in return. What do I need in order to get a valid response.

Ali

/*Truncate from all tables*/
select 'Truncate table ' + name from sysobjects where type ='u'
order by name

/*Count all table rows from all tables*/
select 'select count(*) as ' +''+ name + ' from ' + name from sysobjects where type ='u'
order by name

/*View all sp*/

select * from sysobjects where type ='p' and name
not like 'dt%'
order by name

/*View all triggers*/

select * from sysobjects where type ='tr'
order by name

/*View all Views*/
use master
select * from sysobjects where type ='v'
order by name

View 1 Replies View Related

Analysis :: YTD / MTD Functions Return Empty Values Probably Due To Old Test Data

Jun 17, 2015

I have managed to use the BI Wizard for time intelligence and added YTD and MTD successfully. I notice the values returned are empty, and I think this is due to the fact that all the test data I use is many years old. What's the simplest way to resolve this issue so that I can see that these MDX functions return correct values? Changing the system date on this company laptop is not an option.

View 4 Replies View Related

Combine Result From Diff Queries

Jun 18, 2008

I have 3 sql queries:ex:select * from table 1 where id = 2select * from table 1 where  name = 'name'select * from table 1 where date = 'date' I want to combine these three queries into one stored procedure.I am not sure how to do this.i want to display  some column data from these 3 queries on 3 table rows as:<td> colum1 </td><td> colum2 </td><td> colum3 </td>so my SP should return some datatable .any suggestiions 

View 3 Replies View Related

Comparing Result Set Values Of 2 Queries ??

Apr 20, 2004

Any assistance would be so helpful !!

We have 2 tables.. lets call them INV and COST

Table INV and COST have 3 related columns, namely ID,AMOUNT and VAT. As shown below...

ID | AMOUNT | VAT ( INV TABLE )
1 |20.125 |2.896
2 |10.524 |1.425

ID | AMOUNT | VAT ( COST TABLE )
1 |20.125 |4.821 .... different to ID 1 in INV Table
2 |10.524 |1.425

If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)

Thats it ???

Please could someone out there offer some ideas ?

THANKS

JON

View 4 Replies View Related

Comparing Result Set Values Of 2 Queries ??

Apr 20, 2004

Any assistance would be so helpful !!

We have 2 tables.. lets call them INV and COST

Table INV and COST have 3 related columns, namely ID,AMOUNT and VAT. As shown below...

ID | AMOUNT | VAT ( INV TABLE )
1 |20.125 |2.896
2 |10.524 |1.425

ID | AMOUNT | VAT ( COST TABLE )
1 |20.125 |4.821 .... different to ID 1 in INV Table
2 |10.524 |1.425

If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)

Thats it ???

Please could someone out there offer some ideas ?

THANKS

JON

View 1 Replies View Related

Comparing Result Set Values Of 2 Queries ??

Apr 20, 2004

Any assistance would be so helpful !!

We have 2 tables.. lets call them INV and COST

Table INV and COST have 3 related columns, namely ID,AMOUNT and VAT. As shown below...

ID | AMOUNT | VAT ( INV TABLE )
1 |20.125 |2.896
2 |10.524 |1.425

ID | AMOUNT | VAT ( COST TABLE )
1 |20.125 |4.821 .... different to ID 1 in INV Table
2 |10.524 |1.425

If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)

Thats it ???

Please could someone out there offer some ideas ?

THANKS

JON

View 4 Replies View Related

Analysis :: Join Result Of Two MDX Queries

May 4, 2015

Required Output

Country Category
Internet Sales Amount
Internet Order Count

[code]....

I need to perform a SQL kind of Cross join to get the Total Count as a column along side as there are .net code and json structures defined that expects output in a certain format.

View 2 Replies View Related

Combining Results Of Two Similar Queries Into One Result Set?

Mar 5, 2012

Customers order a product and enter in a source code (sourceCd). This sourceCd is tied to a marketing program. Idea being we can see that 100 customers ordered from this promo, 200 from this catalog, etc etc. The sourceCd that a customer enters is not always accurate so there is a magic process that adjusts this OrigSourceCd into a final SourceCd, that may or may not be the same.

I am trying to generate a result set of customer count by sales program based on both the original and final source code. Problem is, I have to do each query separately because in one, I have to join SourceCdKey to SourceCdKey to get the program associated with that SourceCd and in the other i have to join OrigSourceCdKey to SourceCdKey to get the program associated with the original sourceCd. There are some programs is one results set that are not in the other, and vice versa.

I'm trying to generate a list of that shows customer counts before and after for each program, some which may be null for one, but have counts for the other. I have tries creating 2 separating views and joining them but that doesn't work because it only returns the ones they have in common.

View 6 Replies View Related

Combine 2 Queries To Produce One Result Table

Jan 9, 2014

I would like to pull all the columns from a table where the date column is within 6 months from the max date (i.e. Jul, Aug, Sep, Oct, Nov, & Dec). In addition to that, I would like to pull another column -the summary column - from the same table where the date = max(date) (Dec only).

I have written 2 queries and they produce the correct data. However, I don't know how to combine them into one resultant table. I tried to do a left join and had difficulties dealing with the different where statements from the 2 queries..

Here is query #1:

select investor, full_date, month_end_summary, category, loan_count
from cust_table
where datediff(month,full_date,(select max(full_date) from cust_table)) < 6
group by investor, full_date, month_end_summary, category, loan_count
order by investor, full_date

Here is query #2:

select investor, full_date, month_end_summary
from cust_table
where datediff(month,full_date,(select max(full_date) from cust_table)) =0
order by investor, full_date

Can they be combined into one query to produce one result table??

View 3 Replies View Related

MDX Queries Return Different Results

May 29, 2008

Hi,

I am new to MDX and I have created a query listed below, this returns the correct information from the cube. However when I split the query into a CREATE SET and Query the data returned is wrong. I need to include the set creation in the cube but this returns the wrong information. I thought that information returned by these two queries would be indentical can anyone explain please.

Thanks David


SELECT
({[Time Calculations].&[Current Period],[Time Calculations].[Prior Year]}) on columns,
Filter (([Store].[Store No].[Store No].Members),
([LFL Month Store].[Month Lf L Store].&[Month LfL Store]) <> 0) on rows
from finance
where( [LFL Calendar].[LFL Calendar Hierarchy].[Year].&[2008].&[Qtr 1 2008].&[P3:April 2008] ,
[Measures].[GL Amount])

----------------------------------------------------------------------------------------------------------------------------------------

create SET [Finance].[LFL Stores List] AS
Filter (([Store].[Store No].[Store No].Members),
([LFL Month Store].[Month Lf L Store].&[Month LfL Store]) <> 0)

SELECT
({[Time Calculations].&[Current Period],[Time Calculations].[Prior Year]}) on columns,
[LFL Stores List] on rows
from finance
where( [LFL Calendar].[LFL Calendar Hierarchy].[Year].&[2008].&[Qtr 1 2008].&[P3:April 2008] ,
[Measures].[GL Amount])

View 8 Replies View Related

How Can I Do Amalgamate 3 Select Queries And Then Get Unique Entries From The Result

Mar 7, 2006

Hi AllStrange request I know, but could somebody give me pointers on how I can put3 queries into 1 'thing' and then get only the unique entries from this'thing'.To explain, I'm using Excel/VBA/ODBC to query an SQL DB. The 3 queriesthemselves aren't that complex and all return the same 2 fieldsets of stockcode and stock desc. Because these separate queries might bring back thesame stock code/description I need to amalgamate the data and then queryagain to bring out only distinct stock values, eg:Query 1 brings back:stock code stock descIVP Invoice PaperSTP Statement PaperKGC Keyboard Coveretc... etc...Query 2 brings back:stock code stock descIVP Invoice PaperBOB Back PackKGC Keyboard Coveretc... etc...Query 3 brings back:stock code stock descKGC Keyboard Cover3.5"D 3.5" Disksetc... etc...I need to produce 1 resultset that shows:stock code stock descIVP Invoice PaperBOB Back Pack3.5"D 3.5" DisksKGC Keyboard CoverSTP Statement Paperetc... etc...(all unique entries)I'm currently just bringing back the 3 query results in Excel, but I'd liketo be able to do the above.In light of I'm using Excel/VBA/ODBC on a PC, is it possible to do?ThanksRobbie

View 1 Replies View Related

Datareader Does Not Return Result

Feb 23, 2007

the following is the code,....When i run this code I get "Invalid operation exception", Trying to read when no data is present, but the query returns result in query analyser...can anyone suggest what the problem is ...
Connection is open and the query is simple select statement
 
SqlCommand myCommand = new SqlCommand(query,myConnection);
myConnection.Open();
 
SqlDataReader myReader = myCommand.ExecuteReader();
 
Thanks
Niranch

View 7 Replies View Related

How To Return Rows 200 - 300 Of A 500 Row Result Set

Sep 6, 2000

What is the easiest way to return rows 200 through to 300 of a 500 row result set using SQL? Is there a simple way of doing this or do I need to write some Transact SQL? Any ideas would be appreciated.

Thanks

Rod

View 4 Replies View Related

Return Result Through ODBC

Oct 4, 1999

Dear all,

We are doing a query to SQL database through ODBC, to return a field named 'description'. The field has a maximum length of 1024 characters, ie, it is defined as varchar(1024). However, ODBC only return the first 255 characters. To rub more salt to the wound, when we are trying to insert a row of data into the field, ODBC actually truncated all the data after the first 255 characters, resulted in a little disaster.

I don't think we have a problem in the C programs, since the whole thing works in a little test database. (also in SQL7) I suspect there is a setting somewhere, only that the documentation doesn't give me much chance to find it. Any hints is desperately needed!!

Thanks in advance!

View 1 Replies View Related

The Statement Did Not Return A Result Set

Oct 28, 2007

I am using MSSQL 2000, SQL Server Developer Edition and product version is : 8.00.760 (SP3)

Application Server: JBoss v 4.0.4
Driver : sql 2005 driver.

Hi,

I am running my application in JBOSS and hibernate to do all the query to the DB. While we are testing our application, it is working fine for the 1st day of stress testing, however after running for one days, it starts throwing the following exception. This can occur in a number of method call with no fixed pattern. Anyone has any idea what is going on?

used by: org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2148)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:392)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:333)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1114)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:756)
at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:63)
... 88 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown Source)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:236)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1669)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2145)
... 97 more

View 2 Replies View Related

Return 2 Result Sets

Feb 29, 2008

Hi, I'm trying to return 2 different result sets using the below query mapped to 2 different variables in my execute sql task. I've tried this with one task and 2 seperate tasks but can't get it to work. Is this possible using only one task? It keeps giving me a result set error.


SELECT COUNT(*) AS DeceasedCount
FROM AMGR_User_Fields_Tbl
WHERE (Client_Id = '' or Client_Id is NULL) and Type_Id = 53

SELECT COUNT(*) AS LostCount
FROM AMGR_User_Fields_Tbl
WHERE (Client_Id = '' or Client_Id is NULL) and Type_Id = 469

View 5 Replies View Related

Transact SQL :: Have 1 Result Set Return

Nov 4, 2015

My desired output is:

abc - 2
def - 2
ghi - 2
jkl - 2

As you can see my query returns all the values from both tables instead of combining them.  This is SQL Server 2008

Create Table #1 (blah varchar(100), cnt int)
Insert Into #1 Values
('abc', 1)
,('def', 1)
,('ghi', 1)
,('jkl', 1)

[Code] ....

View 3 Replies View Related

Merging Queries To Return Different Fields In Same

Oct 25, 2005

hi all,

can anybody help in combining all the mentioned queries into a single query so as to display all fields in a row.

1.number of imported imported animal type:

select count(*) as import_lic_no from appln_clip ac,consign_animal c,pet p

where ac.ac_id=c.ac_id and p.pet_no=ac.clip_id and ac.appln_id like 'A8%'

2. number of imported animal type that are licensed:

select count(*) as lic_imp_ani_type from pet p,clip c where p.pet_no=c.clip_id


3.percentage: 2/1*100

select percentage=

((select count(*) as lic_imp_ani_type from pet p,clip c where p.pet_no=c.clip_id)
(select count(*) as lic_imp_ani_type from pet p,clip c where p.pet_no=c.clip_id))*100

4.local animal type:total pet records - 1

select number=

(select count(*) from pet p) - (select count(*) from appln_clip ac,consign_animal c where ac.ac_id=c.ac_id)


5.local animal type that are licenced:total pet records-2

select number1=
(select count(*) from pet p) - (select count(*) from pet p,clip c where p.pet_no=c.clip_id)


6.percentage: 5/4*100

select percentage=((select count(*) from pet p) - (select count(*) from pet p,clip c where p.pet_no=c.clip_id)/
(select count(*) from pet p) - (select count(*) from appln_clip ac,consign_animal c where ac.ac_id=c.ac_id))*100

thx,
vani

View 1 Replies View Related

Return Result Set From Stored Procedure

Dec 8, 2011

I know how to write a stored procedure that does various things to a database. I know that a stored procedure returns a value of 0 by default if it executes successfully, and a non-zero value otherwise. I know you can use output variables to return other values from a stored procedure. I am moderately familiar with these things.

But... how do I fashion it if I'm calling the stored procedure from VB.NET in a web application, and I don't just want a couple of variable values, I want the whole result set?

I know there's #temporaryTables that ...exist within the scope of the stored procedures... ##Globaltemptables... regular_tables... and @tableVariables. I'm reading furiously to figure out what these things...all...do... and I'm leaning in the direction of ... a variable table as an output variable, but I just don't know... how and what I can stuff that into in the front end so I can shove it into the nice and neat grid view thing.

(fyi, I'm trying to return a consolidated table of available rooms fitting the user's specified reservation dates and amenity preferences - that part, I've gotten done like a boss. It's...getting it back to the front end I'm struggling with.)

View 3 Replies View Related

Return Result Not Working With IN Clause

Feb 28, 2008

I have a form that has many checkboxes (more than 40) that provide information about companies.

For example. Company ABC
Checkbox 1 (Windows XP)
Checkbox 2 (Windows Vista)
Checkbox 3 (Windows NT4)
etc.

I save these in a SQL table like the following
Row 1 - Col 1 (CompanyID), col2 (checkbox value)
Row 2 - Col 1 (CompanyID), col2 (checkbox value)
etc.. so a comapany can have multiple checkbox selected..

I am currently developing a report where the end user can select one or many of the checkbox to see if the company exist.

This is where I get stuck - I only want to return the results of the companies that meet the report selections. So if I want to see all the companies that current have Windows NT and Windows Vista...how would I build that querry based on the data model above.

I have tried using the IN clause - however that use the OR connector and doesnt show ONLY the compaines that meet. I have tried using a UNION and INTERSECT, and running the select statement many times.. (SELECT CompanyID from xx WHERE CheckboxVal = xx)
INTERSECT
(SELECT CompanyID from xx WHERE CheckboxVal = xx)
etc.. howevert this creates a very large quesry that is unable to be handled by SQL 2005. I get a error message asking to minimize the query.

Any suggestions on this please...this is my 4th day working on this..

View 8 Replies View Related

Store Return Result From Sp_columns

Oct 31, 2007

I excute sp_columns in my Stored Procedure script to get the data type of a table column.
EXEC sp_columns @table_name = 'XXX', @column_name='YYY'
How do i store the column 'TYPE_NAME' in the return row into a variable so that i can use it later in my stored procedure?
Thanks
Hannah

View 1 Replies View Related







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