Query Syntax





SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

Share tech news, updates, or what's on your mind.

Sign up to Post

I have a SQL statement that works when I enter it manually in SQL Management Studio, but fails when I run it in a C# program. The statement is:
select CaseHistory.CaseNo, CaseInvoice.InvoiceNo, CaseHistory.Code, Paid = cast(Paid as decimal(10,2)), VoucherDate, 
PaymentNo, CaseDebtCollection.RegisteredDate, Debtor.CountryCode, Client.Country from CaseHistory
inner join CaseDebtCollection on CaseDebtCollection.CaseNo = CaseHistory.CaseNo
inner join dbo.Debtor on dbo.CaseDebtCollection.DebtorNo = dbo.Debtor.DebtorNo
inner join dbo.Client on dbo.CaseDebtCollection.ClientNo = Client.ClientNo
inner join CaseInvoice on CaseInvoice.CaseNO=CaseHistory.CaseNo
    LEFT JOIN TempCHUnik ON (TempCHUnik.Unik = CaseHistory.Unik)
WHERE (CaseHistory.Code='301' or CaseHistory.Code='400') and
ClosingDate = '' and CaseDebtCollection.Workflow not in ('0012','0015','0016') and CaseInvoice.InvoiceLineNo=1 and TempCHUnik.Unik IS NULL

Open in new window

When running it in a C# program I get this error in the log:
Invalid object name 'TempCHUnik'.   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
The table TempCHUnik is there both before and after, so this is very strange.
I use this code segment to get data from the SQL Server, and it works perfectly with a different sql statement:
      cmd = new SqlCommand(sql, con);

                DataTable table = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);

                dr = cmd.ExecuteReader();

Open in new window

Cloud Class® Course: Microsoft Windows 7 Basic
LVL 12
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.


The below SQL query works for me, but I have one issue.  Which is that if it happens to find a second or third "contact" (this part of the join:  "LEFT JOIN contact ON de_rowid = co_rowid_debtor AND co_done_date between CONVERT(DATE,'@tvarDatef',103) and CONVERT(DATE,'@tvarDatet',103)" ) it will return duplicated data.

,CASE WHEN contact.co_description IN ('@tvarDirectDebitClaim', '@tvarBPAYClaim') THEN 'CLPD' 
WHEN contact.co_type = '22' THEN 'SMS1' 
WHEN contact.co_description = 'Copy' THEN 'CONC' 
WHEN contact.co_description = '@tvarTransVar' THEN 'TRAN' 
WHEN contact.co_description = 'HARD' THEN 'FINH' 
WHEN contact.co_description = '@tvarInfo' THEN 'INFO' 
WHEN contact.co_description = 'MRT' THEN 'RTS' 
WHEN contact.co_type = '1' and co_user_1 = 'Invalid' THEN 'NOAN' 
WHEN contact.co_type = '1' and co_user_1 = '@tvarMD' THEN 'LM' 
WHEN contact.co_type = '1' and co_user_1 = '@tvarMUd' THEN 'DISC' 
    FROM blobs
    JOIN debtor ON de_rowid = bl_rowid_debtor
    JOIN client ON cl_rowid = de_rowid_client
    LEFT JOIN contact ON de_rowid = co_rowid_debtor AND co_done_date between CONVERT(DATE,'@tvarDatef',103) and CONVERT(DATE,'@tvarDatet',103)
    WHERE cl_number ='2005' and de_group_id < '1' AND bl_date between CONVERT(DATE,'@tvarDatef',103) and CONVERT(DATE,'@tvarDatet',103)

Open in new window

this is what it returns:
4961674      471146      INFO      NEW STATUS
I had this question after viewing After SQL 2016 Upgrade SA Account Keeps Getting Locked by Backup Jobs.

I am getting the exact same error... except there is no message in the logs about a failed login attempt  by the SA account. And what is even more interesting, the SA password was never changed...

I am administering an isolated SQL Server database, managing a specific task for my company. This database takes data from other sources and consolidates it for marketing.

Two years ago, we updated the server and migrated the database, using SQL 2012 Standard. The server is a Dell

Last month, with the assistance my our corporate DBA, we upgraded from 2012 to 2016 Standard SP1.

Since then, I have been getting these messages about the SA account being locked out... well, why would that do that when the SA account password has never been changed? I can log into SSMS using that password... I can run jobs using that password, as soon as the SQL Server agent tries to run it, the errors fly.

This morning, I have updated the maintenance jobs to use a direct connection to the server vs the "local connection" default. I will test that shortly.

This is one of two problems... the other is different and will be under a different title.

Thank you all in advance!

Need help with a SQL Server select to join tow tables.  There are duplicate rows between the two tables so I want include the duplicate row from Table A.  A duplicate row is a the same CatNum and Attribute ID.  So join two tables and resolve duplicates by only including the duplicate row from table A.

CatNum        Attribute ID     Description
999                444                   Test
888                211                    Test 2

Table B
CatNum      Attribute ID
999               444
500               422

Expected Results
CatNum        Attribute ID     Description
999                444                   Test              
500                422
888                211                    Test 2
I have a project for our shipping department - I need to created in SQL query that will provide the following results;
1. group by style & color (example: style:123R12, color: BLK) and create an alpha sequence (A) which 123R12BLK = A

2. if style is the same but color is different (example: style:123R12, color: BLUE) it should be sequence (B) which 123R12BLUE = B and so on.

3. Sequence could go as far A thru ZZZ
Hello there,

I've working in the following script for the whole day, but I'm getting the following error:

ORA-01843: not a valid month
01843. 00000 -  "not a valid month"

Basically, I have to extract a date from a char type field called 'text' if it meets some conditions: if name field starts with 'P' otherwise, it should take another field which is date type.

supplier_id, name,SUM(net_amount) Net_Amount
FROM supplier_table
     WHEN name like 'A%' and SUBSTR( text, 4 , 1 ) = '/'
                  THEN to_date(SUBSTR(text,5,8),'MM-DD-YY')                -- here, this field char type format 12-30-18
      WHEN name like 'B%' SUBSTR( text, 4 , 1 ) = '-')
                   THEN to_date(SUBSTR(text,4,8),'MM-DD-YY')               -- here, this field char type format 12-30-18
      WHEN name like '%C%'
                   THEN invoice_date                                                            -- here, this field date type format 30-DEC-18
      end) between TO_DATE('&START_DATE', 'mm/dd/yy') and TO_DATE('&END_DATE','mm/dd/yy')
group by supplier_id, name
order by name

I'm so confused about these type of date formats.
My database save date type with this format 01-JAN-18

Thanks for help.

I'm looking to optimize the execution plan for a specific SQL statement, so I'm looking at how to achieve this using SQL profiles.

What I'm looking to do is add an index hint to a SQL statement, but this would need to be on the basis of matching against the first half of the statement as the number of predicates following this will be subject to change.

Is it possible to set up a profile based on a pattern match like this? Or should I be looking at something else to achieve this?

Any help much appreciated.
I need help with a SQL select that will remove a static value from a column value.  

ID           Name
123        http://attribute?id=eco_123
233        http://attribute?id=eco_444

Expected results
ID           Name
123        eco_123
233        eco_444

I just want to remove "http://attribute?id=" value from NAME column.
How do i enter array value into database.

My Sql

$stmt = new Database();
					//Inserting check state.
					$query = "INSERT INTO a_test (charityID, contactID, chk_name) VALUES (:clientId, :contactId, :chkName) ";
					$stmt->query( $query );
					$stmt->bind( ':clientId', $clientId );
					$stmt->bind( ':contactId', $contactId );
					$stmt->bind( ':chkName', $_POST['chkStateCollection'] );

					$result = "Record added";

Open in new window

Result -

SQL result
The Array -

The Array, Normal Array
the field chk_name needs to equal that of the array
The idea is from a Form a combo box is selected and the button is pressed once the selection is complete. when the button is pressed an Append Query is ran and appends to a table using the value that was just selected in the combo box along with a date and time stamp.

 Here is the Coder for the Button
Private Sub SubmitEvent_Click()

DoCmd.OpenQuery "Event"
DoCmd.SetWarnings False

End Sub

Open in new window

The SQL for the Append Query is

INSERT INTO TroubleTicket ( TroubleCallBody, DateReported, TimeReported )
VALUES ('Set hero condition ' & [Hero].HeroCon, date(), now());

Open in new window

where Hero is the table and HeroCon is the field the combo box is stored it. I thought of pulling the value direct from the combo box but wasn't able to get it to work.

when I run the query it prompts me for user input with a message box. I'm not sure what I am doing wrong.
Cloud Class® Course: Microsoft Azure 2017
LVL 12
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

There is a question on https://www.experts-exchange.com/questions/28687829/ADO-NET-Entity-Framework-LINQ-on-Visual-2013.html about whether to use Entity Framework or ADO.net for large databases.  The accepted answer is:

Again depends on the use case and what you intend to do with retrieved data. If you intend to display it in a control such as grid then it makes sense to use SQL to pull that data. Entity framework, as the name suggests, is more useful when you are dealing with entities in your code.

What is meant by an entity?
I'm used to connecting to a database with ADO.net and doing CRUD operations using stored procedures.  When is this approach better than Entity Framework and when is Entity Framework better?
I'm using C# for Visual Studio 2017
I need to update my mysql table for tax codes in woo commerce 3.4.  What query would I used to put in tax code PH400930 for all products in the essentials_oil category?

Hi all,

I have a new server with windows 2016, and I need to copy across an small number of ASP websites.

So, starting with a single website, I installed all the necessary files and ran the site. When loading a page that connects to an SQL server database, an error occurs and I get a link to a website assuming that ASP is not activated.

To verify that it is, I have loaded a test .asp page and it does load correctly.

I think the issue is related to MDAC, I have tried to install version 2.8 but I can't tell if it actually did install or not. I can't confirm this is the issue but do believe it is database related.

Has anyone else encountered a similar issue, and resolved it?

Any help is appreciated.

Hi All

Just a clarification on DSN less connection, I have assembled the code below by first creating a file called AccountingFileDsn.dsn and the final code looks like below:

ODBC:Driver = ODBC driver 13 for SQL Server,SERVER = CHRIS\SQLEXPRESS;Trusted_Connection= Yess;

Well the above string works very well on my computer I'm able to do anything on the SQL Server Back-end no issue at all.

Clarifications required:

(1) Suppose I deploy my application to another computer called PETER (Server after installing Sql server express),will the above connection work? Since the new string was more likely to look like below:

ODBC:Driver = ODBC driver 13 for SQL Server,SERVER = PETER\SQLEXPRESS;Trusted_Connection= Yess;

For sure on all work stations the same ODBC driver 13 for SQL Server and Ms access run-time  will be installed, but again how is it going to locate the database Accounting without directing it ? I know this sounds very funny, I'm sorry I just want understand this ODBC thing, I will appreciate any further notes as well.


I have the following SQL query,

   SELECT Nodes.[Uri]  
    , Nodes.[DisplayName]  
    FROM Orion.Nodes AS Nodes

Open in new window

and its output is something like the following:
Uri                                                            DisplayName
swis://LLC1CCVORION01.corp.lcl/Orion/Orion.Nodes/NodeID=1475	aces.domain.com
swis://LLC1CCVORION01.corp.lcl/Orion/Orion.Nodes/NodeID=193	b1-a01-sw03.domain.lcl
swis://LLC1CCVORION01.corp.lcl/Orion/Orion.Nodes/NodeID=624	B1-AS01.domain.lcl

Open in new window

I wonder how can i query the sring "SQL" or "sql" within the DisplatName  ?

Thanks for your help

I have the following MySql select which shows those records where the customer uploaded more than one picture for the same product:

 select ao_autoinc1 ,count(*) db  from _autoinc_join
 left join product on(product_id=ao_autoinc1)
   left join product_pictures on(picture_product_id=ao_autoinc2)
  group by ao_autoinc1

having count(*)>1

The product table and the product_pictures are connected via _autoinc_join table (because many products can share the same picture).

I need to delete the pictures in such a way that only on picture should remain per product.
e.g. no matter if its 67 pieces or 2 pieces, it should remain 1 picture.

How can I do that?

Thank you!
SQL row merge / concatenate

I have following query -

SELECT        dbo.v_Contact_AllFields.Contact_ContactId, dbo.v_Contact_AllFields.Contact_Christian, dbo.v_Contact_AllFields.Contact_Surname, dbo.v_Contact_AllFields.Contact_FullName, dbo.v_Contact_AllFields.Contact_Title,
                         dbo.v_Contact_AllFields.Contact_HomeAddressStreet, dbo.v_Contact_AllFields.Contact_HomeAddressCity, dbo.v_Contact_AllFields.Contact_HomeAddressCounty, dbo.v_Contact_AllFields.Contact_HomeAddressPostCode,
                         dbo.v_Contact_AllFields.Contact_HomeAddressCountry, dbo.v_Contact_AllFields.Contact_HomeEmail, dbo.v_Contact_AllFields.Contact_EMail, dbo.v_Contact_AllFields.Contact_HomeAddressTelNo,
                         dbo.v_Contact_AllFields.Contact_MobileTelNo, dbo.v_Contact_AllFields.Contact_WorkTelNo, dbo.v_Contact_AllFields.Contact_Comments, dbo.ContactSkillsQuery.SkillId, dbo.v_Contact_AllFields.Contact_TypeOfWorkRequired,
                         dbo.v_Task_AllFields_WithContactInfo.DisplayDescription, dbo.v_Contact_AllFields.Contact_DateOfBirth
FROM            dbo.v_Contact_AllFields INNER JOIN
                         dbo.ContactSkillsQuery ON dbo.v_Contact_AllFields.Contact_ContactId = dbo.ContactSkillsQuery.ContactId INNER JOIN
                         dbo.v_Task_AllFields_WithContactInfo ON dbo.v_Contact_AllFields.Contact_ContactId = dbo.v_Task_AllFields_WithContactInfo.Contact_ContactID
WHERE        …
I have the following code to copy some data over:

Update Documents Set EAD number
SET a.Ead = b.Description
FROM bluedot_eimmigration.dbo.Users a
    INNER JOIN [bluedot_eimmigration].[dbo].[zzz_eimmigration_customdocuments] AS b
        ON b.Userid = a.userid
WHERE b.DocName = 'EAD';

Open in new window

The problem is that the field "EAD" is only 20 characters long and some of the 'b.Description'  data field are longer. I want to cut them off at 20 characters.
I tried the code below but didn't work:

SET a.Ead = CAST(b.DescriptionAS VARCHAR(20)) AS Description,  
FROM bluedot_eimmigration.dbo.Users a
    INNER JOIN [bluedot_eimmigration].[dbo].[zzz_eimmigration_customdocuments] AS b
        ON b.Userid = a.userid
WHERE b.DocName = 'EAD';

Open in new window

Hi Expert,

I have a following requirement could anyone please suggest on this!

Assume a Sales table with columns for Sales_Amount, Customer_Name, and Region.
There are other columns and indeed other tables, but that’s not important.
 Give me the top 5 customers by total Sales_Amount, within each Region.
 The number of regions may vary over time, and your query needs to keep working as regions come and
 go (i.e., you don’t get to handle this with a CASE statement). You may assume there are more than
 5 customers with sales for each Region. So if I have 3 unique values of region in the table,
 I should get 15 rows, 5 for each region. But if there are 5 regions, I should get 25 rows.
 Write a Oracle-SQL script for this. No JOINs are needed, you’re only using one table.
You can use as many queries, and as many Temp Tables, as you need.

create table sales (customer_name varchar2(10), region varchar(20), amount number(10));


insert into sales values ('ahmed2', 'Asia', 40000);
insert into sales values ('ahmed3', 'Asia', 60000);
insert into sales values ('ahmed4', 'Europe', 210000);
insert into sales values ('ahmed5', 'Europe', 30000);
insert into sales values ('ahmed6', 'Europe', 210000);
insert into sales values ('ahmed8', 'Europe', 5000);
insert into sales values ('ahmed9', 'Africa', 8000);
insert into sales values ('ahmed10', 'Africa', 86000);
insert into sales values ('ahmed11', 'Africa', 99000);
insert into sales values ('ahmed12', 

Open in new window

Cloud Class® Course: Ruby Fundamentals
LVL 12
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Hi there!

I do not know how to use "ELSE IF" properly...

My situation is the following:

 IF @MaterialID in (411, 412, 413, 414, 415)  then I want to RETURN 1 , always.
 ELSE, that is, if @MaterialID is not in the list above, and only in this case, then there is a decision to make:

If (@ param1 > @param2) then return 2
If not, that is, @MaterialID is not in the list above AND @param1 > @param2 is false, then return 3

Could someone put this in SQL form, using "ELSE IF" whenever possible, so that I learn the structure?

Hi Expert,

I Have a following procedure Could anybody please guide me how can i replace the IF-ELSE part with a Cursor?

PROCEDURE check_mandatory_dv_claim
( pi_claim_id          IN claim_request.claim_id%TYPE, -- Claim Reg ID (required)
  pi_request_id        IN claim_request.request_id%TYPE,
  pi_claim_obj_seq     IN claim_objects.claim_obj_seq%TYPE, -- Claim Object Sequence ID (required)
  pi_inspect_no        IN claim_objects_dscr.inspect_num%TYPE, -- -- Claim Inspect Number (required)
  pio_check_value      IN OUT VARCHAR2,
  pio_sucessful_flag   IN OUT VARCHAR2,
  pio_Err              IN OUT SrvErr) -- passing back the error code //SELECT OBJECT_NAME,OBJECT_TYPE FROM USER_OBJECTS WHERE OBJECT_TYPE='TYPE' AND OBJECT_NAME='SrvErr';
    l_SrvErrMsg       SrvErrMsg; -- stores the desired text for an error
    l_count           Number;
	l_err_message     srv_messages.msg_id%TYPE := 'USER_MESSAGE_RULE.NO_DV';
	l_func_name       VARCHAR2(1000 CHAR) := 'BRTM_CUST_CONSISTENCY_GEN.check_mandatory_dv_claim';
  pio_check_value := 'Y';     --for AMX
  pio_sucessful_flag := 'Y';  --for AMX
       IF pi_inspect_no is NULL                 //CURSOR C1 IS SELECT * FROM T1 WHERE COL1=10;
            SELECT COUNT(1)			//OPEN C1; FETCH C1 INTO V1,V2; CLOSE C1
                INTO l_count
                FROM insis_gen_v10.claim_objects_dscr
                WHERE claim_id = pi_claim_id
                AND request_id = pi_request_id

Open in new window

For the insert and update statements below, I need to check field canonical_url_id for an existing record value of 0 or empty... if it is, and if no other value has been entered, I need to replace the 0 or empty with NULL. How to, please?

              $existing = $db->runQuery("SELECT COUNT(*) AS cnt FROM dynamic_url WHERE qm_url_id = ".$qm_url_id);
              if($existing[0]['cnt'] == 0) {
              	  $db->runQuery("INSERT INTO dynamic_url (qm_url_id, title, meta_keywords, meta_description, heading, canonical_url_id)
              	  	             VALUES (".$qm_url_id.", '".addslashes($data['title'])."', '".addslashes($data['meta_keywords'])."', 
              	  	                     '".addslashes($data['meta_description'])."', '".addslashes($data['heading'])."', 
              	  	                     ".intval($data['canonical_url_id']).")", true);           
              } else {
              	  $db->runQuery("UPDATE dynamic_url SET title = '".addslashes($data['title'])."',
              	                                    meta_keywords = '".addslashes($data['meta_keywords'])."',
              	                                    meta_description = '".addslashes($data['meta_description'])."',
              	                                    heading = '".addslashes($data['heading'])."',
              	                                    canonical_url_id = ".intval($data['canonical_url_id'])."
              	                 WHERE qm_url_id = 

Open in new window


I have developed a large number of Access app with MS SQL Server or Sybase or Oracle as backend, and with a new app using Access 2016 I have a problem that I NEVER had before.  

The setup is simple:
- Access 2016
- linked SQL Server tables
- SQL Server 2012
- accessing the tables using DAO

Now I have one SQL Server table where I need to insert in rapid sequence a small number of rows (around 40). This is a table that will hold changes made to another table (let's call that latter one the main table), so it is a history table of sorts. The table has only few fields, as I store in it each modified field of the main table, with original and modified value.  A row has around 800 bytes. What happens is that after a couple of successful inserts, suddenly and without warning, I get into a timeout of 30 seconds, and then get an error saying "ODBC call failed", and each subsequent trial at an insert gets the same error. Seems to be a locking problem but why after about 4 or 6 inserts ?

I have tried 2 versions for the history table:
- Key of identity type
- key self-managed (because I thought the problem was with the identity key)
In both cases I get the same symptoms: a few successful inserts , then stalling, even if I step manually through the insert process !

The weird thing is that I have a similar table, with an identity key, where I insert log entries, describing what my app is doing, and that table NEVER had any problems.

And the main table is also …
Format date and then sort by datevalue.  

I am joining three tables and formatting the dates.  I receive error "ORDER BY items must appear in the select list if SELECT DISTINCT is specified." if a column in ORDER BY is NOT found in the SELECT statement.  Hence sorting by CreatedDate and NOT SO.dateCreation.  The problem is that the sort is performed by 01/xx/2017 and 01/xx/2018.  I want the sort to be performed by date and not text.
	SO.Name AS 'SalesOrder', 
	CONVERT(VARCHAR, SO.dateCreation, 101) AS 'CreatedDate',
FROM SalesOrder SO

Open in new window

Insert into pdo table not working and no error.

$stmt = new Database();
					//check to make sure there is a record to update 
				$query = "SELECT chk_name FROM a_test WHERE charityID = :clientID AND contactID = :contactId";
				$stmt->query( $query );
				$stmt->bind( ':clientID', $clientId);
				$stmt->bind( ':contactId', $contactId );
				$result = $stmt->all();

		if($result == null){
			foreach($_POST['chkStateCollection'] as $chkState) {
				$stmt = new Database();
					//Inserting check state.
				$query = "INSERT INTO a_test (charityID, contactID, chk_name) VALUES (:clientId, :contactId :chkName) ";
				$stmt->query( $query );
				$stmt->bind( ':clientId', $clientId );
				$stmt->bind( ':contactId', $contactId );
				$stmt->bind( ':chkName', $chkState );
				$result = $stmt->all();
		} else {
				$testreply = "no rows detected";

Open in new window

Ok so what this code is doing and suppose to do.  I am checking to see if a record exists first and this part works as expected if record exist goto else if not then create a record.

Now the record is created with two ID which is stored in a session they seem fine. now im not sure if the last one is the issue as i tried hard coding and same response but the field chk_name is populated by the forach which gets it details from ajax post array which looks like this -


Open in new window

the problem is when it gets to the INSERT it doenst add and the ajax response is not successful with not errors. so im lost on what is the issue.

Query Syntax





SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.