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
Are you using an ADO connection string (with an ADO connection)?
 
Upvote 0

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
xenou said:
Are you using an ADO connection string (with an ADO connection)?
Click to expand...

Hi Xenou

I have set a ref to both:
Microsoft ActiveX Data Objects 6.0 Library
Microsoft DAO 3.6 Object Library

Code: 
VBA:

Dim cmd As ADODB.Command
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset
Set cn = New ADODB.Connection


'Loads database login details
    strDB = "MVWLM"     '(Schema Details)
    strlogin = "SIYANA" '(MyLogin UserId)
    strpass = "PASSWORD" '(MyPassword)

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

  '//Open a connection with the connection string
    With cn
        .ConnectionString = connection_string
       [COLOR=#FF0000] .Open '(IT WONT OPEN IT)[/COLOR]
    End With
 
Upvote 0
Upvote 0
Think about what are the differences between the two workbooks. As always test with simple cases first to make sure that you are able to connect and run simple queries.
 
Upvote 0
xenou said:
Think about what are the differences between the two workbooks. As always test with simple cases first to make sure that you are able to connect and run simple queries.
Click to expand...

Hi Xenou, im now finding that sometimes the connection opens in my workbook snd sometimes i get the same error message therefore the sql most definitely works but for some reason doesnt always pick it up
 
Upvote 0
Micron said:
If this TMASON1.accrual_app_data t0" is actually your table name for the FROM clause,
that's bad form - spaces and dots? You'd have to enclose it all in [ ]..
Click to expand...

would actually result like
TMASON1.accrual_app_data AS t0, and as is not formerly required it doesn't have to be there. The dot separates out table from database
 
Upvote 0
what do you use to set up your connection string

in excel I do

Code: 
sConn = "Provider=SQLOLEDB; Data Source =DATABASE ; Initial Catalog = TABLE; User Id = ID; Password=PASSWORD"

output from SQL Formatter
Code: 
Var1 = ""
    Var1 = Var1 & "Select SHIFTCYCLE.SHIFTDATE " & vbCrLf

then

Code: 
sSql = Var1

    Set adoConn = New ADODB.Connection
    adoConn.Open sConn
    adoConn.CommandTimeout = 60
    Set adoRs = New ADODB.Recordset
    adoRs.Open Source:=sSql, ActiveConnection:=adoConn
    On Error Resume Next
    If Not (adoRs.BOF Or adoRs.EOF) Then
        Do While Not adoRs.EOF
            rnStart.CopyFromRecordset adoRs
            adoRs.MoveNext
        Loop
        sOutput = Left(sOutput, Len(sOutput) - 1)
    Else
        sOutput = "NO MATCHED DATA"
        Sheets("REFERENCE LOOKUP").Range("A24") = sOutput
    End If
    adoRs.Close
    adoConn.Close
    Set adoRs = Nothing
    Set adoConn = Nothing



I have been told EOF isn't necessary to use, but its not done me any harm
 
Last edited:
Upvote 0
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.
 
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,407
Messages
6,124,723
Members
449,184
Latest member
COrmerod

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