|
|
|
It lets you replace [MyDB].[dbo].[MyTable] with a shorter name, such as a:
SELECT a.*
FROM [MyDB].[dbo].[MyTable] AS a You can also use the aliasiing feature on column names, like so:
SELECT [MyReallyLongColumnName] AS ShorterName
FROM [MyDB].[dbo].[MyTable] A simple google search will give you all the nuances regarding the use of aliasing in SQL Server (for columns, sometimes you should, sometimes you must, and sometimes you don't have to use aliasing.
BTW, you generally alias table names when using join or merge.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
How can you get the alternate records from the table in the SQL?
|
|
|
|
|
The same way you get any records: you create a query that has the relevant parameters to extract the records that you are interested in.
|
|
|
|
|
|
1.If your looking random records from the table every time then use "order by NEWID()" with top clause.
EX:- SELECT TOP(10) * FROM TABLE ORDER BY NEWID();
2.Alternate records is identified with Identity column AND ROW_NUMBER() Rank function in
advance Rank function has choice to sorting based on columns under "CTE" and derived table.
|
|
|
|
|
I find your solution confusing.
Your first query
SELECT TOP(10) * FROM TABLE ORDER BY NEWID(); Just randomises the output and has nothing to do with selecting alternate records. Incidentally, you should avoid using reserved words (e.g. TABLE) as tablenames, but if you insist, then get into the habit of surrounding the reserved word with square brackets (i.e. [TABLE])
In your section 2 you have put Identity column in bold suggesting that it is someway relevant to identifying alternate records - it is not, as I stated earlier.
You then go on to mention "ROW_NUMBER() Rank function" - which one do you mean?
Your final comment I think, is saying that the Rank function has an option to order columns - all Window functions have the potential for ORDER BY and/or PARTITION BY, that's why they are sometimes referred to as OVER functions. You can use Window functions on any table, not just derived tables or CTEs.
Here is an example of why Identity Column is not appropriate: Consider this sample data
create table test (d varchar(10))
insert into test (d) values
('Test 1'), ('Test 2'), ('Test 3'), ('Test 4'),
('Test 5'), ('Test 6'), ('Test 7'), ('Test 8')
DELETE from test WHERE Id = 3The contents of the table are
Id d
1 Test 1
2 Test 2
4 Test 4
5 Test 5
6 Test 6
7 Test 7
8 Test 8 Note the missing Id 3.
So I would expect to return rows where Id = 1, 4, 6 and 8. But if I just use the Identity Column
SELECT * FROM test where id % 2 = 1 I only get rows where id = 1, 5 and 7. Incorrect.
An example where RANK is inappropriate. Consider the following test data
create table test2 (Id int, d varchar(10))
insert into test2 (id,d) values
(1,'Test 1'), (1,'Test 2'), (1,'Test 3'), (2,'Test 4'),
(2,'Test 5'), (2,'Test 6'), (3,'Test 7'), (3,'Test 8') The table contains the data
Id d
1 Test 1
1 Test 2
1 Test 3
2 Test 4
2 Test 5
2 Test 6
3 Test 7
3 Test 8 So I would expect to return the rows where d is Test... 1, 3, 5, 7.
If I try to use Rank like this
;with CTE AS
(
select *, ROW_NUMBER() OVER (ORDER BY d) as rn, RANK() OVER (ORDER BY d) as r
FROM Test2
)
SELECT * FROM CTE WHERE r % 2 = 1 I get the correct answer. But I could just have easily used
SELECT * FROM CTE WHERE rn % 2 = 1 as both RANK and ROW_NUMBER return the same value in this instance. I contend that using ROW_NUMBER is clearer and less prone to risk - what if someone changes it to use a PARTITION … RANK() OVER (PARTITION BY id ORDER BY d) as r … you're going to get the rows where Test is … 1, 3, 4, 6, 7. Incorrect again.
Even if partition is not used, RANK can fail depending on the data being returned. Try adding some more data to test2 e.g.
insert into test2 (id,d) values
(1,'Test 1'), (1,'Test 2'), (1,'Test 3'), (2,'Test 4') Note that test2 now contains duplicate rows so the query that forms the CTE
select *, ROW_NUMBER() OVER (ORDER BY d) as rn, RANK() OVER (ORDER BY d) as r
FROM Test2 returns the following values
Id d rn r
1 Test 1 1 1
1 Test 1 2 1
1 Test 2 3 3
1 Test 2 4 3
1 Test 3 5 5
1 Test 3 6 5
2 Test 4 7 7
2 Test 4 8 7
2 Test 5 9 9
2 Test 6 10 10
3 Test 7 11 11
3 Test 8 12 12 Expected results would be Test 1, Test 2, Test 3, Test 4, Test 5, Test 7 but if I re-run the CTE I actually get Test 1, 1, 2, 2, 3, 3, 4, 4, 5, and 7. Incorrect again.
However using ROW_NUMBER will always work, regardless of the data contents
;with CTE AS
(
select *, ROW_NUMBER() OVER (ORDER BY d) as rn
FROM Test2
)
SELECT * FROM CTE WHERE rn % 2 = 1
|
|
|
|
|
CHill60 wrote: You then go on to mention "ROW_NUMBER() Rank function" - which one do you mean?
ROW_NUMBER is a ranking function; I suspect that's what Santosh meant. 
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I was trying to make him aware that his post was as clear as mud.
I'd just had a session with a user group from whom I'm trying to get some requirements. They (the group and the requirements!) are woollier than a woolly mammoth.
I wasn't in the best of moods.
|
|
|
|
|
You mean there can be requirements that aren't that woolly? 
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
We cannot share same solution..this site for sharing our knowledge and experience to find best and alternate solution to reach OP's expectations.I always try to understand others i hope u too 
|
|
|
|
|
I used SQLSERVER 2012,today when I query data,I found a very strange thing. As when I used the sql:
select count(1) as num from(select * from View_paymentApproval v1 where exists(select 1 from payment_Approval where contractNo=v1.contractNo and payNo=v1.payNo and SendSAPStatus='0' and v1.approvalManCode='zhouyx') )t
it returns 22 items
but when I use statement query in parentheses:
select * from View_paymentApproval v1 where exists(select 1 from payment_Approval where contractNo=v1.contractNo and payNo=v1.payNo and SendSAPStatus='0' and v1.approvalManCode='zhouyx')
it returns 20 items
how can this possible?I'm confused.
|
|
|
|
|
Quote:
select count(1) as num from(select * from View_paymentApproval v1 where exists(select 1 from payment_Approval where contractNo=v1.contractNo and payNo=v1.payNo and SendSAPStatus='0' and v1.approvalManCode='zhouyx') )t
it returns 22 items
As to the semantic used in your statement: it does NOT return 22 items. It returns a number which should be equal to the count of records returned by the view.
As to the strange issue... I can't reproduce your issue, but i can recommend to test this statement:
SELECT COUNT(*)
FROM View_paymentApproval v1
WHERE EXISTS (
SELECT 1
FROM payment_Approval
WHERE contractNo=v1.contractNo AND payNo=v1.payNo AND SendSAPStatus='0' AND v1.approvalManCode='zhouyx'
)
|
|
|
|
|
When you have a query that involves multiple tables, it's a good idea to prefix every column with the table name / alias:
SELECT *
FROM View_paymentApproval v1
WHERE Exists
(
SELECT 1
FROM payment_Approval a1
WHERE a1.contractNo = v1.contractNo
And a1.payNo = v1.payNo
And a1.SendSAPStatus = '0'
And v1.approvalManCode = 'zhouyx'
)
Not only does it make it easier to work out which table the column comes from, it can also prevent bugs.
For example, if the column contractNo existed in View_paymentApproval, but not in payment_Approval, then:
a1.contractNo = v1.contractNo would produce an error, whereas:
contractNo = v1.contractNo would produce incorrect results, since it would be equivalent to:
v1.contractNo = v1.contractNo which would always be true.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
it's right.Thank you,dude.The column 'contractNo' has null value.
|
|
|
|
|
Could someone who knows MySql well take a look at my question here?
Much appreciated.
[Editor: Moved]
Ger
modified 17-Apr-19 14:03pm.
|
|
|
|
|
Please don't do this. All questions get looked at in turn. Just think what the Lounge would be like if everyone followed your example.
|
|
|
|
|
This is now an older question and that phase has passed without much of value so this is the post of last resort.
Ger
|
|
|
|
|
You still have options:
- edit the question and add further information.
- reply to the person(s) who have already responded with some feedback or requests for more assistance.
|
|
|
|
|
I am trying retrieve record count for each column of a table with blank, not null, null and distinct count in oracle sql with query below.. however I am encountering below.. unable to find the missing paranethesis
select owner, table_name, column_name,
to_number(xmlquery('/ROWSET/ROW/C/text()'
passing xmltype(dbms_xmlgen.getxml(
'select count(distinct "' || column_name || '") as c '
|| 'from "' || owner || '"."' || table_name || '"'))
returning content)) as distinct_count,
to_number(xmlquery('/ROWSET/ROW/C/text()'
passing xmltype(dbms_xmlgen.getxml(
'select count(case when (' || column_name || ' = ' ' ) then 0 end) as c '
|| 'from "' || owner || '"."' || table_name || '"'))
returning content)) as null_count,
to_number(xmlquery('/ROWSET/ROW/C/text()'
passing xmltype(dbms_xmlgen.getxml(
'select count(case when "' || column_name || '" is not null then 1 end) as c '
|| 'from "' || owner || '"."' || table_name || '"'))
returning content)) as notnull_count
from all_tab_columns
where owner = 'JAMES'
and table_name = 'TEST'
and data_type in ('NUMBER', 'DATE', 'TIMESTAMP', 'CHAR', 'VARCHAR2',
'NCHAR', 'NVARCHAR2');
ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"
*Cause:
*Action:
Error at Line: 9 Column: 58
Please can u help
|
|
|
|
|
Start with the first left parenthesis and find its matching right one. Then go on to the next left, and so on until you find the missing one. It should not take more than a couple of minutes. You would also probably be better using less complex queries.
|
|
|
|
|
I am still unable to resolve
|
|
|
|
|
Maybe you should rewrite this query into its separate clauses so you can see the breaks more clearly. You have a number of parts where you are concatenating text and variable fields so it may be that you have unbalanced quote characters.
|
|
|
|
|
I am trying to work through a table of linked server names to get their version and productversion
Here is the code I'm using but I need to be able to get the values and use them elsewhere. I cannot get them into parameters. Also despite it populating the output with a list it isn't for each server. I mean its exactly the same info for every server. But its wrong its showing
EG:
Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Express Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 16299: )
BUT should be
Microsoft SQL Server 2012 (SP4-GDR) (KB4057116) - 11.0.7462.6 (X64)
Jan 5 2018 22:11:56
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
<pre>BEGIN
DECLARE @srv SYSNAME, @db SYSNAME, @exec NVARCHAR(1024);
DECLARE @serverName NVARCHAR(100)
DECLARE @i INT = 1
DECLARE @ServerCount INT=0
DECLARE @retval nvarchar(1000)
DECLARE @ParmDefinition nvarchar(500);
DECLARE @sSQL nvarchar(500);
SET @ServerCount = (SELECT COUNT(*) FROM SQLServerList.dbo.ListOfServers)
SET @db = N'tempdb';
WHILE (@i <= @ServerCount)
BEGIN
SET @serverName = (SELECT serverName FROM SQLServerList.dbo.ListOfServers where id = @i)
IF @serverName IS NOT NULL
BEGIN
SET @exec = N'' + QUOTENAME(@serverName) + N'.' + N'' + QUOTENAME(@db) + N'.sys.sp_executesql ';
SELECT @sSQL = N'SELECT ''' + cast(@serverName as nvarchar) + ''',@@VERSION as version,SERVERPROPERTY(''ProductVersion'') AS ProductVersion ';
SET @sSQL = @exec + ' ' + @sSQL
EXEC sp_executesql @sSQL;
END
SET @i = @i + 1
END
END
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
