| Subject | RE: [firebird-support] Go ahead and laugh, SQL gurus |
|---|---|
| Author | Graeme Edwards |
| Post date | 2005-07-25T23:19:12Z |
Without looking at the normalisation issues at all, one reason why it
wouldn't return any records in every
link to Multichoices, which means that one unmatched value in your main
table will lead to the entire
query returning no records.
Left outer joins would fix this.
If the Excellent and Good replies are effectively hard-coded and don't need
to change you could do it more simply
with a case statement like
Case when e.Reason_For_Leaving is Null then 'No Reply' when
e.Reason_for_Leaving= 1 then 'Excellent' when e.Reason_For_Leaving= 2 then
'Good' end
There are probably more elegant ways of writing this.
_____
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Clay Shannon
Sent: Tuesday, 26 July 2005 7:49 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Go ahead and laugh, SQL gurus
It's Monday, so you may as well have yourself a good belly laugh.
I am storing beaucoup integers in a table which correspond to a
multiple-choice selection (1 = excellent, 2 = good, etc.)
When displaying the values on a form, I want to show those text values
rather than integers.
So, I created a table like:
ID DESCRIPTION
1 Excellent
2 Good
Etc.
I tried the following SQL, but I'm pretty sure there's got to be an
easier/more elegant way:
select e.EXITINTERVIEW_DATE, e.HIRE_DATE, e.TERMINATE_DATE,
e.LOCATION_WORKED,
e.CONSIDER_REEMPLOYMENT, e.RECOMMEND_LAKEVIEW, e.COMMENTS,
o.OTHER_POS_DISC_METHOD,
r.REASON_FOR_LEAVING, h.fullname as FullName, d.deptname, n.ncname,
h2.fullname,
m.DESCRIPTION, m2.DESCRIPTION, m3.DESCRIPTION, m4.DESCRIPTION,
m5.DESCRIPTION,
m6.DESCRIPTION, m7.DESCRIPTION, m8.DESCRIPTION, m9.DESCRIPTION,
m10.DESCRIPTION,
m11.DESCRIPTION, m12.DESCRIPTION, m13.DESCRIPTION, m14.DESCRIPTION,
m15.DESCRIPTION,
m16.DESCRIPTION, m17.DESCRIPTION, m18.DESCRIPTION, m19.DESCRIPTION,
m20.DESCRIPTION,
m21.DESCRIPTION, m22.DESCRIPTION, m23.DESCRIPTION, m24.DESCRIPTION,
m25.DESCRIPTION,
m26.DESCRIPTION, m27.DESCRIPTION, m28.DESCRIPTION, m29.DESCRIPTION
from exit_interview e
join empinfo_history h on e.empno = h.empno
join empinfo_history h2 on e.MANAGER_ID = h2.empno
join dept d on e.dept = d.deptno
join natcl n on e.jobtitleid = n.ncno
join REASONS_FOR_LEAVING_DLU r on e.REASON_FOR_LEAVING_ID = r.id
join OTHER_POSITION_DISCOVERY_DLU o on e.OTHER_POSITION_ID = o.id
join LAKEVIEWPOSITIVES_DLU l on e.LAKEVIEW_POSITIVES_ID =
l.LAKEVIEW_POSITIVES
join EXIT_INTERVIEW_MULTICHOICES m on e.COMPENSATION = m.id
join EXIT_INTERVIEW_MULTICHOICES m2 on e.PERFORMANCE_REVIEW = m2.id
join EXIT_INTERVIEW_MULTICHOICES m3 on e.BENEFITS = m3.id
join EXIT_INTERVIEW_MULTICHOICES m4 on e.VACATION = m4.id
join EXIT_INTERVIEW_MULTICHOICES m5 on e.COMMUNICATION_INTERDEPT = m5.id
join EXIT_INTERVIEW_MULTICHOICES m6 on e.COMMUNICATION_INTERLOCATION = m6.id
join EXIT_INTERVIEW_MULTICHOICES m7 on e.COMMUNICATION_OTHERLOCATION = m7.id
join EXIT_INTERVIEW_MULTICHOICES m8 on e.DEPT_MORALE = m8.id
join EXIT_INTERVIEW_MULTICHOICES m9 on e.ORIENTATION_PROGRAM = m9.id
join EXIT_INTERVIEW_MULTICHOICES m10 on e.TRAINING_RECEIVED = m10.id
join EXIT_INTERVIEW_MULTICHOICES m11 on e.EQUIP_TECH_TOOLS = m11.id
join EXIT_INTERVIEW_MULTICHOICES m12 on e.PERFORMANCE_EVALS = m12.id
join EXIT_INTERVIEW_MULTICHOICES m13 on e.ADVANCEMENT_OPPORTUNITIES = m13.id
join EXIT_INTERVIEW_MULTICHOICES m14 on e.UTILIZATION_SKILLS_POTENTIAL =
m14.id
join EXIT_INTERVIEW_MULTICHOICES m15 on e.EXPECTATIONS = m15.id
join EXIT_INTERVIEW_MULTICHOICES m16 on e.SENIOR_MGMNT_EVAL = m16.id
join EXIT_INTERVIEW_MULTICHOICES m17 on e.SENIOR_MGMNT_COMM_BUS_STRATEGY =
m17.id
join EXIT_INTERVIEW_MULTICHOICES m18 on e.COMMUNICATION_STRATEGY_STATUS =
m18.id
join EXIT_INTERVIEW_MULTICHOICES m19 on e.WILLING_TO_LISTEN = m19.id
join EXIT_INTERVIEW_MULTICHOICES m20 on e.MGR_COMPETENCE = m20.id
join EXIT_INTERVIEW_MULTICHOICES m21 on e.COMMUNICATION_DEPT_GOAL = m21.id
join EXIT_INTERVIEW_MULTICHOICES m22 on e.FAIR_POLICIES = m22.id
join EXIT_INTERVIEW_MULTICHOICES m23 on e.RECOGNITION = m23.id
join EXIT_INTERVIEW_MULTICHOICES m24 on e.SAFE_PLEASANT = m24.id
join EXIT_INTERVIEW_MULTICHOICES m25 on e.CLEAR_INSTRUCTIONS = m25.id
join EXIT_INTERVIEW_MULTICHOICES m26 on e.ADMIT_CORRECT_MISTAKES = m26.id
join EXIT_INTERVIEW_MULTICHOICES m27 on e.KEEP_EMPS_INFORMED = m27.id
join EXIT_INTERVIEW_MULTICHOICES m28 on e.APPROPRIATE_TRAINING = m28.id
join EXIT_INTERVIEW_MULTICHOICES m29 on e.LEADERSHIP = m29.id;
Besides, although it runs, it doesn't return any records-warum denn nicht?
Clay Shannon,
Dimension 4 Software
[Non-text portions of this message have been removed]
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Visit http://firebird.sourceforge.net and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !
Also search the knowledgebases at http://www.ibphoenix.com
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
_____
YAHOO! GROUPS LINKS
* Visit your group "firebird-support
<http://groups.yahoo.com/group/firebird-support> " on the web.
* To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com
<mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>
* Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .
_____
[Non-text portions of this message have been removed]
wouldn't return any records in every
link to Multichoices, which means that one unmatched value in your main
table will lead to the entire
query returning no records.
Left outer joins would fix this.
If the Excellent and Good replies are effectively hard-coded and don't need
to change you could do it more simply
with a case statement like
Case when e.Reason_For_Leaving is Null then 'No Reply' when
e.Reason_for_Leaving= 1 then 'Excellent' when e.Reason_For_Leaving= 2 then
'Good' end
There are probably more elegant ways of writing this.
_____
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Clay Shannon
Sent: Tuesday, 26 July 2005 7:49 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Go ahead and laugh, SQL gurus
It's Monday, so you may as well have yourself a good belly laugh.
I am storing beaucoup integers in a table which correspond to a
multiple-choice selection (1 = excellent, 2 = good, etc.)
When displaying the values on a form, I want to show those text values
rather than integers.
So, I created a table like:
ID DESCRIPTION
1 Excellent
2 Good
Etc.
I tried the following SQL, but I'm pretty sure there's got to be an
easier/more elegant way:
select e.EXITINTERVIEW_DATE, e.HIRE_DATE, e.TERMINATE_DATE,
e.LOCATION_WORKED,
e.CONSIDER_REEMPLOYMENT, e.RECOMMEND_LAKEVIEW, e.COMMENTS,
o.OTHER_POS_DISC_METHOD,
r.REASON_FOR_LEAVING, h.fullname as FullName, d.deptname, n.ncname,
h2.fullname,
m.DESCRIPTION, m2.DESCRIPTION, m3.DESCRIPTION, m4.DESCRIPTION,
m5.DESCRIPTION,
m6.DESCRIPTION, m7.DESCRIPTION, m8.DESCRIPTION, m9.DESCRIPTION,
m10.DESCRIPTION,
m11.DESCRIPTION, m12.DESCRIPTION, m13.DESCRIPTION, m14.DESCRIPTION,
m15.DESCRIPTION,
m16.DESCRIPTION, m17.DESCRIPTION, m18.DESCRIPTION, m19.DESCRIPTION,
m20.DESCRIPTION,
m21.DESCRIPTION, m22.DESCRIPTION, m23.DESCRIPTION, m24.DESCRIPTION,
m25.DESCRIPTION,
m26.DESCRIPTION, m27.DESCRIPTION, m28.DESCRIPTION, m29.DESCRIPTION
from exit_interview e
join empinfo_history h on e.empno = h.empno
join empinfo_history h2 on e.MANAGER_ID = h2.empno
join dept d on e.dept = d.deptno
join natcl n on e.jobtitleid = n.ncno
join REASONS_FOR_LEAVING_DLU r on e.REASON_FOR_LEAVING_ID = r.id
join OTHER_POSITION_DISCOVERY_DLU o on e.OTHER_POSITION_ID = o.id
join LAKEVIEWPOSITIVES_DLU l on e.LAKEVIEW_POSITIVES_ID =
l.LAKEVIEW_POSITIVES
join EXIT_INTERVIEW_MULTICHOICES m on e.COMPENSATION = m.id
join EXIT_INTERVIEW_MULTICHOICES m2 on e.PERFORMANCE_REVIEW = m2.id
join EXIT_INTERVIEW_MULTICHOICES m3 on e.BENEFITS = m3.id
join EXIT_INTERVIEW_MULTICHOICES m4 on e.VACATION = m4.id
join EXIT_INTERVIEW_MULTICHOICES m5 on e.COMMUNICATION_INTERDEPT = m5.id
join EXIT_INTERVIEW_MULTICHOICES m6 on e.COMMUNICATION_INTERLOCATION = m6.id
join EXIT_INTERVIEW_MULTICHOICES m7 on e.COMMUNICATION_OTHERLOCATION = m7.id
join EXIT_INTERVIEW_MULTICHOICES m8 on e.DEPT_MORALE = m8.id
join EXIT_INTERVIEW_MULTICHOICES m9 on e.ORIENTATION_PROGRAM = m9.id
join EXIT_INTERVIEW_MULTICHOICES m10 on e.TRAINING_RECEIVED = m10.id
join EXIT_INTERVIEW_MULTICHOICES m11 on e.EQUIP_TECH_TOOLS = m11.id
join EXIT_INTERVIEW_MULTICHOICES m12 on e.PERFORMANCE_EVALS = m12.id
join EXIT_INTERVIEW_MULTICHOICES m13 on e.ADVANCEMENT_OPPORTUNITIES = m13.id
join EXIT_INTERVIEW_MULTICHOICES m14 on e.UTILIZATION_SKILLS_POTENTIAL =
m14.id
join EXIT_INTERVIEW_MULTICHOICES m15 on e.EXPECTATIONS = m15.id
join EXIT_INTERVIEW_MULTICHOICES m16 on e.SENIOR_MGMNT_EVAL = m16.id
join EXIT_INTERVIEW_MULTICHOICES m17 on e.SENIOR_MGMNT_COMM_BUS_STRATEGY =
m17.id
join EXIT_INTERVIEW_MULTICHOICES m18 on e.COMMUNICATION_STRATEGY_STATUS =
m18.id
join EXIT_INTERVIEW_MULTICHOICES m19 on e.WILLING_TO_LISTEN = m19.id
join EXIT_INTERVIEW_MULTICHOICES m20 on e.MGR_COMPETENCE = m20.id
join EXIT_INTERVIEW_MULTICHOICES m21 on e.COMMUNICATION_DEPT_GOAL = m21.id
join EXIT_INTERVIEW_MULTICHOICES m22 on e.FAIR_POLICIES = m22.id
join EXIT_INTERVIEW_MULTICHOICES m23 on e.RECOGNITION = m23.id
join EXIT_INTERVIEW_MULTICHOICES m24 on e.SAFE_PLEASANT = m24.id
join EXIT_INTERVIEW_MULTICHOICES m25 on e.CLEAR_INSTRUCTIONS = m25.id
join EXIT_INTERVIEW_MULTICHOICES m26 on e.ADMIT_CORRECT_MISTAKES = m26.id
join EXIT_INTERVIEW_MULTICHOICES m27 on e.KEEP_EMPS_INFORMED = m27.id
join EXIT_INTERVIEW_MULTICHOICES m28 on e.APPROPRIATE_TRAINING = m28.id
join EXIT_INTERVIEW_MULTICHOICES m29 on e.LEADERSHIP = m29.id;
Besides, although it runs, it doesn't return any records-warum denn nicht?
Clay Shannon,
Dimension 4 Software
[Non-text portions of this message have been removed]
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Visit http://firebird.sourceforge.net and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !
Also search the knowledgebases at http://www.ibphoenix.com
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
_____
YAHOO! GROUPS LINKS
* Visit your group "firebird-support
<http://groups.yahoo.com/group/firebird-support> " on the web.
* To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com
<mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>
* Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .
_____
[Non-text portions of this message have been removed]