Query Round Off To Zero...no Real Values Returned Ever.

Apr 29, 2004

The query below gets percentage of coils for a location by anneal cycle. It does this correctly (ie, if you change count(*)/b.total to count(*), b.total ... show both columns, the numbers its pulling are correct), but in its current state (trying to divide count by total), it always returns a zero (no decimal values).

If I change that section to : (count(*)*100/ b.total*100)/100, I get integer percentages: no decimals (this is a problem because some records round off to zero). I'm pretty sure this is the entire problem (why I'm getting zeros as written below), but I don't have a clue how to fix it. Is this some kind of server setting that allows the query to only return decimal values or something?



select a.department, a.cycle, count(*)/ b.total percentage
from inventory a join (select department, count(*) total from inventory
where location not like 'bas%' and archived = 0 and quality_code = 'p'
group by department) b on a.department = b.department
where a.archived = 0 and a.quality_code = 'p' and location not like 'bas%'
group by a.department, a.cycle, b.total
order by a.department, a.cycle

View 2 Replies


ADVERTISEMENT

Query Results - Not Getting Different Values Returned For Each Row

Jan 24, 2012

I am getting the following results from my query that contains a subquery, but I don't understand why the values in the [Total Volume M_Active_Previous] are being repeated with the same value. I should be getting different values returned for each row like in the [Total Volume M_Active] column.

Why the values are all the same and how I can fix this?

DayNoDayTotal Volume M_ActiveTotal Volume M_Active_Previous
6 Friday11161 72491
7 Saturday5687 72491
2 Monday14354 72491
1 Sunday3966 72491
4 Wednesday12340 72491
3 Tuesday12018 72491
5 Thursday11833 72491

Here is the SQL I'm using.

Code:
DECLARE @StartDate datetime, @EndDate datetime, @Prev_StartDate datetime, @Prev_EndDate datetime
SET @StartDate = '2011-03-13 00:00:00.000'
SET @EndDate = '2011-03-19 23:59:59.999'
SET @Prev_StartDate = '2011-03-06 00:00:00.000'
SET @Prev_EndDate = '2011-03-12 23:59:59.999'

[Code] ....

View 5 Replies View Related

SQL Server 2014 :: SSAS Stored Procs (CLR) - Identify Real Data Type Of MDX Value Returned From Expression

Feb 13, 2015

I have a SSAS stored procedure with a signature:

public Set DoSomthing(Set toBeProcessed, Set measuresToWorkWith)The set measurseToWorkWith is passed as {[Measures].[Measure1], [Measures].[Measure2] ...}

with the measures being real or query-scoped calculated members.

To get the value of the measure for each tuple in the set toBeProcessed, I create an Expression for each tuple (measure) in the set measuresToWorkWith then for each tuple in toBeProcessed call expression.Calculate(tuple) which returns a MDXValue.

My problem is that in order to make the code generic I need to get the real (.NET) data type of the MDXValue. The class only has explicit conversion methods ToInt16() etc which implies that the data type is known at design time.

However, if one of the measures is a query-scoped calculation then it could return a .NET double, int, bool or string.

If the measure is real then I can look up its metadata. However, it appears that if it is a formula (scoped member) then are all bets are off?

View 0 Replies View Related

Round Off Sum Values

Mar 19, 2014

I have already summed the values and this below query sum the values and returns zero if there are no values. How can I roundoff the values for these columns.

isnull(sum([Old_Values]),0) [Old Values],
[New_Value] = isnull(sum([Yearly_Value]-[Previous_Year_Value]),0)

Even this query I use for calculation and I need to round off the values

[Product_Price] = [Product_Profit]/4,
[Product_Profit] = [ActualValue] * 0.75,
[GrossValue]

View 1 Replies View Related

NULL Values Returned When Reading Values From A Text File Using Data Reader.

Feb 23, 2007

I have a DTSX package which reads values from a fixed-length text file using a data reader and writes some of the column values from the file to an Oracle table. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Data Reader. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?

View 12 Replies View Related

Reporting Services :: Round Up Values In SSRS?

Oct 21, 2015

I want to round 0.356 to 1? How can i do a round up in SSRS reporting services. 

I am using this code:

The Sum of the Price_Call here is  0,3965 ---> I want to round it to one!

= Round (     Sum(Fields!Price_Call.Value), MidpointRounding.AwayFromZero   ) --- > This is always giving me 0

View 4 Replies View Related

Data Returned From .WriteXML Is Different Than What Is Returned In Query Analyzer.

Jun 20, 2006

I have a strange problem. I have some code that executes a sql query. If I run the query in SQL server query analyzer, I get a set of data returned for me as expected. This is the query listed on lines 3 and 4. I just manually type it into query analyzer.
Yet when I run the same query in my code, the result set is slightly different because it is missing some data. I am confused as to what is going on here. Basically to examine the sql result set returned, I write it out to an XML file. (See line 16).
Why the data returned is different, I have no idea. Also writing it out to an XML file is the only way I can look at the data. Otherwise looking at it in the debugger is impossible, with the hundreds of tree nodes returned.
If someone is able to help me figure this out, I would appreciate it.
1. public DataSet GetMarketList(string region, string marketRegion)2. {3.   string sql = @"SELECT a.RealEstMarket FROM MarketMap a, RegionMap b " + 4."WHERE  a.RegionCode = b.RegionCode"; 5.   DataSet dsMarketList = new DataSet();6.   SqlConnection sqlConn = new SqlConnection(intranetConnStr);   7.   SqlCommand cmd = new SqlCommand(sql,sqlConn);8.  sqlConn.Open();9.   SqlDataAdapter adapter = new SqlDataAdapter(cmd); 10.   try11.   {12.   adapter.Fill(dsMarketList);
 13.  String bling = adapter.SelectCommand.CommandText;//BRG 14.   dsMarketList.DataSetName="RegionMarket"; 15.  dsMarketList.Tables[0].TableName = "MarketList"; 16.    dsMarketList.WriteXml(Server.MapPath ("myXMLFile.xml" )); // The data written to  17. myXMLFile.xml is not the same data that is returned when I run the query on line 3&4 18.           // from the SQL query 19.  } 20.  catch(Exception e) 21. {  22. // Handle the exception (Code not shown)

View 2 Replies View Related

SQL Server 2012 :: Decimal Data Type Round Off The Values

Sep 11, 2014

I have table with data type decimal (18,2) when i try to load more then 2 decimal it is rounding off

Example 34.456 is rounded with 34.46

I want to store 34.45 only with out round in decimal data type how can i achive this

View 2 Replies View Related

Returning Formated Real Values

Jan 21, 2008



I have some Sp that return real values, I''m using function like SELECT CONVERT(REAL,field) FROM .... to ensure that conversion and rounding is done.

When I got the values in my application (Datagrid) I receive values having even 9 decimals

How can efectively return only 2 decimals from my query?

Thanks

View 3 Replies View Related

SQL Server 2K Problem With Fractional Real Values

Jul 23, 2005

I have a field of type Real in my SQL 2K database. I stored a value of..35 in the field.When I "Open Table"->"Return All Rows" in the Enterprise Manager I getback .35 for the field value.I went to The SQL Query Analyzer and executed the following T-SQL:SELECT fieldFROM tableI got back 0.34999999 for the field.When running stored procs against the field I also get back 0.34999999.This is causing problems in my app. I can use the Round T-SQLstatement to get back the value I expect, but this causes appdevelopment problems. For a goof I put 1.35 in the field and T-SQL didreturn 1.35. This problem only seems to occur with 0.nnn values. Ialso tried a float data type for the field but I had the same problemsI had with real.Why is T-SQL returning 0.34999999 for my field?

View 1 Replies View Related

SQL Query - 'ROUND' COMMAND

May 26, 2007

Has any of you ever had problems with the ROUND command?
I have this operation:
ROUND((FIELD1 / 360 * FIELD5),3) AS FIELD7
(I want the result to be rounded at the third decimal).
SQL makes mistakes in rounding. It seems to me that
the problem relies in the quantity of decimals taken
into account in any single step of the operation.
Thank you in advance.
Anna - Verona (Italy)

View 5 Replies View Related

Using Returned Values From One SP In Another

Jun 20, 2006

Hi all,

I have a SP that needs to return a record set based upon the values of calling another SP. The 2nd SP uses the output parameter and works fine. However, the resulting Insert doesnt see the value. I cannot for the life of me work out why not!

My other question is how do I stop the imbedded procedures from outputing a result? When the procedure is run in analyser it produces three sets of data. The first two being the outputs for the two SPs.

Any help would be much appreciated!

Code as follows:

CREATE PROCEDURE [dbo].[trl_GetOpenDurations] AS

--- CALLS the sp trl_CallOpenDuration with the following params
---1 min minute value - int
---2 max minute value - int
---3 return value - int - interval in minutes

set nocount on

declare @min int --- less than 1 min
declare @hour int --- more than 1 min, less than 1 hour

create table #t ([Type] VARCHAR(10) ,[Value] int)

EXECUTE trl_callOpenDuration 0,1,@min
insert into #t ([Type],[Value]) values ('<1Min', @min)

EXECUTE trl_callOpenDuration 1,60,@hour
insert into #t ([Type],[Value]) values ('<1Hour',@hour)
---get data from table
Select * from #t
GO


RESULTS:

<1Min NULL
<1Hour NULL

View 1 Replies View Related

Ambiguous Values Returned

May 15, 2008

Hello,

I'm brand new to this forum and I'm stuck. We've got a query which queries three tables: booking, catheter and patients. The following (dodgy) query has pulled off the data for years, without problems:


SELECT
CARDIAC_CATHETER.CARDIAC_CATHETER_ID,
CARDIAC_CATHETER.OPERATOR1,
CARDIAC_CATHETER.TEST_DATE,
BOOKING.STATUS,
BOOKING.TEST_DATE AS BOOKING_TEST_DATE,
BOOKING.PROCEDURE_DATE_TIME,
CARDIAC_CATHETER.ITEM_NO,
CARDIAC_CATHETER.BILLING_CATEGORY,
PATIENTS.REFERRING_PHYSICIAN1,
PATIENTS.HOSPITAL_UNIT_NUMBER
FROM
(CARDIAC_CATHETER INNER JOIN PATIENTS ON CARDIAC_CATHETER.PATIENT_ID = PATIENTS.PATIENT_ID)
LEFT OUTER JOIN BOOKING ON PATIENTS.PATIENT_ID = BOOKING.PATIENT_ID AND 'CATHETER' = BOOKING.CLINIC AND BOOKING.STATUS <> 'CANCELLED' AND BOOKING.PROCEDURE_DATE_TIME >= (CARDIAC_CATHETER.TEST_DATE - 1)
AND BOOKING.PROCEDURE_DATE_TIME < (CARDIAC_CATHETER.TEST_DATE + 1)
WHERE
CARDIAC_CATHETER.TEST_DATE > '2005-04-01' AND CARDIAC_CATHETER.BILLING_CATEGORY LIKE '3%'


About 2500 perfect rows and 12 rows which have duplicate catheter id values. There are six pairs of duplicated records. It's only the cardiac_catheter.catheter_id and cardiac_catheter.test_date which are duplicated - the first row shown below contains correct values, the second doens't. The other values returned, on both rows, are correct. Here is some sample data:

CARDIAC_CATHETER_ID
cat_id1234
cat_id1234

CARDIAC_CATHETER_TEST_DATE
19/12/2005 13:17
19/12/2005 13:17

BOOKING_TEST_DATE
19/12/2005 13:33
19/12/2005 08:46

PROCEDURE_DATE_TIME
20/12/2005 10:30
19/12/2005 08:46

CLINIC
CATHETER
CATHETER

I know the query is rubbish. Has anyone got any idea how it can be improved and sorted so that we don't pull back ambiguous data?

Thanks,

Stewart

View 5 Replies View Related

How Do I Get Uppercase Values Returned Only.

Jul 23, 2005

I have a table where inactive names are lower case and active names areuppercase. Note: Not my design.Anyways I want to select all names form this table where the name isuppercase. I see collate and ASCII pop up in searches but the examplesdon't seem usable in queries as much as they were for creating tablesand such.Thanks,Phil

View 5 Replies View Related

Stored Procedure && Returned Values

Dec 23, 2007

I have a stored procedure that selects * from my table, and it seems to be working fine:
 USE myDB
GO
IF OBJECT_ID ( 'dbo.GetAll', 'P') IS NOT NULL
DROP PROCEDURE GetAll
GO
CREATE PROCEDURE GetAll
AS
DECLARE ref_cur Cursor
FOR
SELECT * FROM myTable
Open ref_cur
FETCH NEXT FROM ref_cur
DEALLOCATE ref_cur
 
The problem is, I'm trying to create a DB class, and I'm not sure what Parameter settings I'm supposed to use for my returned values.  Can anyone help me finish this?public class dbGet_base
{
public dbGet_base()
{
_requestId = 0;
}

public dbGet_base(Int64 RequestId)
{
this._requestId = RequestId;
getDbValues(RequestId);
}
public void getDbValues(Int64 RequestId)
{
getDbValues(RequestId, "GetAll");
}
public void getDbValues(Int64 RequestId, string SP_Name)
{
using(SqlConnection Conn = new SqlConnection(ConfigurationManager.AppSettings["WSConnection"]))
using (SqlCommand Command = new SqlCommand(SP_Name, Conn))
{
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Add("@Request_Id", SqlDbType.Int).Value = RequestId;
Command.Parameters.Add(??
}

View 3 Replies View Related

Any Way To Check If All The Values In A Column Returned Are The Same Value?

Jan 4, 2005

say i have a table, and in it are two columns, column1 and column2 and i do the following query:

SELECT column1, column2 FROM table WHERE column2 = '12345'

i want to check if column1 has all the same values, so in the first case, no


column1 column2
------- --------
4 12345
9 12345
5 12345




column1 column2
------- --------
9 12345
9 12345
9 12345


in the 2nd case, column1 contains all the same values, so yes

is there anyway i can check this? i would be doing this in a trigger.. say when a new row is inserted, the value of column1 is inserted, but col 2 is null.. so when they try to fill in the value for col2 of that row, the trigger checks to see if the value they put for col 2 is already in the table.. if it isn't, then everything is ok. but if it is already in teh table, then it checks col1 to see if all the values of col1 are the same

i hope this makes sense

thanks

View 2 Replies View Related

Changing Values Returned...Newbie Needs Help.

May 29, 2008

Hi Guys,

I have had a look through the forum and I can't seem to be able to find how to do this... I have a vague recollection of achieving this before but it was many years ago since I've done any sql.

I have a table like this

StaffID JobNumber Status
------- --------- ------
1 50 'In Progress'
2 20 'On Hold'
3 30 'Completed'
4 40 'Completed'


what I want to be able to do is change the status to something common if it is not 'Completed' I don't want to update the database though, I just want to return it in a select statement. Is this possible.

My output would be something like;

StaffID JobNumber Status
------- --------- ------
1 50 'NOT Completed'
2 20 'NOT Completed'
3 30 'Completed'
4 40 'Completed'

Many thanks in Advance.

Bobg

View 3 Replies View Related

Reading Values Returned From A Stored Procedure

Nov 10, 2000

I am trying to use a stored procedure inside the scripter in a site server pipeline. Can anyone tell me how the scripter will read the the result which is a variable. The stored procedure is returning the right value when run in query analyzer but I don't know how to retrieve it inside the pipeline.

Thank you
JG

View 1 Replies View Related

Having Rows With Some Null Values Returned Conditionally

Aug 8, 2012

This should be a simple solution, but it has been a long time since I've done any query writing (mostly in Oracle) and I am stumped, so here goes:

We are in the process of converting Access database to MSSQL with web form front ends.

I have a table, all columns are nullable, and want users to be able to query from a form, which has a field for each column and defaults to a % wild card for the entered value.

I want the users to be able to put any string in any field, and have it return each row that matches that, including rows with null values in the other columns, but not the column with the entered criteria.

Here is a sample of the data:

Code:
SQL> select * from test;

COL1 COL2 COL3 COL4
----- ----- ----- -----
this is a test
this is not test
this is not
this is test too
is test too
is too
is too

7 rows selected.

Now, if I have this SQL run, it will return only rows that have no nulls in any columns:

Code:
select
col1,
col2,
col3,
col4from test
where
col1 like'th%'
and col2 like '%'
and col3 like '%'
and col4 like '%';

COL1 COL2 COL3 COL4
----- ----- ----- -----
this is a test
this is not test
this is test too

Now, if I use an OR clause for each column, this mostly works, but the trouble is it will also return rows with null values for the field that has criteria entered in it:

Code:
select
col1,
col2,
col3,
col4from test
where
(col1 like'th%' OR col1 is null)
and (col2 like '%' OR col2 is null)
and (col3 like '%' OR col3 is null)
and (col4 like '%' OR col4 is null);
COL1 COL2 COL3 COL4
----- ----- ----- -----
this is a test
this is not test
this is not
this is test too
is test too
is too
is too

The idea is to only select the first 4 rows in the above example.

I was playing with ISNULL in the select clause, but all it does is substitute a string for a null, and I think CASE will do the same thing.

Is there a way I can write this query so it will return rows with NULL values in any column, except the one(column) that has user entered criteria in it?

View 9 Replies View Related

Using Values Returned From SQL For Report Column Names

Apr 17, 2008

I am building reports against our TFS development db.
One of the reports tracks days spent (Dwell Time) in various status categories (eg: New, Assigned, In Development, Hold, etc) for a given "ticket".
For a fixed list of values from {Work Item].System_State, I can send the results (days in Assigned) to the column named (Assigned) for each status for each event in the [Work Item History], and then sum them for each ticket as:
Ticket ID New Assigned InDev etc
1230001 2 0 0 ...
1230001 0 1 2 ....

SUM 2 1 2 ....
However, I have many different Projects, each of which use their own Status names.
I don't want to duplicate the same basic report, if I can avoid it.

How can I name and generate this data for the unique Status list for each Project?

Simplest analog is: name = First(Fields!Status.Value, "TFSdb")
and allows value for a column name (category) as: =IIF(Fields!Status.Value = First(Fields!Status.Value, "TFSdb"), Fields!Days.Value, 0)
However this fails beause:
1. It only delivers the FIRST status value, and,
2. I cannot SUM an expression which is itself an aggregate (using First).

View 4 Replies View Related

Values For SEARCHABLE Column Returned From Sp_datatype_info

Sep 15, 2006

I have a question regarding the values returned in the SEARCHABLE column of the system stored procedure sp_datatype_info. What do ALL of the values mean? The SQL Server BOL only provides meanings for the values 0 and 1, but I am also seeing values 2 and 3. What are the meanings of these values?

Thanks,
Sean

View 2 Replies View Related

MSOLAP_NODE_SCORE Values Returned By Store Proc DTGetNodeGraph

Oct 19, 2006

Hello--

For extracting the link structure of a dependency network with a large number of nodes (for problems having a large number of variables), we have been using the stored procedure:

System.Microsoft.AnalysisServices.System.DataMining.DecisionTreesDepNet.DTGetNodeGraph('{model-name}', value)

The stored procedure returns a resultset with columns: [Node_type], [Node_unique_name_1], [Node_unique_name_2], and [MSOLAP_NODE_SCORE]

Are there any pointers, references or descriptions of the values of [MSOLAP_NODE_SCORE]?

Thanks,

- Paul

View 5 Replies View Related

Query Question For A Real Guru

Feb 27, 2007

Hello,

I've been trying to fill a tree view based on some criteria in another table.
I have the standard table with a ID - ParentID - Text
when I bind the table everything works fine.

What I need to do is display only the tree items based off a username and ID in another table.

Files can be found http://www.cthere.com/treeviewhelp.zip


EXAMPLE:
tblTourgroup(UserName = Jim)
tblTourgroup (GroupID = 110)
tblGroups (GroupID = 110)

Results from tblGroup:
- RESTAURANTS
- Cuisine
- Steakhouse

Query Results should be layed out like tblGroups

Any ideas?
This is what I have so far... But it does not traverse up and get the other nodes..

USE SQL2005_335573_cplanet;
GO
WITH DynamicTree (ParentID, GroupID, GroupName)
AS
(
SELECT e.ParentID, e.GroupID, e.GroupName
FROM tblGroups AS e
INNER JOIN tblTourGroup AS edh
ON e.GroupID = edh.GroupID
)

SELECT e.ParentID, e.GroupID, e.GroupName
FROM DynamicTree as e

INNER JOIN tblTourGroup AS dp
ON e.GroupID = dp.GroupID
WHERE dp.UserName = N'Test'
GO

View 1 Replies View Related

Can The OLE DB Command Transformation Support Multiple Values Returned By A Stored Procedure Or Is The Limit One Value Only?

Apr 10, 2007

I have no problem getting OLE DB Command transformations to support single returns by a procedure.

For example, exec name_of_procedure ?,?,? OUTPUT



However, I have a stored procedure which accepts 1 input and returns 5 outputs. This procedure works fine at the command line but when I try to incorporate it into a OLE DB Command I don't get the multiple values returned. There's no problem at all configuring the transform as it recognizes all input and output parameters. For some reason I just don't get values returned.



thanks

John

View 14 Replies View Related

Query Analyzer Backbreaker, A Real Toughie.

Feb 18, 2002

I know that you can alter the maximum characters per column value in Query Analyzer by using the menu item 'TOOLS' then 'OPTIONS', then selecting the 'RESULTS' tab. I'm convinced that there is a way to do this programmatically (need it for a stored procedure) but cannot figure out how and would appreciate any help given.

Thanks,
rdchalker@hotmail.com

View 1 Replies View Related

Integration Services :: SSIS Excel Data Source Numeric Values Returned As Null

May 8, 2009

I'm using SSIS 2005 Enterprise edition,  I'm creating a package that reads an excel (xls) file using the "excel source" component, and it dumps the data into an OLEDB destination (a sql server). When I drag the excel source component and create the excel connection to my file the component automatically reads the columns and their datatypes.

The problem is that I have a column which has numeric data and the package uploads as NULL every number that starts with a zero. (note: in excel this column is formatted as "text", despite it has only numbers, because it's the only way excel maintains the left sided zeros).

So I checked the data types by right clicking the excel source component -> show advanced editor and my surprise is that this column's data type is detected as double-precision float, and it doesn't let me change it. URL... but it only works when the first row of data has a number beginning with zero on this column. How to get the data imported correctly?

View 15 Replies View Related

Noob Alert! What I Thought Was A Real Simple Query...

Oct 22, 2007

Hi

I have a table which has the column [itemNumber] Which contains numbers from 000 to 999. I have another table which has the UPC data for given items
I am trying to get results from my query that will show me every number in the itemNumberSet table that does not already exist (in the substring) of the UPCcode column.

By using the query below i am able to retrieve the opposite, and it works by returning results that do exist in the UPCcode column. But I cannot seem to get it to do the opposite which is what i am after. I figured it would be as simple as using NOT IN but that returned 0 results.


SELECT itemNumber FROM itemNumberSet
WHERE itemNumber IN (select SUBSTRING(UPCcode, 9, 3) FROM itemUPCtable)
ORDER BY itemNumber


Thanks for any suggestions you might have.
J

View 3 Replies View Related

Reporting Services :: Date Range Filter Based On Date Values Returned In Report?

Aug 27, 2015

I have a QA Deployment Date field that is being returned in a custom report I created. I also found a sample date range parameter:

What I want to accomplish:

I want to select a From and To Date and filter the report to only display the rows that have the QA Deployment Date within the selected range.

For example.. I want to select From Date (8/1/2105) and To Date (8/31/2015) and I only want to return only the results that have a QA Deployment date between that selected range.

View 3 Replies View Related

Error: Cursor Not Returned From Query

May 26, 2006

I'm a really beginner about sql2000.During my test I have created the following query. It's works ok until Ido't add the code included in section A, when I add it the i obtain theerror: Cursor not returned from queryAnyone can help me?Thanks Carlo M.set nocount onIF OBJECT_ID('storico_big') IS NULL --- section A begincreate table storico_big( data datetime,bcarrier varchar(20),bda CHAR(30),bzone char(50),bdur int) ;insert into storico_big --- section Aendselect top 10000adetdate,bcarrier,bda,bzone,bdurfrom pp_cdr (nolock)whereadetdate < :data_fin and adetdate > :data_in order by adetdateset nocount off------ end of query

View 3 Replies View Related

Executing Multiple Query Returned

Feb 24, 2008

Hi,

I am executing following script which return more than 1 query/row as there are more than 1 log backup :
select @Query = 'RESTORE LOG ' + @dbName + ' FROM DISK=' + '''' +@path+''''+ ' WITH' + ' NORECOVERY, FILE= ' + convert(varchar(10),Position) from #temp where BackupName = 'Log_Backup' and BackupType = 2 and Position> @Diff_Position EXEC(@QUERY)

[Above script restores database with latest Differential backup file and latest log files. ]
Now I want to execute this, but @Query don't work for more than one row returned . Can you please suggest me how to execute such query?



Thanks,
Dipak.

View 1 Replies View Related

Can SQL Assign Zero When NULL Is Returned From A Sub-query?

Dec 26, 2006

Hi Folx,
I am new to
SQL Server. I am rolling summed values from table profile to two columns in table txn. When the value for one (not necessarily both...) column is null, can that value be translated to zero? Or must I write a procedure or use a cursor?
Versions:
Microsoft SQL Server Integration Services Designer
Version 9.00.1399.00

Microsoft SQL Server Management Studio
9.00.1399.00

For example, when version_count for a particular transaction_id is null in the profile table but profile_count has summable integers, how do I update the version_count to zero in the same pass that I update the profile_count?

update txn
set
version_count =
(
select
sum(a.version_count)
from
profile a
where
a.transaction_id=txn.transaction_id
and
a.extraction_date=txn.extraction_date
and
txn.version_count is null
),
profile_count =
(
select
count(*)
from
profile a
where
a.transaction_id=txn.transaction_id
and
a.extraction_date=txn.extraction_date
and
txn.profile_count is null
)

View 5 Replies View Related

Listbox To Only Appear If There Are Records Returned From The SQL Select Query

Oct 19, 2006

I would like to make a listbox only appear if there are results returned by the SQL select statement. I want this to be assessed on a click event of a button before the listbox is rendered.I obviously use the ".visible" property, but how do I assess the returned records is zero before it is rendered?

View 3 Replies View Related

Row Count Returned By A Select Query In Result

Jul 30, 2013

I want to show the number of rows returned by a select query in the result.

e.g. select policy,policynumber,datecreated,Firstname, '-' as recordcount from policy

If it returns 5 rows, then the 'recordcount' need to show '5' in all row in the result

OutPut

0y96788,HGYG564,29/07/2013,SAM,5
FJUFBN7,JLPIO67,29/07/2013,Test,5
...
..
..

How can i get this number in the result.

View 3 Replies View Related







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