SQL Side Gig

[MDFF2008]

I'm looking to hire someone for an hour or two to help me touch up a few SQL queries I've written for work. You'd have to meet me due to the login issues, but I'd be willing to pay you for your time.

 

[whistlersmother]

I'm looking to hire someone for an hour or two to help me touch up a few SQL queries I've written for work. You'd have to meet me due to the login issues, but I'd be willing to pay you for your time.

Left outer join, group by having?

Which DBMS?

 

[MDFF2008]

Mostly left outer join.

Peoplesoft Campus Solutions

 

[mac1_131]

Select *

You can pay me via PayPal

 

[drking2]

pm me, I could give you a hand. Where are you located?

 

[MDFF2008]

Is anyone else available to help with this? I really need some help.

 

[DraKhen99]

Where are you located?

If it's standard T-SQL, the DBMS doesn't really matter. I know each has their own eccentricities, but it's still just SQL ATEOTD.

-John

 

[MDFF2008]

Where are you located?

If it's standard T-SQL, the DBMS doesn't really matter. I know each has their own eccentricities, but it's still just SQL ATEOTD.

-John

Towson, but willing to travel to get this shit fixed.

 

[DraKhen99]

Towson, but willing to travel to get this shit fixed.

If it's not executing or you can explain what you want it to do, send it to me, I'll take a look see.

-John

 

[MDFF2008]

If it's not executing or you can explain what you want it to do, send it to me, I'll take a look see.

-John

Basically, the problem is it's returning two entries per person, one is their major, and one their minor, when it's only supposed to return 1 per person.

I've tried enabling the unique check box, but I'm pretty sure it's an issue with the fact that their are two records for each student.

That's why you might need actually see how it's configured, which I'd have to be there to let you log in.

I would be happy to drive though.

 

[DraKhen99]

Basically, the problem is it's returning two entries per person, one is their major, and one their minor, when it's only supposed to return 1 per person.

I've tried enabling the unique check box, but I'm pretty sure it's an issue with the fact that their are two records for each student.

That's why you might need actually see how it's configured, which I'd have to be there to let you log in.

I would be happy to drive though.

OK, have you tried using the DISTINCT keyword in your SQL statement?

-John

 

[Keystone70]

OK, have you tried using the DISTINCT keyword in your SQL statement?

-John

Distinct could work as long as major and minor are not in the same field and present in the select statement. Distinct is the way to go. You just have to make sure you don't include any fields in your select statement that could have different values for the same person


Sent from my XT1254 using Tapatalk

 

[DraKhen99]

Distinct could work as long as major and minor are not in the same field and present in the select statement. Distinct is the way to go. You just have to make sure you don't include any fields in your select statement that could have different values for the same person


Sent from my XT1254 using Tapatalk

True.

Also, IIRC, every other field will have to be in an aggregate (in the GROUP BY clause or MAX(), MIN(), etc.)

-John

 

[Keystone70]

True.

Also, IIRC, every other field will have to be in an aggregate (in the GROUP BY clause or MAX(), MIN(), etc.)

-John

Also correct if he is using any aggregate functions. I didn't see that in his statement. Really need to see the actual SQL script

Sent from my XT1254 using Tapatalk

 

[steveh326]

my first thoughts would be try "fetch first row only" or "select distinct" ?

 

[MDFF2008]

SELECT A.EMPLID, B.EMAIL_ADDR, C.FIRST_NAME, C.MIDDLE_NAME, C.LAST_NAME, C.NAME_SUFFIX, case A.ACAD_LEVEL_BOT
when '00' then 'Non-Degree'
when '10' then 'Freshman'
when '20' then 'Sophomore'
when '30' then 'Junior'
when '40' then 'Senior'
when '50' then 'Post Bachelor'
when 'GR' then 'Graduate'
else 'unknown' END, G.VISA_PERMIT_TYPE, MAX( case when M.ADDRESS_TYPE ='CAMP' then 'On-Campus'
when ( M.ADDRESS_TYPE ='LOCL' and M.POSTAL = '21252') then 'On-Campus'
else 'Off-Campus'
end), case
when L.PROG_REASON like 'F%' then 'Native'
when L.PROG_REASON like 'T%' then 'Transfer'
when L.PROG_REASON like 'U%' then 'Post-Bachelor'
when L.PROG_REASON like 'NOND' then 'Non-Degree'
else L.PROG_REASON
end, C.SEX, A.CUM_GPA, F.ACAD_PROG, MIN( case when ( H.PLAN_SEQUENCE <11 and O.PERCENT_OWNED > 50) then O.ACAD_GROUP
end), MIN( case when H.PLAN_SEQUENCE <11 then
P.DESCR
end), MIN( case when ( H.PLAN_SEQUENCE <11 and ( TO_CHAR(J.EFFDT,'YYYY-MM-DD') = TO_CHAR(H.EFFDT,'YYYY-MM-DD')) and J.ACAD_SUB_PLAN is not null)
then D.DESCR
end), MIN( case when H.PLAN_SEQUENCE between 51 and 60 then P.DESCR
end), P.ACAD_PLAN
FROM (PS_STDNT_CAR_TERM A LEFT OUTER JOIN PS_SCC_EMAIL_QVW B ON A.EMPLID = B.EMPLID AND B.E_ADDR_TYPE = 'CAMP' ), PS_SCC_PERDATA_QVW C, PS_TERM_TBL E, (PS_ACAD_PROG F LEFT OUTER JOIN PS_SCC_VISA_P_QVW G ON F.EMPLID = G.EMPLID AND G.EFFDT >= F.EFFDT ), ((PS_ACAD_PLAN H LEFT OUTER JOIN PS_ACAD_SUBPLAN J ON H.EMPLID = J.EMPLID AND H.ACAD_CAREER = J.ACAD_CAREER AND H.STDNT_CAR_NBR = J.STDNT_CAR_NBR AND H.EFFSEQ = J.EFFSEQ AND H.ACAD_PLAN = J.ACAD_PLAN AND J.EFFDT <= SYSDATE ) LEFT OUTER JOIN PS_ACAD_SUBPLN_TBL D ON J.ACAD_PLAN = D.ACAD_PLAN AND J.ACAD_SUB_PLAN = D.ACAD_SUB_PLAN AND D.EFFDT <= SYSDATE ), (PS_ACAD_PROG K LEFT OUTER JOIN PS_ACAD_PROG L ON K.EMPLID = L.EMPLID AND K.ACAD_CAREER = L.ACAD_CAREER AND K.STDNT_CAR_NBR = L.STDNT_CAR_NBR AND K.EFFSEQ = L.EFFSEQ AND L.EFFDT <= K.EFFDT AND L.PROG_ACTION = 'MATR' AND L.ADMIT_TERM = K.ADMIT_TERM ), PS_ADDRESS_NPC_VW M, PS_ACAD_PLAN_OWNER N, PS_TU_ACAD_ORG_GRP O, PS_ACAD_PLAN_TBL P
WHERE ( A.STRM = :1
AND A.UNT_TAKEN_PRGRSS+ A.UNT_AUDIT > 0
AND A.EMPLID = C.EMPLID
AND A.ACAD_CAREER = E.ACAD_CAREER
AND A.INSTITUTION = E.INSTITUTION
AND A.STRM = E.STRM
AND A.EMPLID = F.EMPLID
AND A.ACAD_CAREER = F.ACAD_CAREER
AND A.INSTITUTION = F.INSTITUTION
AND F.STDNT_CAR_NBR = A.STDNT_CAR_NBR
AND F.EFFDT =
(SELECT MAX(F_ED.EFFDT) FROM PS_ACAD_PROG F_ED
WHERE F.EMPLID = F_ED.EMPLID
AND F.ACAD_CAREER = F_ED.ACAD_CAREER
AND F.STDNT_CAR_NBR = F_ED.STDNT_CAR_NBR
AND F_ED.EFFDT <= SYSDATE)
AND F.EFFSEQ =
(SELECT MAX(F_ES.EFFSEQ) FROM PS_ACAD_PROG F_ES
WHERE F.EMPLID = F_ES.EMPLID
AND F.ACAD_CAREER = F_ES.ACAD_CAREER
AND F.STDNT_CAR_NBR = F_ES.STDNT_CAR_NBR
AND F.EFFDT = F_ES.EFFDT)
AND F.EMPLID = H.EMPLID
AND F.ACAD_CAREER = H.ACAD_CAREER
AND F.STDNT_CAR_NBR = H.STDNT_CAR_NBR
AND F.EFFSEQ = H.EFFSEQ
AND H.EFFDT =
(SELECT MAX(H_ED.EFFDT) FROM PS_ACAD_PLAN H_ED
WHERE H.EMPLID = H_ED.EMPLID
AND H.ACAD_CAREER = H_ED.ACAD_CAREER
AND H.STDNT_CAR_NBR = H_ED.STDNT_CAR_NBR
AND H_ED.EFFDT <= SYSDATE)
AND H.EFFSEQ =
(SELECT MAX(H_ES.EFFSEQ) FROM PS_ACAD_PLAN H_ES
WHERE H.EMPLID = H_ES.EMPLID
AND H.ACAD_CAREER = H_ES.ACAD_CAREER
AND H.STDNT_CAR_NBR = H_ES.STDNT_CAR_NBR
AND H.EFFDT = H_ES.EFFDT)
AND EXISTS (SELECT I.EMPLID
FROM PS_ACAD_PLAN I
WHERE I.EMPLID = H.EMPLID
AND I.ACAD_CAREER = H.ACAD_CAREER
AND I.STDNT_CAR_NBR = I.STDNT_CAR_NBR
AND I.ACAD_PLAN LIKE '%-%'
AND I.EFFDT = H.EFFDT)
AND F.PROG_STATUS IN ('AC','CM')
AND F.EMPLID = K.EMPLID
AND F.ACAD_CAREER = K.ACAD_CAREER
AND F.STDNT_CAR_NBR = K.STDNT_CAR_NBR
AND F.EFFSEQ = K.EFFSEQ
AND K.EFFDT = F.EFFDT
AND K.PROG_ACTION = F.PROG_ACTION
AND K.ADMIT_TERM = F.ADMIT_TERM
AND C.EMPLID = M.EMPLID
AND M.EFFDT =
(SELECT MAX(M_ED.EFFDT) FROM PS_ADDRESS_NPC_VW M_ED
WHERE M.EMPLID = M_ED.EMPLID
AND M.ADDRESS_TYPE = M_ED.ADDRESS_TYPE
AND M_ED.EFFDT <= SYSDATE)
AND H.ACAD_PLAN = N.ACAD_PLAN
AND N.EFFDT =
(SELECT MAX(N_ED.EFFDT) FROM PS_ACAD_PLAN_OWNER N_ED
WHERE N.INSTITUTION = N_ED.INSTITUTION
AND N.ACAD_PLAN = N_ED.ACAD_PLAN
AND N_ED.EFFDT <= SYSDATE)
AND N.INSTITUTION = O.INSTITUTION
AND N.ACAD_ORG = O.ACAD_ORG
AND O.EFFDT =
(SELECT MAX(O_ED.EFFDT) FROM PS_TU_ACAD_ORG_GRP O_ED
WHERE O.INSTITUTION = O_ED.INSTITUTION
AND O.ACAD_ORG = O_ED.ACAD_ORG
AND O_ED.EFFDT <= SYSDATE)
AND H.ACAD_PLAN = P.ACAD_PLAN
AND P.EFFDT =
(SELECT MAX(P_ED.EFFDT) FROM PS_ACAD_PLAN_TBL P_ED
WHERE P.INSTITUTION = P_ED.INSTITUTION
AND P.ACAD_PLAN = P_ED.ACAD_PLAN
AND P_ED.EFFDT <= SYSDATE))
GROUP BY A.EMPLID, B.EMAIL_ADDR, C.FIRST_NAME, C.MIDDLE_NAME, C.LAST_NAME, C.NAME_SUFFIX, case A.ACAD_LEVEL_BOT
when '00' then 'Non-Degree'
when '10' then 'Freshman'
when '20' then 'Sophomore'
when '30' then 'Junior'
when '40' then 'Senior'
when '50' then 'Post Bachelor'
when 'GR' then 'Graduate'
else 'unknown' END, G.VISA_PERMIT_TYPE, case
when L.PROG_REASON like 'F%' then 'Native'
when L.PROG_REASON like 'T%' then 'Transfer'
when L.PROG_REASON like 'U%' then 'Post-Bachelor'
when L.PROG_REASON like 'NOND' then 'Non-Degree'
else L.PROG_REASON
end, C.SEX, A.CUM_GPA, F.ACAD_PROG, P.ACAD_PLAN
ORDER BY 5, 6, 3, 4, 1

 

[drking2]

Does it always put the major in the first row returned? If so, select top 1. Which fields determines major/minor?

 

[vector03]

You've got a syntax error and character placement error on this line.


WHERE ( AND A.UNT_TAKEN_PRGRSS+ A.UNT_AUDIT > 0



Should be this
WHERE (A.UNT_TAKEN_PRGRSS + A.UNT_AUDIT > 0




Start there and report back.

 

[DadOSix]

subd

 

[jamesenoch]

I would recommend :

01) Reviewing your FROM clause, especially the columns that connect your "left outer joins".

02) Reviewing your WHERE clause (ex -- a missing clause)

If that does not work, try rebuilding your query, starting with the main table (A), and then add in 1 table in at a time and execute the query, until you see the duplication in your result set.

Note : Table/View "PS_ACAD_PROG" is being referenced multiple times in this query. Also, you have 1 bind variable, but multiple literals. Not saying that these will cause a data issue, but it might cause a performance issue, especially if it will be called multiple times.. If this is Oracle (it looks like it), check your explain plan.