Home » SQL & PL/SQL » SQL & PL/SQL » How to get a Output as mentioned below (Product: Oracle Database 18c EE Extreme Perf ,Version:18.0.0.0.0 , Os: Windows 10)
How to get a Output as mentioned below [message #681520] Sun, 26 July 2020 12:18 Go to next message
gsaravanan
Messages: 16
Registered: April 2015
Location: chennai
Junior Member
Hi,
I have a two tables one is Master table and another one Transaction table with Foreign Key relationship.

Master table:
Id, FIle_Name,Frequency, Schedule_days
1 ABC_123 Weekly Monday,Tuesday
2 ABC_000 Weekly Friday
3 XYZ_000 Monthly 4
4 XYZ_123 Monthly 1,7

Note: Schedule_Days column value not fixed,Its may different for each file

Transaction Table:

Id, File_Name ,Date
1 ABC_123 ***
2 ABC_000 ***
3 XYZ_000 ***

I want to display Master table File Name in Column wise and Date /Month based on given date range in row wise. How to achieve this logic please help me.

Logic: Want to compare two tables using Id Column and date range (Date range applied to Transaction table date column),In particular given date period if file is available in Transaction table then mark as A(Arrived ),If Not available then mark as F(Failed)

Input parameter is Date for my Procedure, i want to create data structure as attached , Please help me on the same,Kindly let me know if needed more details.

[Updated on: Sun, 26 July 2020 12:21]

Report message to a moderator

Re: How to get a Output as mentioned below [message #681522 is a reply to message #681520] Sun, 26 July 2020 14:12 Go to previous messageGo to next message
Michel Cadot
Messages: 67303
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Sun, 08 December 2019 09:57

As told you in your previous topics:


BlackSwan wrote on Mon, 20 April 2015 19:50
Welcome to this forum.
Please read and follow the forum guidelines, to enable us to help you:
http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/
Lalit Kumar B wrote on Tue, 28 April 2015 17:01
...
In none of your posts so far you have followed the forum guidelines. You didn't use code tags. Please read and follow the OraFAQ Forum Guide and How to use [code] tags, to enable us to help you...

So I repeat:
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

In the end, you did neither feedback nor answer to our questions in your previous topics, this won't lead us to be in good mood to help your now.

[Updated on: Sun, 26 July 2020 14:13]

Report message to a moderator

Re: How to get a Output as mentioned below [message #681535 is a reply to message #681520] Tue, 28 July 2020 08:44 Go to previous messageGo to next message
gsaravanan
Messages: 16
Registered: April 2015
Location: chennai
Junior Member
Hi,
I have a two tables one is Master table and another one Transaction table with Foreign Key relationship.

Note: Schedule_Days column value not fixed,Its may different for each file


CREATE TABLE master_table (
id NUMBER,
file_name VARCHAR2(100),
frequency VARCHAR2(20),
schedule_days VARCHAR2(200),
CONSTRAINT pk_key PRIMARY KEY ( id )
);

CREATE TABLE transaction_table (
tranid NUMBER,
tran_file_name VARCHAR2(100),
tran_date DATE,
CONSTRAINT fk_key FOREIGN KEY ( tranid )
REFERENCES master_table ( id )
);

insert into master_table values(1,'ABC_123','Weekly','Monday,Wednesday');
insert into master_table values(2,'XYZ_123','Weekly','Friday');
insert into master_table values(3,'ASD_123','Monthly','8,10');
insert into master_table values(4,'CKS_123','Daily','Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday');

insert into Transaction_table values(1,'ABC_123','06-Jul-2020');
insert into Transaction_table values(1,'ABC_123','08-Jul-2020');
insert into Transaction_table values(3,'ASD_123','08-Jul-2020');
insert into Transaction_table values(4,'CKS_123','09-Jul-2020');
insert into Transaction_table values(4,'CKS_123','10-Jul-2020');


I want to display Master table File Name in Column wise and Date /Month based on given date range in row wise. How to achieve this logic please help me.

Logic: Want to compare two tables using Id Column and date range (Date range applied to Transaction table date column),In particular given date period
if file is available in Transaction table then mark as A(Arrived ),If Not available then mark as F(Failed)

Input parameter is Date for my Procedure, i want to create data structure as attached , Please help me on the same,Kindly let me know if needed more details.


My Sample code below,But no idea how to handle it in code further

create or replace procedure test123 (P_From_Date In date,
                                     P_To_date In Date,
                                     P_Out out SYS_REFCURSOR)
as
  start_date number;
  end_date number;
  business_date varchar2(20);
begin
  start_date := to_number(to_char(to_date(P_From_Date, 'DD-mm-yy'), 'j'));
  end_date := to_number(to_char(to_date(P_To_Date, 'DD-mm-yy'), 'j'));
  
       for i in start_date..end_date loop
    business_date := to_char(to_date(i, 'j'), 'YYYY-MM-DD');

  dbms_output.put_line(business_date);

  open P_Out for   Select
        File_Name,(case when (Select '1'  From  master_table 
    Where
        Trim(Upper(Frequency)) = 'WEEKLY'
       And Replace(Upper(Schedule_Days), Trim(To_Char(to_date(business_date,'YYYY-MM-DD'), 'DAY')), '$') Like '%$%'
        And ( Id ) In (
            Select  TranId
            From Transaction_Table)
            and rownum=1)=1 then 'X' End  ) "Exchanged"  From  master_table ;
 end loop;
end;
Output structure as attached



Re: How to get a Output as mentioned below [message #681536 is a reply to message #681535] Tue, 28 July 2020 10:18 Go to previous messageGo to next message
BlackSwan
Messages: 26730
Registered: January 2009
Location: SoCal
Senior Member
>start_date number;
>end_date number;
>business_date varchar2(20);

DATES should ALWAYS be DATE datatype & NEVER a NUMBER or STRING!
Above qualifies as Worst Practice programming!
Re: How to get a Output as mentioned below [message #681537 is a reply to message #681536] Tue, 28 July 2020 11:01 Go to previous messageGo to next message
Michel Cadot
Messages: 67303
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In addition to BlackSwan's post:
SQL> insert into Transaction_table values(1,'ABC_123','06-Jul-2020');
insert into Transaction_table values(1,'ABC_123','06-Jul-2020')
                                                 *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
As s string is not a date, use TO_DATE.

Re: How to get a Output as mentioned below [message #681542 is a reply to message #681537] Wed, 29 July 2020 03:19 Go to previous message
gsaravanan
Messages: 16
Registered: April 2015
Location: chennai
Junior Member
Hi,
Thanks for your query. i want to put it into loop based on date range,for this reason i am using date range in number.

If its not correct way , Please help to get a query to achieve attached structure output.
Previous Topic: Query is taking 41 minute in Production environment
Next Topic: Need Help in Query (merged)
Goto Forum:
  


Current Time: Thu Aug 13 20:35:18 CDT 2020