Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Can not run an sql query on qlik sense data loader

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');

1 Solution

Accepted Solutions
Not applicable
Author

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.

View solution in original post

5 Replies
ogster1974
Partner - Master II
Partner - Master II

What error do you get?

Not applicable
Author

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
Partner - Master II
Partner - Master II

Perhaps treating it as a keyword.  Glad its sorted.

adarsh1234
Partner - Contributor
Partner - Contributor

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

adarsh1234
Partner - Contributor
Partner - Contributor

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