[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Query Syntax

52K

Solutions

20K

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

I have a SQL table as such:
User1    User2          ID
KH         45                1
KH         45,41,65     2
KI           46               3

and so on

Within a stored procedure, I need to find all records where User1 = 'KH' and '45' appears in User2, so records 1 and 2

I cannot use the CONTAINS function because the table is not full-text indexed
"select id where user1 = 'KH' and contains(user2,'45')" does not work.

Is there any way I can do this without looping through the table and parsing user2 on each loop?

Thanks!
0
Starting with Angular 5
LVL 12
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

Hello,

I'm trying to write a select where I can get part numbers sold in the last hour. The thing is, I'm having difficulties in getting the results right. Here's the info...

select c.partnumber,o.timeplaced,count(o.orders_id) AS count
from orders o
inner join orderitems oi on (o.orders_id = oi.orders_id)
...
and o.timeplaced > current_timestamp - interval '1' hour
group by c.partnumber,o.timeplaced;

This all works fine. The issue is that since timeplaced is a timestamp, it goes down to the millisecond. Therefore, I'm not able to get the grouping I'm looking for. I really only want orders placed in the last hour. I really don't care about doing any ordering, although being able to order by the count would be nice.

How can I run my query but only get results in the last hour so the grouping will be better? For example, in my current query, the results look something like this...

PARTNUMBER  TIMEPLACED                    COUNT
123456      10/16/2018 7:22:23.325000 AM      1
123456      10/16/2018 7:34:40.448000 AM      1
123456      10/16/2018 7:54:51.566000 AM      1
123457      ...

When I'd like it to be:
PARTNUMBER  TIMEPLACED                    COUNT
123456      10/16/2018 07 AM                  3  
123457      ...

I did try using TO_CHAR(o.timeplaced,'yyyy-mm-dd hh24') in my select, but the results are basically the same. I'm wondering if this is because of the "and o.timeplaced > current_timestamp - interval '1' hour" while the …
0
Hi, from yesterday I'm unable to access my database. It indicating Suspect mode there. Kindly suggest how to make my SQL database online from Suspect mode?


Thanks
0
Hi, I hv two Access Tables: LinkedTbl and LocalTbl, both share the exact identical structures with autonum running on both tables.
LinkedTbl is constantly updating with records from other users, and obviously the autonum in LinkedTbl will out numbered the LocalTbl which resides in Notebook.
At the end of the day, Records of LocalTbl from Notebook shall append to LinkedTbl provided the records are new to LinkedTbl.

I run the following Append Query for the update:

INSERT INTO LinkedTbl
SELECT *
FROM LocalTbl
WHERE (((Exists (SELECT * FROM LinkedTbl WHERE LinkedTbl.ProductNo = LocalTbl.ProductNo))=False) AND ((Exists (SELECT * FROM LinkedTbl WHERE LinkedTbl.OrderDate = LocalTbl.OrderDate))=False));

However, even without configuration, the above query will try to append autonum from LocalTbl to LinkedTbl along with the new records, which I want to prevent, is there a way to configure the above query to leave out LocalTbl's autonum when append? Where new autonum from LinkedTbl shall assign to the inserted new records from LocalTbl.

LocalTbl data will be cleared and replaced again from LinkedTbl first thing in next working day , therefore LinkedTbl is the primary data source.

Appreciate if anyone can shed some lights in this matter thx.
0
Hi ,
I have a table table A having column col1,col2,col3,col4

I want to select the data in the table which has null values but with a statement which shows that what are the column having null values as below example:

INPUT:

col1 col2 col3
A               B
         X      B
A              

OUTPUT:

col1 col2 col3  statement
A               B      NULL IS COL2
         X               NULL IS COL1,COL3
A                        NULL IS COL2,COL3
0
I have a continuous form based on a view from SQL Server 2008R2.  
If i open the view it takes 10 seconds for the records to display.  
If i open the form it takes 20 seconds for the records to display.  You can see the Running Query in the bottom right, it runs for 10 seconds, then starts again for another 10 seconds.
It looks like it runs twice (and unfortunately takes twice as long).

Is this normal?

Any help would be greatly appreciated
0
I have an oracle 11g table TB_Doc with column DocNo among other columns
Sample values for DocNo
   Doc1
   Doc1_AR1
   Doc2
    Doc2_AR2
   Doc3
   Doc4
   Doc4_AR1

The ending _ARx need to be trimmed and I need to get a view with DerivedDoc as

  Doc1
   Doc1
   Doc2
    Doc2
   Doc3
   Doc4
   Doc4

   Please give me the Sql for the view
0
I am teaching myself visual studio with visual basic. Please help a newbie.

I have a connected sql table setup as follows
TABLE [dbo].[Suffix](
      [SuffixID] [int] IDENTITY(1,1) NOT NULL,
      [Suffix] [nvarchar](3) NOT NULL,
      [myCount] [nvarchar](5) NULL,
 CONSTRAINT [PK_Suffix] PRIMARY KEY CLUSTERED

I created a visual basic windows form in visual studio.  the save button on the form as the following code behind it in the click event.
            cmd.CommandType = System.Data.CommandType.Text

            cmd.CommandType = "insert into Suffix values ('" & TextBox30.Text & "', '" & TextBox28.Text & "')"
cmd.Connection = con
            con.Open()
            cmd.ExecuteNonQuery()
            MsgBox("Succesfully added", MsgBoxStyle.Information, "add")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

I keep getting the following error Conversion from string "insert into suffix value ('txx'" to type 'integer' is not valid.

How do I fix this?
0
Hello Experts!

I need help on the following:

I have this View - view_all_enrollment:
Student_ID    Subject_Code   Subject_Name   Class_Name  Session   1st_Term   2nd_Term   3rd_Term   Average
001           ENG            English        SS 1A       2018       44         43         45         44
001           MTH            Maths          SS 1A       2018       45         54         61         53.3
001           BIO            Biology        SS 1A       2018       50         60         70         60
001           PHY            Physics        SS 1A       2018       48         48         48         48

Open in new window

From which I want to generate a report (Student Result) with the following criteria:
1.  Only the Average Scores will be used in Final Grading.
2.  The Pass Mark for English and Maths is 45. Less that 45 is Fail.
3.  The Pass Mark for other subjects is 50. Less that 50 is Fail.
4.  A Student must Pass 6 subjects including English and Maths

There are 3 Positions:
a.  Promoted (must pass 6 subjects including English and Maths);
b.  Resit [when there's Fail in either English or Maths or Both but Pass in at least 5 subjects (in case of Fail in either English and Maths) or Pass in at least 4 subjects (in case of Fail in both English and Maths) ];
c.  Repeat (when a student has less than 4 Passes even if he Passes both English and Maths )

Note: If a student has 5 Passes including English and Maths, the Position is "Repeat" because, a student can only "Resit" English or Maths or Both.

More so, this WHERE condition will be used: WHERE Student_ID = ? AND Class_Name = ? AND Session = ?

Please, from the query, I need the following info on the result:

Number of Subjects Offered : e.g.  4 (Four)
Number of Subjects Passed : e.g.  2 (Two)
Number of Subjects Failed : e.g.   2 (Two)
Remark:  RESIT

See attached for example.

Thank you.
sample.png
0
I have an Oracle 11g database with table TB_Doc  and column DocNo ( and there are more columns
 
Following are some sample values for DocNo
  10-123-Doc
  10-123-Doc_DH
   MyDoc1
   AnotherDoc
   AnotherDoc_DH
   DH_GoodDoc
I need a view with additional column DocNoDerived in which all the _DH  in the end should go as  follows
   10-123-Doc
  10-123-Doc
   MyDoc1
   AnotherDoc
   AnotherDoc
   DH_GoodDoc
0
OWASP: Avoiding Hacker Tricks
LVL 12
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

I have a long string and need to replace a "%" with a "-" but only if the "%" is within the first 60 characters.

Example input
recording-20181012_183357-7205756104-0-141850%252317199648923-0-HVDA-callhalf-15475983977%3A0_%5BcallingPartyNumber%5D.wav

example output
recording-20181012_183357-7205756104-0-141850-252317199648923-0-HVDA-callhalf-15475983977%3A0_%5BcallingPartyNumber%5D.wav
0
Need help with stripping out the city and State name from the Customer Name field (TKSuppliers (ABCity, TX)

SELECT   CustomerName
        ,LEFT(CustomerName, CHARINDEX(',', CustomerName) - 1) AS [Surname]
        ,REPLACE(SUBSTRING(CustomerName, CHARINDEX(',', CustomerName), LEN(CustomerName)), '(', '') AS CitySt
FROM    Sales.Customers CUST 

Open in new window


what am I missing?
1
Hello Experts!

I have this table - tbl_enroll:
ENROLL_ID  STU_ID     SUBJECT_CODE    CLASS_NAME    SESSION      TERM     MARKS
1          001        ENG             SS1A          2018         1st      40
2          001        MATHS           SS1A          2018         1st      40
3          002        ENG             SS1A          2018         1st      50
4          002        MATHS           SS1A          2018         1st      50
5          001        ENG             SS1A          2018         2nd      45
6          001        MATHS           SS1A          2018         2nd      50
7          002        ENG             SS1A          2018         2nd      55
8          002        MATHS           SS1A          2018         2nd      55
9          001        ENG             SS1A          2018         3rd      60
10         001        MATHS           SS1A          2018         3rd      70
11         002        ENG             SS1A          2018         3rd      65
12         002        MATHS           SS1A          2018         3rd      50

Open in new window


Now the challenge: I want a SELECT QUERY to give something like:
ENROLL_ID   STU_ID   SUBJECT_CODE     CLASS_NAME   SESSION      1st_TERM    2nd_Term    3rd_Term   Total    Average
1           001      ENG              SS1A         2018         40          45          60         145      48.3
2           001      MATHS            SS1A         2018         40          50          70         160      53.3
3           002      ENG              SS1A         2018         50          55          65         170      56.7
4           002      MATHS            SS1A         2018         50          55          50         155      51.7

Open in new window


Is that doable, please?
0
Hi,

I have this query that i would like to modify but i don't know how.

Select Description from Comment_T

Open in new window


In the Description field, i have value like: TT_MB_1. TT_CC_6, TT_FF_9 etc...

I would like to get everything from the string up to the last underscore "_".
Ex: If i have TT_MB_1, it would give me "TT_MB_".

How can i do that?

Thank you for your help
0
Searching in an Oracle database, one of my parameters for exception of the record is conditional on one field.  Here is the SQL:

select tax_id, mcal_code, taxonomy_cd
from ODW.PRV30_SERVICE_PRV a JOIN odw.prv301_abs_service_prv_npi b ON b.prv301_tax_id = b.prv301_tax_id
WHERE mcal_code = '50' and taxonomy_cd not IN ('261Q00000X', '261QP0904X')
ORDER BY a.prv30_mcal_code desc

In the where clause the two fields, mcal_code  and  taxonomy_cd are linked as both field values have to be present.  
In pseudo code:  When the mcal_code = 50 and if the taxonomy_cd =  '261Q00000X' or '261QP0904X' then that record is skipped
So in the results I will see records that have mcal_code 50 and taxonomy_cd '261Q00000X' and '261QP0904X', but not on the same record.
What is the SQL that will allow me to do this?

Thanks,
Scott
0
I have the following statement where I am trying to filter by RN = 1. I am using row_count. However, I keep receiving invalid column RN even though it is a column with the results.
Screenshot attached.

SELECT DISTINCT 
                         dbo.AUDIT_TRAIL.SOURCE_TABLE, dbo.AUDIT_TRAIL.SOURCE_AK, dbo.AUDIT_TRAIL.SOURCE_FIELD, dbo.AUDIT_TRAIL.ORIG_VALUE, 
                         dbo.AUDIT_TRAIL.NEW_VALUE, dbo.BA_VIEW_SHIPPING_ORDERS.ENTRY_DATE AS SHIP_ENTRY_DATE, dbo.AUDIT_TRAIL.STAMPTIME, 
                         dbo.BA_VIEW_SHIPPING_ORDERS.SM_NUMBER, dbo.SYS_USERS.USER_NAME, dbo.SYS_USERS.FIRST_NAME, dbo.SYS_USERS.LAST_NAME, 
                         dbo.VIEW_SM_STOCK.STM_AUTO_KEY, dbo.VIEW_SM_STOCK.QTY_RESERVED, dbo.VIEW_SM_STOCK.QTY_SHIPPED, dbo.VIEW_SM_STOCK.STOCK_LINE, 
                         dbo.VIEW_SM_STOCK.CONDITION_CODE, dbo.VIEW_SM_STOCK.WAREHOUSE_CODE, dbo.VIEW_SM_STOCK.ORIG_WAREHOUSE_CODE, 
                         dbo.VIEW_SM_STOCK.LOCATION_CODE, dbo.VIEW_SM_STOCK.SERIAL_NUMBER, dbo.SYS_USERS.EMPLOYEE_CODE,
						 ROW_NUMBER() OVER(PARTITION BY BA_VIEW_SHIPPING_ORDERS.SM_NUMBER,AUDIT_TRAIL.SOURCE_AK ORDER BY VIEW_SM_STOCK.STM_AUTO_KEY DESC) AS RN
FROM            dbo.VIEW_SM_STOCK RIGHT OUTER JOIN
                         dbo.BA_VIEW_SHIPPING_ORDERS ON dbo.VIEW_SM_STOCK.SMD_AUTO_KEY = dbo.BA_VIEW_SHIPPING_ORDERS.SMD_AUTO_KEY LEFT OUTER JOIN
                         dbo.AUDIT_TRAIL LEFT OUTER JOIN
                         dbo.SYS_USERS ON 

Open in new window

0
cn_pk    cn_contractorid      cn_slip      cn_paycycle                              cn_total
1             ES-01                   420908      Saturday, July 20, 2018      $480.07
2             ES-01                   420908      Saturday, July 20, 2018      -$480.07
3             DH-02                   420917      Saturday, July 21, 2018      -$436.89
4             DH-02                   420917      Saturday, July 21, 2018      -$320.85
5             DH-02                   420917      Saturday, July 21, 2018      $436.89
6             DH-02                   420917      Saturday, July 21, 2018      $436.89
7             TR-03                   420919      Saturday, July 23, 2018      -$463.40
8             TR-03                   420919      Saturday, July 23, 2018      -$340.32
9             TR-03                   420919      Saturday, July 23, 2018      $463.40
10           TR-03                   420919      Saturday, July 23, 2018      $463.40

Hello,

I would like to write a delete records SQL that will delete any two records that who's combined sum of cn_total = zero where cn_contractorid, cn_slip, and cn_paycycle are the same?

In this example records 1, 2, 3, 5, 7 and 9 should delete.
0
Need to select only the first row of each unique value. As you can see from the attached screenshot, I only want to display the record STM_AUTO_KEY = 42962. In other words, I want to see only the lowest record of STM_AUTO_KEY.

SELECT DISTINCT 
                         dbo.AUDIT_TRAIL.SOURCE_TABLE, dbo.AUDIT_TRAIL.SOURCE_AK, dbo.AUDIT_TRAIL.SOURCE_FIELD, dbo.AUDIT_TRAIL.ORIG_VALUE, 
                         dbo.AUDIT_TRAIL.NEW_VALUE, dbo.BA_VIEW_SHIPPING_ORDERS.ENTRY_DATE AS SHIP_ENTRY_DATE, dbo.AUDIT_TRAIL.STAMPTIME, 
                         dbo.BA_VIEW_SHIPPING_ORDERS.SM_NUMBER, dbo.SYS_USERS.USER_NAME, dbo.SYS_USERS.FIRST_NAME, dbo.SYS_USERS.LAST_NAME, 
                         dbo.VIEW_SM_STOCK.STM_AUTO_KEY, dbo.VIEW_SM_STOCK.QTY_RESERVED, dbo.VIEW_SM_STOCK.QTY_SHIPPED, dbo.VIEW_SM_STOCK.STOCK_LINE, 
                         dbo.VIEW_SM_STOCK.CONDITION_CODE, dbo.VIEW_SM_STOCK.WAREHOUSE_CODE, dbo.VIEW_SM_STOCK.ORIG_WAREHOUSE_CODE, 
                         dbo.VIEW_SM_STOCK.LOCATION_CODE, dbo.VIEW_SM_STOCK.SERIAL_NUMBER, dbo.SYS_USERS.EMPLOYEE_CODE
FROM            dbo.VIEW_SM_STOCK RIGHT OUTER JOIN
                         dbo.BA_VIEW_SHIPPING_ORDERS ON dbo.VIEW_SM_STOCK.SMD_AUTO_KEY = dbo.BA_VIEW_SHIPPING_ORDERS.SMD_AUTO_KEY LEFT OUTER JOIN
                         dbo.AUDIT_TRAIL LEFT OUTER JOIN
                         dbo.SYS_USERS ON dbo.AUDIT_TRAIL.SYSUR_AUTO_KEY = dbo.SYS_USERS.SYSUR_AUTO_KEY ON 
                         

Open in new window

0
Extracting API Data Using Python and Loading into SQL Server

Hi,
I am new to Python in SQL Server. I'd like to load json data from an API into SQL Server, I thought the best way to do this is to utilise the new SQL Server Machine Learning Services with Python.

I can call the API and print the json data in SSMS:

execute sp_execute_external_script 
@language = N'Python',
@script = N'

# We import the requests module which allows us to make the API call
import pandas as pd
import json
import requests
 
# Call API to pull data
url = ''https://samples.openweathermap.org/data/2.5/weather?q=London,uk&appid=b6907d289e10d714a6e88b30761fae22''

response = requests.get(url = url)
response_data = response.json()

print(response_data)
'

Open in new window


I'm pretty happy using the JSON functions in SQL Server to format and parse the data into SQL tables, but with Python how do I read/access the json data from the response into an TSQL query?

Thank you
0
Python 3 Fundamentals
LVL 12
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Dear expert, I need to modify the query below, now this will return 22 results adn 22 results windows will appear. Now I would like to show this in one big window like in a column. This is MSSQL.

	
DECLARE 
	@SCustomerID varchar(30) = '56201710194';
	DECLARE
	@SSN varchar(30) = '1123560344'
		
	Select * from Data_Person WHERE CustomerID= @SCustomerID
	Select * from Data_Address WHERE CustomerID= @SCustomerID
	Select * from Data_Email WHERE CustomerID= @SCustomerID
    Select * from Data_Employment WHERE CustomerID= @SCustomerID
    Select * from Data_Membership WHERE CustomerID= @SCustomerID
    Select * from Data_Telephone WHERE CustomerID= @SCustomerID
	Select * from Work WHERE ContactId= @SCustomerID
	Select * from Index_Interactions where ContactId= @SCustomerID
	Select * from Data_MembershipHistory where CustomerID= @SCustomerID
	Select * from Data_MembershipProgress where CustomerID= @SCustomerID
	Select * from Index_IncompleteCases where SSN= @SSN
	Select * from Data_CustomerNotes where SSN= @SSN
	Select * from History_Work where pxHistoryForReference in (Select InsKey from Work where ContactId= @SCustomerID)
	Select * from Data_WorkAttach where pxRefObjectKey in (Select InsKey from Work where ContactId= @SCustomerID)
	Select * from History_Work where pxHistoryForReference in (Select InsKey from Work where ContactId= @SCustomerID)
	Select * from Index_ToDoList where pxInsIndexedKey in (Select InsKey from Work where ContactId= @SCustomerID)
	Select * from 

Open in new window

0
Dear expert
Please help with a select query
i got a multiple select query like:
select * from data.test where id = 1245
select * from data.test2 where id = 1245
select * from data.test5 where id = 1245
I would like them to be in a column instead of multiple results windows shows up.
i tried:
Select (
select * from data.test where id = 1245
select * from data.test2 where id = 1245
)
Didnt work that way. Thx.
0
How to calculate linear regression in oracle plsql.
Please see the file attached.
C--Tanuja-Lake_IL-BRDs-linear-regre.docx
0
Here are the table structures
                                   customer and customer order

customer
cust_id      integer
name      string
status      char

cust_order
order_id      integer
product_id      integer
cust_id      integer
amt      decimal

Need help on this :

      Write a SQL statement that returns the name of every customer along with the total value of all orders for each customer.  The result should not include customers with no orders, nor should it include customers whose total order amount is less than $2000.00
0
I have a long running query because it is reading a very large database.  The issue is there is a string of codes that each have an order number.  There can be as many as 30 on a  line. The order is then matched to another table to get the actual value.  What is the best way to loop through this row to get the codes for all of the 20 values.  This is an SQL statement.
0
Hi,

Is there a way to return more than one value in a subquery inside a select? for example:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate, (select x from table z where x=1) as abc
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID

Instead of using  (select x from table z where x=1) as abc and (select y from table z where x=1) as def to just one query (select x,y from table z where x=1)

thanks
0

Query Syntax

52K

Solutions

20K

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.