Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

I have a table that lists all possible combinations of class sections when we group them.

Example of existing table - three class sections have been grouped, 6134, 6135, 6136

YEAR      TERM      SECTION       CROSS_SECTION
2018      SPRING      6134         6135
2018      SPRING      6134         6136
2018      SPRING      6135         6134
2018      SPRING      6135         6136
2018      SPRING      6136         6134
2018      SPRING      6136         6135

I want to add a trigger on the existing table that will insert/update a custom table that identifies the class sections within a group.  I can create a table with unique group numbers, but am not sure how to identify the group:
YEAR      TERM      GROUP   SECTION
2018      SPRING      1              6134
2018      SPRING      1              6135
2018      SPRING      1              6136
0
Configuration Guide and Best Practices
LVL 1
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

I using the script below and would like to add a FY Column based on C_COMPLETE_REQUEST_RECVD_DT.
The FYs are
FY2002 Between 10/1/2001 and 9/30/2002
FY2015 Between  10/1/2014 and 9/30/2015
FY2016 Between  10/1/2015 and 9/30/2016
FY2017 Between  10/1/2016 and 9/30/2017
FY2018 Between  10/1/2017 and 9/30/2018
FY2019 Between  10/1/2018 and 9/30/2019

Select distinct

C_ALJ_APPEAL_NUM as "ALJ Appeal Num"
,C_RECONSIDER_NUM as "Reconsider Num"
,MT.C_Name as  "Medicare Type"
,C_L2_APPEAL_CATEGORY as "Appeal Category"
,C_CLAIM_CNT as "Claim Cnt"
,C_REQUEST_RECV_DT as "Request Recv Dt"
,C_COMPLETE_REQUEST_RECVD_DT as "Complete Request Recvd Dt"
,C_RAC_REGION as "RAC Region"
,To_date(CURRENT_DATE) - to_date(C_REQUEST_RECV_DT) as Appeal_Age
, W.C_NAME as "Status"


From ((T_APL inner join 
T_MEDICARE_TYPE MT on C_MEDICARE_TYPE = MT.id)
inner join T_APL_CLAIM AC on AC.ID_Parent = T_APL.ID)
inner join T_RF_STATE W on t_apl.C_WORKFLOW_STATE = W.ID

Open in new window

0
Select * from OrderDetails inner join Products on Products.ProductID = OrderDetails.ProductID inner join Discounts on OrderDetails.DiscountID = Discounts.DiscountID where OrderID = 213

Open in new window


I'm trying to pull all the OrderDetails records where OrderID = 213.
Some OrderDetails records have Discounts (and a Discount ID)
If no discount has been applied to the OrderDetails, the value for DiscountID is Null.

I need to pull OrderDetails for ALL records for OrderID 213 - even those with a NULL value for DiscountID.

I would appreciate any help sorting out this query.  right now it's returning 1 record, it should return 6.

Thanks for your help!!!

Steve
0
How can we updated Nth character in a column in SQL . I want to update only 25th  character in my selected query .
0
Hello,

We want to insert clients information into salseforce.
We have all the information in an SQL Server database.
We want to use ssis to update the data in SalesForce in a daily basis.

We don't want to purshase any toolkit or ready to use component.

Can I find any  step by step tutorial to do so ? I came across some tutorials suggesting webservice option, but It didn't work in my case because of my poor knowlege in Webservices.

Any help is apprecited.

Thanks
Salma
0
A client recently asked me to start uploading a text file into a SQL Server database each day.  This file is provided by a third party, and reflects current values, not changes.  I've got this upload process working as a simple append query, but it is resulting in a bunch of duplicate values (records that are identical except for the upload_date field).

The SQL table contains fields like:

Upload_Date, Company_ID, Column1, Column2, Column3

I'm seriously thinking about modifying the upload, so that it is not a simple append, but a merge which would use all of the columns to perform the merge and if the record is matched, (everything but the upload_date), then I would simply update a counter field, but if the record is unmatched, I would perform the append, but I need to discuss this with the client first.

In the meantime, what I need to do is create a query which will identify the dates a companies data (Column1, Column2, or Column3) changed. Assuming these 5 columns, with data similar to

UPload_Date          Company_ID         Column1     Column2     Column3
2018-01-01                      1                         a                     b                  c
2018-01-02                      1                         a                     b                  c
2018-01-03                      1                         a                     b                  d
2018-01-04                      1                         a                     b                  d
0
Does SQL server reduce the LDF file when doing the backup?
0
Dear SQL Server Experts,

I am basically from Oracle DBA background and I don't have much experience in SQL Server. I hope I will get some experts advise and comments on my SQL Server DB issue :)ert

We have MS CRM DB with the size of 350 GB. We're experiencing DB locks (blocking) issue on production SQL DB. As per MS article "“Blocking is an unavoidable characteristic of any relational database management system (RDBMS) with lock-based concurrency”. Having blockings detected is not by itself enough and sufficient to decide the problem"".

My Questions:
1. what was the reason for blocking?
2.any advice on optimizing that particular cause?
3. Is there any chance of more blocking  if I use more NOLCOK DB hint in update or insert or select operations ?
0
Hey guys,

Need some help with my reports. I only know  a little with SQL since I'm currently in the networking field.
I have this problem with SSRS - Report Builder where I have a column name PublicIP which have

sample values :
10.10.1.232 (saaskhsk2121)
122.233.55.42
32.55.10.12 (sasa)

Now, i need to remove the characters with parenthesis () and retain only the IP address

I tried below command and it perfectly removed the parenthesis and retain the IP address
=LEFT(Fields!PublicIP.Value, InStrRev(Fields!PublicIP.Value, "(") -1)

sample result :
10.10.1.232


Now the problem lies in my iif statement since there are also values which doesn't have any parenthesis () and doesn't need to be trimmed down.
I tried this expression:

=Iif((Fields!PublicIP.Value Like "*(*") ,LEFT(Fields!PublicIP.Value, InStrRev(Fields!PublicIP.Value, "(") -1),Fields!PublicIP.Value)

Result:
10.10.1.232
#Error
32.55.10.12

As you can see, the result shows #Error for those values with no parenthesis.
Need advise. Thanks

Lester
0
Repair Corrupt SQL Database with Stellar Phoenix SQL Database Repair Software
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recovery of Deleted records and recovers all components of SQL Database.
0
Granular recovery for Microsoft Exchange
LVL 1
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

I have been using Access for a very long time and have some, but not a lot of experience working with SQl Server as a backend.

I am working on a conversion of legacy system data using Access 2013.  I had all of my conversion logic written and tested using a subset of the data provided by the client.  However, when I got the full set of data problems began.  I am reading a fixed length .txt file in my Access application and writing the output to a SQL table.  The input file has over 200k records but my logic will never get past processing 35,000 or so.  The record size of the input record is over, 14,000 characters.  One of the fields is 14,000 characters long.

After I got the 3183 I tried including BeginTrans and CommitTrans in the loop but the 3183 is still thrown after about 35,000 records.

The exact message I am getting is:
Error
MY system has 32GB of Ram and over 80GB of disk space free.  My Accdb is only about 60MB when I get this message.

In all my many years of working with Access I have never seen this error before and don't know how to resolve this situation to be able to process the entire file.

Here is my code:
Public Sub readFixedLengthTextFile()

'On Error Resume Next

clearSQLTable_Sproc "SQCS_CostFile_Import"
' output file
Set db = getCurrentDbC
Dim rsOut As DAO.Recordset
Set rsOut = db.OpenRecordset("SQCS_CostFile_Import", dbOpenDynaset, dbSeeChanges)


Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText 

Open in new window

0
We have a bit of an odd issue.  We have a 2012 server that is in a sql cluster.  Most of the drives are functioning normally; however we have found there are four drives that the local permissions do not seem to be taking affect and cannot be accessed when locally in an admin’s session on the server itself.  
First some background, all of these drives are iSCSI connected back to a SAN, they are all in the SQL cluster and so far everything appears to be operating from a production standpoint.  SQL does not appear to have issues accessing the data contained in the drives, and the logs show no errors.    
Now the issue, it seems the four drives that have a share on them, when logged in as an admin account on the server, those drives are unable to be accessed locally within the admin’s session.  The share access works fine using the UNC path.  
For example, I login under admin1 through RDP, attempt to open up the X: drive, which is an iSCSI attached drive to the server, and I get an access denied error message.  However, if I use that same account to access the share (or even administrative $ share) I can get in fine.  Admin1 is a member of both the domain admins, and the local admins on the server.  If I look at the security properties of drive X: (after being prompted to grant it access), both domain admins and local admins have full control.  So it I should not get an access denied message.
I did find if I grant the user account explicit full control it resolves the issue,…
0
PLEASE DELETE
0
Hi Experts,
I select the following 3 columns from two tables:
Table1.AliasName
Table1.HostName
Table2.ProductType

I'm inserting these 3 columns to Table3, where I added a new column, namely ProductId.
So, Table3 has 4 columns: AliasName, HostName, ProductType, ProductId

The content of the ProductId column has to be generated based on what my query returns from the AliasName, HostName, ProductType columns.
For this I would need a function.

This it the pseudo code:
Is ProductType "Vegetable" OR "Dairy" AND HostName start with "PACKAGE"
  If/Validate/Parse that AliasName is 6 HEX digits (0-F)
  Return "AAA.BBB.CCC.AAAAAA.1"+AliasName
 
Is ProductType "Fruit", "Meat", "Grocery" or "Plants"
 If HostName starts with "USA"
 If/Validate/Parse AliasName is 6 NUMERIC (0-9) digits
Return "AAA.BBB.CCC.BBBBBB.1"+AliasName

Is HostName start with "PACKAGE"
If/Validate/Parse that AliasName is 6 HEX digits (0-F)
Return "AAA.BBB.CCC.CCCCCC.1"+AliasName

If/Validate/Parse that AliasName is 5 or 7 UPPERCASE ALPHA (A-Z)
Return "AAA.BBB.CCC.DDDDDD.1"+AliasName

What is the correct syntax of the query to achieve this. I would appreciate your help.
0
In my query, we are using the following code:
spool c:\Work\PS PR Register 2018.csv

Select /*csv*/prd.EMPLOYEE, etc...

The query is working fine, but I wanted to know if there was a way to omit the sql that always shows ups at the beginning of the code? Also, is there a way to remove the column headings as well?
0
Hello ,
Need help in this query, please see the excel sheet for the expected result.

;WITH Drivers
AS ( SELECT   J.DriverID ,
              MAX(J.CollectionDatetime) AS LastAllocStDate
     FROM     Table_JOB J
     GROUP BY J.DriverID ) ,
     A
AS ( SELECT j.ID ,
            j.ClientName ,
            j.DriverID ,
            CAST(j.CollectionDateTime AS DATE) AS AllocStDate ,
            j.Allocation ,
            CAST(j.AllocEndDate AS DATE) AS AllocEndDate
     FROM  Table_JOB j
            INNER JOIN Drivers d ON j.driverId = d.driverId
                                    AND j.CollectionDatetime = d.LastAllocStDate ) ,
     T
AS ( SELECT B.NAME ,
            A.Allocation ,
            A.AllocStDate ,
            A.AllocEndDate ,
            A.ClientName ,
            A.ID ,
            IIF(A.AllocStDate <= CAST(GETDATE() AS DATE) AND A.AllocEndDate >= CAST(GETDATE() AS DATE), 'Allocated', NULL) AS OnJob
     FROM   A
            INNER JOIN FieldResource B ON A.DriverID = B.ID ) ,
     Z
AS ( SELECT DISTINCT T.Name ,
            T.Allocation ,
            T.AllocStDate ,
            T.AllocEndDate ,
            IIF(T.OnJob IS NULL, NULL, T.ClientName) AS ClientName ,
            IIF(T.OnJob IS NULL, NULL, T.ID) AS ID ,
            IIF(T.OnJob IS NULL, 'YES', NULL) AS NotAllocated ,
            T.OnJob AS Allocated
     FROM   T )
,CTE1 AS
(
	SELECT   
			 Z.Name ,
			 Z.Allocation, 
			  Z.Allocated ,
			 Z.AllocStDate ,
			 Z.AllocEndDate ,
			 Z.ClientName ,

Open in new window

0
Hi,

I have a requirement to make a manual work to automation. Here is the complete details about the automation plan.

1. Need to prepare a Java script by combining the SQL db as back end. These JavaScript can be scheduled in a 15 minutes once.
Once this Job got scheduled, it will run the java script and get the details from the SQL database(Select script which is ready with me!)
2. My condition in the java code should if the customer Which we are passing having any values in that time frame, it should pick and prepare a spreadsheet if there is no transactions it can be ignored for that run.
3. Also another requirement is to pull the logs from the servers based on the customer ID passed in the code. If there is a way to do that. We can implement the same.

Please share your thoughts and suggestions if any.

Thanks
Venkatesh.
0
Dear Experts,

I have some queries which needs to be created as a Stored Procedure but also by that changing to contain the database information after the FROM clause.

Briefly for example query in such format:
SELECT TOP 10
	AD.LFA1.LIFNR
FROM
	AD.LFA1

Open in new window


will be need to be re-formatted as (SAP_DATA added so the database name):

SELECT TOP 10
	AD.LFA1.LIFNR
FROM
	SAP_DATA.AD.LFA1

Open in new window


Could you please advise regarding the best practice, how to add database information to the FROM clause relatively easily?

Actually my only idea is to replace text 'AD.' with 'SAP_DATA.AD.'  in Excel in the code

Thanks in advance,
0
Hi ,
i have installed sccm on our one site and have created a sccm server for our another site which is connected with different domain, trusted relationship though.
can anyone please tell me if i need to configure and install the second site same as my first site with all the pre-requisites and sql in it??
0
Veeam Disaster Recovery in Microsoft Azure
LVL 1
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

HSSFSheet sheet = wb.getSheetAt(0);
            HSSFRow row;
            for(int i=1; i<=sheet.getLastRowNum(); i++){
                row =  sheet.getRow(i);
                int id = (int) row.getCell(0).getNumericCellValue();
                String name = row.getCell(1).getStringCellValue();
                String address = row.getCell(2).getStringCellValue();
                String sql = "INSERT INTO test VALUES('"+id+"','"+name+"','"+address+"')";
                pstm = (PreparedStatement) con.prepareStatement(sql);
                pstm.execute();
                System.out.println("Import rows "+i);
            }
i got error in declarating id, name and address
0
I have an attendance table in SQL Server 2012 named tblOrgHours. (attached)  This is where I log all my attendance for different agencies in tracking how many hours each activity has been performed each day by different kids.   The RegID in that same table is relational with the student information found in table tblOrgRegistrations. (attached)  I need to create an sql query that will output the number of unique days of activity for each student during a specified time frame.  Each day containing greater than 0 hours is considered a day of activity.  

Here is the code I setup to get this data described above.  Am I properly using the Distinct clause with ActivityDAte to attain what I described.  Also, I am collecting other columns that concern me.  I am not entirely familiar with the Group By clause, but am I using it correctly, once again, to get the desired output.  Thus far, my output seems good, but I'd like experts to take a look at my query/syntax.

select  H.Agency,
		h.SubGroup
	    ,Count(distinct H.ActivityDate) as [# of days]
		,r.LastName
		,r.FirstName
		,r.BirthDate
		,r.Grade
		,r.StudentID

from tblOrgHours H
inner join tblOrgRegistrations R on H.Regid = R.RegID

where H.[Hours] > 0
and h.fiscal = 2018
And h.ActivityDate >= '7/1/17' And h.ActivityDate <= '12/31/17'

group by H.Agency,h.SubGroup, r.LastName,r.FirstName,r.BirthDate,r.Grade,r.StudentID
Order by 1,4,5

Open in new window

tblOrgHours.xlsx
tblOrgRegistrations.xlsx
0
Hi Team,

MS sql server is showing CPU 98% and Memory 99%. This is sql server 2016.

I didn't some testing. When i disable application user, the cpu and memory is coming down to just 5-10%, but at the moment when i enable applicaiton user, within 5-10 mins CPU and going to 99%. when when i run SP_who2, i found lot of blocking.
Please go thourgh the screenhot and suggest us, how to resolve.

els01blocking.JPG
0
Hi,

I have this page:
http://kroweb.dk/gfdev/godser/
and my trouble is, that when I try to perform a search nothing happens at all and I get no error messages either.

I have tried to create a query directly in the db editor like this: Sk-rmbillede-2018-01-13-10.47.18.png and it works nicely and returns the expected results.

I am connected to the right db (have doubble checked several times :) ), and I simply can't see why this is not working.

I have this main file:
<?php
session_start();
error_reporting(E_ALL);
ini_set('display_errors', 1);
?>

<!DOCTYPE html>
<html lang="da">
<head>
  <!-- Check for title--> 
  <title>Godser</title>

  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css" integrity="sha512-dTfge/zgoMYpP7QbHy4gWMEGsbsdZeCXz7irItjcC3sPUFtf0kuFbDz/ixG7ArTxmDjLXDmezHubeNikyKGVyQ==" crossorigin="anonymous">
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/dt/dt-1.10.16/datatables.min.css" />
  <link rel="stylesheet" type="text/css" href="style_stednavne.css" />

  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
  <script 

Open in new window

0
Hi Experts.

I'm having a sql file about 3.5GB with some tables about 7 millions records , and a Ubuntu 16.04 server configured to run on a VPS with 8 vCPU, 32GB RAM. (RAM Disk)
The RAM Disk mount command is:
# sudo mount -t tmpfs -o size=25G tmpfs /opt

When importing that sql file using default MySQL configuration, its took about 3 hours and a half to finish.
After using this optimized MySQL configuration, it's took only about 20 minutes:
innodb_buffer_pool_size = 8G
innodb_log_buffer_size = 256M
innodb_log_file_size = 1G
innodb_write_io_threads = 16
innodb_flush_log_at_trx_commit = 0

When importing, import speed goes around 1MiB/s to 7MiB/s. In the end, the average importing speed with this new parameters is 2.5 MiB/s.
My question is, which parameter should i optimize to increase average importing speed, in order to reduce import time from 20 minutes to about 10-12 minutes?

Hope to see experts's answers soon, thank you.
0
SQL 2008

I have three datetime fields I am comparing in a CASE statement below. The problem I am having is with NULL values. If one field has a datetime value in it and the other 2 have nulls then the result is NULL. How can I avoid this. Thanks

    CASE WHEN dy.LastCall > emaildate
                 AND dy.LastCall > crystalcldate
                 THEN dy.LastCall
            WHEN emaildate > crystalcldate
                 AND emaildate > dy.LastCall
                 THEN emaildate
            WHEN crystalcldate > dy.LastCall
                 AND crystalcldate > z.emaildate
                 THEN cm.crystalcldate END crystalcldate
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.