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'
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
I have tried to have a go with the smaller string but it says From keyword not found where expected?

Code: 
strSQL = "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" & "'"

rs.Open strSQL, cn, adOpenStatic, adLockReadOnly
 
Upvote 0
I'm assuming this is Access VBA and sql, but have to admit you've got some stuff in there that I've never seen before in sql (Case, Then, Else...).

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 [ ]. You also have 'Not Billed' as status [Not Billed]; one or two good reasons to not use spaces and special characters (except maybe underscore) in names. If it's not, then it looks like you are asking for all those fields to be selected from one table field ([accrual_app_data t0]), not a table: FROM TMASON1 WHERE (criteria here).

As for your other question, it's best to create the sql statement by using query design, get it working (even if you have to provide actual table values as criteria) then copy from sql view and dump into your code and edit there. I never use the line continuation character method (I find it too wonky and hard to read), so my way is not necessarily best, it's just my preference:
strSql = "SELECT T0.NO_PROPERTY, T0.NO_ACCOUNT ,T0.CD_PROPERTY_USE ,T0.MY_TYPE, T0.MY_SUBTYPE, "
strSql = strSql & "T0.ACCRUED_FROM, T0.ACCRUAL_DATE, T0.OVER_YEAR, T0.ST_ACCOUNT, T0.B_STATUS, "
strSql = strSql & "T0.I_TYPE, T0.USER_ID, T0.TS_UPDATE, T0.DEL_TO, T0.HOLD_DATE, "
etc. etc. I always end the line with the required space.
My other suggestion would be to use aliases for long table/field references if you want to shorten things, but sometimes that makes it harder to decipher the sql statement.
 
Last edited:
Upvote 0
Thank you

This an SQL statement that has been given to me which i input in toad etc..im trying to automate it through excel vba and unfortunately im struggling putting the SQL STATEMENTs into a strvariable..

Ps
Do i need to wrap a double quote around single quotes?
 
Upvote 0
If it's the only part of a concatenation, yes. "...stuff here'" & someVar & "'"
or "stuff here'" & someVar & "');" but not if it's
"...WHERE tblMyTable.myField = '" & someVar & "' AND..." because this would become
"...WHERE tblMyTable.myField = 'apples' AND..."

If need be, you can use ASCII character codes to insert quotes Chr(39) for single, I think.
 
Last edited:
Upvote 0
Micron said:
If it's the only part of a concatenation, yes. "...stuff here'" & someVar & "'"
or "stuff here'" & someVar & "');" but not if it's
"...WHERE tblMyTable.myField = '" & someVar & "' AND..." because this would become
"...WHERE tblMyTable.myField = 'apples' AND..."

If need be, you can use ASCII character codes to insert quotes Chr(39) for single, I think.
Click to expand...

Thank you for your help

Im sorry but im not understanding what you mean by when to wrap quotes around single quotation marks

If i have a SQL statement like this
" and user_id != 'NINJA01'"

Do i need to wrap double quotes around the single quotation?

and how can i encorporate a variable in the string - this variable holds a number

No_Day = 2

" where to_date(to_char(ts_update,'dd/mm/yyyy'),'dd/mm/yyyy') = to_date(to_char(sysdate,'dd/mm/yyyy'),'dd/mm/yyyy') -" & No_Day & _

Thank You again
 
Upvote 0
I'd try something like this (you'll have to change stuff -- I'm jsut doing a quick and easy example)


take this


Code: 
select 
  firstname,
  lastname,
  email,
  ip_address,
  login_date
  
from
  table_1
where 
(
  (
    lastname like 'PARAM_LASTNAME%'
  )
)


save it as an access passthrough query
set the connection string to your oracle database
name the query
list_user_login_info




then make this vba code


Code: 
'******************************************
sub doit()
  
  dim qdf as querydef
  set qdf = CurrentDB().QueryDefs("list_user_login_info")
  
  dim old_sql as string 
  old_sql =  qdf.sql
  
  dim new_sql as string 
  new_sql = replace(old_sql, PARAM_LASTNAME, "Obama")
  
  qdf.sql = new_sql 
  
  dim rs as RecordSet
  set rs = qdf.OpenRecordset( dbOpenDynamic )


  ' read the rows from the record set
  ' blah blah blah 
  
  rs.close 
  set rs = nothing 
  
  qdf.sql = old_sql 
  
  set qdf = nothing 
  
end sub
'******************************************

I'm sure my vba code is a little incorrect, I'm doing this from memory
 
Upvote 0
james_lankford said:
I'd try something like this (you'll have to change stuff -- I'm jsut doing a quick and easy example)


take this


Code: 
select 
  firstname,
  lastname,
  email,
  ip_address,
  login_date
  
from
  table_1
where 
(
  (
    lastname like 'PARAM_LASTNAME%'
  )
)


save it as an access passthrough query
set the connection string to your oracle database
name the query
list_user_login_info




then make this vba code


Code: 
'******************************************
sub doit()
  
  dim qdf as querydef
  set qdf = CurrentDB().QueryDefs("list_user_login_info")
  
  dim old_sql as string 
  old_sql =  qdf.sql
  
  dim new_sql as string 
  new_sql = replace(old_sql, PARAM_LASTNAME, "Obama")
  
  qdf.sql = new_sql 
  
  dim rs as RecordSet
  set rs = qdf.OpenRecordset( dbOpenDynamic )


  ' read the rows from the record set
  ' blah blah blah 
  
  rs.close 
  set rs = nothing 
  
  qdf.sql = old_sql 
  
  set qdf = nothing 
  
end sub
'******************************************

I'm sure my vba code is a little incorrect, I'm doing this from memory
Click to expand...

H
I appreciate all your help...i dont use access or Sql much and all this is new to me. Can you please help me through this..im a lil familiar with it but what are query defs, pass through access query etc..

Thank you
 
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,427
Messages
6,124,831
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