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

SQL

Solutions

Content

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.

String to integer conversion error

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?

5 Comments

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.

5 Comments

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

3 Comments

Hello,
Thanks for your time. In SQL Query can i get a data like attached spreadsheet. In this the month column is dynamically generated with grouping of the exam completed date and student count. Means in the yellow is the cells with month the values is student count who did completed exams as per the exam date completed. The yellow month columns will be dynamically generated. This is just a sample to get an idea. So is there any possibility in SQL Query . Please suggest.
Book1.xlsx

2 Comments

Question by

Ramanth Lex

1d ago

DELETE

Please delete, the script does work - it was my bad data. Thanks!

1 Comment

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

2 Comments

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?

4 Comments

Question by

Opeyemi AbdulRasheed

2d agoSolved

I Need Help on Query Syntax

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?

3 Comments

Question by

Lone Ranger

2d ago

SCOM 1801 install —-SQL setup

Installing SCOM 1801 - Server:2016 standard - Virtual Server - SQL - 2016 Enterprise. I need some help installing this product i am having difficulty’s setting up the databases at the moment, I need a full comprehensive guide of installation and setup. I have installed the SQL software but it isn’t really set up the best and need tips on how to configure this correctly. I have about 1000 servers to support in the environment.

Any Help would be much appreciated.

1 Comment

Has Powershell sent you back into the Stone Age?

LVL 8

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

LVL 12

Question by

Nathan Riley

2d ago

WordPress User Revision SQL Query

I'm looking for a sql query to pull any woocommerce products or wordpress pages edited by a certain Wordpress user. Ideally I'd get the post and the post revision along with the user that I'm targeting.

I did some Google'ing but couldn't find what I was looking for. Any ideas?

Thanks!

1 Comment

LVL 11

Question by

Wilder1626

2d agoSolved

Oracle - Query pull string up to a specific character

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

5 Comments

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

8 Comments

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

…

6 Comments

Is it possible to create an SSIS package in Visual Studio with the Server Name and Data Source in the Source object as a parameter, so that you can provide the SQL server name at runtime?

1 Comment

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.

7 Comments

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

…

9 Comments

How to have Case Syntax in the IF Condition Join Query ?

How to have these two blocks of select query in a single case else statement?

declare @provid int
declare @stageprovid int

-- If this Syntax satisfies, then don't execute the next syntax.
-- If this syntax does not satisfy, then execute the next block.

SELECT @provid = prv.ID
FROM StagingProvider StagePrv
JOIN Providers prv
ON StagePrv.ClientID = prv.ClientID
AND prv.ExternalProviderID = StagePrv.[Provider_ID]
WHERE StagePrv.ID = @stageprovid;

-- This block of syntax executes only if the first block did not satisfy.

SELECT @provid = prv.ID
FROM StagingProvider StagePrv
JOIN Providers prv
ON StagePrv.ClientID = StagePrv.ClientID
AND prv.NPI = StagePrv.NPI
WHERE StagePrv.ID = @stageprovid;

3 Comments

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

4 Comments

Discover the Answer to Productive IT

Discover app within WatchGuard's Wi-Fi Cloud helps you optimize W-Fi user experience with the most complete set of visibility, troubleshooting, and network health features. Quickly pinpointing network problems will lead to more happy users and most importantly, productive IT.

LVL 1

Question by

WeTi

3d agoPrivate

Gather 22 results windows into one.

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

…

22 Comments

LVL 1

Question by

WeTi

3d agoSolvedPrivate

SQL select query

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.

9 Comments

Question by

Tanuja Kadam

3d ago

Sales ratio using plsql, sql

Hi, I need help in coding sales ration in sql, plsql. I have a file attached with details
C--Tanuja-Lake_IL-BRDs-sales-ratio-.docx

2 Comments

Question by

Tanuja Kadam

3d agoSolved

Linear regression

How to calculate linear regression in oracle plsql.
Please see the file attached.
C--Tanuja-Lake_IL-BRDs-linear-regre.docx

6 Comments

Question by

zachvaldez

3d agoPrivate

Sql Statement

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

8 Comments

Hi!

I have a table of inventory transactions. I need to find the latest transaction that has a type of "S" and price higher than 0 for each part number.

Here is some sample data from the table:

item_no	trx_type	trx_qty	unit_price	trx_date
CT0603CSF-10NG	S	10	0	09/20/13
CT0603CSF-10NG	R	-4000	0	09/04/13
CT0603CSF-10NG	R	4000	0	09/04/13
CT0603CSF-10NG	S	4000	0.07	09/04/13
CT0603CSF-10NG	S	30	0	08/28/13
CT0603CSF-10NG	R	33	0	08/15/13
CT0603CSF-10NG	R	-33	0	08/15/13
CT0603CSF-10NG	R	33	0	08/15/13
CT0603CSF-10NG	R	4000	0	03/22/13
CT0603CSF-10NG	R	-4000	0	03/22/13
CT0603CSF-10NG	R	4000	0	03/22/13
CT0603CSF-10NG	S	4000	0.08	03/22/13
CT0603CSF-10NG	R	2000	0	08/29/12
CT0603CSF-10NG	S	2000	0.07	08/29/12

Open in new window

I'm been working with this and I am stuck. Here is the current query I am using:

SELECT trx.item_no, trx.unit_price, trx.trx_type, trx.Trx_Date
FROM trx
WHERE trx.unit_price <>0 AND trx.trx_type= 'S' AND trx.Trx_Date=
(Select Max(Table2.Trx_Date) From trx Table2
WHERE trx.item_no = Table2.item_no Group by Table2.item_no);

Open in new window

It works fine unless the latest transaction has a price of 0 or a type that is not "S" then I get no results.

With this data I am expecting a result of:

item_no	trx_type	trx_qty	unit_price	trx_date
CT0603CSF-10NG	S	4000	0.07	09/04/13

Open in new window

I am working in Access 2016. Any suggestions will be greatly appreciated.

Thanks!

2 Comments

First
Prev
1
2
3
4
5
6
7
Next
Last

SQL

Solutions

Content

Contributors

SQL

Related Topics

SQL

Related Topics