Pass-thru Union help needed

[mdjks]

I need to rearrange a table that has values stored in 12 fields, one for each month. Below is the union query that I wrote, it does give me what I'm looking for but errors if I make it a pass-thru. Running as a non-pass-thru takes FOREVER!

I need to be able to perform the concatenation on the first field or create a column that just contains the 01 or 02 value and I can combine them later.

SELECT

PATIENT_ACTG_J_ACCOUNT_MSTR.fSCL_YR & "01" AS AcctPeriod,
PATIENT_ACTG_J_ACCOUNT_MSTR.GL_DPT,
PATIENT_ACTG_J_ACCOUNT_MSTR.SUBACCT,
PATIENT_ACTG_J_ACCOUNT_MSTR.ACT_PER_01 as DollarValue


FROM
PATIENT_ACTG_J_ACCOUNT_MSTR

Union Select


PATIENT_ACTG_J_ACCOUNT_MSTR.fSCL_YR & "02" AS AcctPeriod,
PATIENT_ACTG_J_ACCOUNT_MSTR.GL_DPT,
PATIENT_ACTG_J_ACCOUNT_MSTR.SUBACCT,
PATIENT_ACTG_J_ACCOUNT_MSTR.ACT_PER_02 as DollarValue

FROM
PATIENT_ACTG_J_ACCOUNT_MSTR;

This code runs and gives me the values I am looking for. It will need further manipulation since the hospital code is actually the first two digits of the GL_DPT code. I'd like instruction on how to put that into a pass-thru.


Any suggestions for running as a pass-thru?

Access 2003 in 2000 format

 

[Newman]

You could create two pass-thru queries and then a union query that uses the previous two.

qry1 : Pass-thru query with the "01" value
qry2 : Pass-thru query with the "02" value
qry3 : qry1 UNION qry2

 

[pbaldy]

What's the error? Pass thru to what? SQL Server uses + as a concatenation operator rather than &.

 

[mdjks]

I corrected & to + and the error I get is:

[Knowledge Based Systems, Inc.][server API][v4.3]SQLPrepare: Invalid Syntax: expecting FOR READ ONLY or FOR UPDATE (#39)

SELECT

PATIENT_ACTG.J_ACCOUNT_MSTR.fSCL_YR + '01' AS AcctPeriod,
PATIENT_ACTG.J_ACCOUNT_MSTR.GL_DPT,
PATIENT_ACTG.J_ACCOUNT_MSTR.SUBACCT,
PATIENT_ACTG.J_ACCOUNT_MSTR.ACT_PER_01 as DollarValue


FROM
PATIENT_ACTG.J_ACCOUNT_MSTR

UNION SELECT

PATIENT_ACTG.J_ACCOUNT_MSTR.fSCL_YR + '02' AS AcctPeriod,
PATIENT_ACTG.J_ACCOUNT_MSTR.GL_DPT,
PATIENT_ACTG.J_ACCOUNT_MSTR.SUBACCT,
PATIENT_ACTG.J_ACCOUNT_MSTR.ACT_PER_02 as DollarValue


FROM
PATIENT_ACTG.J_ACCOUNT_MSTR;

 

[mdjks]

You could create two pass-thru queries and then a union query that uses the previous two.

qry1 : Pass-thru query with the "01" value
qry2 : Pass-thru query with the "02" value
qry3 : qry1 UNION qry2

I did do this but with only 2 out of 12 months it took a huge amount of time (>30 min). I'm afraid if I add the other 10 months it will have to run over-night.

 

[pbaldy]

What program is storing the data? It looks like a syntax problem, but the correct syntax would depend on the program.

 

[mdjks]

The data is stored in a propriatary system called Knowledge Base. I don't know much about the system and this is just a short contract to automate one report.

I have a work-around which includes gathering the Historical data and then going forward the customer will only need to append current month's information.

This is tedious for me but I think I can make it fairly smooth for them.

Thanks for your help. I have also given the problem to the client's dba for the system. She is going to look at creating a view but isn't sure it will be possible.