Oracle Database

79K

Solutions

26K

Contributors

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

Share tech news, updates, or what's on your mind.

Sign up to Post

Hi Experts,

Below query is used LISTAGG() which is supporting in 11g .
same query is not executing in 10g  showing error -ORA00923-FROM Keyword not found.

what is the alternative for this query in 10g
i have tried with xmlagg(),and stringagg() but not succeeded.

Please provide the alternative solution.

query:
=====
SELECT DEAL_ID,
  DOC_ID,
  CHRG_MARGIN_RATE,
  LISTAGG (CHRG_AMT, ',') WITHIN GROUP (
ORDER BY CHRG_AMT) CHRG_AMT
FROM
  (SELECT S.DEAL_ID,
    MAX (DOC_ID) DOC_ID,
    SUM (CHRG_AMT)
    || ' '
    || CHRG_CURR_CODE CHRG_AMT,
    MAX (CHRG_MARGIN_RATE) CHRG_MARGIN_RATE
  FROM TR_CHARGES C,
    TR_SUMMARY S
  WHERE C.owner_cust_id IN ('10033')
  AND C.DEAL_ID      = S.DEAL_ID
  GROUP BY S.DEAL_ID,
    CHRG_CURR_CODE
  ) C
GROUP BY DEAL_ID,
  DOC_ID,
  CHRG_MARGIN_RATE;

Open in new window


Thanks,,
0
Big Business Goals? Which KPIs Will Help You
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PORTNOI, MARK
ZANNIKOU, MARIA
SLINGER, ANDREW
DAVIES, LYDIA
ZANNIKOU, MARIA

The whole name (last name, first name) is in one column (column_name is assigned_to)
In oracle how can I get 1st alphabet of first name and full last name?

MPORTNOI
ASLINGER
LDAVIES
MZANNIKOU
0
Hi,
How can I make typeid as surrogated key

create table type
typeid number(10),---this as surrogated key
typecode varchar2(10 byte)
)

result as
typeid typecode

1         aa
2       bb
0
Untitled.jpg errror display as not a valid month while executing the querry


/* Formatted on 2018/09/18 13:55 (Formatter Plus v4.8.8) */
SELECT a.dat, b.division, b.sm_name, b.dt, b.cust_code
  FROM (SELECT dat
          FROM (SELECT       TO_DATE (:r_from_cust_main_acnt_code,
                                      'DD/MM/RRRR'
                                     )
                           + ROWNUM
                           - 1 dat
                      FROM ot_job_exec_head
                CONNECT BY LEVEL <=
                                TO_DATE (:r_till_cust_main_acnt_code,
                                         'DD/MM/RRRR'
                                        )
                              - TO_DATE (:r_from_cust_main_acnt_code,
                                         'DD/MM/RRRR'
                                        )
                              + 1)
         WHERE ROWNUM <= 31) a
       LEFT JOIN
       (SELECT division, sm_name, dt, cust_code
          FROM (SELECT division, sm_name,
                       TO_CHAR (TRUNC (TO_DATE (dat, 'DD-MM-YYYY')),
                                'DD-Mm'
                               ) dt,
                       cust_code
                  FROM (SELECT vssv_field_02 division, vssv_name sm_name,
                               jeh_dt dat, jed_flex_01 cust_code,
                               jed_flex_03 purpose, jed_flex_11 next_action
                          FROM …
0
Need help figuring out why my dashboard continues to show the color white when the hex color I am specifying is green and orange.
SELECT WO_NUMBER AS WONUM
      ,PART_NUMBER AS PN
      ,DESCRIPTION AS DESCR
      ,CASE CONTRACT_TYPE WHEN 'PBH Pool' THEN 'PBH' ELSE 'TM' END AS CONTRACT
      ,DAYS_OPEN AS TAT
      ,CASE WARRANTY_REQUESTED WHEN 'F' THEN ' ' WHEN 'T' THEN 'W' END AS INFO
      ,TECH
,case when priority = 1 or priority = 2 then '#FFFF00'
when days_open > 3 or priority >= 1 then '#ff0000' 
when days_open < '-1' then '#66b65d'
when days_open in ('0', '-1') then '#f68835' 
end row_color
FROM BA_VIEW_WIP_SHOP_SCREENS
WHERE STATUS IN ('OPEN', 'AWT SHOP EVAL', 'SHOP EVALUATION') AND SHOP = 'SH ELE/FUE ARO'
order by days_open desc

Open in new window

Capture.JPG
0
Hi Expert,

I want to insert multiple records on a single query and my records are in an external file.

Please any advice for me ...

I have 2 Approach:: Please suggest which one will work effectively.

1: Using external table ... "insert into table select * from exttbl;"
2: Using SQL loder .
0
In an Excel VBA procedure, I am retrieving data from an Oracle table and writing it to a worksheet. The procedure works fine, except for one oddity. I can filter the data on anything but a date column. I checked - this column is a DATE in the Oracle table, but the recordset result completely ignores any attempt to filter on it. No error, it just ignores it.

So, for example, if I run any of these three queries in an Oracle-connected UI (I use PL/SQL Developer), i get the exact same records:

select *
from myTable
where myDate >= '07SEP2018'
order by myDate desc

select *
from myTable
where myDate >= '07-Sep-2018'
order by myDate desc

select *
from myTable
where myDate >= to_date('07-SEP-18')
order by myDate desc
   
The results are filtered by the date 9/7/2018.

However, when I run the same query in ADO to fill a recordset using any of these formulations, I simply get the whole table - all dates, going back to 2016. Here is my code:

strSQL = "select * " & _
    "from myTable " & _
    "where myDate >= to_date('07-SEP-18') " & _
    "order by myDate desc"
   
    'create, configure, and populate recordset
    Set rs = New ADODB.Recordset
    rs.CursorType = adOpenForwardOnly
    rs.Open (strSQL), cn

I have tried all 3 of the above variations in ADO also, with the same result. I am using the ADO 6.1 library and Oracle 11g.

Any idea what the issue is?

Thanks.
0
HI Experts,

how to get the substring from the below query

SELECT ADD_MONTHS(TRUNC (LAST_DAY (SYSDATE)), -2)+1 start_date FROM DUAL
//01-AUG-18
expected o/p: AUG-18
1
I want to use Oracle regular expression to extract dates from a varchar2 field in Oracle.  
I need to convert the data into date format so that I can then evaluate it to filter out dates older than today.

regexp_like(batch_id,'[0-9]{2}.[0-9]{2}.[0-9]{2}') works in my where clause and brings back all the data, but I also need to convert it and compare it to SYSDATE to filter out old dates.

The data is in one varchar2 column and looks like this:


Student Term 01.31.19
Student Term 09.15.18
Student Term 09.30.18
Student Term 11.30.18
STAFF 08/31/18-08/15/19
EXTRA 8.31.18-12.21.18
EXTRAS END 08.31.18
AUGMENT END 08.31.18


All help is greatly appreciated.
0
In Oracle SQL how could I do create this Query.  I would like to check for multiple conditions with the where clause.

select * from lawprod.WHSHIPLINE where company=10 location=98MDC update-date='01/20/2018'

It works if I do this

select * from lawprod.WHSHIPLINE where company=10  

However, I would like to check for more conditions like

I would like to include data of the company=10 and location=98MDC and update_date = 01/20/2018

Thanks for any help!
0
Determine the Perfect Price for Your IT Services
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Dear Experts,

How do I in Oracle SQL search a string in my schema views code ?

for example I need to know all the views in my schema which contain the string: "left join"

Thanks
0
we have created a formula placeholder to show the working days between 2 dates but it gives an error as invalid number attached the RDF and the error image
please help us to resolve the isssue



function CF_1Formula return Number is
m_acal_dr number;
m_acal_cr number;
CURSOR C1 IS
select distinct (workdays) from (
SELECT   :r_from_cust_main_acnt_code,:r_till_cust_main_acnt_code,
           (:r_till_cust_main_acnt_code - :r_from_cust_main_acnt_code)
         - 2 * FLOOR ((:r_till_cust_main_acnt_code - :r_from_cust_main_acnt_code) / 7)
          AS workdays
    FROM ot_job_exec_head);
 
begin
           IF C1%ISOPEN THEN
        CLOSE C1;
  END IF;
 
  OPEN C1;
  FETCH C1 INTO M_acal_dr;
  CLOSE C1;
   
  return( M_acal_dr);
 
  end;
0
Hi Experts, one more question on Flashback. I have one small table with 100 rows only. It has updates on every batch run. And suppose a batch aborts we want to flashback this. So, enabled the row movement on this table.  And it helps to restore the data of this table. And it is very effective. But the question is can this flash back (row movement at table level) also after the entire DB restore?
Or on the DB restore it will erase the logs and cannot flashback before the restore time?

Thank you.
0
Need a SQL statement that eliminates duplicates and chooses the last record entered.

Ex. WO1002421 has two entries. I want to keep the record of the last person Wilmer Tamayo-Sanchez.

This statement below is what I am using but as unfortunately, it still brings in duplicates.  
SELECT MAX(WO_NUMBER) as wo_number
	  ,FIRST_NAME
      ,LAST_NAME
  FROM BA_VIEW_WO_LABOR
WHERE SKILL NOT LIKE '%INSPECTOR'
GROUP BY WO_NUMBER
      ,FIRST_NAME
      ,LAST_NAME
	  having count(*)>1
ORDER BY WO_NUMBER 

Open in new window

Capture.JPG
0
Hi Expert,

I have a table called student with ID and NAME as the column name whereas i have
made ID as sequence which will generate auto number 1..2..and so on when any new record will be inserted.

But the senario is that when i want delete the record or truncate the table
i want that the when i will re insert the table with values then the ID column should start
from begining i.e. 1..2..3...and so on instead of starting from last sequence number.

Could you please anyone can help out and provided the tried and tested solution.

Thanks.
0
What is the correct statement to subtract TARGET_TAT from NET_TAT from the SQL below?
Keep getting an error when using, round(TARGET_TAT-BA_VIEW_WO_SUMMARY.NET_TAT,0) DAYS_LEFT

sql
0
Need help with a statement that will display the value in ctd.TAT_DAYS but when the value is null, default it to 21.

Ex. ctd.TAT_DAYS = 15, show 15 but if NULL show 21. The full statement is below. Thank you in advance for any help.
select wos.description STATUS
,woo.si_number WO_NUMBER
,pnm.pn PART_NUMBER
,pnm.description
,woo.WARRANTEE_FLAG
,woo.WO_UDF_010 AS WARRANTY_REQUESTED
,dpt.dept_name SHOP
,loc.location_code SHOP_LOCATION
,ctd.TAT_DAYS TARGET_TAT
,round(sysdate-woo.entry_date,0) DAYS_OPEN
,woo.last_status_chg
,round(sysdate-woo.LAST_STATUS_CHG,0) DAYS_IN_STATUS
,round((sysdate-woo.LAST_STATUS_CHG)*24,0) HOURS_IN_STATUS
,round(woo.due_date-sysdate,0) DAYS_LEFT
,hours_punched.hours,open_labor.name CURRENT_TECHNICIAN
,woo.woo_auto_key,woo.entry_date
,woo.due_date
,cmp.company_code CUSTOMER_CODE
,cmp.company_name CUSTOMER
,woo.priority
,ba_view_wo_summary.last_quote_approved
,decode(woo.ctd_auto_key,null,'N','Y') contract
,cth.contract_number
,cth.sdf_cth_001 contract_type
,woo.manual_ECD ECD
,sysur2.first_name||' '||sysur2.last_name TECHNICIAN
,sysur3.first_name||' '||sysur3.last_name INSPECTOR
from wo_operation woo
left join ba_view_wo_summary
on ba_view_wo_summary.wo_number=woo.si_number
inner join wo_status wos
on woo.wos_auto_key=wos.wos_auto_key
inner join parts_master pnm
on woo.pnm_auto_key=pnm.pnm_auto_key
inner join department dpt
on woo.dpt_auto_key=dpt.dpt_auto_key
left join companies cmp
on woo.cmp_auto_key=cmp.cmp_auto_key
left join  

Open in new window

0
Hi Experts,

The below query is getting 00911 ERROR INVALID CHARACTER
What will be the wrong in the below query

SELECT DISTINCT NVL ((SELECT ml_curcurrmajor
                         FROM curr_ref curr_ref
                         WHERE curr_ref.ml_curcurrcode = dv_curr_ref.curcurrcode
                           AND curr_ref.ml_curlanguage = NVL ('en', 'en')),
                         dv_curr_ref.curcurrmajor) AS currencyname,dv_curr_ref.curcurrcode AS currid
   FROM dv_curr_ref dv_curr_ref where dv_curr_ref.DELETEFLAG='N';

Open in new window

0
oracle sql.  I created a field using this trunc(sysdate) - trunc(EVENT_DT) as TotalDays,   I have tried different things and none work.   Can you help with syntax to do this?

In the where statement I want only get the days >10
and TotalDays > (10)

ORA-00904: "TOTALDAYS": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 40 Column: 5
0
CompTIA Security+
LVL 12
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Hi Expert,

Please give your valuable inputs on the below query.

How to validate each record based on some conditions and insert into error table if the condition fails during bulk insert into actual table (insert query is having inner join, eg:- insert into sample1(a,b,c) select x,y,z from sample2 s2,sample3 s3 where s2.col1=s3.col2). Which one is the best approach to follow here?

Thanks,
0
Hi

I have a varchar2 Oracle field called "" that contains a special date time format "YYYY-MM-DD"T"HH:MM:SS". Ex: 2018-09-02T19:30:00

From that date, i would like to get the day name of it. In above example, "Sun" would be the day of August 2 2018.

I tried multiple TO_CHAR(TO_DATE(DATE_TIME_START but does not recognize the value as a date. How can i do that?

Select  ID, CUSTOMER, DATE_TIME_START from Schedule where DATE_TIME_START = 'SUN'

Open in new window


Thank you for your help
0
Hi

Since Flashback is so good can this be an alternative to restore database? My use case is database setup is done. Testing started and after 2 days came to know there is issue in some batch and this has corrupted the data. So, here I want to restore the DB back to 2 days before. This I can easily achieve with Flashback.

Assuming Enable Row Movement is true for most of the important tables. Is there anyway to Flashback whole database without worrying about row movement enable?

Does enabling row movement cause more memory?

Does Flashback as such needs more memory? Means of my DB is day 700 MB so I need 1400MB if I wanna use Flashback?
0
Dear Experts,

 How do I in Oracle SQL check if the field contain text with length 18 & contain [a..z,A..Z,0..9] chars only

Thanks
0
Hi

While moving the data from production to staging or development test environment, what is the best way to mask some columns of some tables?

The two option I can think of are:
(A) Apply redaction policy as it is in built in oracle 12c
(B) Export data then apply script on columns the move the data to test environment.

Which of the two ways will be better?

If I go with (A) then I apply the redact.add_policy. But will this be automatically applied on the exported data?
0
I want to join BA_VIEW_COMPANIES_ PARENT to the following statement by CMP_PARENT. How can I properly accomplish without adversely affecting the query? The CMP_PARENT within the statement below resides in a table named  COMPANIES which is a joined table. Any help will be greatly appreciated.

select 

Open in new window

0

Oracle Database

79K

Solutions

26K

Contributors

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.