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

x

SQL

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

hi,

I am trying this tools ? http://www.sqlines.com/download: to try migrate tables and data from oracle 12c to mariaDB, but the downloaded SQL data tools binary always shows error:

when I test the connection to Oracle, it shows this error

SQLdata_Oracle.jpg
when I test the connection to MariaDB, it shows this error

SQLdata_MariadB.jpg
any idea on it ? come file missing ? how can I get it back?

the installation instruction of SQL data is :

How to Start:

  1. Download and unzip the file, no installation is required
  2. Run sqldataw.exe on Windows to launch the GUI version
  3. Run ./sqldata on Linux or sqldata.exe on Windows to launch the command line tool. Command Line Reference
  4. The tool uses low level native C/C++ connections and in-memory bulk loader APIs, so if you are able to connect to your databases using the existing client software, you should be able to start using SQLines Data immediately. SQLines Data Connections.

I do nothing wrong here.
0
Challenges in Government Cyber Security
Challenges in Government Cyber Security

Has cyber security been a challenge in your government organization? Are you looking to improve your government's network security? Learn more about how to improve your government organization's security by viewing our on-demand webinar!

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
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
0
Please delete, the script does work - it was my bad data. Thanks!
0
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?
0
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
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.
0
Has Powershell sent you back into the Stone Age?
LVL 8
Has Powershell sent you back into the Stone Age?

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.

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!
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
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?
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
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;
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
Discover the Answer to Productive IT
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.

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
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
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
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!
0

SQL

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.