I have been handed the task of merging two scrips so that we can display notes on the results of the first query. I know this might be a long shot but if someone could help that would be great.
First Query being the bulk of the data which was given to me from an EXCEL query.
SELECT hgmprty1.la_rep_cd AS 'Area',
hraptvpd.prty_ref,
hraptvpd.init_void_dt AS 'Void Date',
hraptvpd.void_reas,
hgmprty1.let_yn,
hgmprty1.void_stat AS 'Property void code',
hraptvpd.init_void_cd AS 'Period initial void code',
hraptvpd.cur_void_cd AS 'Period current void code',
hraptvps.void_cd AS 'phase void code',
hraptvps.st_date,
hraptvps.end_date,
hgmprty2.sh_size_desn,
hgmprty1.need_cat,
hgmprty3.status,
hraptvpd.void_reas,
hraptvps.lst_revn,
hgmprty1.dwelling,
hraptvps.delay_cd_reas
FROM qlfdat.dbo.hgmprty1 hgmprty1,
qlfdat.dbo.hgmprty2 hgmprty2,
qlfdat.dbo.hgmprty3 hgmprty3,
qlfdat.dbo.hraptvpd hraptvpd,
qlfdat.dbo.hraptvps hraptvps
WHERE hgmprty1.comp_id = hraptvpd.comp_id
AND hraptvpd.prty_ref = hgmprty1.prty_id
AND hgmprty2.comp_id = hgmprty1.comp_id
AND hgmprty2.comp_id = hraptvpd.comp_id
AND hgmprty2.prty_id = hgmprty1.prty_id
AND hgmprty3.comp_id = hgmprty1.comp_id
AND hgmprty3.comp_id = hgmprty2.comp_id
AND hgmprty3.comp_id = hraptvpd.comp_id
AND hgmprty3.prty_id = hgmprty1.prty_id
AND hgmprty3.prty_id = hgmprty2.prty_id
AND hraptvps.comp_id = hgmprty1.comp_id
AND hraptvps.comp_id = hgmprty2.comp_id
AND hraptvps.comp_id = hgmprty3.comp_id
AND hraptvps.comp_id = hraptvpd.comp_id
AND hraptvps.prty_ref = hraptvpd.prty_ref
AND hraptvpd.seq_no = hraptvps.vd_prd
AND ((hgmprty1.dwelling='1')
AND (hraptvpd.status='3')
OR (hgmprty1.dwelling='2')
AND (hraptvpd.status='3')
OR (hgmprty1.dwelling='4')
AND (hraptvpd.status='3'))
ORDER BY hgmprty1.la_rep_cd
Second Query was written by someone else but could possibly be done a ot better.
select prty_ref,void_cd,a2.void_desr,a2.note, n1.Notes from hraptvps
left join
(SELECT
note_id, ql_dt, Note1 + data1 + data2 + data3 + data4 + data5 + data6 + data7 + data8 + data9 + data10 + data11 + data12 + data14 AS Notes
FROM (SELECT
c1.note_id, c1.ql_dt, LEFT(c1.note_detail, 251) AS Note1, CASE WHEN c2.data1 IS NULL THEN ' ' ELSE c2.data1 END AS data1,
CASE WHEN c3.data2 IS NULL THEN ' ' ELSE c3.data2 END AS data2, CASE WHEN c4.data3 IS NULL THEN ' ' ELSE c4.data3 END AS data3,
CASE WHEN c5.data4 IS NULL THEN ' ' ELSE c5.data4 END AS data4, CASE WHEN c6.data5 IS NULL THEN ' ' ELSE c6.data5 END AS data5,
CASE WHEN c7.data6 IS NULL THEN ' ' ELSE c7.data6 END AS data6, CASE WHEN c8.data7 IS NULL THEN ' ' ELSE c8.data7 END AS data7,
CASE WHEN c9.data8 IS NULL THEN ' ' ELSE c9.data8 END AS data8, CASE WHEN c10.data9 IS NULL THEN ' ' ELSE c10.data9 END AS data9,
CASE WHEN c11.data10 IS NULL THEN ' ' ELSE c11.data10 END AS data10, CASE WHEN c12.data11 IS NULL THEN ' ' ELSE c12.data11 END AS data11,
CASE WHEN c13.data12 IS NULL THEN ' ' ELSE c13.data12 END AS data12, CASE WHEN c14.data13 IS NULL THEN ' ' ELSE c14.data13 END AS data13,
CASE WHEN c15.data14 IS NULL THEN ' ' ELSE c15.data14 END AS data14
FROM dbo.cmpnote AS c1 LEFT OUTER JOIN
(SELECT note_id, segm, LEFT(data, 251) AS Data1
FROM dbo.ocmpnote
WHERE (segm = 0001)) AS c2 ON c1.note_id = c2.note_id LEFT OUTER JOIN
(SELECT note_id, segm, LEFT(data, 251) AS Data2
FROM dbo.ocmpnote AS ocmpnote_13
WHERE (segm = 0002)) AS c3 ON c1.note_id = c3.note_id LEFT OUTER JOIN
(SELECT note_id, segm, LEFT(data, 251) AS Data3
FROM dbo.ocmpnote AS ocmpnote_12
WHERE (segm = 0003)) AS c4 ON c1.note_id = c4.note_id LEFT OUTER JOIN
(SELECT note_id, segm, LEFT(data, 251) AS Data4
FROM dbo.ocmpnote AS ocmpnote_11
WHERE (segm = 0004)) AS c5 ON c1.note_id = c5.note_id LEFT OUTER JOIN
(SELECT note_id, segm, LEFT(data, 251) AS Data5
FROM dbo.ocmpnote AS ocmpnote_10
WHERE (segm = 0005)) AS c6 ON c1.note_id = c6.note_id LEFT OUTER JOIN
(SELECT note_id, segm, LEFT(data, 251) AS Data6
FROM dbo.ocmpnote AS ocmpnote_9
WHERE (segm = 0006)) AS c7 ON c1.note_id = c7.note_id LEFT OUTER JOIN
(SELECT note_id, segm, LEFT(data, 251) AS Data7
FROM dbo.ocmpnote AS ocmpnote_8
WHERE (segm = 0007)) AS c8 ON c1.note_id = c8.note_id LEFT OUTER JOIN
(SELECT note_id, segm, LEFT(data, 251) AS Data8
FROM dbo.ocmpnote AS ocmpnote_7
WHERE (segm = 0008)) AS c9 ON c1.note_id = c9.note_id LEFT OUTER JOIN
(SELECT note_id, segm, LEFT(data, 251) AS Data9
FROM dbo.ocmpnote AS ocmpnote_6
WHERE (segm = 0009)) AS c10 ON c1.note_id = c10.note_id LEFT OUTER JOIN
(SELECT note_id, segm, LEFT(data, 251) AS Data10
FROM dbo.ocmpnote AS ocmpnote_5
WHERE (segm = 0010)) AS c11 ON c1.note_id = c10.note_id LEFT OUTER JOIN
(SELECT note_id, segm, LEFT(data, 251) AS Data11
FROM dbo.ocmpnote AS ocmpnote_4
WHERE (segm = 0011)) AS c12 ON c1.note_id = c12.note_id LEFT OUTER JOIN
(SELECT note_id, segm, LEFT(data, 251) AS Data12
FROM dbo.ocmpnote AS ocmpnote_3
WHERE (segm = 0012)) AS c13 ON c1.note_id = c13.note_id LEFT OUTER JOIN
(SELECT note_id, segm, LEFT(data, 251) AS Data13
FROM dbo.ocmpnote AS ocmpnote_2
WHERE (segm = 0013)) AS c14 ON c1.note_id = c14.note_id LEFT OUTER JOIN
(SELECT note_id, segm, LEFT(data, 251) AS Data14
FROM dbo.ocmpnote AS ocmpnote_1
WHERE (segm = 0014)) AS c15 ON c1.note_id = c15.note_id) as n1) as n1
on a2.note=n1.note_id
where n1.notes is not null
If somesome could either get both queries to work as one or help me re-write it that would be great. Ive been struggling for a good week.
Advertisement
Answer
If you can give us some Querys to generate some data and tables I can debug this further. Try to comment out most of the stuff not directly necessary for the relation between the 2 querys.
Select * from (
SELECT hgmprty1.la_rep_cd AS 'Area',
hraptvpd.prty_ref as 'prty_ref',
hraptvpd.init_void_dt AS 'Void Date',
hraptvpd.void_reas as 'void_reas',
hgmprty1.let_yn as 'let_yn',
hgmprty1.void_stat AS 'Property void code',
hraptvpd.init_void_cd AS 'Period initial void code',
hraptvpd.cur_void_cd AS 'Period current void code',
hraptvps.void_cd AS 'phase void code'
--,
--hraptvps.st_date,
--hraptvps.end_date,
--hgmprty2.sh_size_desn,
--hgmprty1.need_cat,
--hgmprty3.status,
--hraptvpd.void_reas,
--hraptvps.lst_revn,
--hgmprty1.dwelling,
--hraptvps.delay_cd_reas
FROM qlfdat.dbo.hgmprty1 hgmprty1,
qlfdat.dbo.hgmprty2 hgmprty2,
qlfdat.dbo.hgmprty3 hgmprty3,
qlfdat.dbo.hraptvpd hraptvpd,
qlfdat.dbo.hraptvps hraptvps
WHERE hgmprty1.comp_id = hraptvpd.comp_id
AND hraptvpd.prty_ref = hgmprty1.prty_id
AND hgmprty2.comp_id = hgmprty1.comp_id
AND hgmprty2.comp_id = hraptvpd.comp_id
AND hgmprty2.prty_id = hgmprty1.prty_id
AND hgmprty3.comp_id = hgmprty1.comp_id
AND hgmprty3.comp_id = hgmprty2.comp_id
AND hgmprty3.comp_id = hraptvpd.comp_id
AND hgmprty3.prty_id = hgmprty1.prty_id
AND hgmprty3.prty_id = hgmprty2.prty_id
AND hraptvps.comp_id = hgmprty1.comp_id
AND hraptvps.comp_id = hgmprty2.comp_id
AND hraptvps.comp_id = hgmprty3.comp_id
AND hraptvps.comp_id = hraptvpd.comp_id
AND hraptvps.prty_ref = hraptvpd.prty_ref
AND hraptvpd.seq_no = hraptvps.vd_prd
AND ((hgmprty1.dwelling='1')
AND (hraptvpd.status='3')
OR (hgmprty1.dwelling='2')
AND (hraptvpd.status='3')
OR (hgmprty1.dwelling='4')
AND (hraptvpd.status='3'))
ORDER BY hgmprty1.la_rep_cd
)Qry1
LEFT JOIN
(
select a2.prty_ref as 'prty_ref', --,a2.void_cd , a2.void_desr,a2.note,
n1.Notes
from hraptvps a2
left join
(SELECT
note_id, ql_dt, Note1 + data1 + data2 + data3 + data4 + data5 + data6 + data7 + data8 + data9 + data10 + data11 + data12 + data14 AS Notes
FROM (SELECT
c1.note_id, c1.ql_dt, LEFT(c1.note_detail, 251) AS Note1, CASE WHEN c2.data1 IS NULL THEN ' ' ELSE c2.data1 END AS data1,
CASE WHEN c3.data2 IS NULL THEN ' ' ELSE c3.data2 END AS data2, CASE WHEN c4.data3 IS NULL THEN ' ' ELSE c4.data3 END AS data3,
CASE WHEN c5.data4 IS NULL THEN ' ' ELSE c5.data4 END AS data4, CASE WHEN c6.data5 IS NULL THEN ' ' ELSE c6.data5 END AS data5,
CASE WHEN c7.data6 IS NULL THEN ' ' ELSE c7.data6 END AS data6, CASE WHEN c8.data7 IS NULL THEN ' ' ELSE c8.data7 END AS data7,
CASE WHEN c9.data8 IS NULL THEN ' ' ELSE c9.data8 END AS data8, CASE WHEN c10.data9 IS NULL THEN ' ' ELSE c10.data9 END AS data9,
CASE WHEN c11.data10 IS NULL THEN ' ' ELSE c11.data10 END AS data10, CASE WHEN c12.data11 IS NULL THEN ' ' ELSE c12.data11 END AS data11,
CASE WHEN c13.data12 IS NULL THEN ' ' ELSE c13.data12 END AS data12, CASE WHEN c14.data13 IS NULL THEN ' ' ELSE c14.data13 END AS data13,
CASE WHEN c15.data14 IS NULL THEN ' ' ELSE c15.data14 END AS data14
FROM dbo.cmpnote AS c1
LEFT OUTER JOIN
(SELECT note_id, segm, LEFT(data, 251) AS Data1
FROM dbo.ocmpnote
WHERE (segm = 0001)
) AS c2 ON c1.note_id = c2.note_id
LEFT OUTER JOIN
(SELECT note_id, segm, LEFT(data, 251) AS Data2
FROM dbo.ocmpnote AS ocmpnote_13
WHERE (segm = 0002)) AS c3 ON c1.note_id = c3.note_id
LEFT OUTER JOIN
(SELECT note_id, segm, LEFT(data, 251) AS Data3
FROM dbo.ocmpnote AS ocmpnote_12
WHERE (segm = 0003)) AS c4 ON c1.note_id = c4.note_id
LEFT OUTER JOIN
(SELECT note_id, segm, LEFT(data, 251) AS Data4
FROM dbo.ocmpnote AS ocmpnote_11
WHERE (segm = 0004)) AS c5 ON c1.note_id = c5.note_id
LEFT OUTER JOIN
(SELECT note_id, segm, LEFT(data, 251) AS Data5
FROM dbo.ocmpnote AS ocmpnote_10
WHERE (segm = 0005)) AS c6 ON c1.note_id = c6.note_id
LEFT OUTER JOIN
(SELECT note_id, segm, LEFT(data, 251) AS Data6
FROM dbo.ocmpnote AS ocmpnote_9
WHERE (segm = 0006)) AS c7 ON c1.note_id = c7.note_id
LEFT OUTER JOIN
(SELECT note_id, segm, LEFT(data, 251) AS Data7
FROM dbo.ocmpnote AS ocmpnote_8
WHERE (segm = 0007)) AS c8 ON c1.note_id = c8.note_id
LEFT OUTER JOIN
(SELECT note_id, segm, LEFT(data, 251) AS Data8
FROM dbo.ocmpnote AS ocmpnote_7
WHERE (segm = 0008)) AS c9 ON c1.note_id = c9.note_id
LEFT OUTER JOIN
(SELECT note_id, segm, LEFT(data, 251) AS Data9
FROM dbo.ocmpnote AS ocmpnote_6
WHERE (segm = 0009)) AS c10 ON c1.note_id = c10.note_id
LEFT OUTER JOIN
(SELECT note_id, segm, LEFT(data, 251) AS Data10
FROM dbo.ocmpnote AS ocmpnote_5
WHERE (segm = 0010)) AS c11 ON c1.note_id = c10.note_id
LEFT OUTER JOIN
(SELECT note_id, segm, LEFT(data, 251) AS Data11
FROM dbo.ocmpnote AS ocmpnote_4
WHERE (segm = 0011)) AS c12 ON c1.note_id = c12.note_id
LEFT OUTER JOIN
(SELECT note_id, segm, LEFT(data, 251) AS Data12
FROM dbo.ocmpnote AS ocmpnote_3
WHERE (segm = 0012)) AS c13 ON c1.note_id = c13.note_id
LEFT OUTER JOIN
(SELECT note_id, segm, LEFT(data, 251) AS Data13
FROM dbo.ocmpnote AS ocmpnote_2
WHERE (segm = 0013)) AS c14 ON c1.note_id = c14.note_id
LEFT OUTER JOIN
(SELECT note_id, segm, LEFT(data, 251) AS Data14
FROM dbo.ocmpnote AS ocmpnote_1
WHERE (segm = 0014)) AS c15 ON c1.note_id = c15.note_id
)
as n1) as n1 on a2.Note =n1.note_id
where n1.notes is not null
) Qry2 on Qry1.prty_ref = Qry2.prty_ref