I have two tables Table A and Table B as listed below. What is the SQL query to get the below output? Answer This looks like a cross join, to generate all possible combinations of rows between the two tables. Assuming a structure like tablea(col), tableb(col): If you want to concatenate the results in a singe…
Tag: oracle
SQL loop through columns with name to find max date
I’m looking for the SQL to loop through 20 columns with dates, to find the max. My code is kind of bad now: it’s oracle database, i konow that max date is in filed annex_20 Answer First of all, you don’t need ANNEX_XX_DATE is not null since ANNEX_XX_DATE<>… can’t be true if…
How to create a column table that holds a file in Oracle Apex
I have created a report with a form in Oracle Apex, for this report I am using a table that have 5 columns and 1 of the columns is filename varchar2(500) which is support to hold a file. In the form I have a page item that uploads the file, however when I create a new record in the form,
SQL SUM OVER PARTITION BY 2 columns not working
Common question, I know. Just haven’t been able to find a solution to my question, so hit me with the removal and or downvotes if you must. (Oracle 12c) I have data that looks like this: Date …
Oracle SQL operations to derive new column value from existing columns using analytical or other functions? I’ve describedd the desired result
I have three columns ELEMENT, START_POSITION & FIELD_LENGTH and trying to derive column SKIPPED(new column) without using any functions. Example: ELEMENT START_POSITION FIELD_LENGTH A …
offset with parameter in PL/SQL procedure
I have a PL/SQL procedure. It executes a SQL statement and returns a json responce. I would like to limit the rows returned using the offset clause. eg: select * from wherever where something = …
Oracle “NOT IN” not returning correct result?
I’m comparing two tables that share unique values between each other using NOT IN function in Oracle but I’m getting the output is: 521254 for all charging ids –< this is the total unique charging ID’s in BILLINGDB201908 Now I want to find id’s in table BILLINGDB201908 that al…
Oracle Remove Duplicates and Update Rows in a Table with a Value from Another Table
In my associates table I have 4,978 people with at least 1 duplicate. asscssn | count(*) ——— ——– 123456789 8 987654321 5 234567890 5 Each duplicate for a person has …
Oracle SQL query times in one line
I apologize in advance for any formatting issues. Here is some sample data. Here is what I would like to produce: Here is what I get when I use a series of case when statements Any coding help is much appreciated. Answer That is what the PIVOT clause was created for:
Filter out records that are not in this date format oracle
How do i filter records that can only be converted to date using to_date(’31-May-2019 00:00:00′, ‘DD-MON-YYYY HH24:MI:SS’) from the above sample query i am expecting results below how do i achieve this in oracle. Reason why i want to do this: i am receiving raw data and loading it to a…