Query Syntax

48K

Solutions

19K

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 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
Free Tool: Path Explorer
LVL 9
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

I need to create a tabbed report in SSRS that displays totals by Customer. See the attached report layout. Tab 1 would be Net Open Sales. This is the top left report in the attached example.

My question/Issue is how do I write the query to make this dataset easy to use in SSRS Report Builder?

For example my Customers are arranged in Alpha order however JC Penny and Kohl's are listed only once on the report however in the database I need to sum them because there are two ID's. One for in-store and one for ecommerce.

This is what I began with however it gives me the same total for every record for KOHLS. More importantly I'll need a sub-total minus walmart and a grand total. I'm not certain that I'm approaching this correctly.

SELECT  ( SELECT    SUM(price * total_qty)
          FROM      [KLL Open Orders - Header & Detail]
          WHERE     ( ( CUSTOMER IN ( 'KOH0010', 'KOH0015' )) )
                    AND ( (division = 'KLL') )
                    AND ( (end_date > = DATEADD(DD, -30, GETDATE())) )
                    AND ( ( ORD_STATUS = 'O' ) )
        ) AS KOHLS2017
FROM    [KLL Open Orders - Header & Detail];

Open in new window

Capture.JPG
0
It's time to update sql server.  But I can't access the databases on this instance of sql server 6.5.  See image for error.  Notice there is a green light, but when I try to expand, I get the error.  Interestingly, the legacy program that is associated with the databases on this instance runs just fine.  Any ideas how I can get to the data via enterprise manager?
error message
0
I have a program that uses sql server database.  When I try to log in the first time it gives me a Login timeout expired window.   I click ok and then try to login again and it works.  Any ideas how to fix this??
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
Hi experts,
Could you please help. How to identify table row inserted or updated date & time using oracle way. E.g system column etc. and without using archive log or flash back files

Since this is a custom table and we don't maintain created or last updated column defined. So could you pls help

Oracle Database 11g
0
Hi. I want to add some values in SQL on the basis of  some other condition(something like using sum-if in excel). Below is the example:

I have a table with three columns using which I want to calculate the total product value and store the value in a column with respect to Bound Status and keeping values for other statuses as Zero.

Input Table:

Product      Status      Value
ABC-01      Bound      10
ABC-02      End              12
ABC-03      Canc      -2
DEF-01      Bound      15
EFG-01      Bound      20
EFG-02      Canc      -15

Output Table:

Product      Status      Value      Final Value
ABC-01      Bound      10            20
ABC-02      End              12              0
ABC-03      Canc      -2             0
DEF-01      Bound      15           15
EFG-01      Bound      20             5
EFG-02      Canc      -15             0

Thanks
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,

Previously I was able to make use of an sysadmin account on our client's server to create jobs and execute packages. The client has now changed the rights of this account so it's not a sysadmin anymore and now the jobs are failing. They don't want to give me another sysadmin account. I now need to know how do I go forward in giving this new account permissions to run Sql Server Agent jobs and to execute packages. The account they have created for me is not a windows login but only a sql login.

Regards
Christian
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
Learn how to optimize MySQL for your business need
LVL 3
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

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
I Created a WebGrid using MVC but when i try to run I am getting this Error Message "SqlException: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.", I Checked SQL Server Logs but it is not even Connecting the Database, It is timing out after 1 minute, I Included these Timeouts but it is not working, Please help.
binding name="basicHttpBinding"  				 
closeTimeout="00:10:00" openTimeout="00:10:00" receiveTimeout="00:10:00" 
sendTimeout="00:10:00"

Open in new window

2
I have a very complex query that needs to either transform to un-pivot then back to pivot results.  I am using SQL.

Original Body of the query than needs to be transformed to add a couple of columns to identify the Sales type and Labor Type( based o the last case statement:

select
              dsd.department_id
 , dsd.department_name           , f.ro_id
            , f.vin
            , f.dim_ro_close_dealer_date_key
            , (de.employee_first_name || ' ' || de.employee_last_name) as serviceadvisorname
            , dd.year_month_start_dt
            , f.advisor_id
            , (f.internal_labor_sale_local + f.warranty_labor_sale_local + f.customer_labor_sale_local) as saleslabor
            , (f.internal_parts_sale_local + f.warranty_parts_sale_local + f.customer_parts_sale_local) as salesparts
            , (f.internal_sublet_sale_local + f.warranty_sublet_sale_local + f.customer_sublet_sale_local) as salessublet
            , (f.internal_lube_sale_local + f.warranty_lube_sale_local + f.customer_lube_sale_local) as salelube
            , (f.internal_misc_sale_local + f.warranty_misc_sale_local + f.customer_misc_sale_local + f.internal_shop_sale_local + f.warranty_shop_sale_local + f.customer_shop_sale_local) as misc_sale
            , (f.internal_labor_cost_local + f.warranty_labor_cost_local + f.customer_labor_cost_local) as costlabor
            , (f.internal_parts_cost_local + f.warranty_parts_cost_local + f.customer_parts_cost_local) as costparts
   

Open in new window

2
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
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
The Orion Papers
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

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
Hi we have job that copies file to a destination, So is there a way that I can be notified after all the files are downloaded completely ? Is there a feature in SQL, SSIS or Batch script that can identify and send a message or raise a flag once all the files are downloaded.
0
We have a server 2008 R2 server with SQL server 2008 R2, running 16 GB of memory, 64 bit OS, Xeon X340 @ 2.4Ghz.

All resources are funnelled towards the SQL application, we seem to have intermittent slow down and performance issues where the Disk queue jumps up to 2.95, IO is 10 MB/Sec

System PID 0 is using the most total Bytes per second of: 8,720,200

Heaviest disk IO is the SQL application itself.

This only seems to happen sporadically two or three times a day, and I haven't been able to correlate it to any specific task or backup job running during that period of time.


This SQL server services multiple sites with users directly accessing the database (no terminal server or replication to local SQL). And all users are affected by the performance issues.

The disk speed is 7200 RPM and the server hosting them is from 2010.

Any ideas?
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 EEE:


I have a dataset of units being bought and/or sold. The deal ID is determined by column D and you may buy and sell units under the same deal. Column E is just some descriptive for the deal.  

Original dataset

D  E  BS B_VAL  S_VAL

d1 e1 B  200  null
d1 e1 S  null 100
d2 e2 B  500  null


Evaluate if B_VAL > S_VAL given same D then return one row only but with BS as B and keep both the B_val and S_val on the same row.

d1 e1  B 200 100
d2 e2  B 500 null
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

Query Syntax

48K

Solutions

19K

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.