SQL Plus

[Gator25]

I'm using SQL Plus 10.2 to query an oracle 10G db.
The script I have works great except several of the insurance company names begin with the same phrase before adding the additional identifier. Then other insurance company names then follow alphabetically.
For example BCBS OF NC Employee,BCBS OF NC Federal,BCBS OF NC State,
then a different insurance company CAREMARk, then MailHandlers.
I'm using Group By Ins_Co_name so all the BCBS prints as one and all the MailHandlers prints as one.
How do I break out the different Insurance carriers so they print as they should ?
I have setup a variable that prints the Ins_Co_name, address,city,state in a header and then the respective claims print below that header.
Sorry to make this lengthy.
Thanks in advance

[debasisdas]

Can you kindly post your query that you are working on with the table structure for reference of our experts.

[Dave44]

could you use a case statement to substring the name and if its BCBS use the rest of the string to sort otherwise use the whole field? something like that?

[Gator25]

Can you kindly post your query that you are working on with the table structure for reference of our experts.

Ok,

 
Select To_Char (Sysdate, 'Mm/Dd/Yy Hh24mi') Todays_Date,
    B.Ins_Co_Name,      
    B.Ins_Co_Address2,
    B.Ins_Co_City,
    B.Ins_Co_State,
    B.Ins_Co_Zip,
    B.Pat_Lname| |','| |B.Pat_Fname| |' '| |Pat_Middle_Initial Name,             
    G.Claim,
    G.Trans_Cd,
    G.Entry_Date Bill_Date,
    G.Trans_Date,
    G.Billed,
    G.Payment,
    G.Adjustments,
    G.Jag,
    G.Totals,
    G.Dummy
From I_Arc_Permanent_Bill B,
(Select
    Substr(G.Cntrl_No,1,10) Claim,
    G.Trans_Cd,
    G.Entry_Date Bill_Date,
    G.Trans_Date,
    Sum( Decode(Substr(G.Trans_Cd,1,2),'01',G.Debit_Amount,0))  Billed,
    Sum( Decode(Substr( G.Trans_Cd,1,1), 'R', G.Credit_Amount, 0)) Payment,
    Sum( Decode(Substr( G.Trans_Cd,1,1), 'W', G.Credit_Amount, 0))  Adjustments,
    Sum( Decode(Substr(G.Trans_Cd,1,1),'U',G.Credit_Amount,0)) Jag,
    Sum(G.Credit_Amount) Totals,
    Sum(0) Dummy
   From I_Arc_General_Ledger G
  Where G.Entry_Date Between Trunc(Sysdate) -210 And Trunc(Sysdate) -120
    And Substr(G.Trans_Cd,1,2) !='AU'
  Group By  G.Cntrl_No, G.Trans_Cd, G.Entry_Date, G.Trans_Date
 Having Sum( Decode(Substr(G.Trans_Cd,1,1), 'U', Credit_Amount, 0)) !=0
) G
Where G.Cntrl_No = B.Cntrl_No
  And B.Status   = 'OPEN'
/
 

[Gator25]

could you use a case statement to substring the name and if its BCBS use the rest of the string to sort otherwise use the whole field? something like that?

Dave,
I'm not sure you can do that in SQL Plus.
Here is the code.

 
Select To_Char (Sysdate, 'Mm/Dd/Yy Hh24mi') Todays_Date,
    B.Ins_Co_Name,      
    B.Ins_Co_Address2,
    B.Ins_Co_City,
    B.Ins_Co_State,
    B.Ins_Co_Zip,
    B.Pat_Lname| |','| |B.Pat_Fname| |' '| |Pat_Middle_Initial Name,             
    G.Claim,
    G.Trans_Cd,
    G.Entry_Date Bill_Date,
    G.Trans_Date,
    G.Billed,
    G.Payment,
    G.Adjustments,
    G.Jag,
    G.Totals,
    G.Dummy
From I_Arc_Permanent_Bill B,
(Select
    Substr(G.Cntrl_No,1,10) Claim,
    G.Trans_Cd,
    G.Entry_Date Bill_Date,
    G.Trans_Date,
    Sum( Decode(Substr(G.Trans_Cd,1,2),'01',G.Debit_Amount,0))  Billed,
    Sum( Decode(Substr( G.Trans_Cd,1,1), 'R', G.Credit_Amount, 0)) Payment,
    Sum( Decode(Substr( G.Trans_Cd,1,1), 'W', G.Credit_Amount, 0))  Adjustments,
    Sum( Decode(Substr(G.Trans_Cd,1,1),'U',G.Credit_Amount,0)) Jag,
    Sum(G.Credit_Amount) Totals,
    Sum(0) Dummy
   From I_Arc_General_Ledger G
  Where G.Entry_Date Between Trunc(Sysdate) -210 And Trunc(Sysdate) -120
    And Substr(G.Trans_Cd,1,2) !='AU'
  Group By  G.Cntrl_No, G.Trans_Cd, G.Entry_Date, G.Trans_Date
 Having Sum( Decode(Substr(G.Trans_Cd,1,1), 'U', Credit_Amount, 0)) !=0
) G
Where G.Cntrl_No = B.Cntrl_No
  And B.Status   = 'OPEN'
/