|
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.
|
|
|
|
|
It's always easier to help sort problems out if you also supply some sample data and expected results.
You are using the TEXT datatype for some key data items - this allows up to 2GB of text and is the wrong choice for things like names, emails etc. You also cannot compare or sort easily. Use Varchar instead e.g. VARCHAR(MAX) (Although that is also overkill, I usually use varchar(50))
You are using WHERE when you should be using CASE WHEN ... END
|
|
|
|
|
Hi all. I have two tables in access (VB6): SupplierInvoices & Sales. They both have 3 common field names: Date, Code & Qty. I need to query both tables via SQL query by a specific date range as follows (I know this is wrong, but just to give you an idea):
rs.Open "Select distinct format(SupplierInvoices.Date, 'dd-MMM-yyyy') as tDate, Sum(SupplierInvoices.QTY) as tTotal from SupplierInvoices, format(Sales.Date, 'dd-MMM-yyyy') as sDate, Sum(Sales.QTY) as sTotal from Sales where PLU = '" & lblPLU.Caption & "' and Date Between #" & StartTime & "# and #" & EndTime & "# Group by format(SupplierInvoices.Date, 'dd-MMM-yyyy');", cn, adOpenKeyset, adLockOptimistic
The result needs to return from both tables the sum of the qty fields for that date range. I can't get it right! Please help.
|
|
|
|
|
From the Lounge response
Quote: but it says "the field PLU could refer to more than one
Indicates that you need to prefix the PLU field (TableName.PLU) with a table name so the query knows which table to use.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
Thanks Mycroft. I am using this one - trying to get it to work:
Select distinct format(SupplierInvoices.Date, 'dd-MMM-yyyy') as tDate, Sum(SupplierInvoices.QTY) as lTotal from SupplierInvoices where PLU = '" & lblPLU.Caption & "' and Date Between #" & StartTime & "# and #" & EndTime & "# Group by format(SupplierInvoices.Date, 'dd-MMM-yyyy') Union Select distinct format(Sales.Date, 'dd-MMM-yyyy') as tDate, Sum(Sales.QTY) as sTotal from Sales where PLU = '" & lblPLU.Caption & "' and Date Between #" & StartTime & "# and #" & EndTime & "# Group by format(Sales.Date, 'dd-MMM-yyyy')
Error is: Item sTotal cannot be found.
I appreciate any feedback (even if it's about vb6/access!)
|
|
|
|
|
Read up on what the UNION command does.
I could tell you what is not working here but you will find out much more if you read, understand than methodically practise what you have read and understood rather than throwing things together hoping they will work then asking for help when they don't work.
What the heck, I will tell you anyway - either change ITotal to sTotal or change sTotal to ITotal.
The column names for the unioned results need to be the same.
As an aside - I don't think that query is going to give you any information that is of any use as you are basically listing sales totals and dates together with invoice totals and dates with no ability to distinguish between what is a sales row and what is an invoice row.
I think you need to step away from the computer, read a book, follow tutorials and work out what you want to do on paper before typing SQL queries that you hope will somehow work.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
You are right, Guy.
At this stage I don't fully understand SQL and am attempting queries out of my depth of understanding!
Thank you for the encouragement.
|
|
|
|
|
Take it slowly and learn from the beginning - it's the classic thing of if you bite off more than you can at first chew you will be put off by it.
If you are diligent, within a couple of weeks you will be able to solve these sorts of issues without too much help.
Good luck!
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
|
I am curious - is this a university or school project question?
I ask because the SQL you have is just not going to work - it's basically a word salad of SQL, a jumble of keywords in the wrong order and missing information.
I would suggest you step back and start learning the basics of SQL.
Also it will not be possible to anyone to help you with the information you have provided as you have not told us which columns are the primary and foreign keys within Sales and SupplierInvoices.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
I appreciate the feedback.
This is what is giving me headaches:
Select distinct format(SupplierInvoices.Date, 'dd-MMM-yyyy') as tDate, Sum(SupplierInvoices.QTY) as lTotal from SupplierInvoices where PLU = '" & lblPLU.Caption & "' and Date Between #" & StartTime & "# and #" & EndTime & "# Group by format(SupplierInvoices.Date, 'dd-MMM-yyyy') Union Select distinct format(Sales.Date, 'dd-MMM-yyyy') as tDate, Sum(Sales.QTY) as sTotal from Sales where PLU = '" & lblPLU.Caption & "' and Date Between #" & StartTime & "# and #" & EndTime & "# Group by format(Sales.Date, 'dd-MMM-yyyy')
It says Item sTotal cannot be found.
I am new to SQL and am trying to learn, hence asking on this forum.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
