September 12, 2022 at 4:22 am
Is it possible convert Excel formula to SQL?
I mean like this site for convert VB.NET to C#.
I have to write lots of complicated excel function in format of SQL function like this:
=VLOOKUP(D4,Table25,MATCH(ِDATA!AB$3,Table25[#Headers],0))*IF(OR(E4="W",E4="KW"),(2*((G4*VLOOKUP(D4,Table25,3,0))*IF(D4="COF",SUM(H4,I4),MAX(H4,I4))*VLOOKUP(D4,Table25,2,0))+2*(F4*IF(D4="COF",SUM(H4,I4),MAX(H4,I4))*VLOOKUP(D4,Table25,2,0))),0)
and it is very hard and time consuming.
Also this question asked in this place.
September 12, 2022 at 7:33 am
I don't think there is any chance of finding something like this. C# and VB.NET are functionally equivalent, Excel and SQL Server are not.
A manual rewrite is required.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 12, 2022 at 1:52 pm
I'm not aware of anything that would allow you to do that, but if it helps:
VLOOKUP is the equivalent of a JOIN
MATCH is the equivalent of a WHERE
SUM and MAX are the equivalents of their aggregating functions in SQL so will require either a GROUP BY clause or the SUM() OVER() syntax,
That particular formula is going to be very difficult because you are trying to find a column (field) based on the dynamic value in DATA!AB$3. This means you would either need to write and execute dynamic SQL, replacing a placeholder with the value from DATA!AB$3 or convert Table25 into an EAV table using UNPIVOT and then finding the correct row using the unpivoted column name.
Also remember that SQL is working on SETs whereas Excel is working on a single value at a time to the two processing models are very different.
Perhaps some context around what your business needs are, we could point you in a different direction. E.g. rather than trying to pull each field across separately, provide you with some SQL that you can execute from VBA and generate the final required results in a single hit. or failing that, do the iterative processing in VBA.
One other option you might want to look at in Excel is User Defined Functions. If your formulas all follow a similar pattern (for example they all look up on TableNN, but take a different DATA field as their reference, you can write the logic of the formula in VBA as a UDF and just pass in the source reference (D4) and the lookup reference (DATA!AB3) . The formula would then look like
=UDF_GetCalcs(D4, DATA!AB3, Table25)
anyone looking at it will have absolutely no context as to what it is doing, but it does make it much easier to parse the fomulas to see where you have got one wrong.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply