logo
down
shadow

Case and When query give me both column filled


Case and When query give me both column filled

By : user2185580
Date : November 22 2020, 04:01 AM
help you fix your problem Here is one approach. Combine the subscribers and unsubscribers tables first. Then bring in the other information:
code :
select g.groupname, m.membername, su.*
from (select groupid, memberid, max(subscribed) as subscribed, max(sdate) as sdate, max(unsubscribed) as unsubscribed, max(udate) as udate
        from ((select groupid, memberid, 1 as subscribed, date as sdate, 0 as unsubscribed, null as udate
               from subscribed s
              ) union all
              (select groupid, memberid, 0 as subscribed, null as sdate, 1 as unsubscribed, date as udate
              from subscribed s
             )
            ) su
       group by groupid, memberid
      ) join
     groups g
     on g.groupid = su.groupid join
     member m
     on m.memberid = su.memberid;


Share : facebook icon twitter icon
Can you give each case statement a column alias? SQL, pl/sql

Can you give each case statement a column alias? SQL, pl/sql


By : Kunica
Date : March 29 2020, 07:55 AM
With these it helps I am trying to do a query where it counts the number of prorata, complete and random results in a column and displays them in 3 seperate columns counted. , Sample data :
code :
with t1 as(
  select 'EM0004357P' ord_deal_code, 'PRORATE' ort_method from dual union all
  select 'EM0004357P' ord_deal_code, 'COMPLETE' ort_method from dual union all
  select 'EM0004357P' ord_deal_code, 'PRORATE' ort_method from dual union all
  select 'EM0004357P' ord_deal_code, 'RANDOM' ort_method from dual union all
  select 'EM0004357P' ord_deal_code, 'RANDOM' ort_method from dual
 )
SELECT count(decode(o.ort_method, 'PRORATE', 1)) as prorate
     , count(decode(o.ort_method, 'COMPLETE', 1)) as complete
     , count(decode(o.ort_method, 'RANDOM', 1)) as random
FROM t1 o
WHERE o.ord_deal_code = 'EM0004357P';
 Prorate  complete  random 
   2         1        2 
How to give Column Name to ResultSet dynamically in this case?

How to give Column Name to ResultSet dynamically in this case?


By : user2766530
Date : March 29 2020, 07:55 AM
around this issue You need as ResultSetMetaData to get the column name associated with your query result.
You can get ResultSetMetaData from ResultSet by using ResultSet.getMetaDate, you can iterate it and get column all column name.
code :
ResultSetMetaData  VendorItems_RSet_metaData = VendorItems_RSet.getMetaData(); 
int numberOfColumns = VendorItems_RSet_metaData .getColumnCount(); 
for(int i=1;i<=numberOfColumns;i++)
{   
   String columnName = VendorItems_RSet_metaData.getColumnName(i);
}
while (VendorItems_RSet.next()) 
{
  for(int i=1;i<=numberOfColumns;i++)
  {   
     String columnName = VendorItems_RSet_metaData.getColumnName(i);
     String tax_name = VendorItems_RSet.getString(columnName);
     System.out.println(tax_name); 
  }         
}
How do I create a new column filled with the results of a query?

How do I create a new column filled with the results of a query?


By : user3139175
Date : March 29 2020, 07:55 AM
wish helps you Since you are on 11g, and as you say that both columns are in same table. I would suggest, do not add a static column, rather add a virtual column.
So, you need not worry about the insert too. Oracle would give you the computed value without actually storing it.
code :
ALTER TABLE t ADD
       column_name GENERATED ALWAYS AS     (to_date(substr(t.notes, 24, INSTR(t.notes, 'for')-1))) VIRTUAL;
add tooltip to datagrid column filled from sqlite query

add tooltip to datagrid column filled from sqlite query


By : user7581924
Date : March 29 2020, 07:55 AM
like below fixes the issue I am trying to add a tooltip to a specific column that may or may not be present, depending on the results of an sqlite query. The columnheaders are filled in from the query results. , I used the following code:
code :
void MainDataGrid_AutoGeneratingColumn(object sender, DataGridAutoGeneratingColumnEventArgs e)
    {
        string tooltip = null;

        switch (e.Column.Header.ToString())
        {
            case "Gauge":
                tooltip = "Double click Gauge ID to view its details";
                break;
        }

        if (tooltip != null)
        {
            var style = new Style(typeof(DataGridCell));
            style.Setters.Add(new Setter(ToolTipService.ToolTipProperty, tooltip));
            e.Column.CellStyle = style;
        }
    }
Query to give count of unique results in column A but then split by values in column B

Query to give count of unique results in column A but then split by values in column B


By : ChrisJ
Date : March 29 2020, 07:55 AM
To fix the issue you can do You could cross join all distinct values of teams and periods, and then left join the original table and aggregate.
code :
select
    te.team,
    pe.period,
    count(ta.team) cnt
from 
    (select distinct team from mytable) te
    cross join (select distinct period from mytable) pe
    left join mytable ta 
        on ta.team = te.team
        and ta.period = pe.period
group by te.team, pe.period
order by pe.period, te.team
team | period | cnt
:--- | -----: | --:
A    |      1 |   2
B    |      1 |   2
C    |      1 |   0
D    |      1 |   0
E    |      1 |   0
A    |      2 |   0
B    |      2 |   0
C    |      2 |   0
D    |      2 |   0
E    |      2 |   1
A    |      3 |   2
B    |      3 |   0
C    |      3 |   0
D    |      3 |   1
E    |      3 |   0
A    |      4 |   1
B    |      4 |   1
C    |      4 |   1
D    |      4 |   0
E    |      4 |   0
Related Posts Related Posts :
  • Splitting 24 hours into 96 equal 15 minute intervals in T-SQL
  • WHERE clause not hitting on LEFT JOIN
  • SQL Pivot String Data
  • Redshift - Find % as compared to total value
  • How to find count of N columns in a table
  • SQL statement giving no result
  • IN condition is not working when the data is like 'Test','Test' from a result query
  • SUM of COUNT with only certain variables
  • How to get all products with at least X reviews in SQL?
  • sql Group by columns to the same row without join
  • Check if an integer is within the next 5 increments in SQL, when the numbers can wrap
  • How to check JSON string on contains field AND like value?
  • Find uncovered periods without exploding each combination
  • How to compare date to format date on oracle
  • SQL Server : duplicate rows when joining tables and inserting new data?
  • Oracle - Combine timestamp with date and remove duplicates
  • Array to json and row to json in SQL Server 2016
  • Modifying SQL code to pick the Dropdown list instead of constant text
  • How to get max value of column values in a record ? (BigQuery)
  • Elasticsearch filter after aggregation
  • Teradata: varchar to timestamp 2
  • specific row number with all the fields
  • INSERT INTO SELECT query in SQL where primary key is used and multiple duplicates are in another table
  • SQL Query - Eliminate records
  • What is wrong with this SQL join query
  • Hybris Flexible search union query to fetch products
  • Stale Data Count
  • Adding new column of total_event
  • Query to Skip the first Delimiter and select the second Delimiter In SQL
  • Very slow (12+ hours) large table joins in postgres
  • LINQ query for specific group
  • SQL - return xpath for multiple XML results
  • Can't compile my trigger in Apex Oracle SQL/PL PLS-00103
  • Select 1 field from a group
  • Airflow + pandas read_sql_query() with commit
  • Get higest value of each relation
  • SQL - select limit column2 values for distinct column 1
  • Compilation failed, PLS-00103 pl/sql
  • SQL Query for all IDs where all rows with that ID match a value in another column
  • How to correctly translate sql-code in Delphi
  • How do I join 2 tables to allocate items?
  • PARTITION BY in CASE doesn't work with several AND statements
  • SQL. How to combine two records with the same ID into one line based on value's in a column
  • How to update tables from an external Access database with identical tables?
  • Trying to find records between two dates on one table and some information on another
  • Why does adding ORDER BY drastically speed up query?
  • SQL syntax explanation (CAST, INSERT, DATE)
  • My query with <> to / NOT IN () clause not returning any values?
  • Comparing 2 tables
  • How do I filter my table to show the result in Oracle?
  • SQL Server 2016: How to read different substrings from a text with special characters
  • Oracle - Left Anti Join on same table
  • Maximum Capacity for BULK INSERT from files such as CSV
  • simple JSON array to Rows
  • SQL Server - matching attributes query
  • SQL Server - Duplicate rows and add date iterator column between two date values
  • Oracle - hh mm ss to hh:mm:ss with one exotic
  • Delete an element from a certain row in an XML-type column in SQL?
  • SQL how to find tables with more than 1 row with criteria?
  • query to get count, subtotal and total over a date range
  • shadow
    Privacy Policy - Terms - Contact Us © bighow.org