What the best way to put these long SQL code into a string to use in VBA - I.E QUOTATION MARKS

S

Siyanna

Well-known Member
Joined
Nov 7, 2011
Messages
1,146
Hi All,

Whats the best/simplest way of putting these 2 long SQL codes in to a VBA string so it recognises it. It is taking me forever getting the right syntaxt for VBA

strSQL =

...These are the 2 SQLs...

Code: 
select
nvl(Status,'Total') as status,
my_type,
my_subtype,
sum(one_month) as one_month,
sum(three_month) as three_month,
sum(seven_month) as seven_month,
sum(twelve_month) as over_seven_month,
sum(over_twelve_month) As over_twelve_month
from (
SELECT
'Not Billed' as Status,
COUNT(*) AS VOLUME,
a.my_type,
A.my_subtype,
ROUND(SUM(
Case WHEN
To_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS
 
THEN
ACCRUED_DAYS * ADCHARGE
Else
(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
end ),2) AS ONE_MONTH,

SUM(CASE
 
WHEN
ACCRUED_DAYS > 0 THEN 1 ELSE 0 END) AS C_ONE_MONTH,

ROUND(SUM(
CASE
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS
AND
ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN
(ACCRUED_DAYS - TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
WHEN
ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN 0
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS
THEN
(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
end ),2) as THREE_MONTH,

SUM(CASE
WHEN
ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN 1 ELSE 0 END) AS C_THREE_MONTH,

ROUND(SUM(
CASE
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS
AND
ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN
(ACCRUED_DAYS -
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
WHEN
ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN 0
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS
THEN
(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
end),2)  as SEVEN_MONTH,

SUM(CASE
WHEN
ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN 1 ELSE 0 END) AS C_SEVEN_MONTH,
 
ROUND(SUM(
CASE
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS
AND
ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN
(ACCRUED_DAYS -
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
WHEN
ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN 0
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS
THEN
(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
end),2) as TWELVE_MONTH,

SUM(CASE
WHEN
ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))

THEN 1 ELSE 0 END) AS C_TWELVE_MONTH,

ROUND(SUM(
CASE

WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS

THEN
(ACCRUED_DAYS -
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
Else
end),2) as OVER_TWELVE_MONTH,

SUM(CASE
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS

THEN 1 ELSE 0 END) AS C_OVER_TWELVE_MONTH

from tmason1.accrual_out2 A, CIS.TVP057TARIFF B
WHERE a.CD_TARIFF = B.CD_TARIFF
AND  A.CD_service_prov NOT in ('SW','TW')
and   a.cd_tariff not in ('1STW-RVD01','1STW-RVD02','1STW-RVN01','1STW-RVS01','1STW-RVS02','1STW-RVU01','1STW-RVW01',
'1STW-RVW02','1STW-RVW03')
and substr(a.cd_tariff,2,3) = 'STW'
AND ACCRUED_DAYS > 0
and a.NO_ACCOUNT IN ( SELECT T0.NO_ACCOUNT FROM tmason1.ACCRUAL_APP_DATA T0 WHERE T0.NO_ACCOUNT = a.NO_ACCOUNT AND T0.NO_PROPERTY = a.NO_PROPERTY AND B_STATUS <> 'Billed')
GROUP BY a.my_type,A.MY_SUBTYPE
Union
SELECT
'Indicates Billed Action' as Status,
COUNT(*) AS VOLUME,
a.my_type,
A.my_subtype,
ROUND(SUM(
Case WHEN
To_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS
 
THEN
ACCRUED_DAYS * ADCHARGE
Else
(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
end ),2) AS ONE_MONTH,

SUM(CASE
 
WHEN
ACCRUED_DAYS > 0 THEN 1 ELSE 0 END) AS C_ONE_MONTH,

ROUND(SUM(
CASE
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS
AND
ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN
(ACCRUED_DAYS - TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
WHEN
ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN 0
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS
THEN
(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
end ),2) as THREE_MONTH,

SUM(CASE
WHEN
ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN 1 ELSE 0 END) AS C_THREE_MONTH,

ROUND(SUM(
CASE
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS
AND
ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN
(ACCRUED_DAYS -
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
WHEN
ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN 0
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS
THEN
(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
end),2)  as SEVEN_MONTH,

SUM(CASE
WHEN
ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN 1 ELSE 0 END) AS C_SEVEN_MONTH,
 
ROUND(SUM(
CASE
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS
AND
ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN
(ACCRUED_DAYS -
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
WHEN
ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN 0
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS
THEN
(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
end),2) as TWELVE_MONTH,

SUM(CASE
WHEN
ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))

THEN 1 ELSE 0 END) AS C_TWELVE_MONTH,

ROUND(SUM(
CASE

WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS

THEN
(ACCRUED_DAYS -
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
Else
end),2) as OVER_TWELVE_MONTH,

SUM(CASE
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS

THEN 1 ELSE 0 END) AS C_OVER_TWELVE_MONTH

from tmason1.accrual_out2 A, CIS.TVP057TARIFF B
WHERE a.CD_TARIFF = B.CD_TARIFF
AND  A.CD_service_prov NOT in ('SW','TW')
and   a.cd_tariff not in ('1STW-RVD01','1STW-RVD02','1STW-RVN01','1STW-RVS01','1STW-RVS02','1STW-RVU01','1STW-RVW01',
'1STW-RVW02','1STW-RVW03')
and substr(a.cd_tariff,2,3) = 'STW'
AND ACCRUED_DAYS > 0
and a.NO_ACCOUNT IN ( SELECT T0.NO_ACCOUNT FROM tmason1.ACCRUAL_APP_DATA T0 WHERE T0.NO_ACCOUNT = a.NO_ACCOUNT AND T0.NO_PROPERTY = a.NO_PROPERTY AND B_STATUS = 'Billed' AND I_TYPE <> 'No Action Required')

GROUP BY a.my_type,A.MY_SUBTYPE
Union
SELECT
'Indicates Billed No Action' as Status,
COUNT(*) AS VOLUME,
a.my_type,
A.my_subtype,
ROUND(SUM(
Case WHEN
To_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS
 
THEN
ACCRUED_DAYS * ADCHARGE
Else
(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
end ),2) AS ONE_MONTH,

SUM(CASE
 
WHEN
ACCRUED_DAYS > 0 THEN 1 ELSE 0 END) AS C_ONE_MONTH,

ROUND(SUM(
CASE
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS
AND
ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN
(ACCRUED_DAYS - TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
WHEN
ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN 0
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS
THEN
(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
end ),2) as THREE_MONTH,

SUM(CASE
WHEN
ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-3)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN 1 ELSE 0 END) AS C_THREE_MONTH,

ROUND(SUM(
CASE
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS
AND
ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN
(ACCRUED_DAYS -
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
WHEN
ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN 0
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS
THEN
(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
end),2)  as SEVEN_MONTH,

SUM(CASE
WHEN
ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-5)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN 1 ELSE 0 END) AS C_SEVEN_MONTH,
 
ROUND(SUM(
CASE
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) >= ACCRUED_DAYS
AND
ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN
(ACCRUED_DAYS -
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
WHEN
ACCRUED_DAYS <= to_number(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY'))
THEN 0
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS
THEN
(TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
end),2) as TWELVE_MONTH,

SUM(CASE
WHEN
ACCRUED_DAYS > TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-9)),'DD/MM/YYYY'),'DD/MM/YYYY'))

THEN 1 ELSE 0 END) AS C_TWELVE_MONTH,

ROUND(SUM(
CASE

WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS

THEN
(ACCRUED_DAYS -
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY'))) * ADCHARGE
Else
end),2) as OVER_TWELVE_MONTH,

SUM(CASE
WHEN
TO_NUMBER(TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-2)),'DD/MM/YYYY'),'DD/MM/YYYY')  -
TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate,-14)),'DD/MM/YYYY'),'DD/MM/YYYY')) < ACCRUED_DAYS

THEN 1 ELSE 0 END) AS C_OVER_TWELVE_MONTH

from tmason1.accrual_out2 A, CIS.TVP057TARIFF B
WHERE a.CD_TARIFF = B.CD_TARIFF
AND  A.CD_service_prov NOT in ('SW','TW')
and   a.cd_tariff not in ('1STW-RVD01','1STW-RVD02','1STW-RVN01','1STW-RVS01','1STW-RVS02','1STW-RVU01','1STW-RVW01',
'1STW-RVW02','1STW-RVW03')
and substr(a.cd_tariff,2,3) = 'STW'
AND ACCRUED_DAYS > 0
and a.NO_ACCOUNT IN ( SELECT T0.NO_ACCOUNT FROM tmason1.ACCRUAL_APP_DATA T0 WHERE T0.NO_ACCOUNT = a.NO_ACCOUNT AND T0.NO_PROPERTY = a.NO_PROPERTY AND B_STATUS = 'Billed' AND I_TYPE = 'No Action Required')

GROUP BY a.my_type,A.MY_SUBTYPE
order by 1,3)
group by rollup(status),my_type,my_subtype

AND

Code: 
select 
T0.NO_PROPERTY
,T0.NO_ACCOUNT
,T0.CD_PROPERTY_USE
,T0.MY_TYPE
,T0.MY_SUBTYPE
,T0.ACCRUED_FROM
,T0.ACCRUAL_DATE
,T0.OVER_YEAR
,T0.ST_ACCOUNT
,T0.B_STATUS
,T0.I_TYPE
,T0.USER_ID
,T0.TS_UPDATE
,T0.DEL_TO
,T0.HOLD_DATE
,to_char(ts_update,'HH24') as hr
,to_char(ts_update,'Day') as My_Day
from TMASON1.accrual_app_data t0
where to_date(to_char(ts_update,'dd/mm/yyyy'),'dd/mm/yyyy') = to_date(to_char(sysdate,'dd/mm/yyyy'),'dd/mm/yyyy') - 3
and user_id != 'NINJA01'
 
Sort by date Sort by votes
xenou said:
If it works sometimes and not other times, then connectivity/networking is a suspect -- ie., remote db is not running, your own network is jammed, or something else that prevents a connection. If you only have this problem in one workbook and not another, then it's too weird to give advice on, other than move everything into the workbook that works.
Click to expand...

Hi Xenou,

thanks for looking into this

Is there a way i can have multiple providers in an If statement so it tries to connect to 1 provider if 1 fails?

1st provider:

connection_string = "Provider=OraOLEDB.Oracle.1;" & "Password=" & strpass & "; User ID=" & strlogin & ";Data Source=" & strDB & "; Persist Security Info=True"

if that fails then

2nd provider etc....
 
Upvote 0

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sure, just wrap in an error handler. Loosely, like this:

Code: 
On Error Resume Next
    '//code to connect using first connection string

    If my_connect_object Is Nothing Then
        '//Code to connect using second connection string
    End If
    
    If my_connect_object Is Nothing Then
        '//Abort because neither worked
    End If
On Error Goto 0

But I don't know what connection string you have in mind for a second provider. I think your only choices are the oracle provider you are using now, or a more generic ODBC driver (if such a thing exists), which might not be as fully featured for all the things Oracle can do.
 
Upvote 0
xenou said:
Sure, just wrap in an error handler. Loosely, like this:

Code: 
On Error Resume Next
    '//code to connect using first connection string

    If my_connect_object Is Nothing Then
        '//Code to connect using second connection string
    End If
    
    If my_connect_object Is Nothing Then
        '//Abort because neither worked
    End If
On Error Goto 0

But I don't know what connection string you have in mind for a second provider. I think your only choices are the oracle provider you are using now, or a more generic ODBC driver (if such a thing exists), which might not be as fully featured for all the things Oracle can do.
Click to expand...

Thank you

Im struggling to get another provider..i only had 1 that i found on the forum :(
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
VBA Automation Error - possibly born from my complete lack of understanding with Error Handling
2 3
Replies
23
Views
7K
RockandGrohl
R
M
  • Question
VBA Code - Not working when another user uses the macro
Replies
6
Views
1K
MHamid
M

Forum statistics

Threads
1,215,425
Messages
6,124,824
Members
449,190
Latest member
rscraig11

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top