January 8, 2008 at 2:04 pm
I didn't write this snippet, but I now need to convert it to T-SQL.
I've struggled with it for several days, and I'm now at my wits end.
Any help?
IIf([DBA_cs_project]![creator_id] Like "DE8*" Or [DBA_cs_project]![creator_id] Like "AT8*",IIf([DBA_CSQUOTES]![block_id]="specials" And [DBA_CSQUOTES]![Sequence_no]=50,0,IIf([DBA_CSQUOTES]![block_id]="X_COMPONENTS_ND" Or [DBA_CSQUOTES]![block_id]="Y_ACCESSORIES" Or [DBA_CSQUOTES]![block_id]="AC_HAND_ND" Or [DBA_CSQUOTES]![block_id]="MP" Or [DBA_CSQUOTES]![block_id]="PLATESF" Or [DBA_CSQUOTES]![block_id]="ANGLESF" Or [DBA_CSQUOTES]![block_id]="CHANNEL_2BF" Or [DBA_CSQUOTES]![block_id]="CHANNEL_3BF" Or [DBA_CSQUOTES]![block_id]="CHANNEL_4BF",Round([DBA_CSQUOTES]![Extended_Price]*[DBA_CS_CURRENCY_EXCH]![exch_rate],2),Round([DBA_CSQUOTES]![Extended_Price]*[DBA_CS_CURRENCY_EXCH]![exch_rate]*(100-[DE_MAX_DISCOUNT]![MAX_DISCOUNT])/(100-IIf([field16]="" Or [field16] Is Null Or [field16]="Listenpreis",0,IIf([field16] Like "*%*",Left([field16],InStr([field16],"%")-1),CDbl([field16])))),2))),0) AS [CS PRODUCT_PRICE]
January 8, 2008 at 2:44 pm
I'm not totally sure, since I can't run the original and the conversion and make sure they get the same results from the same data, but try this:
case
when
[DBA_cs_project].[creator_id] Like "DE8*"
Or [DBA_cs_project].[creator_id] Like "AT8*" then 0
when
[DBA_CSQUOTES].[block_id]="specials"
And [DBA_CSQUOTES].[Sequence_no]=50 then 2
when
[DBA_CSQUOTES].[block_id]="X_COMPONENTS_ND"
Or [DBA_CSQUOTES].[block_id]="Y_ACCESSORIES"
Or [DBA_CSQUOTES].[block_id]="AC_HAND_ND"
Or [DBA_CSQUOTES].[block_id]="MP"
Or [DBA_CSQUOTES].[block_id]="PLATESF"
Or [DBA_CSQUOTES].[block_id]="ANGLESF"
Or [DBA_CSQUOTES].[block_id]="CHANNEL_2BF"
Or [DBA_CSQUOTES].[block_id]="CHANNEL_3BF"
Or [DBA_CSQUOTES].[block_id]="CHANNEL_4BF" then Round([DBA_CSQUOTES].[Extended_Price]*[DBA_CS_CURRENCY_EXCH].[exch_rate],2)
else Round([DBA_CSQUOTES].[Extended_Price]*[DBA_CS_CURRENCY_EXCH].[exch_rate]*(100-[DE_MAX_DISCOUNT].[MAX_DISCOUNT])/(100-
case
when [field16]="" Or [field16] Is Null Or [field16]="Listenpreis" then 0
when [field16] Like "*%*" then Left([field16]
when InStr([field16],"%")-1) then CDbl([field16]))) end
,2)
end
AS [CS PRODUCT_PRICE]
You'll probably also need to make sure I got the parenthesis and all that right. It's been a LONG time since I delt with Access queries.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 8, 2008 at 2:53 pm
Thanks G. You are far better at this then I am!
January 8, 2008 at 2:59 pm
Does that mean it worked? Or just got you far enough along to make it work on your own? (I'm curious about how well I remember how Access queries work.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 8, 2008 at 3:35 pm
GSquared,
These IIF look a little more nested than you displayed, but my ACCESS is pretty rusty. I thought the query should look something like this.
select
case when [DBA_cs_project].[creator_id] Like 'DE8%' Or [DBA_cs_project].[creator_id] Like 'AT8%' then
case when [DBA_CSQUOTES].[block_id]='specials' And [DBA_CSQUOTES].[Sequence_no]=50 then
0
else
case when [DBA_CSQUOTES].[block_id]='X_COMPONENTS_ND'
Or [DBA_CSQUOTES].[block_id]='Y_ACCESSORIES'
Or [DBA_CSQUOTES].[block_id]='AC_HAND_ND'
Or [DBA_CSQUOTES].[block_id]='MP'
Or [DBA_CSQUOTES].[block_id]='PLATESF'
Or [DBA_CSQUOTES].[block_id]='ANGLESF'
Or [DBA_CSQUOTES].[block_id]='CHANNEL_2BF'
Or [DBA_CSQUOTES].[block_id]='CHANNEL_3BF'
Or [DBA_CSQUOTES].[block_id]='CHANNEL_4BF' then
Round([DBA_CSQUOTES].[Extended_Price]*[DBA_CS_CURRENCY_EXCH].[exch_rate],2)
else
Round([DBA_CSQUOTES].[Extended_Price]*[DBA_CS_CURRENCY_EXCH].[exch_rate]*
(100-[DE_MAX_DISCOUNT].[MAX_DISCOUNT])/(100-
case when [field16]=''
Or [field16] Is Null
Or [field16]='Listenpreis' then
0
when [field16] Like '!%' escape '!' then
Left([field16], CHARINDEX([field16],'%')-1)
else
CONVERT(NUMERIC(8,2),[field16])
end),2)
end
end
end AS [CS PRODUCT_PRICE]
January 9, 2008 at 8:18 am
Yes, G. I got it working. You filled in some missing gaps for me. Thanks again.
January 9, 2008 at 8:31 am
Adam, thank you very much.
I used your code, but I needed to make several changes.
1) In the 'like' comparison - changed '!%' to '%!%%'
2) In the CHARINDEX - changed CHARINDEX([field16],'%') to CHARINDEX('%', [field16])
3) You had an extra parenthesis ')' in the last Case statement - changed 'end),2)' to 'end,2)'
I really don't know what I'm doing!!!
Thanks again.
January 9, 2008 at 9:10 am
1) In the 'like' comparison - changed '!%' to '%!%%'
2) In the CHARINDEX - changed CHARINDEX([field16],'%') to CHARINDEX('%', [field16])
3) You had an extra parenthesis ')' in the last Case statement - changed 'end),2)' to 'end,2)'
You are absolutely right, I forgot to change the syntax between instr and char index. The parameters reverse. :hehe:
3) You had an extra parenthesis ')' in the last Case statement - changed 'end),2)' to 'end,2)'
There was really no way for me to test the syntax, other than the parse button, which it passed.
I am glad everything worked out. Are you getting desired results with the query?
January 9, 2008 at 11:35 am
Hi Adam,
So far looks good.
Complete testing will take some time, though.
All the best.
January 11, 2008 at 6:44 am
Hi Adam,
Turns out that on item 3 you did not have an extra parenthesis. I re-typed the query and did not copy/paste yours. I missed a parenthesis.
The query produces the correct results.
BTW, I meant in no way that you do sub-par work.
I'll probably need further help on a different query. It's a really ugly query that I think I need to do a self-join. I've been struggling on it for two days now. Will give it another day before posting.
All the best.
January 11, 2008 at 7:15 am
BTW, I meant in no way that you do sub-par work.
I took no offense to any of your comments, I was simply explaining. Sorry if I came off the wrong way.
If you do decide you need help with any more queries, post them and we will see what we can do.
Thanks,
Adam
January 11, 2008 at 7:17 am
Oh, forgot to thank you for the feed back :D. It is nice to hear that everything worked out. Some posters just take the code and we never hear back from them, until another problem arises.
-Adam
January 11, 2008 at 7:30 am
Since MS Access munges SQL code so bad, I've found it useful to run the Access SQL through the Instant SQL Formatter at:
http://www.wangz.net/gsqlparser/sqlpp/sqlformat.htm
It is not without some flaws*, and it won't convert to T-SQL, but I find it invaluable to start work on some of my old convoluted Access queries.
* Does not recognize "with owner access"
* Requires brackets around some words (e.g. DESC used as a column alias) which Access does not insist on.
January 11, 2008 at 9:28 am
Thanks, Jim.
I've been wondering about a SQL formatter or translator for some time. Have never come across one yet...until now.
January 11, 2008 at 10:08 am
This formatter is good and FREE :D. You can choose the online or desktop version. You can always use the online version, but I believe the desktop version expires at the end of this month. However, the new desktop version will be available in March.
Happy coding.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply