Another nested SQL question

I am trying to get a nested SQL statement to work in my main SQL report code below. I can successfully run the nested code by itself and the main code by itself, however, I am having some trouble getting them to work together. [Note: the chart_components table and the episodes tables can be linked via the episode_key field ]

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

--Begin nested query

(SELECT et1.episode_key, MAX(et1.status_date)

FROM srm.chart_components et1, srm.chart_components et2

WHERE et1.episode_key = et2.episode_key

AND et1.chart_component_ke = et2.chart_component_ke

AND et1.deficiency_type = et2.deficiency_type

AND et1.deficiency_status = et2.deficiency_status

AND et1.status_date = et2.status_date AND et2.deficiency_status = 'C'

GROUP by et1.episode_key

HAVING COUNT(et1.episode_key) = COUNT(et2.episode_key)) AS Chart_Comp_Date

-- End nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

See the much simpler version of the query that gets the Chart_Comp_Date from my reply in your other thread. If you want just the date in the SELECT list then you need to select only that column. You are selecting the episode_key and the date. This will raise errors. So fix it like:

Code Snippet

-- Begin nested query:

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c1.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

|||

Thanks. The code below reflects your code snippet. However, I am receiving the following error when I run it.

"The multi-part identifier "c.deficiency_status" could not be bound."

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

-- begin nested query

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

--end nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

|||

I had a mistake in the alias inside the case expression. Change c.deficiency_status to c1.deficiency_status.

|||

I just noticed that I get the same error is I run your code snippet (see below) just by itself.

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

|||

I made the change above and and now receiving this error.

Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Warning: Null value is eliminated by an aggregate or other SET operation.

My code follows:

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

--begin nested query

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c1.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

-- end nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

|||

You have no correlation between the query in the SELECT list and the tables in the FROM clause. You need to reference the EPISODE_KEY from one of the outer tables also like below. Otherwise, you will get errors depending on the data.

Code Snippet

(select max()

...

where c1.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

having count(*) .... ) as Chart_Comp_Date

|||

Sometimes you can look at a problem for too long and not see the answer right in front of you. I can't believe I missed this! Thank you Umachandar for all your help with this query. I appreciate it.

|||

My thanks to Umachandar, Arnie and Shawn for their help on this problem. Below is the code I ended up using in the event someone else finds themselves in a similar situation.

select max(c1.status_date)as Chart_Comp_Dt,
c1.chart_component_ke,
c2.episode_type as Visit_Type,
c1.deficiency_type,
c1.deficiency_status,
c1.episode_key,
c2.account_number as Account_No,
c2.medrec_no as MRN,
c2.episode_date as Disch_Date,
c4.patientname as Patient_Name,
MAX(c3.event_date) as ABSCOMPDT
from srm.chart_components c1, srm.episodes c2, srm.event_history c3, dbo.PtMstr c4
where c1.EPISODE_KEY = c2.EPISODE_KEY
and c2.EPISODE_KEY = c3.ITEM_KEY
and c2.ACCOUNT_NUMBER = c4.accountnumber
and c2.episode_date between @.StartDate and @.EndDate
and c2.episode_type IN(@.visittype)
group by c1.episode_key,c1.chart_component_ke,c1.deficiency_type,
c1.deficiency_status,c1.episode_key,c2.account_number,c2.episode_type,
c2.medrec_no,c2.episode_date,c4.patientname
having (c2.episode_date < max(c1.status_date)) and
count(*) = sum(case c1.deficiency_status when 'C' then 1 end)
order by c2.episode_date desc

Another nested SQL question

I am trying to get a nested SQL statement to work in my main SQL report code below. I can successfully run the nested code by itself and the main code by itself, however, I am having some trouble getting them to work together. [Note: the chart_components table and the episodes tables can be linked via the episode_key field ]

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

--Begin nested query

(SELECT et1.episode_key, MAX(et1.status_date)

FROM srm.chart_components et1, srm.chart_components et2

WHERE et1.episode_key = et2.episode_key

AND et1.chart_component_ke = et2.chart_component_ke

AND et1.deficiency_type = et2.deficiency_type

AND et1.deficiency_status = et2.deficiency_status

AND et1.status_date = et2.status_date AND et2.deficiency_status = 'C'

GROUP by et1.episode_key

HAVING COUNT(et1.episode_key) = COUNT(et2.episode_key)) AS Chart_Comp_Date

-- End nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

See the much simpler version of the query that gets the Chart_Comp_Date from my reply in your other thread. If you want just the date in the SELECT list then you need to select only that column. You are selecting the episode_key and the date. This will raise errors. So fix it like:

Code Snippet

-- Begin nested query:

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c1.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

|||

Thanks. The code below reflects your code snippet. However, I am receiving the following error when I run it.

"The multi-part identifier "c.deficiency_status" could not be bound."

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

-- begin nested query

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

--end nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

|||

I had a mistake in the alias inside the case expression. Change c.deficiency_status to c1.deficiency_status.

|||

I just noticed that I get the same error is I run your code snippet (see below) just by itself.

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

|||

I made the change above and and now receiving this error.

Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Warning: Null value is eliminated by an aggregate or other SET operation.

My code follows:

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

--begin nested query

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c1.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

-- end nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

|||

You have no correlation between the query in the SELECT list and the tables in the FROM clause. You need to reference the EPISODE_KEY from one of the outer tables also like below. Otherwise, you will get errors depending on the data.

Code Snippet

(select max()

...

where c1.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

having count(*) .... ) as Chart_Comp_Date

|||

Sometimes you can look at a problem for too long and not see the answer right in front of you. I can't believe I missed this! Thank you Umachandar for all your help with this query. I appreciate it.

|||

My thanks to Umachandar, Arnie and Shawn for their help on this problem. Below is the code I ended up using in the event someone else finds themselves in a similar situation.

select max(c1.status_date)as Chart_Comp_Dt,
c1.chart_component_ke,
c2.episode_type as Visit_Type,
c1.deficiency_type,
c1.deficiency_status,
c1.episode_key,
c2.account_number as Account_No,
c2.medrec_no as MRN,
c2.episode_date as Disch_Date,
c4.patientname as Patient_Name,
MAX(c3.event_date) as ABSCOMPDT
from srm.chart_components c1, srm.episodes c2, srm.event_history c3, dbo.PtMstr c4
where c1.EPISODE_KEY = c2.EPISODE_KEY
and c2.EPISODE_KEY = c3.ITEM_KEY
and c2.ACCOUNT_NUMBER = c4.accountnumber
and c2.episode_date between @.StartDate and @.EndDate
and c2.episode_type IN(@.visittype)
group by c1.episode_key,c1.chart_component_ke,c1.deficiency_type,
c1.deficiency_status,c1.episode_key,c2.account_number,c2.episode_type,
c2.medrec_no,c2.episode_date,c4.patientname
having (c2.episode_date < max(c1.status_date)) and
count(*) = sum(case c1.deficiency_status when 'C' then 1 end)
order by c2.episode_date desc

Another nested SQL question

I am trying to get a nested SQL statement to work in my main SQL report code below. I can successfully run the nested code by itself and the main code by itself, however, I am having some trouble getting them to work together. [Note: the chart_components table and the episodes tables can be linked via the episode_key field ]

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

--Begin nested query

(SELECT et1.episode_key, MAX(et1.status_date)

FROM srm.chart_components et1, srm.chart_components et2

WHERE et1.episode_key = et2.episode_key

AND et1.chart_component_ke = et2.chart_component_ke

AND et1.deficiency_type = et2.deficiency_type

AND et1.deficiency_status = et2.deficiency_status

AND et1.status_date = et2.status_date AND et2.deficiency_status = 'C'

GROUP by et1.episode_key

HAVING COUNT(et1.episode_key) = COUNT(et2.episode_key)) AS Chart_Comp_Date

-- End nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

See the much simpler version of the query that gets the Chart_Comp_Date from my reply in your other thread. If you want just the date in the SELECT list then you need to select only that column. You are selecting the episode_key and the date. This will raise errors. So fix it like:

Code Snippet

-- Begin nested query:

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c1.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

|||

Thanks. The code below reflects your code snippet. However, I am receiving the following error when I run it.

"The multi-part identifier "c.deficiency_status" could not be bound."

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

-- begin nested query

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

--end nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

|||

I had a mistake in the alias inside the case expression. Change c.deficiency_status to c1.deficiency_status.

|||

I just noticed that I get the same error is I run your code snippet (see below) just by itself.

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

|||

I made the change above and and now receiving this error.

Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Warning: Null value is eliminated by an aggregate or other SET operation.

My code follows:

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

--begin nested query

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c1.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

-- end nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

|||

You have no correlation between the query in the SELECT list and the tables in the FROM clause. You need to reference the EPISODE_KEY from one of the outer tables also like below. Otherwise, you will get errors depending on the data.

Code Snippet

(select max()

...

where c1.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

having count(*) .... ) as Chart_Comp_Date

|||

Sometimes you can look at a problem for too long and not see the answer right in front of you. I can't believe I missed this! Thank you Umachandar for all your help with this query. I appreciate it.

|||

My thanks to Umachandar, Arnie and Shawn for their help on this problem. Below is the code I ended up using in the event someone else finds themselves in a similar situation.

select max(c1.status_date)as Chart_Comp_Dt,
c1.chart_component_ke,
c2.episode_type as Visit_Type,
c1.deficiency_type,
c1.deficiency_status,
c1.episode_key,
c2.account_number as Account_No,
c2.medrec_no as MRN,
c2.episode_date as Disch_Date,
c4.patientname as Patient_Name,
MAX(c3.event_date) as ABSCOMPDT
from srm.chart_components c1, srm.episodes c2, srm.event_history c3, dbo.PtMstr c4
where c1.EPISODE_KEY = c2.EPISODE_KEY
and c2.EPISODE_KEY = c3.ITEM_KEY
and c2.ACCOUNT_NUMBER = c4.accountnumber
and c2.episode_date between @.StartDate and @.EndDate
and c2.episode_type IN(@.visittype)
group by c1.episode_key,c1.chart_component_ke,c1.deficiency_type,
c1.deficiency_status,c1.episode_key,c2.account_number,c2.episode_type,
c2.medrec_no,c2.episode_date,c4.patientname
having (c2.episode_date < max(c1.status_date)) and
count(*) = sum(case c1.deficiency_status when 'C' then 1 end)
order by c2.episode_date desc

Another GUID problem... What is wrong with this code ?

Hi,
I have the following code:
Dim RowLoopIndex As Integer
DsFuncties.Clear()
A_Functies.Fill(DsFuncties)
For RowLoopIndex = 0 To (DsFuncties.Tables("Functies").Rows.Count - 1)
If DsFuncties.Tables("Functies").Rows(RowLoopIndex).Item("FunctieID") = Func_ID Then
Func_naam = (DsFuncties.Tables("Functies").Rows(RowLoopIndex).Item("Functienaam"))
DDL_Functie.SelectedIndex = RowLoopIndex + 1
Exit For
End If
Next
Func_ID has been declared as follows:
Public Property Func_ID() As Guid
Get
If Not viewstate("Func_ID") Is Nothing Then
Return viewstate("Func_ID")
End If
End Get
Set(ByVal Value As Guid)
viewstate("Func_ID") = Value
End Set
End Property

On the red line I got the following tooltip (error):
Operator '=' is not defined for types 'System.Object' and 'System.Guid'.
How can I define '=' and eg. '&' for type System.Guid ?? Or another solution ??
Help is appreciated, Ger.This is the solution:

If DsFuncties.Tables("Functies").Rows(RowLoopIndex).Item("FunctieID").ToString() = Func_ID.ToString() Then
Hope this helps others too...
regards, Ger.

|||The underlying issue with that exception is you're validating two separate types.
By converting the type System.Object to System.GUID, it would be easier to work with.

Func_ID().Equals(CType(DsFuncties.Tables("Functies").Rows(RowLoopIndex).Item("FunctieID"), GUID))
That would be a more appropriate method than to cast both into strings then evaluate.
|||Hey KraGiE, thanks for the advice.
With your help I become a rather good programmer, (I hope).
regards from the North Sea.

Another Date Question

There is no "Last business day of the month" option in SQL
Server job schedular. What is the best way to achive
this ?. Is there any code out there to get this ?
Thanks.
Todd wrote:
> There is no "Last business day of the month" option in SQL
> Server job schedular. What is the best way to achive
> this ?. Is there any code out there to get this ?
> Thanks.
Calculate the first day of the next month by adding a month to the
current month, using Day 1, and using the year as it returned from the
add of the month. Then dateadd a -1 day and you are on the last day of
the current month. In a loop, check the day of the week using
datepart(dw,...) and if Sat or Sun keep subtracting one day until you
find the correct day.
A calendar table would also be useful here as it could be used to hold
holidays as well.
David Gugick
Imceda Software
www.imceda.com
|||Are holidays considered a business day?
Jeff
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:0ed701c4bab8$33015290$a501280a@.phx.gbl...
> There is no "Last business day of the month" option in SQL
> Server job schedular. What is the best way to achive
> this ?. Is there any code out there to get this ?
> Thanks.
|||http://www.aspfaq.com/show.asp?id=2519
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:0ed701c4bab8$33015290$a501280a@.phx.gbl...
> There is no "Last business day of the month" option in SQL
> Server job schedular. What is the best way to achive
> this ?. Is there any code out there to get this ?
> Thanks.

Another Date Question

There is no "Last business day of the month" option in SQL
Server job schedular. What is the best way to achive
this '. Is there any code out there to get this '
Thanks.Todd wrote:
> There is no "Last business day of the month" option in SQL
> Server job schedular. What is the best way to achive
> this '. Is there any code out there to get this '
> Thanks.
Calculate the first day of the next month by adding a month to the
current month, using Day 1, and using the year as it returned from the
add of the month. Then dateadd a -1 day and you are on the last day of
the current month. In a loop, check the day of the week using
datepart(dw,...) and if Sat or Sun keep subtracting one day until you
find the correct day.
A calendar table would also be useful here as it could be used to hold
holidays as well.
David Gugick
Imceda Software
www.imceda.com|||Are holidays considered a business day?
Jeff
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:0ed701c4bab8$33015290$a501280a@.phx.gbl...
> There is no "Last business day of the month" option in SQL
> Server job schedular. What is the best way to achive
> this '. Is there any code out there to get this '
> Thanks.|||http://www.aspfaq.com/show.asp?id=2519
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:0ed701c4bab8$33015290$a501280a@.phx.gbl...
> There is no "Last business day of the month" option in SQL
> Server job schedular. What is the best way to achive
> this '. Is there any code out there to get this '
> Thanks.

Another Date Question

There is no "Last business day of the month" option in SQL
Server job schedular. What is the best way to achive
this '. Is there any code out there to get this '
Thanks.Todd wrote:
> There is no "Last business day of the month" option in SQL
> Server job schedular. What is the best way to achive
> this '. Is there any code out there to get this '
> Thanks.
Calculate the first day of the next month by adding a month to the
current month, using Day 1, and using the year as it returned from the
add of the month. Then dateadd a -1 day and you are on the last day of
the current month. In a loop, check the day of the week using
datepart(dw,...) and if Sat or Sun keep subtracting one day until you
find the correct day.
A calendar table would also be useful here as it could be used to hold
holidays as well.
David Gugick
Imceda Software
www.imceda.com|||Are holidays considered a business day?
Jeff
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:0ed701c4bab8$33015290$a501280a@.phx.gbl...
> There is no "Last business day of the month" option in SQL
> Server job schedular. What is the best way to achive
> this '. Is there any code out there to get this '
> Thanks.|||http://www.aspfaq.com/show.asp?id=2519
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:0ed701c4bab8$33015290$a501280a@.phx.gbl...
> There is no "Last business day of the month" option in SQL
> Server job schedular. What is the best way to achive
> this '. Is there any code out there to get this '
> Thanks.

Annoying, cant insert into DB for some reason, even using a stored procedure.

Hello, I am having problems inserting information into my DB.

First is the code for the insert

Sub AddCollector(Sender As Object, E As EventArgs)
 Message.InnerHtml = ""
 If (Page.IsValid)
 Dim ConnectionString As String = "server='(local)'; trusted_connection=true; database='MyCollection'"
 Dim myConnection As New SqlConnection(ConnectionString)
 Dim myCommand As SqlCommand
 Dim InsertCmd As String = "insert into Collectors (CollectorID, Name, EmailAddress, Password, Information) values (@.CollectorID, @.Name, @.Email, @.Password, @.Information)"
 myCommand = New SqlCommand(InsertCmd, myConnection)
 myCommand.Connection.Open()
 myCommand.Parameters.Add(New SqlParameter("@.CollectorID", SqlDbType.NVarChar, 50))
 myCommand.Parameters("@.CollectorID").Value = CollectorID.Text
 myCommand.Parameters.Add(New SqlParameter("@.Name", SqlDbType.NVarChar, 50))
 myCommand.Parameters("@.Name").Value = Name.Text
 myCommand.Parameters.Add(New SqlParameter("@.Email", SqlDbType.NVarChar, 50))
 myCommand.Parameters("@.Email").Value = EmailAddress.Text
 myCommand.Parameters.Add(New SqlParameter("@.Password", SqlDbType.NVarChar, 50))
 myCommand.Parameters("@.Password").Value = Password.Text
 myCommand.Parameters.Add(New SqlParameter("@.Information", SqlDbType.NVarChar, 3000))
 myCommand.Parameters("@.Information").Value = Information.Text
 Try
 myCommand.ExecuteNonQuery()
 Message.InnerHtml = "Record Added<br>"
 Catch Exp As SQLException
 If Exp.Number = 2627
 Message.InnerHtml = "ERROR: A record already exists with the same primary key"
 Else
 Message.InnerHtml = "ERROR: Could not add record"
 End If
 Message.Style("color") = "red"
 End Try
 myCommand.Connection.Close()
 End If
End Sub

No matter what I get a "Could not add record" message

Even substituting the insert command string with my stored procedure I would get the same thing

Stored Procedure:

CREATE Procedure CollectorAdd
 (
 @.Name nvarchar(50),
 @.Email nvarchar(50),
 @.Password nvarchar(50),
 @.Information nvarchar(3000),
 @.CustomerID int OUTPUT
 )
 AS
 INSERT Collectors
 (
 Name,
 EMailAddress,
 Password,
 Information
 )
 VALUES
 (
 @.Name,
 @.Email,
 @.Password,
 @.Information
 )
GO

Can anyone see any problems with this code? It looks good to me but I get the same message always.

ThanksWhy not print out the actual exception text (exp.ToString(), for instance)? Then the exception will tell you why.|||Wow, nice little trick. It helped me find the problem. I had an expected parameter in the SP that I was not supplying.

Thank You

Analyzer 2000 using 2005 database gives error

I am using Query Analyzer 2000 and pointing at a 2005 database. I keep
getting an error that I think it related to some code that is selecting data
into a temp table. The procedure runs perfect on a 2000 database.
here is the message
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
Any ideas?
Thank you
RichardAnd the procedure? Can we see it?
ML
http://milambda.blogspot.com/|||CREATE PROCEDURE arLedgerListingGenAP
@.ProcMode CHOICE = NULL, -- 'R'- Report Mode, ' ' or
NULL - Customer Care Mode
@.CustomerKey SMARTKEY,
@.StartDate DATETIME = NULL,
@.EndDate DATETIME = NULL,
@.cStartDate VARCHAR(40),
@.cEndDate VARCHAR(40),
@.gsUserLanguage LANG
AS
SET NOCOUNT ON
SET ANSI_NULLS OFF
DECLARE @.BeginingBalance AMOUNT2DEC,
@.EndingBalance AMOUNT2DEC
IF @.StartDate IS NULL OR LTRIM(RTRIM(@.cStartDate)) = ''
SELECT @.StartDate = "1900-01-01 00:00:00"
IF @.EndDate IS NULL OR LTRIM(RTRIM(@.cEndDate)) = ''
SELECT @.EndDate = CAST(CAST(YEAR(@.EndDate) as varchar) + CASE
WHEN
LEN(MONTH(@.EndDate)) = 1
THEN
'0' + CAST(MONTH(@.EndDate) AS varchar)
ELSE
CAST(MONTH(@.EndDate) AS varchar)
END + CASE
WHEN LEN(DAY(@.EndDate)) = 1
THEN '0' + CAST(DAY(@.EndDate) AS varchar)
ELSE CAST(DAY(@.EndDate) AS varchar)
END
+ ' 23:59:59' AS datetime)
IF @.cStartDate IS NOT NULL AND LTRIM(RTRIM(@.cStartDate)) <> ''
SELECT @.StartDate = @.cStartDate
IF @.cEndDate IS NOT NULL AND LTRIM(RTRIM(@.cEndDate)) <> ''
SELECT @.EndDate = CAST(CAST(YEAR(@.cEndDate) as varchar) + CASE
WHEN
LEN(MONTH(@.cEndDate)) = 1
THEN
'0' + CAST(MONTH(@.cEndDate) AS varchar)
ELSE
CAST(MONTH(@.cEndDate) AS varchar)
END + CASE
WHEN LEN(DAY(@.cEndDate)) = 1
THEN '0' + CAST(DAY(@.cEndDate) AS varchar)
ELSE CAST(DAY(@.cEndDate) AS varchar)
END
+ ' 23:59:59' AS datetime)
-- The temp tables used in the Ledger Listind Stored procedures.
-- i.e. arLedgerListingGenAP.sql.
CREATE TABLE #Activity
(ActivityKey SMARTKEY IDENTITY(1,1),
ActivityDate DATETIME,
Amount AMOUNT2DEC NULL,
Discount AMOUNT2DEC NULL,
InvoiceNo INVOICENO NULL,
ContractKey SMARTKEY NULL,
CreditInvoiceNo INVOICENO NULL,
TranType VARCHAR(8) NULL,
ShortDesc CHAR(6) NULL,
ActDesc MAXCHAR NULL,
TransDescription MAXCHAR NULL,
InvoiceTotal AMOUNT2DEC NULL,
InvoiceBalance AMOUNT2DEC NULL,
AccountBalance AMOUNT2DEC NULL,
EmployeeCode CHAR(3) NULL,
PaymentReference DESCRIPTION NULL,
RecordType CHAR(5) NULL,
Target INVOICENO NULL,
TransactionGroupNo INTEGER NULL,
TransactionType VARCHAR(40) NULL,
AmtAffectingBal AMOUNT2DEC NULL,
PaymentApplicationKey SMARTKEY NULL,
InvoiceType CHOICE NULL,
ReversalReference CHOICE NULL,
DatePaid DATETIME NULL,
InvoiceTerms DESCRIPTION NULL,
TermsCode CHAR(3) NULL,
DueDate DATETIME NULL,
PastDueDays INTEGER NULL,
ForeColor INTEGER NULL,
CustomerKey SMARTKEY,
OwnerKey SMARTKEY NULL,
PaymentOrInvoice CHAR(1) NULL,
InvoiceDate DATEONLY NULL,
SortinvoiceNo INVOICENO NULL,
TranFrom CHOICE NULL)
CREATE TABLE #TempActivity (ActivityKey SMARTKEY,
InvoiceNo INVOICENO,
TransactionType VARCHAR(40),
TrxCnt INTEGER,
TransDescription MAXCHAR)
/* CreditInvoiceNo INVOICENO,
Amount AMOUNT2DEC,
Total AMOUNT2DEC,
AppliedCredits AMOUNT2DEC,
CreationDate DATETIME,*/
-- Find all Invoice Transaction for the customer. We have to go to the line
item to find out what was done, Refunds, Returns, etc.
-- Get also the pending credits and their Status.
/*
INSERT INTO #TempActivity EXEC arLedgerListingGen1AP
@.CustomerKey = @.CustomerKey,
@.StartDate = @.StartDate,
@.EndDate = @.EndDate,
@.gsUserLanguage = @.gsUserLanguage
*/
-- Store all Transaction for the customer in a temp table.
INSERT INTO #Activity EXEC arLedgerListingGen2AP
@.CustomerKey = @.CustomerKey,
@.StartDate = @.StartDate,
@.EndDate = @.EndDate,
@.gsUserLanguage = @.gsUserLanguage
--IF EXISTS (SELECT * FROM #Activity WHERE ShortDesc LIKE '%MT' OR
ShortDesc = 'DM')
BEGIN
EXEC arLedgerListingGen1AP
@.CustomerKey = @.CustomerKey,
@.StartDate = @.StartDate,
@.EndDate = @.EndDate,
@.gsUserLanguage = @.gsUserLanguage
UPDATE X
SET TransactionType = Y.TransactionType,
TransDescription = Y.TransDescription,
ShortDesc = LTRIM(RTRIM(ShortDesc)) + 'MT'
FROM #Activity X
JOIN #TempActivity Y ON X.InvoiceNo = Y.InvoiceNo
END
EXEC arLedgerListGetBegBalGenAP @.CustomerKey, @.StartDate,
@.BeginingBalance OUTPUT
IF @.BeginingBalance IS NULL
SELECT @.BeginingBalance = 0
EXEC arLedgerListGetEndBalGenAP @.CustomerKey, @.EndDate, @.EndingBalance
OUTPUT
IF @.EndingBalance IS NULL
SELECT @.EndingBalance = 0
-- Get the Customerkey, RecordType and Contract key of the record that was
transferred IN/OUT, get running balance per Account and
-- per Invoice and Update Balances.
SELECT a.ActivityKey, a.ActivityDate, a.Amount, a.Discount, a.InvoiceNo,
a.ContractKey, a.CreditInvoiceNo,
a.TranType, ShortDesc = LEFT(a.ShortDesc,5), a.ActDesc,
a.TransDescription, a.InvoiceTotal,
InvoiceBalance = CASE
WHEN a.InvoiceNo NOT IN ('CREDIT',
'ACCCRD', 'ACCDEP', 'SECDEP','PREPAY')
THEN a.AmtAffectingBal + (SELECT
ISNULL(SUM(c.AmtAffectingBal), 0.00)
FROM
#Activity c
WHERE
c.ActivityKey < a.ActivityKey
AND
a.InvoiceNo = c.InvoiceNo)
ELSE 0.00
END,
AccountBalance = a.AmtAffectingBal + @.BeginingBalance + (SELECT
ISNULL(SUM(c.AmtAffectingBal),0.00)
FROM
#Activity c
WHERE
c.ActivityKey < a.ActivityKey),
a.EmployeeCode, a.PaymentReference, a.RecordType, a.Target,
a.TransactionGroupNo,
a.TransactionType, a.AmtAffectingBal, a.PaymentApplicationKey,
a.InvoiceType, a.ReversalReference,
a.DatePaid, a.InvoiceTerms, a.TermsCode, a.DueDate,
a.PastDueDays, a.ForeColor, a.CustomerKey, a.OwnerKey,
TransferCustomerKey = b.CustomerKey, TransferRecordType =
b.RecordType, TransferContractKey = b.ContractKey,
a.PaymentOrInvoice, a.InvoiceDate, a.SortinvoiceNo, a.TranFrom
INTO #Activity2
FROM #Activity a
LEFT JOIN arPaymentApplications b ON
a.TransactionGroupNo = b.TransactionGroupNo
AND
LEFT(a.RecordType, 1) = 'X'
--This record contains
the sum of all PREPAY adjustments for all customers
AND a.RecordType <>
'XIADP'
--XOADP is a Transfer
Out of Access Deposit for PREPAY
--In this case the
CustomerKey's must match.
AND a.CustomerKey =
CASE
WHEN a.RecordType = 'XOADP'
THEN a.CustomerKey
END
--Else any other X
RecordTypes are transfers of Security Deposits
--The CustomerKey's do
not match here because we are picking up who
--the moneies are
transfered to.
AND a.CustomerKey <>
CASE
WHEN a.RecordType <> 'XOADP'
THEN b.CustomerKey
END
-- get the Descriptions.
IF @.ProcMode IS NULL OR @.ProcMode = ' '
BEGIN
EXEC arLedgerListingGen3AP
@.BeginingBalance = @.BeginingBalance,
@.EndingBalance = @.EndingBalance,
@.gsUserLanguage = @.gsUserLanguage
END
ELSE
BEGIN
EXEC arLedgerListingGen4AP
@.BeginingBalance = @.BeginingBalance,
@.EndingBalance = @.EndingBalance,
@.gsUserLanguage = @.gsUserLanguage
END
GO
"ML" <ML@.discussions.microsoft.com> wrote in message
news:8899258C-9E33-4876-B54F-408943B42727@.microsoft.com...
> And the procedure? Can we see it?
>
> ML
> --
> http://milambda.blogspot.com/|||After going through the code there's one issue that stands out - your
INSERT...EXECUTE statements lack column declarations. Try fixing that, e.g.
insert <table>
(
<column list>
)
exec <procedure> <parameter list>
Make certain that the columns in the INSERT statement match the columns in
the result set of the procedure.
ML
http://milambda.blogspot.com/

Analysis Services connection using AMO

Hi,

I have AS2005 running on a development machine, and i am trying to connect to it via my application running on another machine.

The code is using the Analysis Mangement Object (AMO) API.

Currently, my application code only tries to connect and disconnect from the server.

Server svr = new Server();

svr.Connect(connectionString); < Exception thrown here

svr.Disconnect();

The connectionString simply contains the remote server's name. I have also tried different connection strings but it has not helped.

I have also enabled remote connections on AS, and opened the default port number on the server's firewall.

Every time it tries to connect the exception thrown is:

"Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host."

Can anybody help me with this problem?

I'm not sure if this will help, but I was getting a similar error with

the BI Development Studio. In that case it was a security

issue. Basically, becasue the machine was on a different domain,

I had to create a user on that machine with the same username /

password as the one that I log into my machine with. Have you

tried connecting in any other way?|||

Is the "other machine" an AS2K5 instance?

_-_-_ Dave