Query Syntax

48K

Solutions

19K

Contributors

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

Hello,
I would like to delete a Table from a database.

Name of the Table = "customer"
Name of the Database = "Sicherung_Thomas"
SQL Server 2000

I tried this:

CString str_Tablelle;
str_Tabelle="customer.dbo.Sicherung_Thomas";
 
//-----------------------------------------------------------
sql.Format("DROP TABLE %s ",str_Tabelle);
//-----------------------------------------------------------

My Error Message is: deleting is not possible because table do not exist in  system catalog

But the table is there....what do I did wrong?
Please help.
Best regards,
Thomas
0
Free Tool: SSL Checker
LVL 9
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

I have a date column that is unfortunately varchar(10) and need to use it in a between statement. I've tried every conversion I could find but always get an error.
select p.person_id, p.last_name, p.first_name,hm.encounterdate,
hm.encountertype, convert(VARCHAR(10), hm.date_px, 120) as date_px, hm.hospitaladmitdate from Histories_Master_ hm
inner join person p on hm.person_id = p.person_id
and convert(VARCHAR(10), hm.date_px, 120) between '2017-06-01'
    AND '2017-06-30'
and hm.encountertype in ('hospitalization', 'emergency room')

Open in new window

1
Background:  I am very new to SQL but here it goes: A serial contains 2 bits of info.  It tells what the "parent" with the first 6 characters, and then tells who the children are with the characters after that.  So e.g. B12345 is the parent, and B12345999, B12345631, B12345110 are the children.  The DB has a column, called Serial_Number and it lists all of this, and there are costs associated with producing this product, which goes through multiple stages (casting, molding, finishing, etc..)  

What I would like to do is sum all of the costs that went into making that child and add that as a value in the record as part of a new column called total_child_cost.  Where the values in the column would be all of the costs associated with producing that child.  So in Excel, it works perfect with 2 SUMIF statements but I can't replicate it in SQL.  The value would show up WHERE the pdn_process = Packing since 'Packing' is the last stage in the process.  So it would sum all of the costs went into making the child.

This was my attempt:

UPDATE data
    SET total__child_cost = 
        (CASE WHEN length(serial) > 6
                    AND process = 'Packing'
                        THEN
                            IF(serial = serial, sum(process_cost),0) END)
                            +
                            (CASE WHEN left(serial,6) = serial
                                THEN sum(process_cost)
                            END)

Open in new window


You can replicate the situation with this CREATE code:


Open in new window

0
Please see the attached error message
The sql connection just isn't being made
I have tried all combinations possible
If I log onto the server with the sql instance I can login to the sql management studio with no problem with the same credentials
Surface area has been configured to allow connections
no firewalls are active
no ports blocked
I can ping the sql server from the machine which is trying to make the connection
I can get into all the shares on the machine
Capture.PNG
0
I am running some queries in Powershell.

I have created a function that returns an object I can use foreach (hence the return ,$table)
I first obtain a sql reader and load a data table with the returned recordset.

$rdr = $command.ExecuteReader()
          
 $table = new-object “System.Data.DataTable”
 $table.Load($rdr )
    
  return ,$table

Open in new window


The issue is that when iterating through the results I cannot perform an additional calls $command.ExecuteReader() even if I create a new command object and set the query text for that new command object.

I get the following error when I try: "There is already an open DataReader associated with this Command which must be closed first."

So I tried to close the reader after loading the data table

$rdr = $command.ExecuteReader()
          
 $table = new-object “System.Data.DataTable”
 $table.Load($rdr)
 $rdr.Close()

  return ,$table

Open in new window


With the data table filled and returned by the function, why can't I close the reader and then make a subsquent call to $command.ExecuteReader() ????
0
I have set up my connection to an Oracle Database in Excel and the Command Text in the definition tab of the Properties window says 'SELECT * FROM "PS_OWNER"."PS_JOB"' which returns too much data for an excel spreadsheet. So my question is, can I use VBA to return the data the same way I return it in SQL Developer?
0
Hi Experts,

I am trying to write a value to a field when it is either null or blank.

I have tried:
Select
IsNull(NULLIF(SiteCode, ''),'NON-VHA') as SITECODE,
From Site_CTE

but it does not work

Please help and thanks
0
I have a stored procedure that I know is probably set up wrong but what is causing problems consistently is the attempt to return the row number.  I have the following code but I'm not sure since the table wasn't specifically designed with an identity row that this will work. Sorry, I'm new to trying to pull in row and not an expert at testing stored procedures either.
I was hoping to test executing the sp and return all declared output columns. I attached a file with a screenshot of the table columns.

 
ALTER PROCEDURE [dbo].[cpsp_preg_num]
(
--@Design Bit,
@pi_enterprise_id  CHAR(5),
@pi_practice_id  CHAR(4),
@pi_cur_person_id  UNIQUEIDENTIFIER,
@txt_date			VARCHAR(10) OUTPUT,
@txt_acttext		VARCHAR(75) OUTPUT,
@txt_method			VARCHAR(30) OUTPUT,
@txt_comments		VARCHAR(100) OUTPUT,
@txt_completedby    VARCHAR(100) OUTPUT,
@txt_time			INT OUTPUT,
@new_identity		INT OUTPUT
)
AS

-- SELECT @new_identity = SCOPE_IDENTITY()
 select top 1
 @txt_date = txt_date,		
@txt_acttext = txt_acttext, 	
@txt_method	= txt_method,	
@txt_comments	= txt_comments,
@txt_completedby = txt_completed_by,    
@txt_time		=	txt_time,
@new_identity	 = SCOPE_IDENTITY()
From cpsp_ext_ 
where txt_pregNum = 1
order by create_timestamp asc

Open in new window

cpsp-table.jpg
0
Hi All,

I have a query where I am trying to find duplicates, and once I find the duplicates I need to make another pass to the table to get a column that was not part of the dup query to display the values in separate columns.
 Below is an example.

Path name is not part of the dup query

Select Date, Drill_group, NameGroup, Drillname, Count(1) As DupCount
From Sourcetable
Group by Date, Drill_group, NameGroup, Drillname,
Having Count(1) > 1

Source table
Date      DRILL_GROUP      Name Group      Drill Name      PATHNAME
20170630      ATTRIB_SECTOR      Gold      OPTION      MATERIAL
20170630      ATTRIB_SECTOR      Gold      OPTION      OTHER
20170630      ATTRIB_SECTOR      Intl      CAPITAL      HEALTH
20170630      ATTRIB_SECTOR      Intl      CAPITAL      INDUSTRIALS
20170630      ATTRIB_SECTOR      Mid Co      CAPITAL      HEALTH
20170630      ATTRIB_SECTOR      Mid Co      CAPITAL      INDUSTRIALS
20170630      ATTRIB_SECTOR      Mid Co      INDUSTRIAL MACHINERY      HEALTH
20170630      ATTRIB_SECTOR      Mid Co      INDUSTRIAL MACHINERY      INDUSTRIALS
20170630      ATTRIB_SECTOR      Mid Co      MACHINERY      HEALTH
20170630      ATTRIB_SECTOR      Mid Co      MACHINERY      INDUSTRIALS
20170630      ATTRIB_SECTOR      Income      AMERICAN      CONSUMER
20170630      ATTRIB_SECTOR      Income      AMERICAN      OTHER
20170630      ATTRIB_SECTOR      MLP      ENERGY      NATURAL
20170630      ATTRIB_SECTOR      MLP      ENERGY      PETRO
20170630      ATTRIB_SECTOR      Advisors      ENERGY      NATURAL
20170630      ATTRIB_SECTOR      Advisors      ENERGY      PETRO
20170630      ATTRIB_SECTOR      GTC      CAPITAL      HEALTH

Result for Dup Query:
Date      DRILL_GROUP      Name Group      Drill Name      Dup Count
20170630      ATTRIB_SECTOR      Gold      OPTION      2
20170630      ATTRIB_SECTOR      Intl      CAPITAL      2
0
Struggling a bit with a query. Here's the details:

Table1

ID, StartDate, EndDate, Approval (Bool/bit), Hours (Decimal 5/2), FiscalYear, EMPLID


Table2

ID, EmployeeCode, FirstName, LastName, Mgr



I need to display T2.FisrtName, T2.LastName, T2.Mgr, T1.StartDate, T1.EndDate, T1.FiscalYear, And the sum of T1.Hours
WHERE T1.EMPLID = T2.EmployeeCode

So basically I'm looking to display 1 record for each record in T2 while displaying the sum of T1.Hours for each correlating T1.EMPLID/T2.EmployeeCode record(s)


Result might look something like:


Peter     Griffin     Glenn Quagmire     1/1/2000     1/6/2000     1999     84.5



How would I do this?
0
Do you have a plan for Continuity?
LVL 2
Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

Hey

How do I connect to a SQL server - and return a recordset? (Trusted_Connection=True)

For example (Select * from mytable where field1=1)

Using VB.NET

Thanks in advance

Mike
0
Team, I need some help diagnosing this issue, asked by one of our DBA's, server is part of a cluster, the cluster was manually failed over to the other node around the time this issue/error occurred?
errorSQL.png
Message
Login failed for user 'XXX\WINXXXX$'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: XX.XXX.XX.XX] - Any ideas where/how I diagnose what the issue is here?

Many thanks
0
I can't seem to find this in my search. I simply have a table or range in my excel spreadsheet and I'm looking for a VBA code to insert the data into SQL Server.  I don't want to use any integrated services or an OPENROWSET in SSMS since this will be a spreadsheet in a shared folder.
0
In SQL Server, I have a View with a function that Calcs a certain number for people based off other settings, such as this (details of the function aren't important), with this output...

select FName, LName, Gender, dbo.CalcNum(field1,field2) as MyNum from tPeople

Al, Smith, M, 12
Bob, Carson, M, 13
Hank, Small, M, 3
Jake, Big, M, 45
Sandra, Lee, F, 9
Ellie, Day, F, 9
Debra, May, F, 10
etc.

What I now need is another View that queries this first View, with a total count of MyNum of all those rows with a specific number (for example, a total of all those with a value of 8) in different columns, and separated into 2 different rows by Gender. Not only that, but include a couple of columns that are number ranges.

For example, columns are Gender, TotNum7, TotNum8, TotNum9, TotNum10, TotNum8to10, TotNum11to15.

So given the above data, the output would be...

F, 0, 0, 2, 1, 3, 0
M, 0, 0, 0, 0, 0, 2

This basically says...

Of Females, 2 have the number 9, 1 has number 10, 3 are in range 8 to 10, and none in other columns.
Of Males, 2 have the number in the range 11 to 15, and none in other columns.

How can I do this? I suspect a PIVOT query? I tried to understand how they work, but in doing so, I'm not sure it's even the right option. And even if it is, I don't grasp how to make it work. Thoughts? To give a starting point with the data from above...

create view View1
as
select 'Al' as FName, 'Smith' as LName, 'M' as Gender, 12 As MyNum
union …
0
I have a query that shows many fields/columns.

There are 6 LEFT OUTER JOINS in this query.

Everything displays and calculates perfect... except for one field.  I have one field in one table that just shows NULL.

It is the only field in that table that I am trying to show.

What would be preventing the field from displaying?
0
Hello

I am looking for a stored procedure to look only in a specific table

I see this procedure all over the internet

https://www.sqlservercentral.com/Forums/Topic1232230-391-1.aspx 

that works like so:

EXEC SearchAllTables 'Computer'

but I want to look only in one table

EXEC SearchTable 'TableName'  'Computer'
0
We moved data from a set of fields in a table to another set of fields in the same table.  

These are the old field names:
QWife1Child1 , QWife1Child2,  QWife1Child3
 QWife2Child1 , QWife2Child2,  QWife2Child3
 QHusb1Child1 , QHusb1Child2,  QHusb1Child3
 QHusb2Child1 , QHusb2Child2,  QHusb2Child3

These are the new field names:
Child1, Child2, Child3 ... Child10

If there is a name in the old field then we copy it to a new field.  Not every couple has a child together.  They could each have children from previous marriages.  Keeping that in mind Child1 doesn't always  = QWife1Child1.  Child1 could = QHusb2Child1.

I wrote the query to complete this task and I believe that it ran like I wanted.  I just need to make sure.  

I need to write a MSSQL query to make sure that every child from the old fields are listed in the new fields.  The query I wrote to do this is giving me syntax errors.
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

How can I change this query to get the data I need?

SELECT q.MattersQTRUSTINFO, 
	QWife1Child1 , QWife1Child2,  QWife1Child3
	QWife2Child1 , QWife2Child2,  QWife2Child3
	QHusb1Child1 , QHusb1Child2,  QHusb1Child3
	QHusb2Child1 , QHusb2Child2,  QHusb2Child3
FROM [MattersQTRUSTINFO] q
JOIN Matters m ON m.matters = q.Matters
WHERE QWife1Child1  NOT IN (SELECT qchild1, QCHILD2, QCHILD3, QCHILD4, QCHILD5, QCHILD6, QCHILD7, QCHILD8, QCHILD9, QCHILD10

Open in new window

0
I have the following info in a column called TOOL.

TOOL

25-111111
1250-111111


How can I write a query that gives me a result set like below:
COLUMN
25
1250
0
I am trying to get an sql procdure to work. it is the second select statement that is causing the problem. i am getting the following error.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. for each record there should be only one result. is it the outer join that is causing the problem.

what i want returned
status 0 should show Order Stated
status 1 should show Order approved

(sample select statement for test)

SELECT
OrderId,
DateCreated,
DateShipped,
BillName,
CustomerId,
Status,
(select OrderStatusText
from OrdersTable od LEFT OUTER JOIN OrderStatusTable OST on OD.Status = OST.OrderStatusId) aS StatusName
FROM OrdersTable ot
where CustomerId = 'acddc51c-99c9-41db-aaed-13e96db35a3d'
0
Industry Leaders: We Want Your Opinion!
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Hello,
Can you please help,
I'm using below code, but it  returns multiple rows.
I need to combine the NoteText, seperated by __

Select Distinct FO.Orderno AS [Order #],FO.Orderdate AS [Order Date], NoteText AS [Reason]
From FinalizedOrders FO
INNER JOIN FinalizedOrderNotes ON FO.OrderNo=FinalizedOrderNotes.OrderNo
Where NoteText Like '%QA1%'
AND FO.orderdate >= ................. and ............... and ..................

Example Results:
Order #                       Order Date                                  Reason
7517502                       2017-07-10 08:31:55.000      Q1-Test1
7517502                       2017-07-10 08:31:55.000      Q1-Test2
7517502                       2017-07-10 08:31:55.000      Q1-Test3

Would like to have it
7517502                       2017-07-10 08:31:55.000      Q1-Test1__Q1-Test2__Q1-Test3

Your help is appreciated.
0
Hi guys,

In SQL SERVER 2008R2, I want to know if today is the 4th BUSINESS DAY DATE before end of the month.

Thanks.
0
I have a form with two list boxes. I want the one on the left to contain all documents assigned to a parent record. I want the other box to show all documents NOT assigned to that record. I used to have a Select query for the Unassigned box that had another Select query within it. The inner select query used a left join to identify records WITHOUT a match. But I lost the query syntax! Any help appreciated.
0
I'm trying to set up a trigger that will execute a stored procedure when the row being inserted contains certain verbiage.

The procedure is only sending an email notification.
I'd like to pass a value from the row to the procedure so that it can be included in the email.   It's a date value.

I keep getting the following error when I try to create the trigger:

SP2-0552: Bind variable "NEW" not declared.

Here is the trigger code:

CREATE OR REPLACE SCHEMA_NAME.TRG_INTERFACE_LOG_ERROR
AFTER INSERT ON SCHEMA_NAME.INTERFACE_LOG
REFERENCING NEW AS NEW
FOR EACH ROW WHEN (new.USER_MESSAGE='java.sql.SQLRecoverableException: Closed Connection')
 
BEGIN
 
 SCHEMA_NAME.EMAIL_EXCHANGE_INCIDENT( :NEW.LOG_DATE );

END;
/

Here is the table:
SQL> desc schema_name.interface_log
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------------

 LOG_ID                                    NOT NULL NUMBER(10)
 LOG_DATE                                           DATE
 USER_MESSAGE                                       VARCHAR2(4000)

I could sure use an expert eye.
0
Hi Experts,

I try to check a value in a SQL where clause, but get the error: CPD4374

On the  Where xxxx in() of the next statement

Exec SQL Select * from my file
Where Field1 = :Parm1
  and Field2 = in(:Parm2)

Field2 is numeric(15,0)
Parm2 is Character(128) and contains  '12,13,45,16,32  '

What is missing?
0
I installed SQL Server 2014 Express in Windows Server 2012 Standart. I've restored a backup of SQL Server 2008 and everything works fine on the server.
I've enabled TCP via Configuration Manager as well as Management Studio.

But I have a problem: I can not configure ODBC access from a terminal, where I downloaded and installed the ODBC driver 11.

Are there any settings to be made in Server 2012 or any other detail in Server 2014 Express?

Tks.

Translate from portuguese:

"Instalei o SQL Server 2014 Express no Windows Server 2012 Standart. Restaurei um backup do SQL Server 2008 e tudo funciona bem no servidor.
Eu habilitei o TCP via Configuration Manager, bem como Management Studio.

Mas tenho um problema: não estou conseguindo configurar o acesso via ODBC a partir de um terminal, onde baixei e instalei o ODBC driver 11.

Tem alguma configuração a ser feita no Server 2012 ou algum outro detalhe no Server 2014 Express? "
0

Query Syntax

48K

Solutions

19K

Contributors

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.