Can not run an sql query on qlik sense data loader

[#1]

stalwar1 need help in running an SQL query, the query runs perfectly fine on my SQL developer but not on my QLIK SENSE  data load. Please who might know what I am doing wrong??

SQL select PROBLEM_ID
       ,CREATE_DATE
       ,TO_CHAR(TO_DATE(ARADMIN.GET_REM_DATE(CREATE_DATE), 'MONTH DD, YYYY HH12:MI:SSAM'), 'YYYY') AS CREATE_YEAR
       ,TO_CHAR(TO_DATE(ARADMIN.GET_REM_DATE(CREATE_DATE), 'MONTH DD, YYYY HH12:MI:SSAM'), 'MON') AS CREATE_MONTH_
       ,TO_CHAR(TO_DATE(ARADMIN.GET_REM_DATE(CREATE_DATE), 'MONTH DD, YYYY HH12:MI:SSAM'), 'MONTH') AS CREATE_MONTH
       ,TO_CHAR(TO_DATE(ARADMIN.GET_REM_DATE(CREATE_DATE), 'MONTH DD, YYYY HH12:MI:SSAM'), 'Mon-YY') AS CREATE_Time
       ,BUSINESS_UNIT
       ,Decode(STATUS ,0,'Unknown Error'
                      ,1,'Work around Provided'
                      ,2,'Root cause Identified'
                      ,3,'Request For Change'
                      ,4,'Closed')  AS STATUS
       ,COUNTRY
       ,WORKING_HOURS
       ,PRIORITY
       ,WORKLOG
       ,SERVICE_OWNER
       ,INCIDENT_COUNT
       ,CLOSED_DATE
       ,TO_CHAR(TO_DATE(ARADMIN.GET_REM_DATE(CLOSED_DATE), 'MONTH DD, YYYY HH12:MI:SSAM'), 'YYYY') AS CLOSED_YEAR
       ,TO_CHAR(TO_DATE(ARADMIN.GET_REM_DATE(CLOSED_DATE), 'MONTH DD, YYYY HH12:MI:SSAM'), 'MON') AS CLOSED_MONTH_
       ,TO_CHAR(TO_DATE(ARADMIN.GET_REM_DATE(CLOSED_DATE), 'MONTH DD, YYYY HH12:MI:SSAM'), 'MONTH') AS CLOSED_MONTH
       ,TO_CHAR(TO_DATE(ARADMIN.GET_REM_DATE(CLOSED_DATE), 'MONTH DD, YYYY HH12:MI:SSAM'), 'Mon-YY') AS CLOSED_Time
       ,SUMMARY
       ,SOLUTION
       ,ROOT_CAUSE_DETAIL
FROM PROBLEM_MANAGEMENT

WHERE to_date(ARADMIN.get_rem_date(CREATE_DATE), 'Month DD, YYYY HH:MI:SSAM') > to_date('01/01/2016', 'MM/DD/YYYY')
  and to_date(ARADMIN.get_rem_date(CREATE_DATE), 'Month DD, YYYY HH:MI:SSAM') < to_date('01/01/2018', 'MM/DD/YYYY')
 
  AND SERVICE_OWNER in ( 'Mark');

[ogster1974]

What error do you get?

[#3 · Accepted solution]

Thanks, but I just resolved it after some hours of troubleshooting. Funny enough, I resolved it as soon as I posted it here. For some weird reason, Qliksense wouldn't load the field WORKLOG.

[ogster1974]

Perhaps treating it as a keyword.  Glad its sorted.

[adarsh1234]

can you please let me how you have solved problem even i am facing same issue with to_char () function

[adarsh1234]

Spoiler

 Not applicable

dear  Not applicable,  

     please let me know how do you had solved the problem even i am facing same problem with to_char() function

here erro,

The following error occurred:

Connector reply error: SQL##f - SqlState: 37000, ErrorCode: 1064, ErrorMsg: [MySQL][ODBC 8.0(a) Driver][mysqld-5.6.10]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'to_char("BNC/POS",'FM999,999,999,999') "BNC/POS", to_char("BNC/WEB",'FM999,999,' at line 2

my query below,

select type as "Gross New Subscribers",
to_char("BNC/POS",'FM999,999,999,999') "BNC/POS",
to_char("BNC/WEB",'FM999,999,999,999') "BNC/WEB",
to_char(MBS,'FM999,999,999,999') mbs,
to_char(TB,'FM999,999,999,999') tb,
to_char(Bartleby,'FM999,999,999,999') bartleby,
to_char(ISNULL("BNC/POS",0)+ISNULL("BNC/WEB",0)+ISNULL(MBS,0)+ISNULL(TB,0)+ISNULL(Bartleby,0),'FM999,999,999,999') Total
from
(
select '0. '||cast(type as char(10)) as type,max("BNC/POS") "BNC/POS",max("BNC/WEB")
"BNC/WEB",max(MBS) MBS,max(TB) TB, max(Bartleby) Bartleby
from
(
select type,
sum(case when Plan_Source_cleaned='POS' then id end) as "BNC/POS",
sum(case when Plan_Source_cleaned in ('WEB','BNC') then id end) as "BNC/WEB",
sum(case when (Plan_Source_cleaned='MBS' or Plan_Source_cleaned = 'MBS attach') then id end) as "MBS",
sum(case when (Plan_Source_cleaned='TB' or Plan_Source_cleaned = 'TB attach') then id end) as "TB",
sum(case when (Plan_Source_cleaned='Bartleby' or Plan_Source_cleaned='Bartleby (promo)') then id end) as "Bartleby"
from
(
select date(getdate()-1) as type,Plan_Source_cleaned,count(distinct user_id) as id
from subscription
where site_id=31
and date(activated) >= date((DATEADD(HOUR, -4, GETDATE()))-1)
and date(activated) < date((DATEADD(HOUR, -4, GETDATE())))
and Plan_Source_cleaned='BNC'
and payment_type not in ('gatewaytoken')
group by 1,2

union

select date(getdate()-1) as type,Plan_Source_cleaned,count(distinct user_id) as id

from subscription
where site_id=31
and date(activated) >= date((DATEADD(HOUR, -4, GETDATE()))-1)
and date(activated) < date((DATEADD(HOUR, -4, GETDATE())))
and Plan_Source_cleaned='Bartleby (promo)'
and plan in ('310050','310060','310070','310080','310090')
group by 1,2

union


select type,
"Plan_Source_cleaned",
count(distinct user_id) as id

from
(
select date(getdate()-1) as type,
substring(invoice_number,6,4),
invoice_number,
case when Plan_Source_cleaned = 'BNC' and substring(invoice_number,6,4) = '7777' then 'WEB'
when Plan_Source_cleaned = 'BNC' and substring(invoice_number,6,4) <> '7777' then 'POS'
when Plan_Source_cleaned <> 'BNC' then Plan_Source_cleaned end as "Plan_Source_cleaned",
user_id

from payment_transaction
where site_id=31
and date("created (America/New_York)") >= date((DATEADD(HOUR, -4, GETDATE()))-1)
and date("created (America/New_York)") < date((DATEADD(HOUR, -4, GETDATE())))
and UniqueSuccessfulpaymentnumber = 1
and status='success'
and action='purchase'
and "Successfulpaymentnotificationnumber"=1
and Plan not in (310070)
)base1
group by 1,2

)
group by type
)
group by type

union all
-----------week to date--------------------------------------------------------------------------

select type,max("BNC/POS") "BNC/POS",max("BNC/WEB") "BNC/WEB",max(MBS) MBS,max(TB) TB,
max(Bartleby) Bartleby

from
(
select type,
sum(case when Plan_Source_cleaned='POS' then id end) as "BNC/POS",
sum(case when Plan_Source_cleaned in ('WEB','BNC') then id end) as "BNC/WEB",
sum(case when (Plan_Source_cleaned='MBS' or Plan_Source_cleaned = 'MBS attach') then id end) as "MBS",
sum(case when (Plan_Source_cleaned='TB' or Plan_Source_cleaned = 'TB attach') then id end) as "TB",
sum(case when (Plan_Source_cleaned='Bartleby' or Plan_Source_cleaned='Bartleby (promo)') then id end) as "Bartleby"
from
(
select '1. '||'Week to Date' as type,
Plan_Source_cleaned,count(distinct user_id) as id
from subscription
where site_id=31
and date(activated) >=DATEADD(DAY, 0- DATEPART(WEEKDAY, date((DATEADD(HOUR, -4, GETDATE())))), CAST(date((DATEADD(HOUR, -4, GETDATE()))) AS DATE))
and date(activated) < date((DATEADD(HOUR, -4, GETDATE())))
and Plan_Source_cleaned='BNC'
and payment_type not in ('gatewaytoken')
group by 1,2

union

select '1. '||'Week to Date' as type,Plan_Source_cleaned,count(distinct user_id) as id
from subscription
where site_id=31
and date(activated) >=DATEADD(DAY, 0- DATEPART(WEEKDAY, date((DATEADD(HOUR, -4, GETDATE())))), CAST(date((DATEADD(HOUR, -4, GETDATE()))) AS DATE))
and date(activated) < date((DATEADD(HOUR, -4, GETDATE())))
and Plan_Source_cleaned='Bartleby (promo)'
and plan in ('310050','310060','310070','310080','310090')
group by 1,2

union

select type,
"Plan_Source_cleaned",
count(distinct user_id) as id

from
(
select '1. '||'Week to Date' as type,
substring(invoice_number,6,4),
invoice_number,
case when Plan_Source_cleaned = 'BNC' and substring(invoice_number,6,4) = '7777' then 'WEB'
when Plan_Source_cleaned = 'BNC' and substring(invoice_number,6,4) <> '7777' then 'POS'
when Plan_Source_cleaned <> 'BNC' then Plan_Source_cleaned end as "Plan_Source_cleaned",
user_id

from payment_transaction
where site_id=31
and date("created (America/New_York)") >= DATEADD(DAY, 0- DATEPART(WEEKDAY, date((DATEADD(HOUR, -4, GETDATE())))), CAST(date((DATEADD(HOUR, -4, GETDATE()))) AS DATE))
and date("created (America/New_York)") < date((DATEADD(HOUR, -4, GETDATE())))
and UniqueSuccessfulpaymentnumber = 1
and status='success'
and action='purchase'
and "Successfulpaymentnotificationnumber"=1
and Plan not in (310070)
)base2
group by 1,2

)
group by type
)
group by type

union all
----------Month to date---------------------------------------------------------------------------

select type,max("BNC/POS") "BNC/POS",max("BNC/WEB") "BNC/WEB",max(MBS) MBS,max(TB) TB,
max(Bartleby) Bartleby

from
(
select type,sum(case when Plan_Source_cleaned='POS' then id end) as "BNC/POS",
sum(case when Plan_Source_cleaned in ('WEB','BNC') then id end) as "BNC/WEB",
sum(case when (Plan_Source_cleaned='MBS' or Plan_Source_cleaned = 'MBS attach') then id end) as "MBS",
sum(case when (Plan_Source_cleaned='TB' or Plan_Source_cleaned = 'TB attach') then id end) as "TB",
sum(case when (Plan_Source_cleaned='Bartleby' or Plan_Source_cleaned='Bartleby (promo)') then id end) as "Bartleby"

from
(
select '2. '||'Month to Date' as type,Plan_Source_cleaned,count(distinct user_id) as id

from subscription
where site_id=31
and date(activated) >= '2019-07-28'
and date(activated) < date((DATEADD(HOUR, -4, GETDATE())))
and Plan_Source_cleaned='BNC'
and payment_type not in ('gatewaytoken')
group by 1,2

union

select '2. '||'Month to Date' as type,Plan_Source_cleaned,count(distinct user_id) as id
from subscription
where site_id=31
and date(activated) >= '2019-07-28'
and date(activated) < date((DATEADD(HOUR, -4, GETDATE())))
and Plan_Source_cleaned='Bartleby (promo)'
and plan in ('310050','310060','310070','310080','310090')
group by 1,2

union

select type,
"Plan_Source_cleaned",
count(distinct user_id) as id
from
(
select '2. '||'Month to Date' as type,
substring(invoice_number,6,4),
invoice_number,
case when Plan_Source_cleaned = 'BNC' and substring(invoice_number,6,4) = '7777' then 'WEB'
when Plan_Source_cleaned = 'BNC' and substring(invoice_number,6,4) <> '7777' then 'POS'
when Plan_Source_cleaned <> 'BNC' then Plan_Source_cleaned end as "Plan_Source_cleaned",
user_id
from payment_transaction
where site_id=31
and date("created (America/New_York)") >= '2019-07-28'
and date("created (America/New_York)") < date((DATEADD(HOUR, -4, GETDATE())))
and UniqueSuccessfulpaymentnumber = 1
and status='success'
and action='purchase'
and "Successfulpaymentnotificationnumber"=1
and Plan not in (310070)
)base3
group by 1,2
)
group by type
)
group by type

union all
-----------Quarter to Date--------------------------------------------------------------------------

select type,max("BNC/POS") "BNC/POS",max("BNC/WEB") "BNC/WEB",max(MBS) MBS,max(TB) TB,
max(Bartleby) Bartleby

from
(
select type,
sum(case when Plan_Source_cleaned='POS' then id end) as "BNC/POS",
sum(case when Plan_Source_cleaned in ('WEB','BNC') then id end) as "BNC/WEB",
sum(case when (Plan_Source_cleaned='MBS' or Plan_Source_cleaned = 'MBS attach') then id end) as "MBS",
sum(case when (Plan_Source_cleaned='TB' or Plan_Source_cleaned = 'TB attach') then id end) as "TB",
sum(case when (Plan_Source_cleaned='Bartleby' or Plan_Source_cleaned='Bartleby (promo)') then id end) as "Bartleby"

from
(
select '3. '||'Quarter to Date' as type,Plan_Source_cleaned,
case when date((DATEADD(HOUR, -4, GETDATE()))) >= '2019-04-28' and date((DATEADD(HOUR, -4, GETDATE()))) < '2019-07-28' then count(distinct user_id)
when date((DATEADD(HOUR, -4, GETDATE()))) >= '2019-07-28' and date((DATEADD(HOUR, -4, GETDATE()))) < '2019-10-28' then count(distinct user_id)
when date((DATEADD(HOUR, -4, GETDATE()))) >= '2018-11-01' and date((DATEADD(HOUR, -4, GETDATE()))) < '2019-02-01' then count(distinct user_id)
when date((DATEADD(HOUR, -4, GETDATE()))) >= '2019-02-01' and date((DATEADD(HOUR, -4, GETDATE()))) < '2019-05-01' then count(distinct user_id) else 0 end as id
from subscription
where site_id=31 and
date(activated) >= '2019-07-28'
and date(activated) < date((DATEADD(HOUR, -4, GETDATE())))
and Plan_Source_cleaned='BNC'
and payment_type not in ('gatewaytoken')
group by 1,2

union

select '3. '||'Quarter to Date' as type,Plan_Source_cleaned,
case when date((DATEADD(HOUR, -4, GETDATE()))) >= '2019-04-28' and date((DATEADD(HOUR, -4, GETDATE()))) < '2019-07-28' then count(distinct user_id)
when date((DATEADD(HOUR, -4, GETDATE()))) >= '2019-07-28' and date((DATEADD(HOUR, -4, GETDATE()))) < '2019-10-28' then count(distinct user_id)
when date((DATEADD(HOUR, -4, GETDATE()))) >= '2018-11-01' and date((DATEADD(HOUR, -4, GETDATE()))) < '2019-02-01' then count(distinct user_id)
when date((DATEADD(HOUR, -4, GETDATE()))) >= '2019-02-01' and date((DATEADD(HOUR, -4, GETDATE()))) < '2019-05-01' then count(distinct user_id) else 0 end as id
from subscription
where site_id=31 and
date(activated) >= '2019-07-28'
and date(activated) < date((DATEADD(HOUR, -4, GETDATE())))
and Plan_Source_cleaned='Bartleby (promo)'
and plan in ('310050','310060','310070','310080','310090')
group by 1,2

union

select type,Plan_Source_cleaned,
case when date((DATEADD(HOUR, -4, GETDATE()))) >= '2019-04-28' and date((DATEADD(HOUR, -4, GETDATE()))) < '2019-07-28' then count(distinct user_id)
when date((DATEADD(HOUR, -4, GETDATE()))) >= '2019-07-28' and date((DATEADD(HOUR, -4, GETDATE()))) < '2019-10-28' then count(distinct user_id)
when date((DATEADD(HOUR, -4, GETDATE()))) >= '2018-11-01' and date((DATEADD(HOUR, -4, GETDATE()))) < '2019-02-01' then count(distinct user_id)
when date((DATEADD(HOUR, -4, GETDATE()))) >= '2019-02-01' and date((DATEADD(HOUR, -4, GETDATE()))) < '2019-05-01' then count(distinct user_id) else 0 end as id
from
(
select '3. '||'Quarter to Date' as type,
substring(invoice_number,6,4),
invoice_number,
case when Plan_Source_cleaned = 'BNC' and substring(invoice_number,6,4) = '7777' then 'WEB'
when Plan_Source_cleaned = 'BNC' and substring(invoice_number,6,4) <> '7777' then 'POS'
when Plan_Source_cleaned <> 'BNC' then Plan_Source_cleaned end as "Plan_Source_cleaned",
user_id

from payment_transaction
where site_id=31
and date("created (America/New_York)") >= '2019-07-28'
and date("created (America/New_York)") < date((DATEADD(HOUR, -4, GETDATE())))
and UniqueSuccessfulpaymentnumber = 1
and status='success'
and action='purchase'
and "Successfulpaymentnotificationnumber"=1
and Plan not in (310070)
)base4
group by 1,2

)
group by type
)
group by type

union all
----------Fiscal Year to Date--------------------------------------------------------------------------

select type,max("BNC/POS") "BNC/POS",max("BNC/WEB") "BNC/WEB",max(MBS) MBS,max(TB) TB,
max(Bartleby) Bartleby

from
(
select type,
sum(case when Plan_Source_cleaned='POS' then id end) as "BNC/POS",
sum(case when Plan_Source_cleaned in ('WEB','BNC') then id end) as "BNC/WEB",
sum(case when (Plan_Source_cleaned='MBS' or Plan_Source_cleaned = 'MBS attach') then id end) as "MBS",
sum(case when (Plan_Source_cleaned='TB' or Plan_Source_cleaned = 'TB attach') then id end) as "TB",
sum(case when (Plan_Source_cleaned='Bartleby' or Plan_Source_cleaned='Bartleby (promo)') then id end) as "Bartleby"

from
(
select '4. '||'Fiscal Year to Date' as type,Plan_Source_cleaned,count(distinct user_id) as id
from subscription
where site_id=31 and
date("activated") >= '2019-04-28'
and date("activated") < date((DATEADD(HOUR, -4, GETDATE())))
and Plan_Source_cleaned='BNC'
and payment_type not in ('gatewaytoken')
group by 1,2

union

select '4. '||'Fiscal Year to Date' as type,Plan_Source_cleaned,count(distinct user_id) as id

from subscription
where site_id=31 and
date("activated") >= '2019-04-28'
and date("activated") < date((DATEADD(HOUR, -4, GETDATE())))
and Plan_Source_cleaned='Bartleby (promo)'
and plan in ('310050','310060','310070','310080','310090')
group by 1,2

union
select type,
"Plan_Source_cleaned",
count(distinct user_id) as id

from
(
select '4. '||'Fiscal Year to Date' as type,
substring(invoice_number,6,4),
invoice_number,
case when Plan_Source_cleaned = 'BNC' and substring(invoice_number,6,4) = '7777' then 'WEB'
when Plan_Source_cleaned = 'BNC' and substring(invoice_number,6,4) <> '7777' then 'POS'
when Plan_Source_cleaned <> 'BNC' then Plan_Source_cleaned end as "Plan_Source_cleaned",
user_id

from payment_transaction
where site_id=31
and date("created (America/New_York)") >= '2019-04-28'
and date("created (America/New_York)") < date((DATEADD(HOUR, -4, GETDATE())))
and UniqueSuccessfulpaymentnumber = 1
and status='success'
and action='purchase'
and "Successfulpaymentnotificationnumber"=1
and Plan not in (310070)
)base5
group by 1,2

)
group by type
)
group by type

)
order by type