Homework question that I’m stuck on. I need to create a Procedure called dept_info with a department id as the IN parameter with department name and full name (concat of first and last name) of the manager of that department as the OUT parameters to be passed back to the invoking program. the procedure …
Tag: oracle
About “group function is not allowed here”
I have a table with job, salary and date columns. I am writing the following query in PL/SQL, but I am getting an error group function is not allowed here How can I solve this problem? Answer Your query makes no sense (to me, at least). What does that average salary represent? Whose average salary? Here’…
Consolidate union result
I am selecting email address from two source tables via a union query. Normally, I would leverage union’s behavior of filtering out duplicate values, but in this case, each source table may or may not have a value for each person, and I have to give “priority” to the email address from the f…
SQL – Selecting rows based on date difference
Suppose we have below table: Code Dt c1 2020-10-01 c1 2020-10-05 c1 2020-10-09 c1 2020-10-10 c1 2020-10-20 c2 2020-10-07 c2 2020-10-09 c2 2020-10-15 c2 2020-10-16 c2 2020-10-20 c2 2020-10-24 Combination of Code and Dt is unique. Rows are sorted based on Code and Dt. Database is Oracle 12. For every code, I wa…
how would I limit the results to only one address based on a preference
Some of the donor ID’s have multiple mailing addresses, and I just need to select only the first one for each donor, and if there is no mailing address tied to a donor ID, then I need to pull a billing address, and if no billing address, then shipping address. but only show one address Answer You could …
Where In SQL Oracle
I have one question, I am trying to make one WHERE statement in Oracle SQL. As result for the TARGET_ADDRESS column, I need the value from this column only if ENTRY_POINT = +322012345, otherwise it should be null. table data Is it possible? It’s possible to used CASE but I want keep my column TARGET_ADD…
How to select values between a XML tag in SQL Query
I have a table with CLOB column storing a XML. The structure of XML is unreadable. I want to get values between few tags like <DOMAINID>; sample is shown below. XML: I am using this: Expected result: Actual XML: Answer Do not use regular expressions to parse XML; use a proper XML parser. However, what y…
SQLPLUS : set sqlformat insert : SP2-0158
I want to export all my datas as insert statement. In Sql Developer, the command “set sqlformat insert” work very well… but when I want to use it on sqlplus, I have this error message : SP2-0158 unknown SET option “sqlformat” This command work when it’s use on a script but …
temporal joins in hierarchical query
I want to join various nodes of a tree, making sure the the returned root-to-leaf path is temporally valid. The tricky part is that the data source is dated with validity from-to dates. ID NVALUE VFROM VTO 1 A 2021-01-01 2021-01-31 1 B 2021-02-01 2021-02-28 2 C 2021-01-01 2021-02-28 3 D 2021-01-01 2021-01-31 …
Oracle 10g sql query
I have some data like this ↓ T_ID T_PERIOD T_COUNT T_SUM T_UPDATE_COUNT 1 2013-2014 3436 20118043 0 2 2014-2015 4298 27101356 0 3 2015-…