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 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
Introduction to Web Design
LVL 13
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

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
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
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
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
Introduction to R
LVL 13
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

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
I Might be overthinking this query…
In the sample below, I am trying to return all 5 columns for each user from the row that has the highest timestamp

USER	ENTRY		COL_C		COL_D	 	COL_E
MIKE	2/13/2019 06:00	123DE		34fDer		EHST6
MIKE	2/14/2019 09:30	94DTE		3578HG  	2342DER
MIKE	2/18/2019 07:32	656TR		3535RE		23FR2
TOM	2/11/2019 11:45	574RE		2738FR		263DR
TOM	2/19/2019 07:26	343WE		3874GF		263WW

Open in new window


Result should be
MIKE      2/18/2019 07:32      656TR            3535RE            23FR2
TOM      2/19/2019 07:26      343WE            3874GF            263WW

I want to select MAX(ENTRY) and group by USER, but to bring back the other 3 columns they need to be in the group by. Because they are all unique values I end up returning all rows.
not sure how to accomplish this
0
Hello,
In current Production environment hosted on Microsoft SQL Server 2008, we have expensive sql query that is killing the database. We wish to add additional index to existing table.
SQL Query. This database is currently monitored by Solar wind and it has recommended to add index to the existing table. This table has currently 20 million records.

SELECT PVT_CHG_LOG_SEQ,  
   "Table_Name",  
   PK_Column_Name,  
   PK_Value,  
   ADP_SYNC_REQ_FLAG,  
   SYNC_STATUS,  
   SIBL_SYNC_STATUS,  
   ADP_SYNC_STATUS,  
   PRIORITY,  
   RETRY_COUNT,  
   SIBL_RESPONSEDESC,  
   ADP_RESPONSEDESC,  
   SIBL_COMPL_TIME,  
   ADP_COMPL_TIME,  
   Created_Date,  
   9,1  
FROM dbo.INT_PVT_DATA_CHANGE_LOG  
WITH  
   (
      UPDLOCK,  
      NOWAIT
   )  
WHERE (SYNC_STATUS = @P0)  
ORDER BY 9,1 ASC  

Recommendation from Solar wind to add inded
Clustered Index Scan (OBJECT:([CPMSPROD].[dbo].[INT_PVT_DATA_CHANGE_LOG].[PK__INT_PVT___61283AF276F68FE1]), WHERE:(CONVERT_IMPLICIT(nchar(1),[CPMSPROD].[dbo].[INT_PVT_DATA_CHANGE_LOG].[SYNC_STATUS],0)=[@P0]))

Question: a) Do we need to create new clustered or non clustered index for column SYNC_STATUS  to the table INT_PVT_DATA_CHANGE_LOG or modify existing index PK__INT_PVT___61283AF276F68FE1 to incorpate the column SYNC_STATUS

b) Any other guidelines to be followed while adding index

Regards
Tom
0
I have a table called "Parameters" in Access 2010 that contains a ranges of years by Category that I'd like to retrieve

Parameters table
I have a data table that I'd like to retrieve records from on "years" that fall within the minimum and maximum year range by category on the Parameters table. For example, based upon my parameters, the data highlighted in yellow below is the data that would be retrieved.

Data
I appreciate any recommendations as to what would be the most efficient way to execute this. Ideally, I'd prefer to do this through SQL, but if this can't be achieved in a single query, I'm open to running a VBA function or whatever you think is best.

Thanks in advance for any suggestions!
0
One of our junior DBAs added a series of Bitmap Indexes 6-months ago, that may have been ill-advised.  Based on recommendations from the SQL Tuning Advisor, as the Lead DBA, I approved and agreed with this change.   At the time our J2EE developers consistently complained about performance of our 3-node Oracle 12.1c database, and I was looking for something to hopefully improve query performance.

Currently we are experiencing very consistent Ora-2047 errors; "Distributed transition waiting for lock" issues.  I wonder if the Bitmap Indexes have contributed to the table locks?  Upon further review I have read that, bitmap indexes are better only when used for Datawarehouse applications; where tables are guaranteed to be read-only?  Is it true that typically bitmap indexes are frequently rebuilt, and it requires a full table lock?
0
We had a requirement to find which strategy is working by giving scoring for each promotions based on customers who received promotions in last 24 months but we wanted to exclude customers who opened promotions which were  send earlier then last 24 months.
so basically the requirement(Emails sent out in the last 24 months by excluding clicks in the last 24 months)Please tell me how  I can query in SQL server.

Thanks
0
Hi...I'm running an old Oracle 8i datbase on a Windows machine. I had successfully implemented Oracle email using UTL_SMTP years at least 5 years ago. Everything has been running smoothly until today.

I was trying to fix an error I was getting in a new schema with one of my existing procedures and based on something I read on the Internet (not from you folks), I ran (or tried to) 2 scripts. initjvm.sql followed by initplsj.sql...ran them in the SYS schema. The first one was taking what seemed like a very long time so I think I prematurely closed the SQL Plus window. WHen I ran the initplsj.sql, I got a number of error messages like this:

call dbms_java.set_output(10000)
*
 ORA-06576: not a valid function or procedure name
 call dbms_java.loadjava('-resolve
 plsql/jlib/plsql.jar')
 
ORA-06576: not a valid function or procedure name


After this, the email procedures that HAD been working for years stopped working. When they get called now from the website I get this:

ORA-29540: class oracle/plsql/net/TCPConnection does not exist
ORA-06512: at "SYS.UTL_TCP", line 533
ORA-06512: at "SYS.UTL_TCP", line 199
ORA-06512: at "SYS.UTL_SMTP", line 99
ORA-06512: at "SYS.UTL_SMTP", line 121
ORA-06512: at "WEBEXAM_APP.SEC", line 1851
ORA-06512: at line 10

...where webexam_app.sec is the procedure involved in generating an email.

I reinstalled the Oracle packages UTL_TCP and UTL_SMTP installed within SYS....didn't help. I then re-ran initjvm.sql, …
0
Keep receiving "Error converting data type nvarchar to float" when dividing two columns. QUOTE_AMOUNT column is set to Float and IC_UDF_ is set to NVARCHAR> when I attempt to convert IC_UDF_001 to float, numeric or INT, I receive the error. The data for both

SELECT        dbo.BA_VIEW_WO_QUOTE.WO_NUMBER, dbo.BA_VIEW_WO_QUOTE.WQ_NUMBER, dbo.PARTS_MASTER.IC_UDF_002 AS FMV, 
                         dbo.BA_VIEW_WO_QUOTE.QUOTE_AMOUNT, CAST(dbo.BA_VIEW_WO_QUOTE.QUOTE_AMOUNT / dbo.PARTS_MASTER.IC_UDF_002 AS NUMERIC)*100 AS FMV1
FROM            dbo.BA_VIEW_WO_QUOTE INNER JOIN
                         dbo.PARTS_MASTER ON dbo.BA_VIEW_WO_QUOTE.PNM_AUTO_KEY = dbo.PARTS_MASTER.PNM_AUTO_KEY

Open in new window

data.csv
Capture.JPG
0
I want to update values in one table 'tblTaxRecs' with information from another table 'tblFees'

tblFees contains two fields 'BRT  Numeric Long Interger' and  'FeeAmount   Numeric Double'

The Pertinent fields in tblTaxRecs' are 'BRT numeric Long Integer', 'TaxYear  Numeric Long Interger', 'FeeAmount  Numeric Double', 'DateRevised Date', 'UserRevised Short TExt'.

For every record in tblTaxRecs with a taxyear of 2018 and matching a BRT in tblFees, I want to replace the 'FeeAmount' with the corresponding FeeAmount in tblFees that matches the BRT in tblTaxRecs.  If possible I also want to stamp each updated record in tblTaxRecs with the current date and time in field 'DateRevised' and the string 'Updt20190318'.
0
Bootstrap 4: Exploring New Features
LVL 13
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

hi,

any one know if SQL 2017/2019 support microservice platform ?

fully supported or any feature doesn't work on top of it?

any URL for it?
0
Hi,

In current production database of Mcirosoft sql server 2008, we recently performed maintenance activity of truncating log table that consumed 110 gb of storage space.
After truncate job is complete, we ran reorganize task for tables and indexes plus update statistics job on the table and indexes (full scan). The job was successfully complete
Even after performing the above task, the application performance had degraded.  
New to the Microsoft sql server world, please recommend any pointers to improve the performance.

Regards
0
Hi Guys,

A quick one for you - In PG sql I want to select the current date minus a number of days as a variable at TIME ZONE 'US/Pacific'.

For instance

 select (now() -  cast( :p as integer) AT TIME ZONE 'US/Pacific');

( :p is a number i.e 2 to be passed when running a query ).

I want the above select to return the current date/time AT TIME ZONE 'US/Pacific' minus a number of days .

How can I achive it please ?
0
Just installed SQLExpress 2008 and SP3.  I called it SQLEXPRESS2008. When I open Configuration Manager that is the name that is displayed.  

I cannot log in with Windows Authentication or SQL Authentication.  

In configuration manager I checked the user name and re-entered the password.  Still cannot get in.

What can be done?

SQL2.jpg
0
I'm experiencing a rather bazar issue I've never run into previously.

I'm inserting data into an Access 2003 text field (255 char) where several rows are null or blank but when I test the length len([Field]), they all come out as over 100 characters wide.

The source of this field (from a customer) is a SQL Server table with the same characteristic.


How can I transfer this field and end up in Access with a zero length value?
0
I have created Five action queries in Ms Access 2016 for inserting data that require further manipulation to give correct information , one of the INSERT QUERY see below:

INSERT INTO tblPosAccounts ( QtySold, UnitCost, CostOfSales, SoldID, POSDate, CosAcc, BSIDCos )
SELECT tblPosLineDetails.QtySold, tblPosLineDetails.UnitCost, tblPosLineDetails.CostOfSales, tblPosLineDetails.ItemSoldID, tblPosLineDetails.POSDate, tblPosLineDetails.CosAcc, tblPosLineDetails.BSIDCos
FROM tblPosLineDetails;

The above query works very well if used against Ms Access 2016 table BUT fail to append in MS SQL Server 2016, kindly assist me on this all the links are just super no errors whatsoever. I do not understand this. The SQL Server has the same tables as MS Access.

See what you can do.

Regards

Chris
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.