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

Hello,

I'm trying to get the data from other field that start with 59 and has 24 characters, I used this string:
Formulas: Mid([SEDA Transactions].[Comments],InStr([SEDA Transactions].[Comments],"59"),24)
Get the code but also the #Func! Error.  Please advise.
0
NEW Veeam Agent for Microsoft Windows
LVL 1
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

We are converting our databases from mysql to sql on a windows server. I am slowly figuring out the small changes in php coding but can't get it to pull date or time fields.

If I have a simple "Select PName from info" and echo $PName it works. but once I add in "Select PName, SCHTM, SDate from info" and try to echo $PName - $SCHTM - $SDate I get a blank page. SCHTM is a time(7) field with data like 12:45:00 and the SDate field is a date field with things like 2017-08-17    

During the conversion process the SCHTM field now shows 12:45:00.0000000 instead of just 12:45:00
0
Hi experts, I have a problem obtaining data in sqlserver in which in my sql command I need to advance 1 day of my time end before able to obtain the data.  My data field in sql is look like this, "2017-08-17 10:00:00.000"  this means that it has a time and this is August 17, 2017. In my sql command I cannot obtain this record if i set the time end to "08/17/2017" but  "08/18/2017" meaning i need to advance 1 day. Is there anyone who can explain me why. What shall i do to make my sql command set "08/17/2017"?

Set rs = cn.Execute("Select Proname, TheDate,  from sTable where TheDate Between '" & "08/17/2017" & "' And '" & "08/17/2017") & ") 

Open in new window

0
DB2 10.5 on Windows

I have a query I am trying to optimize and I want to test a couple of different things.  The issue is that after I run it for the first time the data is in cache so first run is 5 minutes and next run is 15 seconds.  In MS SQL I would clear the execution plan for the query before each run to compensate for that.  Is there something similar in DB2?

Thanks!
0
Using SQL SERVER 2008 R2

I have a script that does some table renames so I need "GO" between the Renames.  I do some checks at the top to verify the table content appears normal before I allow the script to proceed.  However, this will not run because the "GO" cancels out the label at the end that I want to jump to and causes an error.

What is the best way to write this to accomplish this?

Thanks,


DECLARE @RecCount Int

SET @RecCount = (SELECT COUNT(*) FROM TEST_SAPbacklogInputTabDlm)

IF (@RecCount = 0)  
	BEGIN
		PRINT 'ERROR - ZERO RECORDS in SAPbacklogInput'
		GOTO EXIT_ME
    END;

-- Check if the file size is less than 80% of previous days file
DECLARE @OldCount Int
DECLARE @NewCount Int

SET @OldCount = (SELECT Count(*) AS OldCount FROM TEST_DashboardData) 
SET @NewCount = (SELECT Count(*) AS NewCount FROM TEST_TheNewDashboardData) 

IF (@NewCount / @OldCount) < 0.8  
	BEGIN
		PRINT 'New File Size Less Than 80% Of Old File'
	    UPDATE FileCheck  
        SET ErrorFlag = 'Y',  
            JobEndFlag = 'N', 
            Comments = 'New File Less Than 80% Of Old File'  
        WHERE FileLocation LIKE '%PeruOpenOrderExtract.xls'
        GOTO EXIT_ME
    END;


-- Looks like we are good to rename tables.

 
UPDATE FileCheck 
SET LastStepCompleted = 'Renaming Files', ErrorFlag = 'N', JobEndFlag = 'Y', RenameFlag = 'Y'
WHERE FileLocation LIKE '%OpenOrderExtract.xls' 
	   
	  
Print 'DashboardData table Renames...'
GO

execute sp_rename 

Open in new window

0
I have this VBA code: -
    objCmd.CommandText = "Exec [sp_UpdateSysproDispatch] '" & TxtOrderNo & "', '" & PurchaseOrderNumber & "', " _
    & "'" & SalesOrderNumber & "', '" & SalesOrderLineNumber & "', '" & SysProDespatchNumber & "', '" & SysProDespatchLineNumber & "', " _
    & "'" & DespatchDate & "', '" & StockItemCode & "', '" & QuantityToRecieve & "', '" & Warehouse & "', '" & SageStatus & "', " _
    & "'" & SysProStage & "', '# & DeliveryDate & #'"
    objCmd.ActiveConnection = adoConn_Sage
    Set objRs = objCmd.Execute

Open in new window


But I get this error: -
SP ErrorCan Anyone help?
0
I have installed SQL Server 2016 with  all the features, but when I open the management studio I am unable to connect to SSIS, all other services are availble and the SSIS  service is running. I read an article which suggested using Dcomcnfg.exe to add users to the permission for integration services which i have done. still unable to connect though

SQL server connector - Browsing for servers
0
Hi

I have been trawling the net to find this but Microsoft don't seem to have put much helpful info up.
Why is there not a simple way in my Azure account to go online and set up a User that can only see and not
edit a View in my SQL database?
0
Hi,

I have data below,needs to identify top sales increase week,need to compare week wise and identify which week made highest sales increase. below query answer is 23 (Increase) column.How to achieve this please

Week    Sale          Increase
20          10000          0
21          10023          23
22          10012          12


Thanks
0
Hi Folks
I'm not sure why this isn't working. I need to retrieve all records except where a few IDs are input.
I tried NOT IN (0,1,2) and I get all data (no filter).
if I use NOT IN (0) the '0's are excluded (as required), same for NOT IN (1)
is it valid to use a comma-separated set of integers in the IN clause? (or just strings in quotes?)
thanks for any useful comment.
0
Get MySQL database support online, now!
LVL 3
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

I am trying to convert a system from MySQL to Miscrosft SQL and things are going pretty smooth except for loop inside of a loop.

I have this simple statement which puts a suite number on each line:
      $sql = "SELECT * from suites WHERE Suite > '0' Order By Suite ASC" ;
      $result = sqlsrv_query ($conn,$sql);
        while( $row = sqlsrv_fetch_array( $result, SQLSRV_FETCH_ASSOC) ) {
      $SuiteNo = $row['Suite'];
      echo"$SuiteNo<br />";
            }
What I want to do is pull data from table "info" and list it next to the suite number. I know I should do a join statement, but have tried INNER JOIN, LEFT JOIN, and others and it will only pull suites where something is in info. I need it to list the suite no and then nothing next to it if nothing is in info that matches.
Where Cust# and Date pulls from info

Output would be

Suite       Cust#            Date
2              1234156      8/1/2017
3  
4
5              985621         8/5/2017
6              548871         8/6/2017
7
8
9
0
I Need to find all the Wordpress Post that do not have a specific meta data value

select p.`ID`,m.meta_key,m.meta_value 
from wp_posts p, wp_postmeta m 
where p.post_status ='publish' and 
p.`ID`= m,post_id in (select post_id from wp_postmeta where  meta_key = 'DocID');

Open in new window


gives all the published posts with a DocID.
I need to filter it further on another m.meta_key that is missing from some posts

in sudo code it would look something like
select p.`ID`,m.meta_key,m.meta_value 
from wp_posts p, wp_postmeta m 
where p.post_status ='publish' and 
p.`ID`= m,post_id in (select post_id from wp_postmeta where  meta_key = 'DocID')
and not exists (m.meta_key = 'wanted')

Open in new window


Hoping this makes sense and someone  can show me a better way
0
Hello all,

I have a view that does the following as an example:

WITH cte
(
SELECT CustomerID,
              CustomerName,
              RelatedCustomerID
FROM Customers
)
SELECT 'C_' + C.CustomerID AS CustomerID,
               CustomerName,
              'R_' + RelatedCustomerID
FROM CustomerActive A --Another Table not important
            JOIN cte C ON C.CustomerID = A.CustomerID
UNION
SELECT 'C' + C.CustomerID AS CustomerID,
               CustomerName,
              'R_' + RelatedCustomerID
 FROM CustomerInactive I --Another table not important
            JOIN cte C ON C.CustomerID = I.CustomerID

Now what I need to do is get a grouped by on the RelatedCustomerID's and append to this result set the data from the CTE joined on the RelatedCustomerID without the 'R_' to the CTE CustomerID.  I don't want the non appended CustomerID (C_ and R_) just the field in the result it should be the same fields as above in the return.  One way would be to surround the whole UNION and then JOIN again but I am wondering if there is any better cleaner way etc.  It should all be a distinct list as well of the records.
0
Hi Guys,

I have got two databases on a server. Both of them have got the same amount of data, structure and stored procedures are also the same. One is for my internal testing and the other is for the dev testing.

For some reasons the same query on dev testing database calle (Spaces_SBDeployment) is very slow as compared to my own dev/test database (Spaces).

Please find below the images of the query execution results and time taken from both the databases using the same stored procedure.

This is the shared dev database which is very slow takes 21 seconds to bring back results
Shared dev database
This is the database for my own dev/test  takes 3 seconds to bring back the results for the same query and from the same amount of data
Internal dev/test database
Kind regards
0
Hi
I logged into my SQL Azure database in SSMS and set up a user called AngloUser
I then tried to run the following code to allow access to a table called zPerformanceAnglo
but got the error further on

GRANT SELECT ON schema.zPerformanceAnglo TO [AngloUser]
GO

Open in new window


Cannot find the object 'zPerformanceAnglo', because it does not exist or you do not have permission.
0
Hi

In Azure SQL is it possible to set up a user or connection string that can only see one table?

Thanks
2
I have a form linked to a sql table (tbl_employee). On the employee table I added a new column called category. Back on the form I created a combo box via the wizard. In the drop down you have three choices. General-Professional, Executive, and Manager. The selection is then stored in the table (category field). I went through a bunch of records and selected the appropriate choices in the drop down. I then verified the selections were being stored in the table. Then I accidentally deleted my combo box. No when I try and repeat the process the combox is blank even though they are still stored in the table. If I click on the combox the choices I re-typed are there but selecting them does nothing. At the bottom of the form it says: control can't be edited; its bound to unknown field category. My control source says category but still no luck. How do I get this back to its original state?
0
Hi,
I have a two tables

1) Batch_Info
2) Document_Info

In the Batch Info table having the batch information along with deleted document information. In the table we have two columns there
Document GUID and Document Count
Document Guid ={3B7F8368-DC22-41a5-BFBA-65D77672881D}|{8F72352D-1F58-4119-96F2-80603F32F06C}|{AC45F547-07A7-4534-A6AD-8A732BC38E43} ( Each document seperatd by pipe '|')
Document Count = 3

I need to write the sql to update those documents delete flag as true in the document_info table...

Like
Document Guid                                                                   Deleted
{3B7F8368-DC22-41a5-BFBA-65D77672881D}                   True
{8F72352D-1F58-4119-96F2-80603F32F06C}                       True
{AC45F547-07A7-4534-A6AD-8A732BC38E43}                    True


Regards
0
Hey all I have the following SQL Query that I am using in order to get comma separated values from a cell:

WITH cte AS(
    SELECT        
        uT.id AS UID, 
		uT.employeeID, 
        uP.type, 
		pP.name 
    FROM 
		usersTbl AS uT
    CROSS APPLY 
		dbo.DelimitedSplit8K(uT.userPerms,',') AS uPcommaItems
    INNER JOIN 
		usersPermissions uP ON uP.id = uPcommaItems.Item
	CROSS APPLY 
		dbo.DelimitedSplit8K(uT.userPermPages,',') AS pPcommaItems
    INNER JOIN 
		pagePermissions pP ON pP.id = pPcommaItems.Item
)
SELECT DISTINCT 
	UID, 
    employeeID,
    STUFF(
			(
				SELECT 
					',' + stuff1.type
				FROM 
					cte AS stuff1
				WHERE 
					t.UID = stuff1.UID 
				FOR XML PATH(''), TYPE
			).value('.', 'NVARCHAR(MAX)'), 1, 1, ''
		  ) AS userPermissions,
	STUFF(
			(
				SELECT 
					',' + stuff2.type
				FROM 
					cte AS stuff2
				WHERE 
					t.UID = stuff2.UID
				FOR XML PATH(''), TYPE
			).value('.', 'NVARCHAR(MAX)'), 1, 1, ''
		  ) AS pagePermissions
FROM 
	cte AS t

Open in new window


The userTbl table looks like this:
------------------------------------------------
ID    | employeeID | userPerms | pagePermPAges
------------------------------------------------
15    | 3FdFieu9I  | 1,3       | 1,4,5,6

Open in new window


The userPermissions table looks like this:
----------------
ID | type
----------------
1  | Read
2  | Write
3  | Upload
4  | Admin

Open in new window


And this is my pagePermissions table looks like:
----------------
ID | name
----------------
1  | bindex
2  | flight
3  | submit
4  | form
5  | information
6  | myPage
7  | register

Open in new window


My current output of the query above is this:
-------------------------------------------------------------------------------------------------------
ID    | employeeID | userPermissions                         | pagePermissions
-------------------------------------------------------------------------------------------------------
15    | 3FdFieu9I  | Read,Read,Read...upload,upload,upload...| Read,Read,Read...upload,upload,upload...

Open in new window


It should read this though:
-------------------------------------------------------------------------
ID    | employeeID | userPermissions | pagePermissions
-------------------------------------------------------------------------
15    | 3FdFieu9I  | Read,Upload     | bindex,form,information,myPage

Open in new window


Any MS SQL guru out there that can help me out?
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.

I have three tables one being the parent (PO) and the other two child tables being (Inv) and (Gen).
The child tables may either both have data related to the PO parent, or only one may have data related to the PO parent table (POID).
I need to create a view that ties to the PO parent but lists the child data on individual lines. I am having issues with lines duplicating.

Only have limited SQL experience

Thanks!
merged-tables.xlsx
0
I need to develop a series of concatenations based on values.. some are numbers some are varchar2.

The placeholders are required:
CREATE TABLE tb1
(
  ID              NUMBER(19)                    NOT NULL,
  tank            NUMBER(10),
  roll         NUMBER(10),
  weld_1    VARCHAR2(2 BYTE),
  weld_2    NUMBER(10),
 weld_3    VARCHAR2(5 BYTE),
  weld_4    VARCHAR2(5 BYTE)
);

Insert into tb1(ID, tank, roll, weld_1, weld_2, weld_3,weld_4)
 Values (1, NULL, NULL, NULL, NULL, NULL, NULL);
 
Insert into tb1(ID, tank, roll, weld_1, weld_2, weld_3,weld_4)
 Values(2, 1037, NULL, NULL, NULL, NULL, NULL);

Insert into tb1(ID, tank, roll, weld_1, weld_2, weld_3,weld_4)
 Values(3, 1038, 6, NULL, NULL, NULL, NULL);

Insert into tb1(ID, tank, roll, weld_1, weld_2, weld_3,weld_4)
 Values(4, 1039, 7, 'Y', NULL, NULL, NULL);

Insert into tb1(ID, tank, roll, weld_1, weld_2, weld_3,weld_4)
 Values(5, 1040, 8, 'Y', 96, NULL, NULL);

Insert into tb1(ID, tank, roll, weld_1, weld_2, weld_3,weld_4)
 Values(6, 1041, 0, 'Y', 55, '102', NULL);

Open in new window


desired results

 id      concat_field
  1
  2      1037.0()()()()
  3      1038.6()()()()
  4      1039.7(Y)()()()
  5      1040.8(Y)(96)()()
  6      1041.0(Y)(55)(102)()


logic:
When tank is null, the record shows ID and nothing else
 when tank is not null and roll is null record shows tank.0 followed by placeholders ()()()()
 when tank is not null and roll is not null and all others are null  record shows tank.roll followed by placeholders ()()()()
 when tank is not null and roll is not null and weld_1 is not null and all others are null record shows tank.roll(weld_1)()()() the placeholders are required.
 when tank is not null and roll is not null and weld_1 is not null and weld_2 is not null  and all others are null record shows tank.roll(weld_1)(weld_2) followed by placeholders ()()
0
What I'm trying to do is get the earliest date from ON_DOCK_DATE field and on QTY_OPEN and DSESCRIPTION field get the qty associated to the earliest.

For Example:
PART                          DESCRIPTION  QTY_OPEN          MinOfON_DOCK_DATE
900-64009-505                SCREW              2                     11/2/2017  
900-64009-505                SCREW FAI       12                     10/5/2017  
900-64009-505                TEST SCREW    28                     12/7/2017  

I would want to only see:
900-64009-505                SCREW FAI       12                     10/5/2017  

Here is my Sql String:

SELECT DISTINCT tbl_PART.PART_NUMBER, tbl_PART.DESCRIPTION, tbl_PART.QTY_OPEN, Min(tbl_PART.ON_DOCK_DATE) AS MinOfON_DOCK_DATE
FROM tbl_PART
GROUP BY tbl_PART.PART_NUMBER, tbl_PART.DESCRIPTION, tbl_PART.QTY_OPEN;
0
I have a sql statement that subtracts two numbers:
CASE ALLOCATED WHEN NULL  THEN QTY_ON_HAND - ALLOCATED ELSE QTY_ON_HAND END AS On_Hand_Less_Allocated

Qty on Hand    Allocated   On_Hand_Less_Allocated
10                        6                       4
4                          6                       2  (should be -2)

What am i doing wrong?
0
Hi out there.

I have a - supposedly - basic issue.  I have a SQL/web-server with a general login for myself. If I use that in a connection string, it doesn't work. I wonder if I need to create a login/user via the SQL Server Management Studio?
Can anyone please give me a quick guide how to create a user/login I can use in a connection string like this:

"Driver={SQL Server};Server=SERVERNAME\SQLEXPRESS;Database=databasename;Uid=username;Pwd=password"

Thank you.

Best regards

Ulrich
0
Hi experts,
I have a colum in a table, filled with numbers of eight digits.  
Let me give you an example of the column:
01482021
01466587
02447852
I read, one by one, each row of this column with a SQLDaraReader loop
while (reader.Read())
            {
                do the job 
            }

Open in new window

I need to split each line  in two numbers :
- one is composed of the first two digits. In the first line of the example table it will be "01"
- the other is composed with the remaining part : in the example it will be 482021 ; 466587 ; 447852
I want, after reading all the lines, to find the MAX (or highest) value of the set " 482021 ; 466587 ; 447852 ; etc.)
I've tried a lot of combinations of split and parse without success.
Can someone help me ?
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.