I have a table that looks like this: I want to aggregate by FileID and split the File Info column into 2 separate count columns. I want 1 column to have the count of the Unique File Info and the other to be a count of non-Unique file info. The result would ideally look like this: where the non-unique count
Tag: oracle
How to return 14th day from month which was 2 months ago with determinated time in Oracle SQL?
How can I take the 14th day from month which was 2 months ago with time 23:59:59 in Oracle SQL ? I have query like below and I need answer something like below (similar style): Above query return: 21/12/31, nevertheless I need query like above which will return value: ’21/12/14 23:59:59′ Answer Yo…
PLSQL – Use variable array on where clause IN
I am looking to use an array of values in the WHERE IN condition. After research, I saw that I had to make a “CREATE TYPE”, since a “TYPE” in local does not work. This solution does not suit me, I do not want to make a new TYPE in Oracle, since the database is used for an ERP/PGI I
NetSuite Case statement works in Saved Search but doesn’t work in Workflow
I have an issue with a formula case statement in a NetSuite workflow, I am using the same case statement in a saved search and it works just fine, but generates an error in the workflow formula for sourcing date, to set the date field on a cashsale record created from a sales order. NetSuite formula which bas…
ORA-00932 convert and format dates and strings to calculate a range of days
I have a table where the date_one field is of type VARCHAR2 (20 BYTE) and the date_two field is of type DATE. Output for date_one is 29-01-2019 20:22:08 and output for date_two is 25-JAN-19. I need to calculate records based on an interval of days between these two fields but I’m having a hard time with…
Oracle SQL Filter common registries (1:1) between two tables
I need some help to approach a proper counting between 2 tables. There are 2 tables with duplicated fields on each table. I need to filter same registries between these 2 tables but it must be counted only once per ID on both tables. I was thinking about selecting by Distinct ID but still not getting the corr…
force query to return duplicate values sql oracle
i have a simple query so when this query runs and returns output it only shows 3 rows but i want the output to be repeated like below is there a way i can achieve this Answer Pass in a collection rather than using an IN filter: Which, for the sample data: Outputs: NO SURNAME GENDER 123 m m 456
Three-table join with NO EXISTS
I have three tables and would like to answer the following question in SQL: “Who has only certifications that do NOT have scores?” For instance, in the setup below, the query would return “John” only. Joana has the “AWS Certification”, which is in SCORE table (id 57). Marry…
Iterate over data set and insert sequence ids for rows
I have a data set with more than 2 million rows in a Oracle SQL table and want to do some association analysis on this. To apply a sequence mining algorithm to this data, I need a column named ‘sequenceId’ and a column ‘eventId’. The table structure looks like this: time pId uId Now I …
Oracle SQL : how to specify Time Zone Region
Underlined, as hours are not specified, that means that hour is ’00:00′ I would like to specify that this is for Europe/Paris time zone region. Can you help me set-up this ? Thanks Answer A DATE data type has the components: year, month, day, hour, minute and second. It ALWAYS has those components…