Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Need help converting this Access query!!

Author  Topic 

mparter
Yak Posting Veteran

86 Posts
Posted - 2004-08-31 : 08:35:15
I've been given the following delighful Access query;

SELECT ENROLLED_THISYR.TEAM, Team_Targets_ThisYr.TEAM_TEXT, Sum(IIf((Left([QUALTYPE],1)="A" Or (Left([QUALTYPE],1)="B")),[STUDSEnrolled],0)) AS Degree_Enrolled, Sum(IIf((Left([QUALTYPE],1)="A" Or (Left([QUALTYPE],1)="B")),[ELIGIBLE],0)) AS Degree_Eligible, Sum(IIf((Left([QUALTYPE],1)="A" Or (Left([QUALTYPE],1)="B")),[TargetEnr],0)) AS Degree_Target, Sum(IIf((Left([QUALTYPE],1)<>"A" And (Left([QUALTYPE],1)<>"B") And [ENROLLED_THISYR]![BLOCK] Like "*2" And [MOA]="01" And [EDLEVEL]="1"),[STUDSEnrolled],0)) AS FT_AdvYr2_enrolled, Sum(IIf((Left([QUALTYPE],1)<>"A" And (Left([QUALTYPE],1)<>"B") And [ENROLLED_THISYR]![BLOCK] Like "*2" And [MOA]="01" And [EDLEVEL]="1"),[ELIGIBLE],0)) AS FT_AdvYr2_eligible, Sum(IIf((Left([QUALTYPE],1)<>"A" And (Left([QUALTYPE],1)<>"B") And [ENROLLED_THISYR]![BLOCK] Like "*2" And [MOA]="01" And [EDLEVEL]="1"),[TargetEnr],0)) AS FT_AdvYr2_Target, Sum(IIf((Left([QUALTYPE],1)<>"A" And (Left([QUALTYPE],1)<>"B") And [ENROLLED_THISYR]![BLOCK] Like "*1" And [MOA]="01" And [EDLEVEL]="1"),[STUDSEnrolled],0)) AS FT_AdvYr1_Enrolled, Sum(IIf((Left([QUALTYPE],1)<>"A" And (Left([QUALTYPE],1)<>"B") And [ENROLLED_THISYR]![BLOCK] Like "*1" And [MOA]="01" And [EDLEVEL]="1"),[ELIGIBLE],0)) AS FT_AdvYr1_eligible, Sum(IIf((Left([QUALTYPE],1)<>"A" And (Left([QUALTYPE],1)<>"B") And [ENROLLED_THISYR]![BLOCK] Like "*1" And [MOA]="01" And [EDLEVEL]="1"),[TargetEnr],0)) AS FT_AdvYr1_Target, Sum(IIf((([MOA]="01" And [EDLEVEL]="2")),[STUDSEnrolled],0)) AS FT_NonAdv_Enrolled, Sum(IIf((([MOA]="01" And [EDLEVEL]="2")),[ELIGIBLE],0)) AS FT_NonAdv_Eligible, Sum(IIf((([MOA]="01" And [EDLEVEL]="2")),[TargetEnr],0)) AS FT_NonAdv_Target, Sum(IIf(((Left([QUALTYPE],1)<>"A" And (Left([QUALTYPE],1)<>"B") And [MOA]<>"01" And [EDLEVEL]="1")),[STUDSEnrolled],0)) AS PT_Adv_Enrolled, Sum(IIf(((Left([QUALTYPE],1)<>"A" And (Left([QUALTYPE],1)<>"B") And [MOA]<>"01" And [EDLEVEL]="1")),[ELIGIBLE],0)) AS PT_Adv_Eligible, Sum(IIf(((Left([QUALTYPE],1)<>"A" And (Left([QUALTYPE],1)<>"B") And [MOA]<>"01" And [EDLEVEL]="1")),[TargetEnr],0)) AS PT_Adv_Target, Sum(IIf((([MOA]<>"01" And [EDLEVEL]="2")),[STUDSEnrolled],0)) AS PT_NonAdv_Enrolled, Sum(IIf((([MOA]<>"01" And [EDLEVEL]="2")),[ELIGIBLE],0)) AS PT_NonAdv_Eligible, Sum(IIf((([MOA]<>"01" And [EDLEVEL]="2")),[TargetEnr],0)) AS PT_NonAdv_Target
FROM (ENROLLED_THISYR LEFT JOIN Team_Targets_ThisYr ON ENROLLED_THISYR.TEAM = Team_Targets_ThisYr.TEAM_CODE) LEFT JOIN PlanTable_03 ON ENROLLED_THISYR.PROGRAMME = PlanTable_03.SITS_Programme
GROUP BY ENROLLED_THISYR.TEAM, Team_Targets_ThisYr.TEAM_TEXT;


Can anybody give me any pointers/help on where to even begin with this?

Thanks.

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2004-08-31 : 08:43:37
basically you just need to convert the iif into case statements:

1st sum:
Sum(IIf((Left([QUALTYPE],1)="A" Or (Left([QUALTYPE],1)="B")),[STUDSEnrolled],0)) AS Degree_Enrolled,
to
sum (case when (Left([QUALTYPE],1)='A') Or (Left([QUALTYPE],1)='B') then [STUDSEnrolled] else 0 end)

Go with the flow & have fun! Else fight the flow :)
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2004-08-31 : 08:43:55
want to help us out with some formatting? look at how the post stretches out the page. add some indents and line breaks, please.

thanks!

FYI -- these two are equivalent:

Access: IIF(Condition, A, B)
SQL Server: CASE WHEN Condition THEN A ELSE B END

that alone might help you translate this sql fairly easily.

- Jeff
Go to Top of Page

mparter
Yak Posting Veteran

86 Posts
Posted - 2004-08-31 : 08:50:13
quote:
Originally posted by jsmith8858

want to help us out with some formatting? look at how the post stretches out the page. add some indents and line breaks, please.

thanks!

FYI -- these two are equivalent:

Access: IIF(Condition, A, B)
SQL Server: CASE WHEN Condition THEN A ELSE B END

that alone might help you translate this sql fairly easily.

- Jeff


Sorry, I didn't go back in to read it once I'd posted it, otherwise I would have changed it. Thanks for your advice and spirit1's.
Go to Top of Page

mr_mist
Grunnio

1870 Posts
Posted - 2004-08-31 : 08:54:05
A bit like this ---

SELECT ENROLLED_THISYR.TEAM,
Team_Targets_ThisYr.TEAM_TEXT,
sum (case when left(qualtype,1) = 'A' then STUDSEnrolled
when left(qualtype,1) = 'B' then STUDSEnrolled
else 0 end) as Degree_Enrolled,

sum (case when left(qualtype,1) = 'A' then ELIGIBLE
when left(qualtype,1) = 'B' then ELIGIBLE
else 0 end) as Degree_Eligible,

sum (case when left(qualtype,1) = 'A' then TargetEnr
when left(qualtype,1) = 'B' then TargetEnr
else 0 end) as Degree_Target,

sum (case when left(qualtype,1) = 'A' And [ENROLLED_THISYR].[BLOCK] Like '%2' and moa = '01'
and edlevel = 1 then STUDSEnrolled else 0 end) AS FT_AdvYr2_enrolled,

sum (case when left(qualtype,1) != 'A' and left(qualtype,1) != 'B' And [ENROLLED_THISYR].[BLOCK] Like '%2' and moa = '01'
and edlevel = 1 then eligible else 0 end) AS FT_AdvYr2_eligible,

sum (case when left(qualtype,1) != 'A' and left(qualtype,1) != 'B' And [ENROLLED_THISYR].[BLOCK] Like '%2' and moa = '01'
and edlevel = 1 then [TargetEnr] else 0 end) AS FT_AdvYr2_Target,


sum (case when left(qualtype,1) != 'A' and left(qualtype,1) != 'B' And [ENROLLED_THISYR].[BLOCK] Like '%1' and moa = '01'
and edlevel = 1 then [STUDSEnrolled] else 0 end) AS FT_AdvYr1_Enrolled

--- I've missed a few out here, but this should give you the idea..


FROM ENROLLED_THISYR
LEFT JOIN Team_Targets_ThisYr ON ENROLLED_THISYR.TEAM = Team_Targets_ThisYr.TEAM_CODE
LEFT JOIN PlanTable_03 ON ENROLLED_THISYR.PROGRAMME = PlanTable_03.SITS_Programme
GROUP BY ENROLLED_THISYR.TEAM, Team_Targets_ThisYr.TEAM_TEXT;


-------
Moo. :)
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2004-08-31 : 08:56:43
I think I did them all....



SELECT 
	ENROLLED_THISYR.TEAM, 
	Team_Targets_ThisYr.TEAM_TEXT, 
	Sum(Case when Left(QUALTYPE,1)='A' Or Left(QUALTYPE,1)='B' then STUDSEnrolled else 0 end) AS Degree_Enrolled, 
	Sum(Case when Left(QUALTYPE,1)='A' Or Left(QUALTYPE,1)='B' then Eligible else 0 end) AS Degree_Eligible, 
	Sum(Case when Left(QUALTYPE,1)='A' Or Left(QUALTYPE,1)='B' then TargetEnr else 0 end) AS Degree_Target, 
	Sum(Case when Left(QUALTYPE,1)<>'A' And Left(QUALTYPE,1)<>'B' and ENROLLED_THISYR.BLOCK Like '%2' And MOA='01' And EDLEVEL='1' then STUDSEnrolled else 0 end) AS FT_AdvYr2_enrolled, 
	Sum(Case when Left(QUALTYPE,1)<>'A' And Left(QUALTYPE,1)<>'B' and ENROLLED_THISYR.BLOCK Like '%2' And MOA='01' And EDLEVEL='1' then ELIGIBLE else 0 end) AS FT_AdvYr2_eligible, 
	Sum(Case when Left(QUALTYPE,1)<>'A' And Left(QUALTYPE,1)<>'B' and ENROLLED_THISYR.BLOCK Like '%2' And MOA='01' And EDLEVEL='1' then TargetEnr else 0 end) AS FT_AdvYr2_Target, 
	Sum(Case when Left(QUALTYPE,1)<>'A' And Left(QUALTYPE,1)<>'B' and ENROLLED_THISYR.BLOCK Like '%1' And MOA='01' And EDLEVEL='1' then STUDSEnrolled else 0 end) AS FT_AdvYr1_enrolled, 
	Sum(Case when Left(QUALTYPE,1)<>'A' And Left(QUALTYPE,1)<>'B' and ENROLLED_THISYR.BLOCK Like '%1' And MOA='01' And EDLEVEL='1' then ELIGIBLE else 0 end) AS FT_AdvYr1_eligible, 
	Sum(Case when Left(QUALTYPE,1)<>'A' And Left(QUALTYPE,1)<>'B' and ENROLLED_THISYR.BLOCK Like '%1' And MOA='01' And EDLEVEL='1' then TargetEnr else 0 end) AS FT_AdvYr1_Target, 
	Sum(Case when MOA='01' And EDLEVEL='2' then STUDSEnrolled else 0 end) AS FT_NonAdv_Enrolled, 
	Sum(Case when MOA='01' And EDLEVEL='2' then ELIGIBLE Else 0 end) AS FT_NonAdv_Eligible, 
	Sum(Case when MOA='01' And EDLEVEL='2' then TargetEnr else 0 end) AS FT_NonAdv_Target, 
	Sum(Case when Left(QUALTYPE,1)<>'A' And Left(QUALTYPE,1)<>'B' and MOA<>'01' And EDLEVEL='1' then STUDSEnrolled else 0 end) AS PT_Adv_Enrolled, 
	Sum(Case when Left(QUALTYPE,1)<>'A' And Left(QUALTYPE,1)<>'B' and MOA<>'01' And EDLEVEL='1' then ELIGIBLE else 0 end) AS PT_Adv_Eligible, 
	Sum(Case when Left(QUALTYPE,1)<>'A' And Left(QUALTYPE,1)<>'B' and MOA<>'01' And EDLEVEL='1' then TargetEnr else 0 end) AS PT_Adv_Target, 
	Sum(Case when MOA<>'01' And EDLEVEL='2' then STUDSEnrolled else 0 end) AS PT_NonAdv_Enrolled, 
	Sum(Case when MOA<>'01' And EDLEVEL='2' then ELIGIBLE Else 0 end) AS PT_NonAdv_Eligible, 
	Sum(Case when MOA<>'01' And EDLEVEL='2' then TargetEnr else 0 end) AS PT_NonAdv_Target
FROM ENROLLED_THISYR 
LEFT JOIN Team_Targets_ThisYr 
ON ENROLLED_THISYR.TEAM = Team_Targets_ThisYr.TEAM_CODE
LEFT JOIN PlanTable_03 
ON ENROLLED_THISYR.PROGRAMME = PlanTable_03.SITS_Programme
GROUP BY ENROLLED_THISYR.TEAM, Team_Targets_ThisYr.TEAM_TEXT



I'm not sure i agree with the method though.... maybe when you learn a little more about SQL you can reapproach this.

Corey
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2004-08-31 : 08:57:11
Man! I took forever converting that...

Corey
Go to Top of Page

mparter
Yak Posting Veteran

86 Posts
Posted - 2004-08-31 : 09:00:19
OK, I've tried the following in QA (following the examples in Books Online):-

SELECT TEAM, TEAM_TEXT, QUALTYPE, 'Degree_Enrolled' = 
	CASE
		WHEN SUBSTRING(QUALTYPE, 1, 1) = 'A' THEN SUM(STUDSEnrolled)
		WHEN SUBSTRING(QUALTYPE, 1, 1) = 'B' THEN SUM(STUDSEnrolled)
		ELSE 0
	END
FROM vw_RS_TeamEnrolFigs


but all I get are the following errors:-


Server: Msg 8118, Level 16, State 1, Line 1
Column 'vw_RS_TeamEnrolFigs.TEAM' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'vw_RS_TeamEnrolFigs.TEAM_TEXT' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'vw_RS_TeamEnrolFigs.QUALTYPE' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'vw_RS_TeamEnrolFigs.QUALTYPE' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'vw_RS_TeamEnrolFigs.QUALTYPE' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2004-08-31 : 09:05:29
that is because you have an aggregate function in your select (sum(...)). you need to put those fields that asre not used in aggregats into a group by as well as in select statement.

Go with the flow & have fun! Else fight the flow :)
Go to Top of Page

mr_mist
Grunnio

1870 Posts
Posted - 2004-08-31 : 09:09:38
Yeah, I have to say that if an upgrade to sql server is taking place, then now might be a good time to reorganise your data structure. For example, all those columns where you are selecting the leftmost 1st character, why not just have a column that only contains that character? (and so on.)

-------
Moo. :)
Go to Top of Page

mparter
Yak Posting Veteran

86 Posts
Posted - 2004-08-31 : 09:15:00
Thanks for the time and effort guys, much appreciated.

I'd love to be able to re-organise the data structure but it's a 3rd party application, I'm just converting an Access report to use Reporting Services!
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site