Today's Messages (on)
| Unanswered Messages (off)
|
Forum: SQL & PL/SQL
|
|
Topic: Convert input from user into UPPERCASE
|
| Re: Convert input from user into UPPERCASE [message #689797 is a reply to message #689761] |
Wed, 24 April 2024 03:34 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Good morning - a bit late, I know, but I happened to come across your question while researching something similar.
It all comes down to the SQL Standard. I don't have the exact reference, but SQL-92 (I think it was 92) specified that lower case identifiers should be converted to upper case, unless double quoted. Oracle is 100% compliant with this: the SQL parser will do the conversion. Some other databases are different. For example, I think that SQL Server will preserve the case of unquoted identifiers when creating an object, but ignore it subsequently. Differences like that can cause bizarre problems when working in a heterogeneous environment.
|
|
|
|
|
Topic: Condition based truncate partition
|
|
|
|
Topic: Missing records
|
| Missing records [message #689798] |
Wed, 24 April 2024 06:25 |
 |
ace_friends22
Messages: 9
Registered: December 2023
|
Junior Member |
|
|
Hi All,
I'm working for a shipment company. We get voyage information. Below is requirement and I'm not able to find right solution for this.
- We get Voyage detail on daily basis and we load it into a target table
- There is a table called voyage master, which shows when voyage is discontinued.
- Even if Voyage is discontinued, we need to create dummy record and populate it in the separate table
- Later, we do UNION in the view to combine both tables to show details to users
- There is also challenges that sometimes, voyages gets discontinued and later re-start. In that scenario, we need to make sure process creates dummy records for days, it was discontinued and later start populating main target table when it re-start
The solution should be ro-bust to take care these thing.
Can someone help me with what logic I should put. Googled one is not solving both problems and Lead is expecting dummy record process to robust.
Please note, voyage start date could be different.
I hope, i'm clear with my doubt.
[Updated on: Wed, 24 April 2024 08:21] by Moderator Report message to a moderator |
|
|
|
| Missing records [message #689799 is a reply to message #689798] |
Wed, 24 April 2024 06:26 |
|
ace_friends22
Messages: 9
Registered: December 2023
|
Junior Member |
|
|
Hi All,
I'm working for a shipment company. We get voyage information. Below is requirement and I'm not able to find right solution for this.
- We get Voyage detail on daily basis and we load it into a target table
- There is a table called voyage master, which shows when voyage is discontinued.
- Even if Voyage is discontinued, we need to create dummy record and populate it in the separate table
- Later, we do UNION in the view to combine both tables to show details to users
- There is also challenges that sometimes, voyages gets discontinued and later re-start. In that scenario, we need to make sure process creates dummy records for days, it was discontinued and later start populating main target table when it re-start
The solution should be ro-bust to take care these thing.
Can someone help me with what logic I should put. Googled one is not solving both problems and Lead is expecting dummy record process to robust.
Please note, voyage start date could be different.
I hope, i'm clear with my doubt.
Source Data:
WITH SRC AS
(
SELECT '1234' VOYAGE, '2024' CAL_YEAR, TO_DATE('04/05/2024', 'MM/DD/YYYY') FILE_DATE, TO_DATE('04/04/2024', 'MM/DD/YYYY') DATA_DATE, '04/04/2024' SAIL_DATE
UNION
SELECT '1234' VOYAGE, '2024' CAL_YEAR, TO_DATE('04/06/2024', 'MM/DD/YYYY') FILE_DATE, TO_DATE('04/05/2024', 'MM/DD/YYYY') DATA_DATE, '04/04/2024' SAIL_DATE
UNION
SELECT '1234' VOYAGE, '2024' CAL_YEAR, TO_DATE('04/07/2024', 'MM/DD/YYYY') FILE_DATE, TO_DATE('04/06/2024', 'MM/DD/YYYY') DATA_DATE, '04/04/2024' SAIL_DATE
UNION
SELECT '1234' VOYAGE, '2024' CAL_YEAR, TO_DATE('04/08/2024', 'MM/DD/YYYY') FILE_DATE, TO_DATE('04/07/2024', 'MM/DD/YYYY') DATA_DATE, '04/04/2024' SAIL_DATE
UNION
--SELECT '1234' VOYAGE, '2024' CAL_YEAR, TO_DATE('04/09/2024', 'MM/DD/YYYY') FILE_DATE, TO_DATE('04/08/2024', 'MM/DD/YYYY') DATA_DATE, '04/04/2024' SAIL_DATE
--UNION
--SELECT '1234' VOYAGE, '2024' CAL_YEAR, TO_DATE('04/10/2024', 'MM/DD/YYYY') FILE_DATE, TO_DATE('04/09/2024', 'MM/DD/YYYY') DATA_DATE, '04/04/2024' SAIL_DATE
--UNION
SELECT '1234' VOYAGE, '2024' CAL_YEAR, TO_DATE('04/11/2024', 'MM/DD/YYYY') FILE_DATE, TO_DATE('04/10/2024', 'MM/DD/YYYY') DATA_DATE, '04/04/2024' SAIL_DATE
UNION
SELECT '1234' VOYAGE, '2024' CAL_YEAR, TO_DATE('04/12/2024', 'MM/DD/YYYY') FILE_DATE, TO_DATE('04/11/2024', 'MM/DD/YYYY') DATA_DATE, '04/04/2024' SAIL_DATE
UNION
SELECT '1234' VOYAGE, '2024' CAL_YEAR, TO_DATE('04/13/2024', 'MM/DD/YYYY') FILE_DATE, TO_DATE('04/12/2024', 'MM/DD/YYYY') DATA_DATE, '04/04/2024' SAIL_DATE
UNION
SELECT '1234' VOYAGE, '2024' CAL_YEAR, TO_DATE('04/14/2024', 'MM/DD/YYYY') FILE_DATE, TO_DATE('04/13/2024', 'MM/DD/YYYY') DATA_DATE, '04/04/2024' SAIL_DATE
UNION
SELECT '1234' VOYAGE, '2024' CAL_YEAR, TO_DATE('04/15/2024', 'MM/DD/YYYY') FILE_DATE, TO_DATE('04/14/2024', 'MM/DD/YYYY') DATA_DATE, '04/04/2024' SAIL_DATE
UNION
SELECT '1234' VOYAGE, '2024' CAL_YEAR, TO_DATE('04/16/2024', 'MM/DD/YYYY') FILE_DATE, TO_DATE('04/15/2024', 'MM/DD/YYYY') DATA_DATE, '04/04/2024' SAIL_DATE
)
select * from src order by data_date
WITH VOYAGE_MSTR AS
(
SELECT '1234' VOYAGE, '2024' CAL_YEAR, TO_DATE('04/04/2024', 'MM/DD/YYYY') SAIL_DATE, TO_DATE('04/15/2024', 'MM/DD/YYYY') RETURN_DATE
)
In the existing table (SRC) we can see, daily VOYAGE information is getting inserted, but somehow we missed entry on 8th and 9th.
Now we need to generate dummy records for 9th & 10th as well as from 4/16/2024 till date. Attachedis output, records marked with yello needs to be generatyed
-
Attachment: Output.PNG
(Size: 15.42KB, Downloaded 1 time)
[Updated on: Wed, 24 April 2024 08:16] Report message to a moderator |
|
|
|
| Re: Missing records [message #689800 is a reply to message #689798] |
Wed, 24 April 2024 08:31 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
From your previous topic:
Michel Cadot wrote on Wed, 27 December 2023 13:28
If you want to continue to get help feedback in your topics.
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.
[Updated on: Wed, 24 April 2024 08:32] Report message to a moderator |
|
|
|
|
|
Current Time: Wed Apr 24 14:42:15 CDT 2024
|
Blog
Search
Help
Members
Register
Login
Home
