|
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
|
|
|
|
|
Check that the ID on the ServerList table is the same as i.
i will be 1 then 2 then 3 etc
I bet the ID field in servertables does not match.
Try using a cursor and looping through the servertable.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
Hi, I have several interviews coming up so I want to prepare for my SQL Interview.
What kind of questions interviewers might ask?
I've done some basic research and found one resoure - sql interview questions cheat sheet.
I've been reading those and solving some problems too.
Anyone here who can give me more tips?
Thanks!
|
|
|
|
|
It is impossible to answer such a question as we have no idea what the interviewers will be looking for. The whole point of an interview is to find out what you actually know, and what experience you have.
|
|
|
|
|
No, What I wanted to ask is how was your interview experience? I want to know about other people's experiences. So, it will help me prepare better for my interviews from learning about their experiences.
|
|
|
|
|
Other people's experiences are irrelevant. The only way to prepare for an interview is to know the subject, and something about the company. If you do not know the subject, and do not have enough experience to answer the questions, then you are wasting both your and the potential employer's time. I say this from personal experience of many interviews in my professional career.
|
|
|
|
|
I don't WANT to give you an interview-advantage, because I don't know if you have the technical skills to match. If you do, you don't need the advantage.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
If your skills are commensurate with the position you're applying for, you should be able to answer pretty much any SQL question they throw at you.
The questions that will trip you up are the ones that HR gives you, like those insanely stupid puzzle questions.
".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
modified 19-Apr-19 9:53am.
|
|
|
|
|
I'm trying to extract some data using SQL queries from different tables I have already created. The tables are as follows:
CREATE TABLE Students
(
Student_ID INTEGER PRIMARY KEY,
Enrollment_Year DATE,
Course_Current_Status VARCHAR(18),
First_Name TEXT,
Last_Name TEXT,
Gender TEXT,
Date_Of_Birth DATE,
Email TEXT,
CourseCode INTEGER REFERENCES Courses(CourseCode)
);
CREATE TABLE Modules
(
Module_Code INTEGER PRIMARY KEY,
Module_Name TEXT,
Module_Credits INTEGER,
Module_Level INTEGER,
ConvenerID INTEGER REFERENCES Conveners(ConvenerID)
);
CREATE TABLE Enrollment
(
Marks_Obtained INTEGER,
Module_Code INTEGER REFERENCES Modules(Module_Code),
Student_ID INTEGER REFERENCES Students(Student_ID),
Program_Year_When_Enrolled TEXT,
PRIMARY KEY(Module_Code, Student_ID)
);
I want to show three columns with my query:
Student_ID, Average_Second_Year_Marks, Average_Third_Year_Marks, Overall_Marks
What I want to do is extract data for students graduating in 2017 i.e. the Course_Current_Status = 'Graduated-2017'.
For second year marks the Enrollment.Program_Year_When_Enrolled = 'Second' And for the third year marks the Enrollment.Program_Year_When_Enrolled = 'Third'. For the overall marks a new column would have to be created by the query i.e. Overall Marks which would be 1/3 of the second year marks and 2/3 of the third year marks.
What I'm using is as follows:
SELECT
Students.Student_ID,
AVG (Enrollement.Marks_obtained WHERE Enrollment.Program_Year_When_Enorolled = 'Third' ) AS avg_third_year_marks,
AVG (Enrollement.Marks_obtained WHERE Enrollment.Program_Year_When_Enorolled = 'Second' ) AS avg_second_year_marks
AVG (Enrollment.Marks_obtained = 1/3 * avg_second_year_marks + 2/3 * avg_third_year_marks) AS Overall_Marks
FROM
Students LEFT JOIN
Enrollment ON Students.Student_ID=Enrollment.Student_ID
WHERE
Students.Course_Current_Year='Graduated-2017'
GROUP BY
Students.Student_ID
|
|
|
|
|
You are going to have to split this into a query and a sub query (unless Richard comes in with a CTE).
Sub query should be everyting EXCEPT
AVG (Enrollment.Marks_obtained = 1/3 * avg_second_year_marks + 2/3 * avg_third_year_marks) AS Overall_Marks The averaging variables do not exist in the current structure until AFTER the query is run.
Then try:
Select *
from (put the sub query here) as SQ
You can then add the totaling column after the * because the subquery would have been run and the averaging values populated.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
I'd recommend normalizing them first, and wait with defining operations until that is completed 
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
