SQL Select Issues

jeckstein — Mon, 23 Jan 2017 02:28:51 GMT

I have a SQL select that looks like this

Main:
load distinct *
;
SQL SELECT

d.DocumentID

,d.PatientID

,d.DepartmentID

,d.ProviderUsername

,p.ProviderFirstName + ' ' + p.ProviderLastName AS ReferredFrom

,p.ProviderNPINumber AS ReferringNPI

,p.ProviderType

,p.ProviderTypeCategory

,cp.ClinicalProviderID

,cp.Name

,cp.NPI AS ReferredToNPI

,d.ClinicalEncounterID

,d.DocumentClass

,d.ClinicalOrderType

,d.ClinicalOrderGenus

,d.[Status]

,d.OrderDatetime

,sfp.chapter

,sfp.pod

,sfp.specialty

,sfp.hospital_affiliation

,CASE WHEN sfp1.NPI IS NULL AND (sfp1.ntwk_term_dt IS NULL OR sfp1.ntwk_term_dt >= d.CreatedDatetime) THEN 'Yes' ELSE 'No' END AS 'In Network'

FROM [Athena_DW].[Athena].[Document] d

INNER JOIN Athena_DW.Athena.[Provider] p ON d.ProviderUsername = p.ProviderUserName

INNER JOIN Athena_DW.Athena.ClinicalProvider cp ON d.ClinicalProviderID = cp.ClinicalProviderID

LEFT OUTER JOIN sandbox.dbo.vAllProviders sfp ON p.ProviderNPINumber = sfp.NPI and sfp.prov_ty='PCP'--Referring Provider
LEFT OUTER JOIN sandbox.dbo.vAllProviders sfp1 ON convert(varchar(30),cp.NPI) = convert(varchar(30),sfp1.NPI) --Referred to Provider --deleted from sfp1, wasn't complete code, wonder if that was needed
   where documentClass='ORDER'

   and ClinicalOrderTypeGroup='CONSULT'

   and p.RowIsCurrent='Y'

   AND d.RowIsCurrent = 'Y'

   AND cp.RowIsCurrent = 'Y'

   AND d.DeletedBy IS NULL

   AND p.DeletedBy IS NULL

   and cp.DeletedDatetime IS NULL

   and Status <> 'DELETED'

   AND ClinicalOrderType LIKE '%REFER%'

   AND d.OrderDatetime > '20160101'

   AND cp.DuplicateOfClinicalProvID IS NULL
;

Anything I put after the last ; is causing an error. Even If I just add a blank new tab. Any ideas??

Re: SQL Select Issues

girirajsinh — Mon, 23 Jan 2017 08:24:36 GMT

first thing I notice is

,CASE WHEN sfp1.NPI IS NULL AND (sfp1.ntwk_term_dt IS NULL OR sfp1.ntwk_term_dt >= d.CreatedDatetime) THEN 'Yes' ELSE 'No' END AS 'In Network'

use "In Network" instead of 'In Network'

Re: SQL Select Issues

marcus_sommer — Mon, 23 Jan 2017 11:58:38 GMT

You get a sql-error or a qlik load-error? Which one?

- Marcus

topic SQL Select Issues in QlikView

SQL Select Issues

Re: SQL Select Issues

Re: SQL Select Issues