topic Re: What is SQL Query in Application Development and Automation Discussions

What is SQL Query

rahul2000 — Tue, 18 Dec 2007 06:23:24 GMT

Dear all,

what is meant by SQL Query?

I have a report which is directly in production and not in development.its a user created program.

some one said that it is SQL query.

What is meant by it ?

Is there any name for that SQL query?

If yes,how can I find it?

Re: What is SQL Query

Former Member — Tue, 18 Dec 2007 06:28:57 GMT

Hi,

SQL Query means select statement u r getting the data from the database table.

Standard SQL

SQL (Structured Query Language) is a largely standardized language for accessing relational databases. It can be divided into three areas:

· Data Manipulation Language (DML)

Statements for reading and changing data in database tables.

· Data Definition Language (DDL)

Statements for creating and administering database tables.

· Data Control Language (DCL)

Statements for authorization and consistency checks.

Each database has a programming interface that allows you to access the database tables using SQL statements. The SQL statements in these programming interfaces are not fully standardized. To access a specific database system, you must refer to the documentation of that system for a list of the SQL statements available and their correct syntax.

The Database Interface

To make the R/3 System independent of the database system with which you use it despite the differences in the SQL syntax between various databases, each work process on an application server has a database interface. The R/3 System communicates with the database by means of this interface. The database interface converts all of the database requests from the R/3 System into the correct Standard SQL statements for the database system. To do this, it uses a database-specific component that shields the differences between database systems from the rest of the database interface. You choose the appropriate layer when you install the R/3 System.

There are two ways of accessing the database from a program - with Open SQL or Native SQL.

Open SQL

Open SQL statements are a subset of Standard SQL that is fully integrated in ABAP. They allow you to access data irrespective of the database system that the R/3 installation is using. Open SQL consists of the Data Manipulation Language (DML) part of Standard SQL; in other words, it allows you to read (SELECT) and change (INSERT, UPDATE, DELETE) data.

Open SQL also goes beyond Standard SQL to provide statements that, in conjunction with other ABAP constructions, can simplify or speed up database access. It also allows you to buffer certain tables on the application server, saving excessive database access. In this case, the database interface is responsible for comparing the buffer with the database. Buffers are partly stored in the working memory of the current work process, and partly in the shared memory for all work processes on an application server. Where an R/3 System is distributed across more than one application server, the data in the various buffers is synchronized at set intervals by the buffer management. When buffering the database, you must remember that data in the buffer is not always up to date. For this reason, you should only use the buffer for data which does not often change. You specify whether a table can be buffered in its definition in the ABAP Dictionary.

Native SQL

Native SQL is only loosely integrated into ABAP, and allows access to all of the functions contained in the programming interface of the respective database system. Unlike Open SQL statements, Native SQL statements are not checked and converted, but instead are sent directly to the database system. When you use Native SQL, the function of the database-dependent layer is minimal. Programs that use Native SQL are specific to the database system for which they were written. When writing R/3 applications, you should avoid using Native SQL wherever possible. It is used, however, in some parts of the R/3 Basis System - for example, for creating or changing table definitions in the ABAP Dictionary.

The ABAP Dictionary

The ABAP Dictionary, part of the ABAP Workbench, allows you to create and administer database tables. Open SQL contains no statements from the DDL part of Standard SQL. Normal application programs should not create or change their own database tables.

The ABAP Dictionary uses the DDL part of Open SQL to create and change database tables. It also administers the ABAP Dictionary in the database. The ABAP Dictionary contains metadescriptions of all database tables in the R/3 System. Only database tables that you create using the ABAP Dictionary appear in the Dictionary. Open SQL statements can only access tables that exist in the ABAP Dictionary.

Authorization and Consistency Checks

The DCL part of Standard SQL is not used in R/3 programs. The work processes within the R/3 System are logged onto the database system as users with full rights. The authorizations of programs or users to read or change database tables is administered within the R/3 System using the R/3 authorization concept. Equally, transactions must ensure their own data consistency using the R/3 locking concept. For more information, refer to Authorization Concept and Programming Database Updates.

Plzz reward points if it helps.

Edited by: manjari kotta on Dec 18, 2007 7:29 AM

Re: What is SQL Query

Former Member — Tue, 18 Dec 2007 06:35:33 GMT

hi.,

SQL stands for Structured Querry Language.

we write SQL query to retrieve the data from Database Tables and present it in a report.

in SAP we can write 'open SQL / native SQL ' querries.

uasually there will be no name for any query.

Cheers.,

Chandra

Re: What is SQL Query

Former Member — Tue, 18 Dec 2007 06:42:45 GMT

Updating records in the database table from an internal table

table: personel.

data: itab like personel.

Read records from the database table where name is space, into an internal table

select * from personal into table itab

where name = space.

Update name in the internal table to Unknown

loop at itab.

itab-name = 'Unknown'

endloop.

Modify records in the database table. Only records with the same key values as then
internal table is modified

MODIFY personel from table itab.

Updating a single entry

In this example the entry with the key value Customerno = 1 is changed.

table customer.

customer-customerno = 1.

customer-customnavme = 'John'.

UPDATE customer.

if sy-subrc <> 0.

..... No entry with the customerno = 1, add error handling

endif.

Updating multiple entries

Examlpe updating the field zchecked to 'X'

UPDATE zcostcheck set zchecked = 'X'

WHERE zcostcheck-zaar = zaar and

zcostcheck-zmaaned = zmaaned and

zcostcheck-zbukrs = zbukrs and

zcostcheck-zsaknr = zsaknr2 and

zcostcheck-zgsber = zgsber.

Re: What is SQL Query

Former Member — Tue, 18 Dec 2007 06:49:26 GMT

Hi,

SQL Trace is a tool useful for performance analysis.

In this case SQL Trace can be used to show the issued SQL statements and their duration, thus helping to identify inefficient SQL statements.

you can use the SQL Trace when you need to check the behavior of a particular application. This is the following scenario:

You launch the SQL Trace application using ST05 or Test -> Performance Trace in the ABAP Workbench.

You activate the SQL Trace.

You run the application in new session that you want to trace.

You deactivate the SQL Trace.

You set filters optionally.

You evaluate the trace.

You can also evaluate a trace that already exists.

Initial Screen

Calling

You can call the initial screen of the test tool using transaction code ST05 or by choosing Test ® Performance Trace in the ABAP Workbench.

Functions

The following functions are available on the initial screen:

Select trace:

· Select the trace mode SQL Trace, Enqueue Trace, RFC Trace, or Table Buffer Trace. You can select mutliple trace modes simultaneously.

Select trace function:

· Start the trace recording.

· Stop the trace recording.

· Branch to trace list, detailed list, or time-sorted list.

· Branch to Explain SQL to analyze an SQL statement without an explicit trace file.

Trace Status

A trace can only be activated once on any application server. The Trace Status display informs you whether another user in the system has already activated a particular trace.

Analyzing Performance Data

Prerequisites

Once you have switched off the performance trace, you can analyze the data The data is analyzed, even repeatedly, until its trace records are overwritten in the trace file. The trace files are managed by the SAP system. You can therefore keep a trace file for editing and delete it later through the menu Performance Trace->Save Trace, Display Saved Trace, and Delete Saved Trace.

Procedure: Overview

Before you start analyzing the trace records, you must first switch off the Performance Trace. (It is also possible to display a trace without switching it off beforehand. In this case, however, the display procedure is also recorded in the trace file, in accordance with the set filter criteria for the trace.

For more information, refer to:

Stopping the Trace Recording.

Before displaying the trace records, you can use a display filter to specify the records to be edited and the information that you want to look at.

For more information, refer to:

Display Filters.

When you display the trace records, you can choose between a basic list and an extended list. Both lists display an overview of the logged actions and performance data. In addition, you can display a trace list sorted by time.

For more information, refer to:

Displaying Lists of Trace Records.

In both lists – the simple trace list and the extended list – you have the same range of functions for analyzing the listed statements and other performance data.

The time-sorted trace lists is only slightly different from the trace lists mentioned above. For more information on the transaction, process type, client, and user, call up the function Display->User, Transaction and so on…… in the Goto menu.

Please reward me if you feel this useful to you

Regards,

suresh

Re: What is SQL Query

Former Member — Tue, 18 Dec 2007 07:38:32 GMT

hi ,

sql is nothing but the structure query language,which is use in the database. to retrive the partcular data in this case the sql is used . it is noting but the rule to access the perticular data in specific format.

thats set.

the advance of sql is the pl/sql that is nothing but the procedural language /structure query language.