Query Syntax

54K

Solutions

20K

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 need help with an SQL Join/results

I have two tables SCHOOLS and STUDENTS  They both have a SCHOOLID that can be joined.  What I need to see are ALL of the SCHOOLS and fields from the SCHOOLS table and  a virtual field telling me if there are ANY STUDENTS that have that SCHOOLID.

Example:

SCHOOLS
   SchoolID
   School
   Active

STUDENTS
   name
   SchoolID

 RESULT Table:
SchoolID     Active     Used (yes or No)  

So the virtual field USED is telling me if there are ANY schools that are in the STUDENTS table.  

Then I want to make another query that I can add a where clause to the SchoolID  So SAME result be asking "Does SchoolID have any students from the STUDENTS table that have that SchoolID"  Then it would list that school (ONE RECORD)  SchoolID, School, Active, Used
0
Announcing the Winners!
LVL 19
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

Need help with why it keeps returning the above mentioned issue.

  --write a query that shows me how many times each car was rented in 2018.

 Select Distinct Count(Car_ID) CtCar, rental.car_id
  from [Car]
	left join [rental]  on car.car_id = Rental.Car_id
Where year(rental.start_date) = '2018'
group by rental.Car_ID

Open in new window

0
Hi,
How to copy contents of Column2 to Column1 .
Example:
Table1
Column1     Column2
ABC                  XYZ

Expected Result:
Table1
Column1     Column2
ABC XYZ

Any suggestions?

Thanks
0
I have this MySQL SQL statement that works fine:

select a.name,a.subTypeName, a.model, a.ver, b.ver, a.eosc, a.eoswmr, a.eorfa, a.ldos, a.eosd 
from inv2 a, modvendorver b 
where a.model = b.model;

Open in new window


But that same code in Oracle gives:

Line 1, ORA-00904: "b.model": invalid identifier

And when I change it to:

select "inv2.name", "inv2.subTypeName", "inv2.model", "inv2.ver", "modvendorver.ver"
from "inv2"
inner join "modvendorver"
on "inv2.model" = "modvendorver.model"

Open in new window


Oracle gives:
Line 4, ORA-00904: "modvendorver.model": invalid identifier

I've tried several other statements using full, left and right join . . . all give me the same error. But I cannot find what I am doing wrong.

Any thoughts?

Steve
0
SQL SSMS was updated to v18, however, SSIS is not connecting. Web search stated if the versions do not match, SSIS will not connect to updated versions. The original SSMS is v14 and SSIS does connect (so I am regulated to use v14 SSIS). I have searched the internet for v18 SSIS with no luck and cannot find it to download.

Does anyone have a suggestion?
0
This sql statement returns a whole number, like:

1, 7, 14

I want it to return a number like:

1.0.0
7.0.0
14.0.0

CONVERT(varchar(10), ISNULL(etw.Listorder, 99)) as 'ListOrder3'

Open in new window


How would I alter it? Currently what I've tried gives errors pertaining to it not being an integer value.

thanks!
1
I have a table of data which consists of three measurements (called NPI) which are collected on a yearly basis for the whole country, counties and communities.

So for each year there are three country measurements, three for each county and three for each district.

The table has many years of data where each row defines the year, measurement number, countyid, communityid and the measured NPI value.

I would like to extract a years data for the country, a specific county and a specific community.  To me it is probaly a pivot but i cannt get my head round pivots.

So I know the year, the countyid and communityid.

The table below shows the original data.
Original data
And this is how i want to collect it:
e2.JPG
Note that a row with -1 for countyid and communityid represents the country value.

Would apprciate any tips in generating the sql query to get the required data,. SQL Server 2016.  Many thanks
0
Hi Experts,
i'm getting following datetime output but how to ignore when sending to the
SQL server. becuase SQL server genearion error message

01/01/0001 00:00:00

Sql error :
SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
0
Simple select query, but one field needs to return a 'Y' if a record exists in a field in another table. Having trouble with the exists syntax.
Example:

                        Select a.main, a.other, b.something
                                  from b inner join
                                                a on b.main = a.main
                                                       where a.criteria = 'x'

Need to add a field like: 'Y' as Flag where exists (select c.sub from c)
so that results would be:
Main  Other  Something  Flag
-------    -------   ---------------   -----
Main, Other, Something, Y
Main, Other, Something, (can be null or 'N')
Main, Other, Something, (can be null or 'N')
Main, Other, Something, (can be null or 'N')
Main, Other, Something, Y
Main, Other, Something, Y
0
In SQL Server 2012, why won't the following dynamic SQL statement execute?

SELECT DISTINCT 7 As EntityId, E7Id As InstanceId FROM ##TempTable_461_0 UNION SELECT DISTINCT 1 As EntityId, E1Id As InstanceId FROM ##TempTable_461_0

Note: I verified that ##TempTable_461_0 has the columns "E7Id" and "E1Id".
0
Exploring ASP.NET Core: Fundamentals
LVL 19
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

so on a btn click I have this code:  not getting any errors but it's not transferring to the database:

 if (rd.HasRows)
                    if (rdshop.HasRows)
                        if (rdoper.HasRows)
                        {
                            SqlConnection uptime = new SqlConnection();
                            uptime.ConnectionString = ConfigurationManager.ConnectionStrings["gcerp"].ToString();
                            uptime.Open();
                            cmd.CommandText = "Insert into [_lcclockfil](empId, shift, clockin, ord_no, item_no, oper_no, oper_desc) values('" + TxtEmp.Text + "', '" + TxtShift.Text + "','" + TxtTime.Text + "', '" + TxtShop.Text + ",'" + TxtSHOPItem.Text + "','" + TxtOp.Text + "','" + TxtOpDesc.Text + "')";
                            uptime.Close();
                            Response.Write("<script>window.alert('Complete')</script>");
                           
                            TxtEmp.Text = "";
                            txtfullname.Text = "";
                            TxtShift.Text = "";
                            TxtShop.Text = "";
                            TxtOp.Text = "";
                            TxtOpDesc.Text = "";
                        }
                        else
                        { Response.Write("<script>window.alert('Did Not Update')</script>"); }
            }
0
proper passing of variables to sql server procedure in excel vba office 365 (64 bit)

Is this the correct way to pass variables to sql server stored procedure ?

Dim strStart As String
Dim strEnd As String


Dim myValue As Variant
Dim myValue2 As Variant
myValue = InputBox("Supply a Start Date Please. Format YYYY-MM-DD")

myValue2 = InputBox("Supply a End Date Please. Format YYYY-MM-DD")
 

Dim sQuery As String
    Dim i As Long
    Dim SearchCodes As String
    Dim dB2 As New ADODB.Connection
    Dim rs2 As New ADODB.Recordset

Dim dbconnstring As String
Dim strsql As String


Set dB2 = New ADODB.Connection
dB2.CursorLocation = adUseClient




dbconnstring = "provider=sqloledb;Server=B025.us.xxxxx.com;Database=PROGRAM_WORK;Uid=xxxx;Pwd=zzzzz;"

' IS THIS LINE CORRECT ? BELOW
strsql = "EXECUTE [program_work].dbo.[sp_Clm] ] N'" & MyValue & "' & N'" & MyValue2 & "' ;"



dB2.CommandTimeout = 400
                With rs2
                 .ActiveConnection = dB2
                 .Open strsql
                End With


-----  more code

Open in new window

0
I am trying to build a small web application in ASP.net that texts our employees when we have an urgent message that we need to distribute.  My idea is that someone with the authority to send one of these messages will login, create a message, and send it.  I have been able to send messages successfully if name a single mobile number to a variable, or even when I assign multiple numbers to an array.  Of course I don't want to go to the code to add/remove numbers, so I have a database with the numbers in it.

This is the part that is frustrating me is that I don't know how to get my data from SQL into my application.  The closest I have gotten appears to assign the current location to my array, I am not even sure how that is happening.

[EDIT: the data I am trying to get from SQL is a single column of integers representing phone numbers.]

Here is my code asking SQL for data:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

namespace ProjectASPudemy.Pages
{


    public partial class Comms : Page
    {
        private const string cs = "Data Source=ElRando\\SQLE;Initial Catalog=Inventory;Integrated Security=True";
        private const string sql = "SELECT CellPhone, LastName FROM Employees WHERE CellPhone IS NOT NULL AND CurrentEmployee = 1";
        private const int V = 0;

        protected void Page_Load(object sender, 

Open in new window

0
Need to remove dashes and add ISNULL function to this data column.

I have this so far:
,'N' + RIGHT(REPLICATE('0', 11) + CAST(RX_NDC.NDC_CODE AS varchar(11)), 11) AS 'ERX_NDC_CODE'

Someone must have helped me with this in the past because I have no idea what Replicate is doing.  
My data currently returns like this:
N50474-002-6
N0409-6629-0
N0781-3204-9
NULL
N0338-0049-0

I need to remove the dashes when there is data, so I changed it to this:
 ,'N' + RIGHT(REPLICATE('0', 11) + CAST(REPLACE(RX_NDC.NDC_CODE,'-','') AS varchar(11)), 11) AS 'ERX_NDC_CODE'

This seems to work, but then when I try to add ISNULL anywhere in the statement it comes back with errors.

Can someone help?
0
I'm using the SQL view USER_INFO to retrieve user status. The same program is called interactively from the command line and during sign-on. Interactive works great but it fails during the sign-on process.  

My SQL:
Exec SQL                                  
  Select STATUS INTO :CUSSTS              
    From USER_INFO                        
    Where AUTHORIZATION_NAME = :INUSER;    

Then I tried the sql below base on an older article by Joe Pluta.  Both behave the same.

 Exec SQL VALUES                                              
   (Select STATUS                                              
     From USER_INFO                                            
     Where AUTHORIZATION_NAME = :INUSER) INTO :CUSSTS;        

Can anyone tell why the sign-on process has issues? Many Thanks!
Lynn
0
Hi:

When I run the query below, SQL gives me the following error:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

How do I modify the syntax to get around this?

BTW, I know that there are "brackets" or something that need to be placed around scripts when they are posted, here.  But, I don't remember what they are.  Forgive me.

Thank you!  Much appreciated!

John

delete IV10301 where IV10301.STCKCNTID IN (
select * from IV10301
INNER JOIN IV00112
ON IV10301.ITEMNMBR = IV00112.ITEMNMBR AND IV10301.BINNMBR = IV00112.BIN
where (IV00112.QUANTITY - IV00112.ATYALLOC) <= 0
AND STCKCNTID = 'TEST 11-12-19')
0
Hi,
This is  a variation on a question answered for me by pcelba
https://www.experts-exchange.com/questions/29159934/SQL-Server-Query-to-return-rows-based-on-a-column-value-change.html
In that question I wanted to run a query everyday against a table to identify the rows that had a LOCATION value change THAT DAY,  which pcelba provided a working solution.

I know want to expand on that requirement, I want to run a query that will return  all rows where a LOCATION changed historically, not just TODAY.

For example , using the code below, I get two rows returned,
What I want returned is all rows  that have had a location changed over their history, in this case in the last 730 days.
This would return  rows 6,7,10 from the top table,
Not just lines 6,7 as it currently does.

I see why line 10 is not returned,
this is due to the sub-query in my select statement which returns the TOP 1 value based on  date descending for each ID

I am looking for ways maybe to modify the existing code, if this is possible? or maybe a rewrite is needed

Any guidance appreciated, thanks

The current code i am using is
DECLARE @YourTable3 TABLE (Id int, Date datetime, location int)
INSERT INTO @YourTable3 VALUES (2833, CAST('2017-08-08' AS DATE), 185), (2833, CAST('2017-12-22'AS DATE), 185), (2833, CAST('2019-04-23' AS DATE), 185), 
  (6392, CAST('2017-05-15' AS DATE), 174), (6392,CAST('2017-05-29' AS DATE), 174), (6392, CAST('2019-10-01' AS DATE), 240),(2833, 

Open in new window

0
If the sql doesn't return any record, the php return an error :
"trying to get property of non-object".

How to check null recordset return in php ?

      
        $sql= "select * from user where userId= $id";
      $result= $mysqli->query($sql);
      
      if (($result->num_rows) ==1) {
           :
           :
1
How can I use if else statement in access query

API10       API14


If API14 is empty then set API14= API10 & "000"

if API10 is empty then set API10= left (API14, 10)

I would really appreciate any help.

Thank you.
0
Microsoft Azure 2017
LVL 19
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Why do I keep getting these large numbers in my results from a function? Within the table, the value is 60 but the results within a view in which the function is embedded, I get 5169006. Screenshots attached and the code for both the function and the view are below.

Function
create or replace FUNCTION BA_WO_TAT_CORRECTION_2(p_woo IN NUMBER)
RETURN NUMBER
IS v_WO_UDF_018 number;
V_WOO_AUTO_KEY number;
BEGIN
-- THIS FUNCTION RETURNS THE NUMBER OF DAYS SPECIFIED IN WO USER FIELD FOR TAT CORRECTION
	select woo.WO_UDF_018,woo.WOO_AUTO_KEY
	into v_WO_UDF_018, v_WOO_AUTO_KEY
    from wo_operation woo
    where woo.woo_auto_key=p_woo;
RETURN(v_WO_UDF_018 + V_WOO_AUTO_KEY);
exception
  when others then
    return 0;
END;

Open in new window


select wo_sum2."WO_NUMBER",wo_sum2."WO_TYPE",wo_sum2."RC_NUMBER",wo_sum2."NOTES",wo_sum2."PEL_TAIL_NUMBER",wo_sum2."MANUAL_TAIL_NUMBER",wo_sum2."AMDT_MOD_IN", 

Open in new window

0
Hi I am struggling with how to apply this in MS SQL.  I want to get the status of a rental unit to populate a table showing the last status on a rental unit by month.  A range between an event status can span several months, ie, a unit is rented for a year.  I am trying to show a historical monthly view of unit status based on the max event of the historical month or the last non null status of the previous month that had a status change.  I've tried applying a max window function with the rows preceding unbounded hint unsuccessfully however because I am right outer joining the dates from a it doesn't seem to apply the partition range.  Any suggestions or help would be appreciated.


IF OBJECT_ID('TempDB..#tmpFUnit','U') IS NOT NULL
DROP TABLE #tmpFUnit

--===== Create the test table with

CREATE TABLE #tmpFUnit
(
rptMth varchar(6),Buiding varchar(4),Unit varchar(4),uStatus varchar(15)
)

insert into #tmpFUnit
SELECT '201809','1820','0203','Occupied' UNION ALL
SELECT '201810','','','' UNION ALL
SELECT '201811','','','' UNION ALL
SELECT '201812','1820','0203','Vacant' UNION ALL
SELECT '201901','','','' UNION ALL
SELECT '201902','','','' UNION ALL
SELECT '201903','','','Unrented' UNION ALL
SELECT '201904','','','' UNION ALL
SELECT '201905','','','' UNION ALL
SELECT '201906','1820','0203','Occupied' UNION ALL
SELECT '201907]','',' ','' UNION ALL
SELECT '201906','','',''

select * from #tmpFUnit

Desired Output
0
I have the following table:

WeekEndDate      WeekNumber      WeekNumSeq
1/5/2019      1      1
1/12/2019      2      2
1/19/2019      3      3
1/26/2019      4      4
2/2/2019      5      5
2/9/2019      6      6
2/16/2019      7      7
2/23/2019      8      8
3/2/2019      9      1
3/9/2019      10      2
3/16/2019      11      3
3/23/2019      12      4
3/30/2019      13      5
4/6/2019      14      6
4/13/2019      15      7
4/20/2019      16      8
4/27/2019      17      1


How can I get SQL to assign a unique value to each 8 week block like this:
WeekEndDate      WeekNumber      WeekNumSeq      UniqueID
1/5/2019      1      1      1
1/12/2019      2      2      1
1/19/2019      3      3      1
1/26/2019      4      4      1
2/2/2019      5      5      1
2/9/2019      6      6      1
2/16/2019      7      7      1
2/23/2019      8      8      1
3/2/2019      9      1      2
3/9/2019      10      2      2
3/16/2019      11      3      2
3/23/2019      12      4      2
3/30/2019      13      5      2
4/6/2019      14      6      2
4/13/2019      15      7      2
4/20/2019      16      8      2
4/27/2019      17      1      3
0
How to modify the following query to add a total column of TaskSelected?

TRANSFORM COUNT(T.TaskSelected)
SELECT R.TAID, T.TaskSelected
FROM RBWS R
LEFT JOIN TaskSelected T  ON R.TaskSelectedID=T.ID
GROUP BY R.TAID
PIVOT T.TaskSelected
0
Unable to upgrade SQL Server from SP1 to SP2.  The database instance is greyed out.
Current version: 13.0.4001.0
Upgrade file being used: SQLServer2016-KB4524334-x64.exe
This file has worked on other instances on the same SQL server and brings those instances up to 13.0.5492.2
The instances that successfully upgraded were at 13.0.5026.0
The service pack installer does see the 13.0.4001.0 instance, and shows it unchecked for the current upgrade, but the checkbox is greyed out and can't be checked.
0
I need to split email addresses in the form Mike.Smith@outlook.com. The result I'm looking for is Smith, Mike. Using Oracle SQL
0

Query Syntax

54K

Solutions

20K

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.