Numbering The SQL Query Statements
Jan 4, 2007Using the TOAD application for Oracle, I am able to number the SQL query lines for readability. Can the same be done for SQL Server 2005?
View 4 Replies
ADVERTISEMENT
Please Help..Query With Numbering
Mar 31, 2006Is It possible that, if there are 5 rows selected out, each row will automatically have the number from 1 to 5?
for example, after select query, results:
---------------------------------------
1Tom100
2May93
3Apple87
4Johnny72
5Noel67
Numbering A Query
Jul 20, 2005Is there a quick way to number a query?ie I want to number the records returned by a recordset consecutively.This seems like it should be simple but I haven't figured out how to do ityet.Any help is appreciated!TIACarter
View 2 Replies View RelatedSQL Server Admin 2014 :: Estimated Query Plan For A Stored Procedure With Multiple Query Statements
Oct 30, 2015When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.
1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?
<ParameterList>
<ColumnReference Column="@Measure" ParameterCompiledValue="'all'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?
Using IIF Statements Inside A Query?
Jan 30, 2008
Hi,
I'm wondering if it is possible to use IF statements in a query, for example if this was my query:
SELECT Asset, Source, Val1, Val2, Val3
FROM tableA
Say the sign of the Vals is always positive, but based on if the Source field is null i want to make the Vals negative.
Could I do something like this:
SELECT Asset, Source, (IIF Source = null, Val1*-1, Val1), (IIF Source = null, Val2*-1, Val2), (IIF Source = null, Val3*-1, Val3)
FROM tableA
When I try something like this it doesn't work, is there a way to do this in a query?
Thanks.
Can We Combine These 3 Statements Into One Single Query
May 13, 2004SELECT 1 as id,COUNT(name) as count1
INTO #temp1
FROM emp
SELECT 1 as id,COUNT(name) as count2
INTO #temp2
FROM emp
WHERE name <>' ' AND name IS NOT NULL OR name <> NULL
SELECT (cast(b.count2 as float)/cast(a.count1 as float))*100 AS per_non_null_names
FROM #temp1 a INNER JOIN #temp2 ON a.id=b.id
Maximum UNION Statements In A Query
Dec 17, 2005Wondering if there is a physical or realistic limitation to the numberof UNION statements I can create in a query? I have a client withapprox 250 tables - the data needs to be kept in seperate tables, but Ineed to be filtering them to create single results sets. Each tableholds between 35,000 - 150,000 rows. Should I shoot myself now?lq
View 15 Replies View RelatedSql Query Statements && ADO.NET Connected Classes.
Oct 9, 2007Hi all,
Do I still need to write the .sql query statements (in SSMS) if I use the ADO.NET connected classes like GetSchema method or the DbDataAdaptor Object?
I'm very new to Visual Studio 2005 & SQL Server 2005..
Thanks!
Help With Optional Parameter Query With IN Statements
Aug 24, 2007I have a query with 17 separate, optional, parameters. I have declared each parameter = NULL so that I can test for NULL in the case that the user didn€™t not pass in the parameter.
I am new enough to SQL Server that I am having difficulty building the WHERE clause with all of these optional parameters.
One solution I was advised on by a well paid SQL programmer, was to use a string in the stored proc and dynamically build the WHERE clause and exec it at the end of the sp. But the whole point of a stored proc is that it can be compiled and cached to make it faster, yet the string approach makes it have to compile every time it€™s run! Not a good solution, but maybe it€™s the best I can do . . .
I have tried many different approaches using different functions, etc. but I€™ve hit a brick wall. Any help in sorting it out with YOUR techniques would be greatly appreciated:
1. To add the parameter to the WHERE clause and test for NULL I€™ve used the COALESCE function such as €œWHERE table.fieldname = COALESCE(@Param, table.fieldname)€?. This works well if there is only one item in the parameter, but in the case that I pass multiple items to the parameter, it completely fails.
2. To handle multiple items, for example, if @Param = €˜3,7,98€™ (essentially, a csv separated list of keys)
Code SnippetWHERE table.fieldname IN(COALESCE(@Param, table.fieldname))
doesn€™t work because @Param needs to be parsed from a string into an array of integers in the parameter. So, I am using a UDF I discovered to parse the multi-item parameter. The UDF can be found at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/TreatYourself.asp and it returns a table variable that can be used in an IN statement. So I€™m using
Code SnippetISNULL(table.fieldname, 0) IN (SELECT value FROM dbo.fn_Split(@Param,€™,€™))
which works brilliantly in my WHERE statement AS LONG AS @Param ISN€™T NULL. So how do I test for NULL first and still use this approach to multi-item parameters?
I€™ve tried
Code SnippetWHERE @Param IS NULL OR ISNULL(table.fieldname, 0) IN (SELECT value FROM dbo.fn_Split(@Param,€™,€™))
and though it works, the OR causes it to slow way down as it compares every record for the OR. (It slows down by approximately 800%.) The other thing I tried was
Code SnippetISNULL (table.fieldname, 0) IN (CASE WHEN @Param IS NULL THEN ISNULL(table.fieldname, 0) ELSE (SELECT value FROM dbo.fn_Split(@Param,€™,€™)))
This fails with €œSubquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression€? due to the multiple values in the parameter. (I can€™t understand why the line without the CASE statement works, but the CASE line doesn€™t!)
Am I even on the right track, cuz this is driving me mad and I just need a way to deal with optional multi-item parameters in an IN statement? HELP!
How Should I Write This Query Wiht Case Statements
Oct 5, 2007Hi
I have a stored procedure and i am trying to add case statements to them.. but i am getting an Error. which is
Msg 125, Level 15, State 3, Procedure udf_EndDate, Line 34
Case expressions may only be nested to level 10.
And This is my sproc-- ================================================
-- Template generated from Template Explorer using:
-- Create Scalar Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:<Description, ,>
-- =============================================
Create FUNCTION [dbo].[udf_EndDate] (@PeriodId int)
RETURNS datetime
AS
BEGIN
DECLARE
@Month int,
@Year char(4)
SELECT
@Month = [Month],
@Year = Cast([Year] as char(4))
FROM
Period
WHERE
PeriodId = @PeriodId
RETURN
CASE @Month WHEN 1 THEN '1/31/' + @Year ELSE
CASE @Month WHEN 2 THEN '2/28/' + @Year ELSE
CASE @Month WHEN 3 THEN '3/31/' + @Year ELSE
CASE @Month WHEN 4 THEN '4/30/' + @Year ELSE
CASE @Month When 5 Then '5/31/' + @Year ELSE
CASE @Month When 6 Then '6/30/' + @Year ELSE
CASE @Month When 7 Then '7/31/' + @Year ELSE
CASE @Month When 8 Then '8/31/' + @Year ELSE
CASE @Month When 9 Then '9/30/' + @Year ELSE
CASE @Month When 10 Then '10/31/' + @Year ELSE
CASE @Month When 11 Then '11/30/' + @Year ELSE
CASE @Month When 12 Then '12/31/' + @Year ELSE null END
END
END
END
END
END
END
END
END
END
END
END
END
Any help will be appreciated.
Regards
Karen
Using SQL Query Columns In Select Case Statements
Jun 5, 2006I am using Visual Web Developer Express 2005 as a test environment. I have it connected to a SQL 2000 server. I would like to use a Select Case Statement with the name of a column from a SQL Query as the Case Trigger. Assuming the SQLDataSource is named tCOTSSoftware and the column I want to use is Type, it would look like the following in classic ASP:
Select Case tCOTSSoftware("Type")
Case 1
execute an SQL Update Command
Case 2
execute a different SQL Update Command
End Select
What would a comparable ASP.Net (Visual Basic) statement look like? How would I access the column name used in the SQLDataSource?
Multiple FROM Statements Inside A Single Query?
Mar 21, 2012what is the logic in multiple "FROM" statements inside a single query?
View 1 Replies View RelatedSelect Query Involving Many JOIN Statements.
Mar 19, 2008Is it possible to have an AND within an inner join statment? The below query works, except for the line marked with --*--.
The error I get is the "multipart identifier pregovb.cellname could no be bound", which usually means that SQL server can't find what I'm talking about, but it's puzzling, as I've created the temp table with such a column in it.
Is there a different way i should be structuring my select statement?
SELECT [Survey Return].SurveyReturnID, '1', #temp_pregovb.paidDate, #temp_pregovb.email
FROM #temp_pregovb, [Survey Return]
INNER JOIN SelectedInvited ON
[Survey Return].SelectedID = SelectedInvited.SelectedID
--*-- AND [SelectedInvited].cellref=#temp_pregovb.cellname
INNER JOIN [panelist Contact]
ON SelectedInvited.PanelistID=[Panelist Contact].PanelistID
WHERE
[panelist contact].email=#temp_pregovb.email
AND SelectedInvited.CellRef IN (
SELECT surveycell
FROm [Survey Cells]
WHERe SurveyRef='5')
How To Use Query Analyzer To Find The Most Efficient One In Many Select Statements?
Nov 8, 2006Hi, everyone.
I have read a lot of topics about execution plan for query, but I got little.
Please give me some help with examples for comparing different select statements to find the best efficient select statement.
Thank you very much.
Row Numbering
May 30, 2008Hello,
I am having trouble getting the results I am looking for from this query:
select ROW_NUMBER() OVER(PARTITION BY TransTime, DriverNo ORDER BY TransTime) as CustCount
, ROW_NUMBER() OVER(PARTITION BY Left(ToName,2), TransTime, DriverNo ORDER BY TransTime) As DelCount
, DriverNo
, Convert(VarChar(8), TransTime, 108) As TransTime
, Left(ToName,2) As AirPortCode
from transactions
The results I am trying to get would be like this:
CustCount DelCount DriverNo TransTime AirPortCode
-------------------- -------------------- -------- --------- -----------
1 1 042000 04:00:00 NW
1 2 042000 04:00:00 NW
1 1 042000 06:51:00 AA
1 1 042000 08:45:00 NW
1 1 042000 08:52:00 NW
1 1 042000 10:30:00 NW
1 1 042000 10:45:00 NW
2 1 042000 10:45:00 AA
1 1 042000 11:30:00 NW
1 2 042000 11:30:00 NW
But the closest I have gotten is:
CustCount DelCount DriverNo TransTime AirPortCode
-------------------- -------------------- -------- --------- -----------
1 1 042000 04:00:00 NW
2 2 042000 04:00:00 NW
1 1 042000 06:51:00 AA
1 1 042000 08:45:00 NW
1 1 042000 08:52:00 NW
1 1 042000 10:30:00 NW
1 1 042000 10:45:00 NW
2 1 042000 10:45:00 AA
1 1 042000 11:30:00 NW
2 2 042000 11:30:00 NW
The CustCount field should read 1 if there is only one unique AirPortCode during the TransTime with the specific DriverNo, but the query is counting both rows during that transtime. Any ideas?
Thanks.
Numbering
Oct 5, 2005Hi guys,
I have a table with 3 col like below
col1 col2 col3
LastName FirstName Address City
Nilsen Fred Kirkegt 56 Stavanger
Rasmussen Nina Storgt 67
none of them are unique, some are null and there are about 800,000 rows
I want to insert a col before 1 or after 3 and have numbering from 1 to the last row. How can I do that?
Thank you
Mina
Numbering In SQL
May 19, 2006I have table with 10-20 rows with field P6 which is empty. I want toupdate numbers to P6 starting 1 and increasing by 1. I suppose it isdone by triggers but I don't know how to do that. Help :-)
View 7 Replies View RelatedRow Numbering...
Sep 20, 2007Good Day everyone...
I created a simple report for getting the top selling items of a store. I am getting the report well but my team mate requested to include a number at the first column that will hold the ranking of the item. Is there a function for including a rowrank or someway that i can add a column to my query that will provide the rowrank. Please show me how... c",)
thanks in advance guys...
SQL Server 2012 :: Adding 2 COUNT Statements Results In Heavy Query
Jan 28, 2014These separate COUNT queries are very fast:
SELECT COUNT(id) as viewcount from location_views WHERE createdate>DATEADD(dd,-30,getdate()) AND objectid=357
SELECT COUNT(id)*2 as clickcount FROM extlinks WHERE createdate>DATEADD(dd,-30,getdate()) AND objectid=357
But I want to add the COUNT statements, so this is what I did:
select COUNT(vws.id)+COUNT(lnks.id)*2 AS totalcount
FROM location_views vws,extlinks lnks
WHERE (vws.createdate>DATEADD(dd,-30,getdate()) AND vws.objectid=357)
OR
(lnks.createdate>DATEADD(dd,-30,getdate()) AND lnks.objectid=357)
Turns out the query becomes immensely slow. There must be something I'm doing wrong here which results in such bad performance, but what is it?
SQL Security :: Database Level Audit - Query Parameters For SELECT Statements
Aug 31, 2015I have setup a Database Audit Specification as follows:
Audit Action Type: SELECT | Object Class: DATABASE | Object Name: SHOPDB | Principal Name: public
Now, when I perform a SELECT query with a bound parameter such as:
SELECT * FROM myTable WHERE name='queryname'
What I see through the Audit Logs is something like:
SELECT * FROM myTable WHERE name='@1'
I understand that it is by design that we cannot see these parameters throught Database Level Auditing. I would like to know whether it is possible to see these parameters by any other means using
(1) SQL Server Enterprise Edition,
(2) SQL Server Standard Edition, or
(3) by an external tool.
Help Needed On Numbering
Sep 19, 2007Hi All,
I've upsized my database from access to SQL server, now im very new to SQL etc and i've been reding up on sqential numbering etc and my mind is buzzing with ideas. This is my problem.
I have a table and a colum called PIR ID this colum needs to start the numbering at 372 and increment by 1 each time. Now initially this was o.k by using identify. However it's a problem when 2 users open the form in access they are given the same number.
Now my though on this was to initially have a seperate table called new PIRID and hold the next bumber in there, so that a new number is taken from the list and entered into the record on save ( does that make sense) now the code i've got up to so far is that i've created the table and i get stuck from there.
Can anyone help me with the code of format of code i'm meant to be using, i'm under the understanding that to perform this funtion i may have to use a trigger of some sort.
Thanks In advance
Carol
Auto Numbering
Jul 23, 2005Hello,For analyses and reporting I would like to add a 'ranking' to atable/view.Example:Using the 'order by desc' clause in query I get a list of Customersordered by Turnover (descending). I would like to add that rankingnumbers (same as recordnumbers) in the query. I would like to have thefollowing result:Cust_nr Cust_Name Turnover_2004 Ranking002234 Bayer 139.000 1003456 Rentokill 123.456 2001231 Air France 105.000 3etc.When the 'ranking' is part of the query/table I can use this ranking inan other query.Important: This questions is not about making an (empty) table structurefor filling in by an application and generating a new unique number eachtime a record is added.I hope you can help me.Thanks,Hans*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies View RelatedAuto Numbering
Feb 23, 2008I have created an ID column for auto numbering, but in the properties, they didn't have the option of setting it to auto numbering, and how am i suppose to set or code it to autonumbering.
Please and thankyou
Numbering In Group
Jan 1, 2007I want to add numbering in group in the reports.(number each row in the detail)
when I worked with access it was possible.
can I do it in the report services?
thanks!
Consecutive Numbering WITHOUT Identity
Nov 23, 2006Hi,I'm
trying to insert records from one table into another table. The
destination table has a ROWID field which cannot be an identity key,
but needs to 'act like' an identity key and have its value populated
with (Max(ROWID) + 1) for each row added to the table.To my
thinking, simply using (Max(ROWID) + 1) in my SELECT statement will not
work as it will only be evaluated once so if I am adding 1000 records
and Max(ROWID) is 1234, all 1000 entries will end up having a ROWID of
1235.Is there a way to accomplish this?Thanks
SQL Unique ID - Auto Numbering
May 31, 2008I know its a simple question. But couldnt find a solution of my own.
My SQL 2005 table has 2 fields, namely - id (primary key), comments(varchar).
I want to automatically increment the value of 'id', starting from 1000.
Is there any DataType which i can specify at the time of table designing, so that the value of the id
field will be automatically incremented, upon inserting each new record.
Im using SQL 2005 Express Edition. Can anyone tell, how to do that ?
Thanks
Numbering Issues For Projects
Jun 5, 2008I'm creating an application to track project issues. I would like to have the issue number start over at 1 for each project. I would like to have the issue numbers assigned to each issue in sequential order for each project. Below is a brief example of what I need it to do. I don't know where to even start to try to figure this out. Thanks
ProjectNumber, IssueNumber
TEST0001, 1TEST0001, 2TEST0002, 1TEST0003, 1TEST0003, 2TEST0001, 3TEST0004, 1TEST0003, 3TEST0002, 2TEST0005, 1
Numbering Rows In A Result Set
Nov 29, 2001work_id Work_item_code
--------------------------------
3 Sup
14 100001
5 100002
16 100003
8 IWSM
9 ES3000
10 AppDev
11 100004
I want to have a counter on this select like
work_id Work_item_code counter
----------------------------------------
3 Sup 1
14 100001 2
5 100002 3
16 100003 4
8 IWSM 5
9 ES3000 6
10 AppDev 7
11 100004 8
I can use rownum in Oracle, what do I do to get this in SQL server ?
Thanks Sonali
Numbering Rows In A Resultset
Nov 19, 2001Hello all,
I'm trying to number rows in a resultset by a grouping.
e.g. if my data looks like this:
personname persondata
----------- -----------------
person1 data
person1 more data
person1 other data
person2 stuff
person2 more stuff
person2 even more stuff
person2 lots of stuff
person2 last bit of stuff
person3 info
person3 more info
I want it to return this, with x as a sub-numbered value (like an identity for each grouping):
x personname persondata
-- ----------- -----------------
1 person1 data
2 person1 more data
3 person1 other data
1 person2 stuff
2 person2 more stuff
3 person2 even more stuff
4 person2 lots of stuff
5 person2 last bit of stuff
1 person3 info
2 person3 more info
Any ideas?
Thanks,
Dan
Table - 'Auto-numbering'
Sep 10, 2004Hi guys,
Is it possible to make a table with 'Auto-numbering'? I mean the same function as in MS Access.
Thanks in advance.
Insertingupdate Line Numbering
Oct 11, 2004I have a table which contains order information, which I would like to have line number associated with them
what SQL statement do I use in order to add the line numbering for each line, and have it dependent on reseting on the sales order number?
Problems With Week Numbering
Jan 7, 2005Hi!
I'm trying to get a MSSQL view to return some data marked with a week number based on a date (which is present on every row in the source table). Since I reside in Denmark, I would like the numbering to follow the rule '1. week of a year is the first week with a least 4 days in it'. My instance of MSSQL however is convinced that the rule '1. week starts 1. January'.
Are there any ways to configure my way out of this problem??
Examples:
This year 1. and 2. January is returned as week=1, 3. January is returned as week 2.
I would like 1. and 2. to be week=53 and 3. to be week=1.
Thanx in advance! :)
Ult
Sequential Numbering Problem
Jul 27, 2006I've created a sproc that will provide a recordset for an Access report via a pass-thru query. The report is a production schedule.
Some of the runs on the schedule have a note associated with them. I need
to be able to number these notes, so that they can be displayed in the report
bibliography style. When I populate the data in the sproc, if the "notes"
field in my table contains any data I display a 1, else it's a 0
Like:
SELECT NOTE_FLAG = CASE WHEN ISNULL(SCHED_NOTE,'')<>'',1,0
FROM MASTER_SCHEDULE
The problem is, I really need to display sequential numbers instead of 1's
If I create a base set with this:
CREATE TABLE #TMPRST (
RECID INTEGER,
PRODUCT VARCHAR(10),
QTY FLOAT,
NOTE_FLAG INTEGER)
INSERT INTO #TMPRST
SELECT 1, 'ABC123', 4, 0
UNION ALL
SELECT 4, 'DEF123', 5, 1
UNION ALL
SELECT 5, 'ABC456', 12, 0
UNION ALL
SELECT 13, 'PQR789', 10, 1
How do I go back and convert the note_flag column to read
... 0
... 1
... 0
... 2