SQL; is it worth learning it? Applicable for SAP and VBA?

[adh2]

Hi,


This is a very, very general question about SQL, databases and SAP.
–I'm working in business development in a small company where SAP is being used. I'm not working much with the SAP yet, but I would like to access the information hidden therein for data analysis / BI purposes (as well as for the fun of it).

Also, doing some work in Access made me interested in databases, so now I want to learn more about this, and the wealth of info in SAP is very appealing. (I think of SAP as an ultra-complicated version of an Access database... probably very simplistic.)

However, since I know so little about these things, I don't even know where to start learning. Therefor, I'd like to ask for your general input:

-SQL; is it worth reading a book about it? Is it useful for connecting Excel with Access, or Excel with SAP? If yes, can you recommend a book?
-SAP; I understand that SAP can be based on either SQL server or Oracle, is the language SQL used in both cases? There is apparently a script recorder in SAP, does this render SQL knowledge superfluous?
-Can Excel macros retrieve say, sales data from SAP to use in a PowerPivot model and create reports automatically?


My very lacking knowledge probably renders my questions somewhat stupid, but any input on Excel / BI / SAP / SQL / database applications, and tips about learning more about these things, is very welcome.


Best,
Alfred

[AB33]

Are you referring to SAP- an accounting system or ERP to be precise?
SAP is not a database, but an application which use database as it is back-end to store data. SQL is a language which helps you to access, modify and retrieve what is on data base. SQL is a general term in data base lingual. TSQL (Microsoft proprietary-modified version of SQL), you have Oracle, MYSQL and so on.

What do you want to do with SQL? Are you going to work as data analyst or data base administrator?
If you are going to work with large data, or wish to access data stored in some form of table (Even excel could be loosely considered as tabular) knowledge in SQL will come handy.

[adh2]

Hi AB33,

Thank you for your reply.

I'm referring to SAP ERP. I won't really work as a data analyst, but I guess I could twist my work more towards that direction. Certainly no database admin job. As I understand it, these versions of SQL are fairly similar? I've seen there are plenty of books on SQL in general.

You say knowledge in SQL will come handy for working with large data, but do you know if it is possible, for instance, to have an Excel macro run queries to SAP tables using SQL?

[AB33]

Yes, but my memory is fading away as I have not used SAP for a decade. I think I have seen people linking excel with SAP.
People who tend to do SAP analyst or consultancy work have a good knowledge and understanding of database applications and more likely to use SQL. If you record your actions in access, you get and see the SQL code.
Not all databases use SQL, but majority do-DB (IBM), TSQL(MS),PSQL(Oracle) Sybase and Maria are known and commonly used in commercial industry.
SQL is the standard language, developed and managed by a body. If you learn the general SQL, it is easy to jump in to the modified versions of SQL server, Oracle and others.
You can access in excel many data stored in many database applications, such as in SQL server, Oracle and MYSQL. You can use Excel's built function for fairly normal functions like getting a table. If you want to do refine and go deep in to databases, you need to understand how to retrieve these data from their source and hence need some basic understanding of SQL

[judgeh59]

Howdy;

I don't use SAP where I work. We use JDE which is an Oracle product, sort of. I use VBA all the time to pull data out of the Oracle DB. You just have to setup the connectors and, in Oracle, have a username and password into the DB. I create an XL File in which the Auto_open() is in VBA and this launches as soon as the file is opened. I "collect" my data, massage it how I need it and then email directly from VBA. I would "ASSUME" that as long as you can connect to the DB you can create an SQL in VBA to pull your data.

Have fun

[AB33]

Hi Ernest,
JDE is vey similar to SAP as both are ERP.
What kind of data do auto collect from JDE? Is it simply SELECT and Filter or sort statements? Do you do all the heavy lifting work in JDE and simply import the data in to excel or do you do SQL statements in excel?

[judgeh59]

In general I pull data on material/Inventory ( I'm an Inventory Analyst) . I'm really into using procedures so my "Main"/Auto_Open() is really a series of calls to procedures. So, something like GetDate, UpDateData, SendEmail. The nice thing is it makes it mostly re-usable. The GetData opens the DB Connection using username and password and a few other things, I create a string for the SQL Statement (that I created using a different program), that gets collected into a recordset and then I read the recordset into an XL worksheet. Then manipulate it, if I need to and email it off. I have tried in the past to use DB connectors and just refresh the connection but it has been problamatic.

If you want to see some code just let me know and I can upload a file with some dummied up stuff....

[adh2]

JB33, thank you for the input again. It seems like SQL is something that will be good to know, sooner or later, so I've decided to pick up a book. Good to have a reference the day it becomes needed, anyhow.

Judgeh59, that’s also very interesting to hear. It convinces me even more that a bit of SQL is good to have. I’d love to see a bit of code, especially how you integrate the SQL parts, if you have time for it.

Alfred

[judgeh59]

I have a template WS and I delete any data so I update it. this code will not work directly because I have changed names so I don't get in trouble with work....

HTH...

and, of course, there are other ways to do the same thing....

Sub GetCF()

    Dim OraDatabase As Object
    Dim OraDynaSet As Object
    Dim OraSession As Object
    Dim qryStr As String
    Dim Cntr As Integer, ThisMonth As Integer, x As Integer, LastRowCal As Integer, MonthNQtr As Integer, Mon2Add As Integer
    Dim LastMonthQtr As Integer, y As Integer, test1 As Integer, LastDay As Integer
    Dim JDate As Single, JDay As Single
    Dim MyFY As Variant
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.DisplayStatusBar = True

    Application.StatusBar = "Getting CF records from JDE. "
    
    EndDate = InputBox("Enter Julian Date")

    JDate = EndDate

    Set OraSession = CreateObject("OracleInProcServer.XOraSession")
    Set OraDatabase = OraSession.OpenDatabase("JDPD", "username/password!", CInt(0))   ' JDPD is the name of our entry in the TNSNames.ora file - this gives the Socket info for the DB

    ' this is the SQL itself
    qryStr = "select TRIM(mf.mflitm) Part_Number, "
    qryStr = qryStr & "SUBSTR(TRIM(mf.mflitm), -3) MPF, "
    qryStr = qryStr & "TRIM(im.imdsc1) Description, "
    qryStr = qryStr & "TO_DATE(TO_CHAR(TO_NUMBER(SUBSTR(mf.mfdrqj,1,1))+ 19) || NVL(SUBSTR(mf.mfdrqj, 2, 5),'00001'),'YYYYDDD') Request_Date, "
    qryStr = qryStr & "mf.mffqt Qty, "
    qryStr = qryStr & "QC.cost QC "
    qryStr = qryStr & "from proddta.f3460 mf, "
    qryStr = qryStr & "proddta.f4101 im, "
    qryStr = qryStr & "(SELECT (F4105.COUNCS / 10000) COST, "
    qryStr = qryStr & "F4105.COLEDG LEDGER, "
    qryStr = qryStr & "F4105.COMCU MCU, "
    qryStr = qryStr & "F4105.COITM SHORT "
    qryStr = qryStr & "FROM PRODDTA.tablename1,tablename2 "
    qryStr = qryStr & "WHERE F4105.COLEDG IN ('QC') "
    qryStr = qryStr & "AND F4105.COMCU = LPAD('000', 12)) QC "
    qryStr = qryStr & "where mf.mfmcu = lpad('000',12) "
    qryStr = qryStr & "and mf.mfitm = im.imitm "
    qryStr = qryStr & "and mf.mfitm = qc.short "
    qryStr = qryStr & "and mf.mfdrqj <= " & JDate

    Set OraDynaSet = OraDatabase.DbCreateDynaset(qryStr, 0&)

    Cntr = 2
    
    Application.StatusBar = "Loading CF Data..."
    
    Sheets(WSCF).Activate
    
    Range("A2:F2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents

    If OraDynaSet.RecordCount >= 1 Then
        With OraDynaSet
            .MoveFirst
            Do While Not .EOF
                Cells(Cntr, 1).Value = OraDynaSet.Fields(0).Value
                Cells(Cntr, 2).Value = OraDynaSet.Fields(1).Value
                Cells(Cntr, 3).Value = OraDynaSet.Fields(2).Value
                Cells(Cntr, 4).Value = OraDynaSet.Fields(3).Value
                Cells(Cntr, 5).Value = OraDynaSet.Fields(4).Value
                Cells(Cntr, 6).Value = OraDynaSet.Fields(5).Value
                Cntr = Cntr + 1
                .MoveNext
            Loop
        End With
    Else
        MsgBox "You Messed Up"
        Exit Sub
    End If

    Cells(1, 1).Select

    Set OraDynaSet = Nothing
    Set OraSession = Nothing
    Set OraDatabase = Nothing
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.StatusBar = ""

End Sub

MODS: if this is getting to much like a real question that should be in a different area. I apologize and let me know....thanks....

[AB33]

Hi Ernest,
Thanks!
Hi Alfred,
I am not a big fun of books as I hardly read them. There are some SQL books and would recommend "SQL by Ben Forta". It is a small book and you can squeeze in your pocket. It can not be used as a reference book, but stuffed with wealthy materials.

[snb]

I am not a big fun of books as I hardly read them.

Sounds like a tautology....

[AB33]

Sounds like a tautology....

Yes, it is called "Compulsive purchasing disorder"

[adh2]

judgeh59, thank for the code, that's really interesting to see how you work SQL into it. (Also I like the "You messed up" message box, I never dare to be that outspoken in my own macros... If they don't work, then it's most of the time my own fauit )

AB33, i see that Ben Forta has written several books on SQL, so I will check them out. It might be hard to digest a 600 pages book on a language I don't know if I'll use, so that's a good start for sure.

[Marius Titulescu]

It's applicable up to a degree.
Let's just say I don't regret learning it.
___________________________________
Marius from SAP programming