KB_SQL question for any gurus left.... use of INSERT INTO with a main and subquery
Kimberly Core
Ok… so I’m not sure if it’s not possible or I’m just doing it wrong
Does anyone know if it’s possible to use the INSERT INTO custom table logic to insert rows from the subquery too?
Everything works for the main query, but I get an “0529: Unresolved tag” message… which I’m sorry to say doesn’t mean much to me
I don’t have any columns called TITLE, let alone TTITLE – so that must mean something else.
I thought it could be that the select in the subquery didn’t match the number of columns in the insert of the main query (even though the output matches exactly) – but that wasn’t it… I suppressed stuff and forced them to match, and I still get the same error
Anyone know what this means?
I’m *THIS* close to just sending the whole thing to a file and then inserting the file instead, that will just make the process take longer
Feeling pretty dumb right about now… and just when I thought I had all this stuff figured out after 17 years ;)
Thanks to anyone that’s still around that may know….
Kim
Kimberly Core | Systems Analyst | Transform Shared Service Organization | (519) 352-6401 x6971 | Kimber...@transformsso.ca
CONFIDENTIALITY STATEMENT: This email transmission may contain confidential or legally privileged information that is intended only for the individual or entity named in the email address. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or reliance upon the contents of this email is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return email and delete this message and any attachments from your system.
P Please ~ only print this email if necessary! Reduce, Reuse, Recycle!
Robinson, Jean
We have several queries that call a sub query and then insert a row if needed, return to the main query and then call another subquery to update that same UDF. Do you want me to share the code offline?
If you think you’re too small to make a difference
You haven’t spent the night with a mosquito…
Jean D.V. <><
--
You received this message because you are subscribed to the Google Groups "hisnet" group.
To unsubscribe from this group and stop receiving emails from it, send an email to
hisnet+un...@googlegroups.com.
To post to this group, send email to his...@googlegroups.com.
Visit this group at https://groups.google.com/group/hisnet.
For more options, visit https://groups.google.com/d/optout.
CONFIDENTIALITY NOTICE: This message, including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, contact the sender by reply e-mail or telephone and destroy all copies of the original message.
Peninsula Regional Medical Center has implemented a secure email policy. You may be the recipient of a Cisco Registered Envelope secured message. First-time recipients will be asked to create and confirm a password before given access to the Cisco Registered Envelope Service. If you need assistance in accessing your Secure Message, contact our Service Desk at sup...@peninsula.org or 410-543-7777.
Robinson, Jean
The main query code looks like this….
Kimberly Core
It’s always an insert… and I’m thinking it may have something to do with the fact that the number of columns in the select doesn’t match the number of columns in the table I’m inserting to. The thing is I *NEED* the extra columns in the subquery there’s a lot of conditions etc (multiple fields with their own internal conditions) in oder to OUPTPUT in the detail sectionthe right columns in the right order, and only the exact columns of the insert
My gut tells me it doesn’t like the extra stuff… I but I tried just hding the extra columns and sending literals for the affected fields instead… but it still didn’t work, which blew my theory right out of the water
I’d share the code, but it’s brutal and hard to explain – if it comes to that I will, maybe another pair of eyes will see it immediately.
It’s using a Canadian table for previous account numbers that I don’t think folks in the US use. (basically, if you change the account number, it keeps the old one but doesn’t have a timestamp…. So I have to use logic for “business rules” of the transaction history to help choose the right time from the transactions…. It’s AWFUL)
I may just let it go to a file, then import that file into the custom table instead. Not a great answer but it would work until I figure this out
Kimberly Core | Systems Analyst | Transform Shared Service Organization | (519) 352-6401 x6971 | Kimber...@transformsso.ca
CONFIDENTIALITY STATEMENT: This email transmission may contain confidential or legally privileged information that is intended only for the individual or entity named in the email address. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or reliance upon the contents of this email is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return email and delete this message and any attachments from your system.
P Please ~ only print this email if necessary! Reduce, Reuse, Recycle!
Robinson, Jean
Could you call the insert subquery from the selection subquery? Or are you already doing that?
Robinson, Jean
I do a selection subquery and then in the detail area I call an insertion subquery.
Kimberly Core
I don’t think so it’s too complex and has nested selects of it’s own already
I can post code…. Of main and sub. And you’ll all think I’m crazy for how it is J
Robinson, Jean
Might be a nice brain tease anyway. I think in the end you’re probably better off looking for a simpler way.
Kimberly Core
Yeah, I’m really leaning towards letting it write to the file to the unix box, then import that file into the custom table – will make my life easier… that tag business I have not seen before.
Kimberly Core
So here they are… and I know this is a roundabout way to do this, and may have been better using the logs, but the data needs to go back farther than the logs have data for, so that option was out completely. Best I could come up with was to use business rules – so the subreport doesn’t always give the right stuff when they break the “rules” in registration
As always… open to other ideas. This one SUCKED to do, and is about 98% accurate – only time it’s not is when they break the rules
Thanks for looking….
Regards,
Kim
Main Query:
***************************************************
DECLARE :XAN CHAR(12)
DECLARE :XINTN INT(10)
INSERT INTO bt_casecost_qa_demog_temp_list
(FAC
,AN
,INTN
,PAT_ACCT_NBR
,UNIT_NBR
,BIRTHDATE
,UC_PAT_HEALTHCARD
,GENDER_CODE
,PAT_CITY
,PAT_COUNTRY
,PAT_NAME_FIRST
,PAT_NAME_LAST
,UC_PAT_PROV
,UC_PAT_POST_CD
,DSCHRG_DT_TM
,ADMIT_DT_TM
,ADM_PH_CD
,ATTEND_PHY
,REF_PHY_CD
,PAT_TYPE
,PAT_ACCT_NBR_MASTER
,PAT_TYPE_MAPPED
,FILE_NAME
,REG_STATUS
,CANCEL_STATUS
,CANCEL_STATUS_RSN)
SELECT CASE WHEN a.fac = 'W' THEN 'M' ELSE '' END AS facility
,a.an
,a.intn
,a.acct
,UNITNBR('W',a.intn) AS mrnbr
,b.demog_link@birthdate AS dob
,b.demog_link@uc_pat_healthcard AS hcn
,b.demog_link@sex AS sex
,b.demog_link@pat_city AS city
,b.demog_link@pat_country AS country
,b.demog_link@pat_name_first AS pnamefirst
,b.demog_link@pat_name_last AS pnamelast
,b.demog_link@uc_pat_prov AS province
,b.demog_link@uc_pat_post_cd AS postalcode
,b.med_link@dschrg_dt_tm AS ddttm
,b.med_link@ADM_DT_TM AS ADTTM
,b.visit_link@adm_phy_cd AS admitting
,b.med_link@attend_phy AS attending
,b.visit_link@ref_phy_cd AS referring
,b.med_link@pat_type AS ptype
,b.pat_acct_nbr AS acctmaster
,CASE WHEN ptype IN('555','ACP','APC','BAC','BLC','BPC'
,'CHE','CPR','CRS','DAP','EMP','LAB'
,'NDC','NNC','NRF','PAS','PCC','PCF'
,'PEN','PMB','PMC','POG','PON','PPC'
,'PSS','TEL','TVC','PMD','PNC','SOC') THEN ptype
WHEN ptype = 'HIV' THEN '866'
WHEN ptype = 'AUD' THEN 'AUW'
WHEN ptype IN('CAR','UCC') THEN 'CAM'
WHEN ptype IN('CUC','URO') THEN 'CUC'
WHEN ptype IN('NUT','DBT') THEN 'DEM'
WHEN ptype = 'RAD' THEN 'DIA'
WHEN ptype = 'SWC' THEN 'DPM'
WHEN ptype IN('SPG','DSR') THEN 'DS1'
WHEN ptype = 'PDS' THEN 'DSP'
WHEN ptype = 'END' THEN 'ENM'
WHEN ptype = 'GYN' THEN 'GCM'
WHEN ptype = 'MOA' THEN 'GMM'
WHEN ptype = 'DSL' THEN 'GO'
WHEN ptype = 'MOP' THEN 'GSM'
WHEN ptype = 'OBSP' THEN 'HUB'
WHEN ptype IN('AAC','MPN','BRO','MBL','SLC','RES'
,'MIV','SLP','THO','MPR') THEN 'M4D'
WHEN ptype IN('IVT','BLD','MDC') THEN 'MDM'
WHEN ptype IN('ERP','EMR') THEN 'MED'
WHEN ptype IN('NAT','MAT') THEN 'MNM'
WHEN ptype IN('EOT','WHC','OBT') THEN 'OBT'
WHEN ptype = 'FRA' THEN 'OCM'
WHEN ptype = 'RHO' THEN 'WHC'
WHEN ptype = 'OBM' THEN 'WHM'
WHEN ptype IN('SIP','SOP') THEN 'PAM'
ELSE ptype END AS ptypemapped
,'Demog' as filename
,case when ptype = 'CNA' then 'Cancelled' else '' end as regstatus
,case when ptype = 'CNA' then 'Cancelled-CNA-Cancelled with Orders'
else '' end as cancelstatus
,case when ptype = 'CNA' then 'CNA-' else '' end as cancelreason
FROM bt_casecost_qa_an_intn_temp AS A
,cg_visit_hist AS b
WHERE a.intn = b.intn
AND a.an = b.an
AND b.med_link@pat_type NOT IN('CRS' --cancer centre series
,'ANS','ARS','CCC','HOD','RPR','HIN'
,'TVC','TEL','URO')
ORDER BY a.intn, a.an
GROUP BY a.an
BREAK AT a.an
WRITE 'M'
,a.an
,a.intn
,SUBSTRING(mrnbr,2,9) LEFT 40
,dob FORMAT'MM/DD/YYYY' LEFT 10
,hcn LEFT 14
,sex LEFT 2
,city LEFT 40
,country LEFT 40
,pnamefirst LEFT 40
,pnamelast LEFT 40
,province LEFT 40
,postalcode LEFT 10
,SUBSTRING(b.pat_acct_nbr,2,8) LEFT 13
,TRANSLATE(TO_MOMENT(ddttm,'MM/DD/YYYY','HH:MM24'),'@',' ') LEFT 16
,TRANSLATE(TO_MOMENT(adttm,'MM/DD/YYYY','HH:MM24'),'@',' ') LEFT 16
,CASE WHEN admitting IS NULL THEN NULL
WHEN SUBSTRING(admitting,1,1) = ';' THEN '00897'
ELSE RPAD(admitting,5) END LEFT 5
,CASE WHEN attending IS NULL THEN NULL
WHEN SUBSTRING(attending,1,1) = ';' THEN '00897'
ELSE RPAD(attending,5) END LEFT 5
,CASE WHEN referring IS NULL THEN NULL
WHEN SUBSTRING(referring,1,1) = ';' THEN '00897'
ELSE RPAD(referring,5) END LEFT 5
,ptype
,SUBSTRING(b.med_link@pat_acct_nbr,2,8) LEFT 13
,intn
,an
,ptypemapped LEFT 3
,filename
,regstatus
,cancelstatus
,cancelreason
DETAIL
BREAK AFTER a.an
SET :XINTN = a.intn
SET :XAN = a.an
RUN qb_kwa_casecost_qa_demog_sub
Subquery: qb_kwa_casecost_qa_demog_sub
***************************************************
SET SUBQUERY = y
declare :XAN char(12)
declare :XINTN int(10)
SELECT 'M' AS facility
,a.an AS an
,a.intn AS intn
,a.prev_acct_nbr AS acct
,UNITNBR('W',a.intn) AS mrnbr
,a.demog_link@birthdate AS dob
,a.demog_link@uc_pat_healthcard AS hcn
,a.demog_link@sex AS sex
,a.demog_link@pat_city AS city
,a.demog_link@pat_country AS country
,a.demog_link@pat_name_first AS pnamefirst
,a.demog_link@pat_name_last AS pnamelast
,a.demog_link@uc_pat_prov AS province
,a.demog_link@uc_pat_post_cd AS postalcode
,a.med_link@dschrg_dt_tm AS ddttm
,a.med_link@ADM_DT_TM AS ADTTM
,a.visit_link@adm_phy_cd as admitting
,a.med_link@attend_phy AS attending
,a.visit_link@ref_phy_cd as referring
,a.med_link@pat_type AS ptype
,SUBSTRING(a.pat_acct_nbr,2,8) AS acctmaster
--use this in the next steps
,SUBSTRING(a.prev_acct_nbr,2,1) AS acctprefix
--IPs use the earliest change patient type row and use sys dttm if eff dttm null
,CASE WHEN acctprefix = 1 THEN
(SELECT CASE WHEN MIN(z.eff_date_time) IS NULL THEN
TRANSLATE(TO_MOMENT(MIN(z.system_date_time),'MM/DD/YYYY','HH:MM24'),'@',' ')
ELSE
TRANSLATE(TO_MOMENT(MIN(z.eff_date_time),'MM/DD/YYYY','HH:MM24'),'@',' ')
END
FROM cg_visit_hist AS z
WHERE a.intn = z.intn
AND a.an = z.an
AND z.trans_type_in = 'CPT'
AND z.pat_type IN('CCI','INP','NCU','NEW','OBW','PED'))
ELSE NULL END AS ipadttm
--DaySurg use the earliest reg row and use sys dttm if eff dttm null
,CASE WHEN acctprefix IN(4,5) THEN
(SELECT CASE WHEN MIN(y.eff_date_time) IS NULL THEN
TRANSLATE(TO_MOMENT(MIN(y.system_date_time),'MM/DD/YYYY','HH:MM24'),'@',' ')
ELSE
TRANSLATE(TO_MOMENT(MIN(y.eff_date_time),'MM/DD/YYYY','HH:MM24'),'@',' ')
END
FROM cg_visit_hist AS y
WHERE a.intn = y.intn
AND a.an = y.an
AND y.trans_type_in = 'REG'
AND y.pat_type IN('DSR','END','MPR','PDS','PMD','MPN'))
ELSE NULL END AS dsadttm
--OPs(non DaySurg) use the earliest OP in a bed row and use sys dttm if eff dttm null
,CASE WHEN acctprefix IN(4,5) THEN
(SELECT CASE WHEN MIN(x.eff_date_time) IS NULL THEN
TRANSLATE(TO_MOMENT(MIN(x.system_date_time),'MM/DD/YYYY','HH:MM24'),'@',' ')
ELSE
TRANSLATE(TO_MOMENT(MIN(x.eff_date_time),'MM/DD/YYYY','HH:MM24'),'@',' ')
END
FROM cg_visit_hist AS x
WHERE a.intn = x.intn
AND a.an = x.an
AND x.trans_type_in = 'OPB'
AND x.pat_type IN('APC','BRO','CUC','MDC','MIV','MOP' --4s
,'POG','RHO','SIP','SOC','SOP','SPG','THO' --4s
,'555','AAC','ACP','AUD','BAC','BLC','BPC','CHB','CPG' --5s
,'DBT','FRA','GYN','LAB','MAT','MBL','MCC','MOA','NAT' --5s
,'NDC','NNC','NRF','NUT','OBM','OBT','PAS','PCF','PEN' --5s
,'PMB','PMC','PNC','PON','PPC','PSS','RES','SLC','SLP' --5s
,'SWC','TEL','TVC','URO','WHC')) --5s
ELSE NULL END AS op45adttm
--OPs SIP use the earliest reg row and use sys dttm if eff dttm null
,CASE WHEN acctprefix = 4 THEN
(SELECT CASE WHEN MAX(p.eff_date_time) IS NULL THEN
TRANSLATE(TO_MOMENT(MAX(p.system_date_time),'MM/DD/YYYY','HH:MM24'),'@',' ')
ELSE
TRANSLATE(TO_MOMENT(MAX(p.eff_date_time),'MM/DD/YYYY','HH:MM24'),'@',' ')
END
FROM cg_visit_hist AS p
WHERE a.intn = p.intn
AND a.an = p.an
AND p.trans_type_in = 'REG'
AND p.pat_type IN('SIP'))
ELSE NULL END AS sipadttm
--ER use the earliest reg row and use sys dttm if eff dttm null
,CASE WHEN acctprefix IN(6,7) THEN
(SELECT CASE WHEN w.eff_date_time IS NULL THEN
TRANSLATE(TO_MOMENT(w.system_date_time,'MM/DD/YYYY','HH:MM24'),'@',' ')
ELSE
TRANSLATE(TO_MOMENT(w.eff_date_time,'MM/DD/YYYY','HH:MM24'),'@',' ')
END
FROM cg_visit_hist AS w
WHERE a.intn = w.intn
AND a.an = w.an
AND w.trans_type_in = 'REG'
AND w.pat_type IN('EMP','EMR','EOT','ERP','RER'))
ELSE NULL END AS eradttm
--OP 7s use the earliest reg row and use sys dttm if eff dttm null
,CASE WHEN acctprefix = 7 THEN
(SELECT CASE WHEN v.eff_date_time IS NULL THEN
TRANSLATE(TO_MOMENT(v.system_date_time,'MM/DD/YYYY','HH:MM24'),'@',' ')
ELSE
TRANSLATE(TO_MOMENT(v.eff_date_time,'MM/DD/YYYY','HH:MM24'),'@',' ')
END
FROM cg_visit_hist AS v
WHERE a.intn = v.intn
AND a.an = v.an
AND v.trans_type_in = 'REG'
AND v.pat_type NOT IN('EMP','EMR','EOT','ERP','RER'))
ELSE NULL END AS op7adttm
--IPs use the earliest change patient type row for pt type at the time
,CASE WHEN acctprefix = 1 THEN
(SELECT u.pat_type
FROM cg_visit_hist AS u
WHERE a.intn = u.intn
AND a.an = u.an
AND u.trans_type_in = 'CPT'
AND u.pat_type IN('CCI','INP','NCU','NEW','OBW','PED'))
ELSE NULL END AS ipptype
--DaySurg use the earliest reg row for pt type at the time
,CASE WHEN acctprefix IN(4,5) THEN
(SELECT t.pat_type
FROM cg_visit_hist AS t
WHERE a.intn = t.intn
AND a.an = t.an
AND t.trans_type_in = 'REG'
AND t.pat_type IN('DSR','END','MPR','PDS','PMD','MPN'))
ELSE NULL END AS dsptype
--OPs(non DaySurg) use the earliest OP in a bed row for pt type at the time
,CASE WHEN acctprefix IN(4,5) THEN
(SELECT s.pat_type
FROM cg_visit_hist AS s
WHERE a.intn = s.intn
AND a.an = s.an
AND s.trans_type_in = 'OPB'
AND s.pat_type IN('APC','BRO','CUC','MDC','MIV','MOP' --4s
,'POG','RHO','SIP','SOC','SOP','SPG','THO' --4s
,'555','AAC','ACP','AUD','BAC','BLC','BPC','CHB','CPG' --5s
,'DBT','FRA','GYN','LAB','MAT','MBL','MCC','MOA','NAT' --5s
,'NDC','NNC','NRF','NUT','OBM','OBT','PAS','PCF','PEN' --5s
,'PMB','PMC','PNC','PON','PPC','PSS','RES','SLC','SLP' --5s
,'SWC','TEL','TVC','URO','WHC')) --5s
ELSE NULL END AS op45ptype
--OPs SIP use the earliest reg row for pt type at the time
,CASE WHEN acctprefix = 4 THEN
(SELECT o.pat_type
FROM cg_visit_hist AS o
WHERE a.intn = o.intn
AND a.an = o.an
AND o.trans_type_in = 'REG'
AND o.pat_type IN('SIP'))
ELSE NULL END AS sipptype
--ER use the earliest reg row for pt type at the time
,CASE WHEN acctprefix IN(6,7) THEN
(SELECT r.pat_type
FROM cg_visit_hist AS r
WHERE a.intn = r.intn
AND a.an = r.an
AND r.trans_type_in = 'REG'
AND r.pat_type IN('EMP','EMR','EOT','ERP','RER'))
ELSE NULL END AS erptype
--OP 7s use the earliest reg row for pt type at the time
,CASE WHEN acctprefix = 7 THEN
(SELECT q.pat_type
FROM cg_visit_hist AS q
WHERE a.intn = q.intn
AND a.an = q.an
AND q.trans_type_in = 'REG'
AND q.pat_type NOT IN('EMP','EMR','EOT','ERP','RER'))
ELSE NULL END AS op7ptype
--determine which ptype logic above is the right one to use for this row
,CASE WHEN SIPptype = 'SIP' THEN SIPptype
WHEN acctprefix = 1 AND ipptype IS NOT NULL THEN ipptype
WHEN acctprefix IN(4,5) AND dsptype IS NOT NULL THEN dsptype
WHEN acctprefix IN(4,5) AND op45ptype IS NOT NULL THEN op45ptype
WHEN acctprefix = 4 AND SIPptype IS NOT NULL THEN SIPptype
WHEN acctprefix IN(6,7) AND erptype IS NOT NULL THEN erptype
WHEN acctprefix = 7 AND op7ptype IS NOT NULL THEN op7ptype
ELSE NULL END AS rowptype
--detemine which admit date/time logic above is the right one to use for this row
,CASE WHEN SIPptype = 'SIP' THEN SIPadttm
WHEN acctprefix = 1 AND ipptype IS NOT NULL THEN ipadttm
WHEN acctprefix IN(4,5) AND dsptype IS NOT NULL THEN dsadttm
WHEN acctprefix IN(4,5) AND op45ptype IS NOT NULL THEN op45adttm
WHEN acctprefix = 4 AND SIPptype = 'SIP' THEN SIPadttm
WHEN acctprefix IN(6,7) AND erptype IS NOT NULL THEN eradttm
WHEN acctprefix = 7 AND op7ptype IS NOT NULL THEN op7adttm
ELSE NULL END AS calcadttm
--convert the determined pt type to the mapping they want
,CASE WHEN rowptype IN('555','ACP','APC','BAC','BLC','BPC'
,'CHE','CPR','CRS','DAP','EMP','LAB'
,'NDC','NNC','NRF','PAS','PCC','PCF'
,'PEN','PMB','PMC','POG','PON','PPC'
,'PSS','TEL','TVC','PMD','PNC','SOC')
THEN rowptype
WHEN rowptype = 'HIV' THEN '866'
WHEN rowptype = 'AUD' THEN 'AUW'
WHEN rowptype IN('CAR','UCC') THEN 'CAM'
WHEN rowptype IN('CUC','URO') THEN 'CUC'
WHEN rowptype IN('NUT','DBT') THEN 'DEM'
WHEN rowptype = 'RAD' THEN 'DIA'
WHEN rowptype = 'SWC' THEN 'DPM'
WHEN rowptype IN('SPG','DSR') THEN 'DS1'
WHEN rowptype = 'PDS' THEN 'DSP'
WHEN rowptype = 'END' THEN 'ENM'
WHEN rowptype = 'GYN' THEN 'GCM'
WHEN rowptype = 'MOA' THEN 'GMM'
WHEN rowptype = 'DSL' THEN 'GO'
WHEN rowptype = 'MOP' THEN 'GSM'
WHEN rowptype = 'OBSP' THEN 'HUB'
WHEN rowptype IN('AAC','MPN','BRO','MBL','SLC','RES'
,'MIV','SLP','THO','MPR') THEN 'M4D'
WHEN rowptype IN('IVT','BLD','MDC') THEN 'MDM'
WHEN rowptype IN('ERP','EMR') THEN 'MED'
WHEN rowptype IN('NAT','MAT') THEN 'MNM'
WHEN rowptype IN('EOT','WHC','OBT') THEN 'OBT'
WHEN rowptype = 'FRA' THEN 'OCM'
WHEN rowptype = 'RHO' THEN 'WHC'
WHEN rowptype = 'OBM' THEN 'WHM'
WHEN rowptype IN('SIP','SOP') THEN 'PAM'
ELSE rowptype END AS mappedrowtype
,'Demog-Sub-RolledUpAccount' AS filename
,'RolledUpAccount' AS regstatus
,'' AS cancelstatus
,'' AS cancelreason
FROM ag_prev_acct_nbrs_can AS a
WHERE a.intn = :XINTN
AND a.an = :XAN
AND calcadttm IS NOT NULL
order by pan_ctr
DETAIL
WRITE 'M'
,a.an
,a.intn
,SUBSTRING(mrnbr,2,9)
,dob FORMAT'MM/DD/YYYY' LEFT 10
,hcn LEFT 14
,sex LEFT 2
,city LEFT 40
,country LEFT 40
,pnamefirst LEFT 40
,pnamelast LEFT 40
,province LEFT 40
,postalcode LEFT 10
,SUBSTRING(ACCT,2,8) LEFT 13
,TRANSLATE(TO_MOMENT(ddttm,'MM/DD/YYYY','HH:MM24'),'@',' ') LEFT 16
,calcadttm LEFT 16
,CASE WHEN admitting IS NULL THEN NULL
WHEN SUBSTRING(admitting,1,1) = ';' THEN '00897'
ELSE RPAD(admitting,5) END LEFT 5
,case when attending is null then null
WHEN SUBSTRING(attending,1,1) = ';' THEN '00897'
ELSE RPAD(attending,5) END LEFT 5
,CASE WHEN referring IS NULL THEN NULL
WHEN SUBSTRING(referring,1,1) = ';' THEN '00897'
ELSE RPAD(referring,5) END LEFT 5
,rowptype
,SUBSTRING(a.pat_acct_nbr,2,8) LEFT 13
,mappedrowtype LEFT 3
,filename
,regstatus
,cancelstatus
,cancelreason
Kimberly Core | Systems Analyst | Transform Shared Service Organization | (519) 352-6401 x6971 | Kimber...@transformsso.ca
CONFIDENTIALITY STATEMENT: This email transmission may contain confidential or legally privileged information that is intended only for the individual or entity named in the email address. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or reliance upon the contents of this email is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return email and delete this message and any attachments from your system.
P Please ~ only print this email if necessary! Reduce, Reuse, Recycle!
Robinson, Jean
You go girl!! I’m just glad I didn’t inherit this. Thanks for sharing….
JoAnne Kosiba
,a.intn AS intn
Since these are column names used by HBOC as primary keys, they may not be allowed?
JoAnne Kosiba
Kimberly Core
Absolutely worth a shot thanks I’ll try that. Typically I don’t do it like that, I know it doesn’t like that, especially with some of the “FAC” fields, and have seen it do it with gender too.
Kimberly Core | Systems Analyst | Transform Shared Service Organization | (519) 352-6401 x6971 | Kimber...@transformsso.ca
CONFIDENTIALITY STATEMENT: This email transmission may contain confidential or legally privileged information that is intended only for the individual or entity named in the email address. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or reliance upon the contents of this email is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return email and delete this message and any attachments from your system.
P Please ~ only print this email if necessary! Reduce, Reuse, Recycle!
--
Kimberly Core
Drat, I so thought that was going to work but it didn’t L
Thanks for the suggestion all the same though
Kimberly Core | Systems Analyst | Transform Shared Service Organization | (519) 352-6401 x6971 | Kimber...@transformsso.ca
CONFIDENTIALITY STATEMENT: This email transmission may contain confidential or legally privileged information that is intended only for the individual or entity named in the email address. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or reliance upon the contents of this email is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return email and delete this message and any attachments from your system.
P Please ~ only print this email if necessary! Reduce, Reuse, Recycle!
Kimberly Core
To those that had suggestions… the final answer is that you can’t insert into a custom table from a subquery – I broke down and asked J
Thanks for the suggestions folks, thought I’d share the answer.
Robinson, Jean
If that’s true then we’re breaking the law…
This works just fine for us and has for several years. We insert a blank row and then update it in a different sub query. This is for a specific business reason.
CONFIDENTIALITY NOTICE: This message, including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, contact the sender by reply e-mail or telephone and destroy all copies of the original message.
Kimberly Core
Interesting…. Perhaps they meant more complex stuff. They said the error I was getting was misleading and that wasn’t really what was the matter.
Basically I got the answer that SQL is fussy and limited as to how much you can do once you have an insert type of query.
I suspect the issue with mine was more that it had multiple nested selects in the subquery too – and the logic isn’t at all simple.
Since I start with a table of temp an and intns, I’m thinking I may not need to actually call it from the main query anyway, and that may solve my problem. (just run them both in a batch separately instead of linked as main and sub)
Failing that I plan to just let it export the whole thing to the unix box and then use the host in method to bring it back.
Thanks for the input!
JoAnne Kosiba
Robinson, Jean
I’ve seen that error somewhere. Let me look at my notes and see if I can help.
As… you… wish…..
Jean Robinson x7584
From: his...@googlegroups.com [mailto:his...@googlegroups.com]
On Behalf Of JoAnne Kosiba
Sent: Monday, January 29, 2018 12:14 PM
To: hisnet
Subject: Re: KB_SQL question for any gurus left.... use of INSERT INTO with a main and subquery
WARNING
This message originated outside of Peninsula Regional Medical Center.
PLEASE VERIFY THE SENDER before opening attachments or links.
NEVER provide sensitive information to external requestors unless authorized.
--
You received this message because you are subscribed to the Google Groups "hisnet" group.
To unsubscribe from this group and stop receiving emails from it, send an email to
hisnet+un...@googlegroups.com.
To post to this group, send email to his...@googlegroups.com.
Visit this group at https://groups.google.com/group/hisnet.
For more options, visit https://groups.google.com/d/optout.
CONFIDENTIALITY NOTICE: This message, including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, contact the sender by reply e-mail or telephone and destroy all copies of the original message.
Peninsula Regional Medical Center has implemented a secure email policy. You may be the recipient of a Cisco Registered Envelope secured message. First-time recipients will be asked to create and confirm a password before given access to the Cisco Registered Envelope Service. If you need assistance in accessing your Secure Message, contact our Service Desk at 410-543-7777.
Kimberly Core
That’s an old mail
Kimberly Core | Systems Analyst | Transform Shared Service Organization | (519) 352-6401 x6971 | Kimber...@transformsso.ca
CONFIDENTIALITY STATEMENT: This email transmission may contain confidential or legally privileged information that is intended only for the individual or entity named in the email address. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or reliance upon the contents of this email is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return email and delete this message and any attachments from your system.
P Please ~ only print this email if necessary! Reduce, Reuse, Recycle!