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,st ate 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,
[code=oracle]

Select To_Char (Sysdate, 'Mm/Dd/Yy Hh24mi') Todays_Date,
B.Ins_Co_Name,
B.Ins_Co_Addres s2,
B.Ins_Co_City,
B.Ins_Co_State,
B.Ins_Co_Zip,
B.Pat_Lname| |','| |B.Pat_Fname| |' '| |Pat_Middle_Ini tial 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_Am ount,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_Am ount,0)) Jag,
Sum(G.Credit_Am ount) Totals,
Sum(0) Dummy
From I_Arc_General_L edger 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'
/
[/code]

[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.
[code=oracle]

Select To_Char (Sysdate, 'Mm/Dd/Yy Hh24mi') Todays_Date,
B.Ins_Co_Name,
B.Ins_Co_Addres s2,
B.Ins_Co_City,
B.Ins_Co_State,
B.Ins_Co_Zip,
B.Pat_Lname| |','| |B.Pat_Fname| |' '| |Pat_Middle_Ini tial 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_Am ount,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_Am ount,0)) Jag,
Sum(G.Credit_Am ount) Totals,
Sum(0) Dummy
From I_Arc_General_L edger 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'
/
[/code]