I’m trying to get the column names of a table using XML datatype and information_schema columns. When I tried to use the result in another select statement, I have the results with the repeated column name instead of the results set. I have even tried to cast it to varchar but it still failed. what have…
ORA-00906: missing left parenthesis – LIES?
I have stared at this until my eyeballs bleed, where am I missing a parenthesis? It does also say Error starting at line: 1 in command-“. The cause and action section of the error report is blank. Answer This just because you missed column name while declaring primary key. There is another problem: cons…
Why Row_Number in a view gives a nullable column
I have a view using a CTE and I want use a row number to simulate a key for my edmx in Visual Studio When I look in properties of column I see Id bigint … NULL And my edmx exclude this view cause don’t find a column can be used to key When I execute my view ID have
How to return data for specific day names from date column
Imagine I have a SQL Server table as shown below, named dbo.tblNAV: How can I select the data only for Mondays and Fridays? I am a beginner in SQL Server and almost have no idea to handle this. I would appreciate your help. I am using Microsoft SQL Server Management Studio. Answer SQL Server does not make thi…
How to calculate rows count in where statement in sql?
I have two tables in SQL Server: order (columns: order_id, payment_id) payment (columns: payment_id, is_pay) I want to get all orders with two more properties: How many rows where is_pay is 1: And the count of the rows (without the first filter) So I wrote this query: The problem is how to calculate the rows …
TSQL to perform aggregation with Exists
My sample data source is following I want SQL to check if there exists any row with cond=X or cond=Y by site and if yes, don’t perform any aggregation; if no, perform a sum of val. I am currently achieving it by following Method1 Method 2 I was wondering if there is any better way of achieving this. Edi…
Compare the triplet rows together and select max of them?
Consider the following time series data These are the values of y, and the x column starts from 1 respectively. I have come to the conclusion that if the data in the y-column are compared three by three, the maximum value in each triad, is the peak point. Does anyone have an idea how to specify peak points wi…
SQL- Retrieve records based on column’s specific value
I have 2 tables with below info Table# 1 – TaskDependecy TaskID DependentTaskID 1 2 1 3 3 6 3 7 5 8 Table # 2 – TaskStatus TaskID Task Status 1 In Progress 2 Completed 3 In Progress 5 In Progress 6 Completed 7 Completed 8 Completed I want to retrieve only taskIDs # 3 and 5 so that
How to count active users using mysql
I’m trying to count active users in the last month by comparing the logged event against today’s date. So for example if a user logged in 2 days ago(09/01/2021), the query would evaluate to true since the logged in month 9 = todays month 9 AND logged year 2021 = todays year 2021. since its only us…
How do I create a table from distinct values in another table?
I have a table of orders that I want to create a table of possible customers from. I would like to take the distinct names in the customer record and create new table in the same SQL database with and assign it an auto incrementing Id as the PK. How can I script this? Answer