Query Syntax

53K

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 am looking for oracle 10g query  to check if a string is starting with anything between 500 to 599 and return derived plant as P5 if plant is empty

The field DocNo  Plant are  part of TB-Doc
 

Example
   DocNo                                                             Plant              
  ======  
   523 Doc1                                                         P1
   Doc1                                                                 P2
   5002Doc2                                            
   Document523SubDoc                                
   512-Test                                                           P3
   522-Doc3                                                

The Query should return  as follows (  Please note 512-test derived plant is P3)
   DocNoDerived                                             PlantDerived              
  ======  
   523 Doc1                                                         P1
   Doc1                                                                 P2
   5002Doc2                                            
   Document523SubDoc    
   512-Test                                                           P3
   522-Doc3                                                         P5
0
Angular Fundamentals
LVL 13
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Hi

How do I build an SQL query passing parameters

In the code bellow I need to pass 2 variables into an SQL Update command  

The SQL is wrong because it's not interpreting the variables properly

How do I build stored procedures passing parramiters


        SQLiteConnection dbConnection;
        public Form()
        {
            InitializeComponent();
            dbConnection = new SQLiteConnection("Data Source=D:\\PathTo\\database\\MyDatabase.db3;Version=3;");
            dbConnection.Open();
       }

................

// Code that bulds Old And New name

                                newName = illegalInFileName.Replace(newName, "");
                                    string path = FI.Directory + "\\" + newName;
                                    if (fName != newName)
                                    {
                                        FI.MoveTo(path);
                                    if (UpDateDBChkBox.Checked == true)
                                    {
                                        string OldName = FI.Directory + "\\" + fName;
                                        string sql = "update episode set path = " + @newName + " where path = '" + OldName ;
                                        SQLiteCommand command = new SQLiteCommand(sql, dbConnection);
                                        command.ExecuteNonQuery();
                                    }

Open in new window

0
PostgreSQL syntax problem.

Query below will run but result is: ERROR:  out of memory for query result

SELECT
AD.ADDRESS_DETAIL_PID as ADDRESS_DETAIL_PID,
AD.STREET_LOCALITY_PID as STREET_LOCALITY_PID,
AD.LOCALITY_PID as LOCALITY_PID,
AD.BUILDING_NAME as BUILDING_NAME,

AD.LOT_NUMBER_PREFIX as LOT_NUMBER_PREFIX,
AD.LOT_NUMBER as LOT_NUMBER,
AD.LOT_NUMBER_SUFFIX as LOT_NUMBER_SUFFIX,

FTA.NAME as FLAT_TYPE,
AD.FLAT_NUMBER_PREFIX as FLAT_NUMBER_PREFIX,
AD.FLAT_NUMBER as FLAT_NUMBER,
AD.FLAT_NUMBER_SUFFIX as FLAT_NUMBER_SUFFIX,

LTA.NAME as LEVEL_TYPE,
AD.LEVEL_NUMBER_PREFIX as LEVEL_NUMBER_PREFIX,
AD.LEVEL_NUMBER as LEVEL_NUMBER,
AD.LEVEL_NUMBER_SUFFIX as LEVEL_NUMBER_SUFFIX,

AD.NUMBER_FIRST_PREFIX as NUMBER_FIRST_PREFIX,
AD.NUMBER_FIRST as NUMBER_FIRST,
AD.NUMBER_FIRST_SUFFIX as NUMBER_FIRST_SUFFIX,
AD.NUMBER_LAST_PREFIX as NUMBER_LAST_PREFIX,
AD.NUMBER_LAST as NUMBER_LAST,
AD.NUMBER_LAST_SUFFIX as NUMBER_LAST_SUFFIX,

SL.STREET_NAME as STREET_NAME,
SL.STREET_CLASS_CODE as STREET_CLASS_CODE,
SCA.NAME as STREET_CLASS_TYPE,
SL.STREET_TYPE_CODE as STREET_TYPE_CODE,
SL.STREET_SUFFIX_CODE as STREET_SUFFIX_CODE,
SSA.NAME as STREET_SUFFIX_TYPE,

L.LOCALITY_NAME as LOCALITY_NAME,

ST.STATE_ABBREVIATION as STATE_ABBREVIATION,

AD.POSTCODE as POSTCODE,

ADG.LATITUDE as LATITUDE,
ADG.LONGITUDE as LONGITUDE,
GTA.NAME as GEOCODE_TYPE,

AD.CONFIDENCE as CONFIDENCE,

AD.ALIAS_PRINCIPAL as ALIAS_PRINCIPAL,
AD.PRIMARY_SECONDARY as …
0
PostgreSQL syntax problem.

Query below will run but result is: ERROR:  out of memory for query result

SELECT
AD.ADDRESS_DETAIL_PID as ADDRESS_DETAIL_PID,
AD.STREET_LOCALITY_PID as STREET_LOCALITY_PID,
AD.LOCALITY_PID as LOCALITY_PID,
AD.BUILDING_NAME as BUILDING_NAME

When  the query is amended to:

set FETCH_COUNT=1000
SELECT
AD.ADDRESS_DETAIL_PID as ADDRESS_DETAIL_PID,
AD.STREET_LOCALITY_PID as STREET_LOCALITY_PID,
AD.LOCALITY_PID as LOCALITY_PID,
AD.BUILDING_NAME as BUILDING_NAME

 the result is:
ERROR:  syntax error at or near "SELECT"
LINE 3: SELECT
        ^
SQL state: 42601
Character: 23

I am seeking help with the syntax.
0
Query problem in postgreSQL

There are 33 tables and there is a query that is meant to set the delimiter as '|'

COPY address_alias_type_aut
C:\GNAF\Authority Code\Authority_Code_ADDRESS_ALIAS_TYPE_AUT_psv.csv' DELIMITER '|' CSV HEADER;
 
But it will not run:
ERROR:  syntax error at or near "C"
LINE 3: C:\GNAF\Authority Code\Authority_Code_ADDRESS_ALIAS_TYPE_AUT...
        ^
SQL state: 42601
Character: 30

I've doublechecked the folders and file names in C: drive and they appear to be correct (files uploaded here)
The full tables setup query and the full delimiter query are attached.

Help will be much appreciated.
create-tables.txt set-delimiter.txt Authority-Code.rar
0
Hi all.

I'm working on a SSIS package, I have an "Execute SQL Task" which will email me the results of a sql query in an html table. I'm getting the following error:

"The query failed to parse. The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it."

I think it has to do with the UNION ALL in the query, I've used the same type of Execute SQL Task for other sql queries that are emailed to me with no issues, but this one is the only one with a UNION ALL. Does anyone have any idea how to fix this?

Begin
DECLARE @tableHTML  NVARCHAR(MAX) ;
SET @tableHTML =
    N'<H1>Below are the results</H1>' +
    N'<table border="1">' +
    N'<tr><th>Account ID</th>'+
    N'<th>Table Name</th>'+

    CAST( ( SELECT  td=  CRMAccount.AccountID,'' , td='CRMAccount' 
FROM         CRMAccount LEFT OUTER JOIN 
                      [ID Cross Reference].dbo.tblReference ON CRMAccount.AccountID = CAST([ID Cross Reference].dbo.tblReference.Division AS nvarchar(10)) 
                      + N'-' + CAST([ID Cross Reference].dbo.tblReference.ReferenceID AS nvarchar(10))
WHERE     (CAST([ID Cross Reference].dbo.tblReference.Division AS nvarchar(10)) + N'-' + CAST([ID Cross Reference].dbo.tblReference.ReferenceID AS nvarchar(10)) IS NULL)


UNION ALL

  SELECT   td=  CRMAttachment.AccountID,'', td = 

Open in new window

0
Need help with changing Microsoft Access SQL query to show date entered records that are more than 7 days old.
So far a lot of suggestions have not really worked for me at this link so I decided to add this as a new question:

https://www.experts-exchange.com/questions/29140112/Need-help-with-creating-a-cross-tab-report-that-would-identify-the-last-date-an-employee-entered-time.html?headerLink=workspace_answered_questions

I know must include : HAVING but no data is coming up with records that do have dates more than 7 days old.

SELECT Employees.EmployeeID, Employees.UserName, Max(TimeSheetData.DateEntered) AS MaxOfDateEntered
FROM Employees LEFT JOIN TimeSheetData ON Employees.EmployeeID = TimeSheetData.EmployeeID
GROUP BY Employees.EmployeeID, Employees.UserName
HAVING DateValue(Max(TimeSheetData.DateEntered)) > DateAdd("d",7,Date())
ORDER BY Max(TimeSheetData.DateEntered) DESC;

Open in new window


When I try the above I get DataType mismatch in criteria expression.

If I try below- I get no errors but I also get no records which can't be right cause 80% are over 7 days old

SELECT Employees.EmployeeID, Employees.UserName, Max(TimeSheetData.DateEntered) AS MaxOfDateEntered
FROM Employees LEFT JOIN TimeSheetData ON Employees.EmployeeID = TimeSheetData.EmployeeID
GROUP BY Employees.EmployeeID, Employees.UserName
HAVING Max(TimeSheetData.DateEntered)>DateAdd("d",7,Now())
ORDER BY Max(TimeSheetData.DateEntered) DESC;

Open in new window

0
I need some help here to see how I can change the orientation of this table.  I have a big table containing about attribution for about 2000 products.  Each product attribute is listed a separate row  and I need to format so that each attribute name is listed horizontally.  There are about 120 unique attribute names so I was looking for a solution that would prevent me from hard coding each attributename in a PIVOT.  

ProductID               AttributeName                     Value
CAF241H100M      DimensionsPalletWidth      48.00
CAF241H100M      CanadianHTSCode      8421.39.90.90
CAF241H100M      DimensionsCaseWidth      9.50
CAF241H100M      ShippingVolumeUnit      427.500
CAF241H100M      WeightShippingUnit      1.450
CAF241H100M      ProductIDBarCode      KAF241H100M
CAF241H100M      CartonType      CTN

Expected Results

Product ID          DimensionsPalletWidth   CanadianHTSCode   DimensionsCaseWidth  ShippingVolumeUnit  WeightShippingUnit ProductIDBarCode CartonType ..........
CAF241H100M    48.00                                    8421.39.90.90            9.50                                     427.500                        1.450                        KAF241H100M        CTN
.... .
......
......
0
change ON in LEFT OUTER JOIN based on another fields value

SELECT [contract_number]
      ,[Comp_Code]
      ,[Debitor]
      ,[Company]
      ,[BusinessPartnerCode]
       ,[Track_Name]
      ,[Cust_ZZNA_SCD]
      ,[SubTrack_Name]
      ,[DSO]
      ,[Track_Name]
      ,etdso.dso_180
      ,etdso.dso_270
      ,etdso.dso_360
      ,etdso.dso_450
  FROM [program_flow].[dbo].[EXTNO_DSO] etno
  left outer join
  [program_flow].[dbo].[EXTENDED_VIEW_DSO] etdso
ON
  etno.BusinessPartnerCode = etdso.Cust_ZZNA_CD
  
  where [Track_Name] is not null

Open in new window


I need to change the "ON" condition based a a fields value if possible

If the [BusinessPartnerCode]   "isNumeric"  no  alpha characters     I.E.(081111111, 354276567  ETC..)
THEN

ON
  etno.BusinessPartnerCode = etdso.[Debitor]

OR

If the [BusinessPartnerCode]  ends with  "AA"   i.e.  (GFDAA, TGRAA, JHGAA  ETC...)
THEN
ON
etno.BusinessPartnerCode = etdso.Cust_ZZNA_CD

OR
ELSE
ON
etno.BusinessPartnerCode = etdso.Cust_ZZNA_SCD

----------------------------------------
Thanks
fordraiders
0
Does the primary mirror server need to to be offline(no transactions) during SQL server mirroring?

When you set up the mirror server, you need to take a full backup and transaction log backup from the primary server and restore in the mirror server in no recovery mode. In this scenario, my primary server is in production (I cannot stop the server since users are being connected and doing transactions). Does the primary server need to be stopped until I complete the mirror setup?  
I'm using sql server 2016 standard version.
0
Microsoft Azure 2017
LVL 13
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.

Good morning team,

 I have an error (The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.) when trying to sum hours and minutes (duration column) from a table where the column data type is nvarchar(50). I need to aggregate the hours+minutes so I can join on another table to get total work hours for installers out in the field. The select query below will work for specific CustomerName however, I receive the error attached when running for all customers. I've tried at least 10 different ways to resolve but the error is always the same.

As always, thank you all for your time and expertise in helping me get this resolved!

SELECT     TOP (100) PERCENT CustomerName, CONVERT(varchar(10), SUM(DATEDIFF(minute, 0, Duration)) / 60) + '.' + CONVERT(varchar(10), SUM(DATEDIFF(minute, 0,
                      Duration)) % 60) AS install_hours_worked
FROM         dbo.vWIP_Install_TimeSheets AS T
GROUP BY CustomerName


Here is the source query for the view above. This is simply to refine to a list of time sheet entries that meet certain service item criteria (in this case, we're only looking for a WIP report on installation jobs).

SELECT     TOP (100) PERCENT CustomerName, CustomerId, Duration
FROM         dbo.TimeTracking
WHERE     (CustomerName IN
                          (SELECT     Job
                            FROM          dbo.vWIP_Install)) AND (ServiceItemName IN
                          (SELECT     …
0
SELECT        COUNT(Receipt_Detail.Receipt_Number) AS totalcountreceipt, SUM(Receipt_Detail.Amount_Applied) AS receiptamount, Invoice_Header.Invoice_Number,?
FROM            Invoice_Detail INNER JOIN
                         Invoice_Header ON Invoice_Detail.Invoice_Number = Invoice_Header.Invoice_Number INNER JOIN
                         Member_Association ON Invoice_Header.Member_Number = Member_Association.Member_Number INNER JOIN
                         Member ON Invoice_Header.Member_Number = Member.Member_Number INNER JOIN
                         Receipt_Detail ON Invoice_Detail.Invoice_Number = Receipt_Detail.Invoice_Number INNER JOIN
                         Receipt_Header ON Member_Association.Member_Number = Receipt_Header.Member_Number AND Receipt_Detail.Receipt_Number = Receipt_Header.Receipt_Number
WHERE        (Invoice_Detail.Charge_Code = 'D' OR
                         Invoice_Detail.Charge_Code = 'T' OR
                         Invoice_Detail.Charge_Code = 'N')  (Invoice_Header.Invoice_Date BETWEEN '20181201' AND '20190131') AND (Receipt_Header.Deposit_Date BETWEEN 
                         '20181201' AND '20190131')  and (Invoice_Header.Invoice_Amount > 0)
GROUP BY Invoice_Header.Invoice_Number
ORDER BY totalcountreceipt DESC

Open in new window

Inside of "?" if I want to add like below, is it possible? Please show me how.

select top 1 (Receipt_Detail.Amount_Applied) where Receipt_Header.Deposit_Date BETWEEN '20181201' AND '20190131' order by Receipt_Header.Deposit_Date asc
0
rrrplease take a look of the attached file. It is a simple invoice to receipt (1 to m) relationship. and my question is.

1. assume I give you receipt received to date range 01/01/2019 to 01/14/2019. how can get the following in SQL query.

Invoice ID Total Receipt Count Receipt Amount
1                 25.00              1
2                 0                      0
3                 0                      0
0
I have two main tables where an employee (Employees) saves the date and hours spent on a task in a table called TimeSheetData.
The query I want is basically give me the last date each employee has saved time which would pull from the fieldname:  DateEntered.

It would look like:

Employee               DateTimeWasSubmitted

EmployeeA              3/21/2019
EmployeeB              2/1/2019
EmployeeC      
EmployeeD              1/14/2019

I provided a Microsoft Access sample - note how some employees maybe blank cause the DateEntered did not get out.  
How would I get this accomplished?  I want it to list all employees whether time was entered or not.
ee-question-last-date-emp-entered-.accdb
0
Suppose I have a SQL table with some date columns and I want to calculate the difference.  Should I write a stored procedure using the datediff function or set up a new persisted computed column.
From what I've seen it seems that a computed column will make the stored procedure run faster, but will take up more disk space.
Is this correct?
If diskspace is not an issue should one always use computed columns instead of scalar functions or are there exceptions?
If diskspace is an issue are there any scalar functions that slow queries down more than others?
0
Hi All,
The following is an Extract from a table.
The table itself has multiple different Server_Name values
result 1
I run the following against the table
select
 Server_Name,
 Audit_Action_Name,
Audited_Result,
'' as Result from SQL_Server_Audit

Open in new window


The Result column needs to have a ‘Pass’ value when and only when a specific Server_Name  has the three entries shown for ‘Audit_Action_Name’ and ‘Audited_Result'
result 2Any guidance appreciated.
I am currently looking at the Table Ranking Function
0
Hello,

I have a SQL Replication problem. I added some tables in an existing replication and I don't know what tables they are. Is there any way to see the history of tables I have added? (SQL 2008 R2)

I hope u can help me!
JN
0
Hello,

I am trying to figure out a SQL query that can do the calculations I am expecting to calculate the internet browse window time for a user within a time period. From the start time (Column B in the spreadsheet), we need to add 60 seconds and check the next following session's start times and see if it falls within that 1 minute's time frame or not. If it does then it is 1. Repeat the same logic for the next browse window and check if it falls within the next minute, then it is 2.

I hope it makes sense if you look into the logic document that I have attached.

The SQL table will have "Start Time" and "Session ID" and the expected outcome will be in "Browse Window Time".

I am attaching a sample logic and the expected result in the spreadsheet (Column A is the expected outcome) to get the value of the browse time. Can someone please help?

Thank you
Logic.docx
Exchange-Experts-sample.xlsx
0
I have a web application I have been developing on my development environment (VS2010).  When I load the application (one asp.net page and code behind) onto the server the rendering is different.  For some controls it is much different.  Charts are use are much smaller than the should be.  Borders on drop down lists are not rendered the same either.

Both my development server (local PC) and sql server use IE11, IIS7.  SQL Server 2012R2.

Any help would be greatly appreciated.

Thanks,
0
Fundamentals of JavaScript
LVL 13
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Hello Team

Can you please advise on the following.

I have an MS Access query which contains the following fields:

GroupID, GroupName, StartDate, EndDate, CompleteDate. Fields are pulled from a table called tblGroupBusiness.

What is required is to create a parameter of within the query which will display only three months at one time. For example:

As we are currently in the month of March, the query would only display records that have a CompleteDate from 01/01/19 through to 31/03/19.  Once we move into the month of April, all records that have a CompleteDate within the month of Jan will be hidden and the recordset would then only display all records from Feb to the end of April.

As this result can be achieved using various methods, e.g., SQL, VBA, Access functions directly into the Criteria line etc, I am happy to receive suggestions in a varied way.

Thank you in advance.

Dale
0
Hi  I have  a table  TB-Docs in Sql Server 2016  with a filed Name ( and other fields)  . There are some names ending  like Doc1VOL01 , Doc1VOL11  , Doc1Vol123

Eg.
    Doc1Vol01
    Doc1Vol11    
    Doc3Vol123
 
   I need result with Volume rows and the substring of the  Document name  ( All I need to know is how to get the substring like Doc1 from a string of Doc1Vol01)
        Name                 Doc
        -----                     ----
     Doc1Vol01           Doc1
    Doc1Vol11            Doc1
    Doc3Vol123          Doc3


Edit : Name can be of any length , for example, there can be a Doooooc1Vol01
0
Does anyone know what language this is? It is embedded within our ERP system. I need to add the word Symptoms above Reason for removal.

begin

IF
   WO_SYMPT_FAUL_ACT['SFA_TYPE'] = 'Symptoms' then 
begin

  Text := 'REASON FOR REMOVAL:';
end
else if
    WO_SYMPT_FAUL_ACT['SFA_TYPE'] = 'Faults' then 
begin

  Text := 'FAULT:';
end 
else if
    WO_SYMPT_FAUL_ACT['SFA_TYPE'] = 'Actions' then 
begin

  Text := 'TESTED WITHIN MFG SPECS:';
end 
else
begin
  Text :=  WO_SYMPT_FAUL_ACT['SFA_TYPE']
end;
end;

Open in new window

code.JPG
symptoms.JPG
0
Hi, my below SELECT is in a stored proc and when I execute it its taking 9 seconds . Is there anyway to fine tune the query to bring the time down and improve the performance ?
Thanks


SELECT
      LatestStatuses.SiteKey,
      LatestStatuses.component,
      LatestStatuses.[Status],
      LatestStatuses.StatusDetail,
      LatestStatuses.[Timestamp]
 FROM (
      SELECT    
            SiteKey,
            Component,
            Timestamp as [Timestamp],
            RANK () OVER (PARTITION BY sitekey, component ORDER BY Timestamp DESC) AS StatusResult
            ,status as [Status]
            ,StatusDetail
      FROM StatusLog) AS LatestStatuses
WHERE StatusResult = 1
ORDER BY SiteKey, Component
0
I have a table called "Parameters" in Access 2010 that contains ranges of years by Category. I'm looking for the SQL that would DELETE all records from the "dataTable" where the range of years for each category does NOT fall within the range of years found in the "Parameters" table by category.

Below is a picture of the "Parameters" table and the "dataTable". The records highlighted in RED are the records that would be deleted since they are outside of the "years" range in the "Parameters" table.

Thanks in advance for your help!

table 1
table 2
0
Hi,
when I run the following query against my 2008 SQL Server
 select 2.13 as Control_Number,
'Ensure the ''sa'' Login Account is set to ''Disabled'' (Scored)' As Control_Name, name,sid, is_disabled from sys.server_principals
WHERE sid = 0x01
AND is_disabled = 0
UNION 
select 2.14 as Control_Number,
' Ensure the ''sa'' Login Account has been renamed (Scored)' As Control_Name, name,sid, is_disabled from sys.server_principals
WHERE sid = 0x01

Open in new window

i get the following output
sis u see it
when I add another UNION below
 select 2.13 as Control_Number,
'Ensure the ''sa'' Login Account is set to ''Disabled'' (Scored)' As Control_Name, name,sid, is_disabled from sys.server_principals
WHERE sid = 0x01
AND is_disabled = 0
UNION 
select 2.14 as Control_Number,
' Ensure the ''sa'' Login Account has been renamed (Scored)' As Control_Name, name,sid, is_disabled from sys.server_principals
WHERE sid = 0x01
UNION 
select  2.17 as Control_Number,
' Ensure no login exists with the name ''sa'' (Scored)' As Control_Name, ' ' as name, ' ' as sid, ' ' as is_disabled from sys.server_principals
WHERE sid = 0x01

Open in new window

i get
sid y dontI dont understand what happened to the sid values above...
any guidance appreciated.
Thanks
0

Query Syntax

53K

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.