Access query in sql (1 Viewer)

krish03

Registered User.
Local time
Today, 19:49
Joined
Jul 5, 2013
Messages
19
NEED THIS ACCESS QUERY FOR SQL ..LET ME KNOW WHAT CHANGES TO BE MADE.....
SELECT SUM(IIf([SYR_CTR_START_MONTH]=1, SYR_SUM_AMZ_INV_AMT, 0)) AS JAN,
SUM(IIf([SYR_CTR_START_MONTH]=2, SYR_SUM_AMZ_INV_AMT, 0)) AS FEB,
SUM(IIf([SYR_CTR_START_MONTH]=3, SYR_SUM_AMZ_INV_AMT, 0)) AS MAR,
SUM(IIF([SYR_CTR_START_MONTH] = 4 ,SYR_SUM_AMZ_INV_AMT ,0)) AS APR ,
SUM( IIF ([SYR_CTR_START_MONTH] = 5 ,SYR_SUM_AMZ_INV_AMT ,0)) AS MAY ,
SUM( IIF ([SYR_CTR_START_MONTH] = 6 ,SYR_SUM_AMZ_INV_AMT ,0)) AS JUN ,
SUM( IIF ([SYR_CTR_START_MONTH] = 7 ,SYR_SUM_AMZ_INV_AMT ,0)) AS JUL ,
SUM( IIF ([SYR_CTR_START_MONTH] = 8 ,SYR_SUM_AMZ_INV_AMT ,0)) AS AUG ,
SUM( IIF ([SYR_CTR_START_MONTH] = 9 ,SYR_SUM_AMZ_INV_AMT ,0)) AS SEP ,
SUM( IIF ([SYR_CTR_START_MONTH] = 10 ,SYR_SUM_AMZ_INV_AMT ,0)) AS OCT ,
SUM( IIF ([SYR_CTR_START_MONTH] = 11 ,SYR_SUM_AMZ_INV_AMT ,0)) AS NOV ,
SUM( IIF ([SYR_CTR_START_MONTH] = 12 ,SYR_SUM_AMZ_INV_AMT ,0)) AS DEC ,
SUM(IIf([SYR_CTR_START_MONTH]<=3, SYR_SUM_AMZ_INV_AMT, 0)) AS Q1,
SUM(IIf([SYR_CTR_START_MONTH]>=4 AND [SYR_CTR_START_MONTH]<=6, SYR_SUM_AMZ_INV_AMT, 0)) AS Q2,
SUM(IIf([SYR_CTR_START_MONTH]>=7 AND [SYR_CTR_START_MONTH]<=9, SYR_SUM_AMZ_INV_AMT, 0)) AS Q3,
SUM(IIf([SYR_CTR_START_MONTH]>=10 AND [SYR_CTR_START_MONTH]<=12, SYR_SUM_AMZ_INV_AMT, 0)) AS Q4,
SUM(SYR_SUM_AMZ_INV_AMT) AS FY
FROM NTLRESULTS_AZ_SMY_YPC_RPT_TBL
WHERE SYR_UPD_USER='EXCEPTION2';
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 16:49
Joined
Aug 30, 2003
Messages
36,118
In T-SQL, you'd use the CASE statement instead of IIf(), like:

CASE WHEN [SYR_CTR_START_MONTH]=1 THEN SYR_SUM_AMZ_INV_AMT ELSE 0 END
 

RainLover

VIP From a land downunder
Local time
Tomorrow, 10:49
Joined
Jan 5, 2009
Messages
5,041
NEED THIS ACCESS QUERY FOR SQL ..LET ME KNOW WHAT CHANGES TO BE MADE.....
SELECT SUM(IIf([SYR_CTR_START_MONTH]=1, SYR_SUM_AMZ_INV_AMT, 0)) AS JAN,
SUM(IIf([SYR_CTR_START_MONTH]=2, SYR_SUM_AMZ_INV_AMT, 0)) AS FEB,
SUM(IIf([SYR_CTR_START_MONTH]=3, SYR_SUM_AMZ_INV_AMT, 0)) AS MAR,
SUM(IIF([SYR_CTR_START_MONTH] = 4 ,SYR_SUM_AMZ_INV_AMT ,0)) AS APR ,
SUM( IIF ([SYR_CTR_START_MONTH] = 5 ,SYR_SUM_AMZ_INV_AMT ,0)) AS MAY ,
SUM( IIF ([SYR_CTR_START_MONTH] = 6 ,SYR_SUM_AMZ_INV_AMT ,0)) AS JUN ,
SUM( IIF ([SYR_CTR_START_MONTH] = 7 ,SYR_SUM_AMZ_INV_AMT ,0)) AS JUL ,
SUM( IIF ([SYR_CTR_START_MONTH] = 8 ,SYR_SUM_AMZ_INV_AMT ,0)) AS AUG ,
SUM( IIF ([SYR_CTR_START_MONTH] = 9 ,SYR_SUM_AMZ_INV_AMT ,0)) AS SEP ,
SUM( IIF ([SYR_CTR_START_MONTH] = 10 ,SYR_SUM_AMZ_INV_AMT ,0)) AS OCT ,
SUM( IIF ([SYR_CTR_START_MONTH] = 11 ,SYR_SUM_AMZ_INV_AMT ,0)) AS NOV ,
SUM( IIF ([SYR_CTR_START_MONTH] = 12 ,SYR_SUM_AMZ_INV_AMT ,0)) AS DEC ,
SUM(IIf([SYR_CTR_START_MONTH]<=3, SYR_SUM_AMZ_INV_AMT, 0)) AS Q1,
SUM(IIf([SYR_CTR_START_MONTH]>=4 AND [SYR_CTR_START_MONTH]<=6, SYR_SUM_AMZ_INV_AMT, 0)) AS Q2,
SUM(IIf([SYR_CTR_START_MONTH]>=7 AND [SYR_CTR_START_MONTH]<=9, SYR_SUM_AMZ_INV_AMT, 0)) AS Q3,
SUM(IIf([SYR_CTR_START_MONTH]>=10 AND [SYR_CTR_START_MONTH]<=12, SYR_SUM_AMZ_INV_AMT, 0)) AS Q4,
SUM(SYR_SUM_AMZ_INV_AMT) AS FY
FROM NTLRESULTS_AZ_SMY_YPC_RPT_TBL
WHERE SYR_UPD_USER='EXCEPTION2';

Because of thee CAPS this is very hard to read.

Can you run this through Word and convert it to Proper Case.

Better still do a copy paste from your Query's SQL.
If you don't have a Query it might be best to create one. You might just pick up your error there. Also you can build the Query Bit by Bit, testing as you go. When it fails you would have located the error.
 

krish03

Registered User.
Local time
Today, 19:49
Joined
Jul 5, 2013
Messages
19
So some thing like this
sum (case when [syr_ctr_start_month]=1 then syr_sum_amz_inv_amt else 0 end ) jan
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 16:49
Joined
Aug 30, 2003
Messages
36,118
Yes, something like that.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 16:49
Joined
Aug 30, 2003
Messages
36,118
Rain: To change the capitalization? Not that I know of, but that doesn't mean there isn't one.

Krish: The brackets are optional as long as there are no spaces, symbols or reserved words used in the field names (the underscore is okay).
 

RainLover

VIP From a land downunder
Local time
Tomorrow, 10:49
Joined
Jan 5, 2009
Messages
5,041
Rain: To change the capitalization? Not that I know of, but that doesn't mean there isn't one.

Paul if you are asking can Word change to proper case then yes it can.

If you are saying something else yhen I am not understanding the question.
 

MSAccessRookie

AWF VIP
Local time
Today, 19:49
Joined
May 2, 2008
Messages
3,428
I am not sure that your Quarterly calculations are not more complicated then they need to be. If you use BETWEEN, then they are all exactly the same format with different values, and are easily converted to T-SQL as in the above example.

SUM(IIf([SYR_CTR_START_MONTH] BETWEEN 1 AND 3, SYR_SUM_AMZ_INV_AMT, 0)) AS Q1
SUM(IIf([SYR_CTR_START_MONTH] BETWEEN 4 AND 6, SYR_SUM_AMZ_INV_AMT, 0)) AS Q2
SUM(IIf([SYR_CTR_START_MONTH] BETWEEN 7 AND 9, SYR_SUM_AMZ_INV_AMT, 0)) AS Q3
SUM(IIf([SYR_CTR_START_MONTH] BETWEEN 10 AND 12, SYR_SUM_AMZ_INV_AMT, 0)) AS Q4
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 16:49
Joined
Aug 30, 2003
Messages
36,118
Paul if you are asking can Word change to proper case then yes it can.

If you are saying something else yhen I am not understanding the question.

I wasn't clear on whether this was related to changing the capitalization: "is it possible to simply apply a Format to get the right result. ". Sounds like it was, so we're good!
 

krish03

Registered User.
Local time
Today, 19:49
Joined
Jul 5, 2013
Messages
19
I am not sure that your Quarterly calculations are not more complicated then they need to be. If you use BETWEEN, then they are all exactly the same format with different values, and are easily converted to T-SQL as in the above example.

SUM(IIf([SYR_CTR_START_MONTH] BETWEEN 1 AND 3, SYR_SUM_AMZ_INV_AMT, 0)) AS Q1
SUM(IIf([SYR_CTR_START_MONTH] BETWEEN 4 AND 6, SYR_SUM_AMZ_INV_AMT, 0)) AS Q2
SUM(IIf([SYR_CTR_START_MONTH] BETWEEN 7 AND 9, SYR_SUM_AMZ_INV_AMT, 0)) AS Q3
SUM(IIf([SYR_CTR_START_MONTH] BETWEEN 10 AND 12, SYR_SUM_AMZ_INV_AMT, 0)) AS Q4


It says missing expression..........
 

krish03

Registered User.
Local time
Today, 19:49
Joined
Jul 5, 2013
Messages
19
SELECT SUM case [SYR_CTR_START_MONTH]=1 then SYR_SUM_AMZ_INV_AMT else 0 end JAN,
FROM AZ_SMY_YPC_RPT_TBL
WHERE SYR_UPD_USER='EXCEPTION2';

when i run this i get ORA-00923: FROM keyword not found where expected
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 16:49
Joined
Aug 30, 2003
Messages
36,118
Add some spaces, add the parentheses for the Sum() and take out the comma before FROM.
 

krish03

Registered User.
Local time
Today, 19:49
Joined
Jul 5, 2013
Messages
19
Sql help

SELECT

SUM(
CASE SYR_CTR_START_MONTH BETWEEN1AND3THEN SYR_SUM_AMZ_INV_AMT ELSE0END) Q1,
SUM(
CASE SYR_CTR_START_MONTH BETWEEN4AND6THEN SYR_SUM_AMZ_INV_AMT ELSE0END) Q2,
SUM(
CASE SYR_CTR_START_MONTH BETWEEN7AND9THEN SYR_SUM_AMZ_INV_AMT ELSE0END) Q3,
SUM(
CASE SYR_CTR_START_MONTH BETWEEN10AND12THEN SYR_SUM_AMZ_INV_AMT ELSE0END) Q4
FromAZ_SMY_YPC_RPT_TBL
where SYR_CTR_START_YEAR=2013;

i AM GETTING ERROR NEAR SUM(CASE SYR_CTR_START_MONTH BETWEEN1AND3THEN SYR_SUM_AMZ_INV_AMT ELSE0END) Q1 SAYING THAT MISSING RIGHT PARENTHESIS.......
 

spikepl

Eledittingent Beliped
Local time
Tomorrow, 00:49
Joined
Nov 3, 2010
Messages
6,144
Re: Sql help

This isn't Access SQL - look in a manual for the relevant SQL-flavour

Update:
I got merged with another thread on apparently same topic, so it makes no sense.
@OP: do not waste people's efforts by double posting.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 16:49
Joined
Aug 30, 2003
Messages
36,118
I merged your new thread into the old one on the same topic. Did you try adding spaces? It's all jammed together there. You also missed WHEN after CASE.
 

Users who are viewing this thread

Top Bottom