Query Syntax

50K

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

We are switching from Teradata to Oracle SQL Developer so this is a big learning curve for me, how do convert a date range from Teradata to Oracle.
 
Teradata version-
and pat_enc.contact_date BETWEEN '01/01/2013' AND '09/18/2013'

Oracle attempt-
AND pat_enc.contact_date BETWEEN TO_DATE('01-01-2015','MM-DD-YYYY') AND TO_DATE('06-01-2017', 'MM-DD-YYYY')

When I run in Oracle I do not return any data but returns report headers.
0
Free Tool: ZipGrep
LVL 12
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

I have a subreport to display data supplied by a SQL view. I need to get fields to display on the same line, so I created the following formula and inserted it into a detail section of the subreport:

stringvar PhoneStr;
stringvar TollFreeStr;
stringvar BothStr;

if {uvw_InvoiceListingDetails.LineDescription} = "Phone" then
    PhoneStr := "Phone: " + {uvw_InvoiceListingDetails.LineData} + "   ";
if {uvw_InvoiceListingDetails.LineDescription} = "Toll Free" then
    TollFreeStr := "Toll Free: " + {uvw_InvoiceListingDetails.LineData};

BothStr := PhoneStr + TollFreeStr;
totext(BothStr);

Open in new window


In the section expert, I use the following suppression formula so that there is no conflict with the other listing details in the subreport:
if {uvw_InvoiceListingDetails.LineDescription} = "Phone" then
    false
else if {uvw_InvoiceListingDetails.LineDescription} = "Toll Free" then
    false
else
    true

Open in new window


What's happening is that if the toll-free number is present in the dataset, the phone number is printed twice (once on it's own line, then again on the next line with the toll-free number).

And if the toll-free number is not present in the dataset, the phone number is printed only once, which is expected and desired.

What am I doing wrong here? I've been battling this for 1.5 days and I've tried pretty much everything I can think of. Any help would be greatly appreciated.
0
We have a Microsoft SQL Server 2014 database.

We have a quarter million users and 30GB database size file, which really isn't that much. But we have grown very quickly, and want to plan ahead for the growth that is going to happen.

What recommendations do you have for load balancing and redundancy and where would I find details of what we need to do specifically?

Yes, we can throw money at it.

thanks!
0
I have created an SSRS report and deployed it to the SQL report Server location.  I want to set this report up so it executes at a certain time and creates a pdf in a network folder. However when I click subscribe I get the error "Subscriptions cannot be created because the credentials used to run the report are not stored, or if linked report, the link is no longer valid." In the  credentials section of the data source of the report I am using Windows Authentication. The only way the report will execute is with Windows Authentication . I have one other report that is set up this way successfully. That report is using a different SQL database. One set up by our ERP providers. The credential setting on that report is do not use credentials. Admittedly my understanding of the security portion of SQL is weak. Any help would be appreciated. Thanks.
1
I am using SQL SERVER 2008R2.

This works in my stored procedure body:

       DECLARE @timestamp datetime2(2)=CAST(CURRENT_TIMESTAMP AS datetime2(2))


BUT if I put it in the parameter listing of the stored procedure, it errors?

EXAMPLE:

ALTER PROCEDURE [dbo].[usp_MyStoredProcedure]
      @PASS_Options            char(1)='H'   -- DEFAULT value
      ,@timestamp datetime2(2)=CAST(CURRENT_TIMESTAMP AS datetime2(2))
      AS
BEGIN
END

ERROR MESSAGE:  Incorrect syntax near '('.
0
I am working on query that pulls data from OPM_Table based on a combo box by location.  I have another combo box that selects grade by OPM_Table.Grade2.  I have already created SQL for a single grade.  The attached code I created makes the drop down select the grade I need:  (OPM_Table.GRADE2)=Forms![Fee Review Summary]!Combo137)
However, there are times when we have GS-5/7/9 where that does not exist in the OPM_Table.  I need to figure out how to write a query that selects GS-5 Annual 3 for FY 2019, GS-7 Annual 1 for FY2020, GS-9 Annual 1for FY2021, GS-9 Annual 2 for FY2022, GS-9 Annual 3 for FY2023, and GS-9 Annual 4 for FY2024.  I know I need to do a If, then else statement, but I can't figure out how to do it.
Pay.txt
0
Hello,

I need to convert a decimal time to hh:mm (that format) from a sql database field in a Production Utilisation Report. The report is written in Crystal.

I have already the following but I'm struggling with the minutes as the code below seems to be adding to many hours calculated from the decimal value after the zero

3.75  = 4.15 after conversion. The structure of the time isn't correct as yet (hh:mm) but I'm first want the value to be right.

See code below:

//(NumberVar OldTime := {rep_v_ProductionAnalysis.OperationLength};) = actual field. Replaced the field so I can check the calculation


NumberVar OldTime := 3.75;
NumberVar NewTime;
NumberVar Hours;
NumberVar Minutes;
Hours := Int(OldTime);
Minutes := Remainder(OldTime, 1) * 100;
Hours := Hours + Int(Minutes / 60);
Minutes := Remainder(Minutes, 60);
NewTime := Hours + (Minutes / 100);

Any help would be useful

Thanks,
Gerd
0
How do SQL clients find a connection point, assuming thats the term.

I have a client who has a very old application that is installed on an old SBS 2008 server.  The server and old domain were retired and a new SBS 2011 domain + server weres etup and all the workstations were migrated to the new domain.  The client portion of a Legal Practice Management solution is still installed on the PC's.

They need to access the old application (has a SQL Server 2005 DB)

The shortcut for client application has a command switch after the exe path that I assume is calling the connection in the SQL Server 2005 instance
e.g. C:\Program Files\LegalApps\LegalAppClient.exe MXDB.

I dont know how to check that the MXDB is being "advertised" as being available.  Does the client machine broadcast to the LAN "i am looking for who has SQL DB by the name of MXDB?" and the relevant SQL server instance responds.  The only thing I do know is that the client uses "named pipes"

How can I check the MXDB is visible to the LAN?
Port 1433 is open bidirectionally on the Workstation and the server
The App doesnt use windows Authentication, it uses its own user dadatabse in the DB
How do I make sure the client machine can see the db?
0
Trying to query a postgress SQL DB of another application, having trouble with the distinct keyword in the select.
I know the field 'type' does exist in the table.  Why does it only let me query on 'id'

SELECT *  -- this select works.
-- Select distinct t3.type  -- this select does not work, produces an error, "t3.type does not exist."
-- Select distinct type   -- this select does not work, produces an error, "t3.type does not exist."
-- Select distinct t3.id  --  this select statement does work, what's the difference.
FROM table1 t1
inner join table2 t2 on t1.id = t2.id 
inner join table3 t3 on t2.id = t3.id
where
   t2.r_date > '2017-12-01' and
   t2.r_date < '2018-02-01'

limit 100

Open in new window

0
Oh men
I forgot the query for shrink the log in database SQL server
Please you guys help me out!
regards
0
Upgrade your Question Security!
LVL 12
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Hello,

I need to do some record matching based on two date fields from two tables.  

For example, I need to identify rows where the TABLE_B.ARRIVAL_DATE is on the same or 1 day greater than the TABLE_A.SHIP_DATE.

I've seen examples using BETWEEN with DATEADD(dd, -1) and eyeballing the results it seems to work correctly, but I've also seen recommendations against BETWEEN due to issues related to time.  My date fields don't contain times and for now I'm just concerned about the same day or +1.  

What is the best (and most simple) way to do this in SQL Server 2012?

Thanks!
0
Help please have issue wiht stored procedure sql

SET @Query +=       
             '
             SELECT                        
      CASE
            WHEN
                  [Group Name]= ''Infraestructura'' OR [Group Name]= ''Help desk Liv-smart'' OR  [Group Name]= ''HELP DESK''
            THEN ''ENTAEH''
            ELSE ''CBC''
            END AS ''Empresa'',
            [Group Name] AS ''Grupo'',
      COUNT([Group Name]) AS ''Añospasado'',                  
            '+@Tipo_Fecha+'  AS opendate,       
         CASE
            WHEN
                  [Group Name]= ''Infraestructura'' OR [Group Name]= ''Help desk Liv-smart'' OR  [Group Name]= ''HELP DESK''
            THEN ''ENTAEH''
            ELSE ''CBC''
            END +'-'+[Group Name] AS ''ordenPivote''            
      FROM _SMDBA_.Incident
      WHERE country = @CN AND
                   CONVERT(datetime,CONVERT(NVARCHAR,[Close Date & Time],101)) BETWEEN CONVERT(datetime,[Due Date & Time:],103) and CONVERT(datetime,[Close Date & Time],103) AND
              [Due Date & Time:]  IS NOT NULL AND [Group Name] IS NOT NULL
            GROUP BY            
                  [Group Name],                  
            '+@Tipo_Fecha+',
                  CASE
                        WHEN
                              [Group Name]= ''Infraestructura'' OR [Group Name]= ''Help desk Liv-smart'' OR  [Group Name]= ''HELP DESK''
                        THEN ''ENTAEH''
                        ELSE ''CBC''
                        END                        
                  ORDER BY
                  CASE
                        WHEN
                              [Group Name]= ''Infraestructura'' OR [Group Name]= ''Help desk Liv-smart'' OR  [Group Name]= ''HELP DESK''
                        THEN ''ENTAEH''
                        ELSE ''CBC''
                  '+@Tipo_Fecha+',
                  END,
                   [Group Name]'
                    +@Tipo_Fecha+''


/
            Msg 8117, Level 16, State 1, Procedure Z_REPORTE_POR_MES, Line 108 [Batch Start Line 7]
Operand data type …
0
Recur-NEW-2-22-2018.accdbGreetings Experts:

I'm having trouble making a query that is based on another Cartesian query.  
The query is named 1-testdue. It is used to query all events due within a parameter date range.
I can't get it to work correctly, looks like it's pulling Cartesian values for all records.
I need to pull only each instance of event that are due within a certain date range.
Please advise.
Thanks!
0
We have been instructed by the IT director to created an active directory service account and give it "access to a SQL 2016" database. In our test environment, we have several active directory user accounts that have been granted Sys Admin on our test databases and we can connect fine. Today we created the same kind of AD account and logged into our SQL server and opened Management Studio, from there we tried to connect to our production database with WIndows Authentication and our active directory service account but got the error "18456". We created a local SQL server account with the exact same username and password and are able to connect SQL Server Authentication, however that is not what our director asked for. Does anyone know the error we are getting and what might be different about our production SQL server?
0
We recently upgraded from SQL Server 2012 to 2016 and have noticed it takes longer than we expected to insert from one database into the other.  See below for details and specific question
Hardware Specs
•      SQL Server 2016 (SP1-CU6)
•      Virtual Server (32 processers and 32GB RAM)
•      Windows Server 2012 R2
•      1 instance of SQL server
•      Database A (compatibility =130)      
•      Database B (compatibility =130)      
•      Table Statistics in database A up-to-date
SSIS package runs Stored Procedure on DatabaseA (Estimated Cost = 2750)  (35Million rows returned from query)
Loads to table in DatabaseB. Table has 2 indexes, one clustered another non clustered.
Stored Procedure returns 25 columns. Most are integers, money or small varchars (255) is the biggest varchar.
When deployed to the server, the package take over 30 minutes to run.  
In SSIS we have tried “AutoAdjustBufferSize” = True
And DefaultBufferMaxRows  = 10,000
AutoAdjustBuffer size seems to be slower than using DefaultBufferMaxRows  = 10,000.

Questions:  
Is there anyway to make this run faster?
What are some possible things to check which might cause it to run slowly?
0
Hello Guys,

We are in process of upgrading one of the SQL Database from 2012 to 2016 version. The Database is moved/upgraded successfully to SQL 2016 version, now the problem is, the database is holding a Stored Proc which is taking longer time in the new version i.e on SQL 2016.  

On SQL 2012 stored proc will execute in 13 minutes where as on SQL 2016 it's taking 13 hours to complete. We been stuck with this since stored proc is having lot more dependencies to business.

We increased the memory , ram on the new server to 32 GB where as on old server it's 28 GB only, but both the servers are holding 2 cores only.

Old server OS is win 2008 and new box build with Win 2012.

We tried to find out blockings by running profiler when ever the stored proc runs but we haven't found any blockings ,this seems mysterious for us since no blockings, dead locks found.

Settings on SQL server are similar on both of the servers we haven't any odd.

Let us know if have any suggestions on how to solve this issue.

Quick help is really appreciated.

Thanks,
Venkat Kokulla
0
there is a checkbox based on selection i need to fetch data from back end. there is a creditunits field is there in sql table that having values 0 to 5.
so when i check checkbox creditunits value is greter than 0 to display, when not checked no filter.
0
How much physical memory is required for a SQL 2012 database of 10Gigs in size and growing?    Currently the windows 2008 server R2 has 16gigs of RAM... Thought?

thanks
0
I have a SSIS package created in MSSQL.  When the package is executed, it imports data to my SQL table. This is all working currently.

I am trying to set up a one step job to run this SSIS package, which then does not run successfully.

The settings for the job is:
Type: SQL Server Integration Services Package
Run as: SQL Server Agent Service Account
Package Source: SSIS Catalog
I am using windows authentication.

Has anyone run into this issue previously for this?
0
Take Control of Web Hosting For Your Clients
LVL 12
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

could a backup of a sql server have an extension .sql, and would there be any benefit in saving backups as SQL files? I am doing a risk assessment on  some files found on shares on a database server, and some have dbbackup in the name, but not the traditional .bak extension, rather they are .sql. I will query it with the DBA but wondered if they could represent actual SQL backups, and if so why would someone save them as .sql as opposed what seems to be the standard .bak.
0
I am looking for scripts for below requirements:

1. How quickly attach 100+ databases in sql 2016?
2. How quickly change 100+ databases compatibility level (from 100 to 130) in sql 2016?

The exact requirement is that we are migrating sql server 2008 to 2016.
0
Hi

I'm having an Access FE with Sql server 2014 BE. when adding a record via ADO and trying to Retrieve the Identity ID I'm getting An error "Record is deleted"
as per my research it has to do with having a trigger "For Insert" but the trigger is only adding a record to a unrelated table, so i'm not sure what's going on over here.

Trigger:
ALTER TRIGGER [dbo].[billingPay.Insert]
    ON [dbo].[BillingPay]
    FOR  INSERT
    AS

    BEGIN

	INSERT INTO [dbo].[Transactions]
			([TransID]
			,[Type]
			,[Date]
			,[Amount]
			,[Balance]
			,[Rep]
			,[CustomerID]
			   --,[Project]
			   --,[ToPrint]
			   --,[ToEmail]
			   --,[Applied]
			   --,[ProjectID]
			   )

		select BilingPayID,'Payment',getdate(),[Amount],[Amount],Rep,CustomerID from inserted
	--end

	--EXEC UpdateCustomerBalance @CustomerID
	SET NOCOUNT ON
	
	
	END

Open in new window



please advise
0
Experts,

  Having a bit of a challenge getting the proper data to show (invoices + credits) between 2 different SQL tables. I cannot seem to find a common denominator that would allow me to see individual invoices and credits, in ONE single query. Of course, if I run them separately, all works well.

The first query below shows all invoices - 100% perfect. The second query shows all credits - also 100% perfect. The third query shows my attempt at trying to combine invoices and credits in one query - NOT perfect :(

Can you take a look and let me know what I'm missing?

I've included the queries I'm using, a spreadsheet with results (alone with each query).

Thank you, in advance!!

INVOICES QUERY:

SELECT     Cust.AccountNumber, inv.CustomerID, Cust.Company, SUM(ISNULL(INV.Amount, N'0')) AS Invoice_Amount,
INV.DATE, INV.EditSequence
FROM         dbo.Invoices AS INV INNER JOIN
                      dbo.Customers AS Cust ON INV.CustomerId = Cust.ID
WHERE     (INV.Date BETWEEN DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0) AND DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), - 1)) AND
                      (Cust.AccountNumber IS NOT NULL) AND (Cust.AccountNumber <> '')
                      AND (CUST.ACCOUNTNUMBER = '205847')
                     ---AND inv.CustomerID = '80001BD9-1479497768'
GROUP BY Cust.Company, Cust.AccountNumber, INV.DATE, inv.CustomerID, INV.EditSequence
order by  cust.Company


CREDITS QUERY:

SELECT     …
0
Hello,

I am getting the attached error when I try to launch the SQL Sentry Client.

I login to the server as a domain admin that is supposed to have permission to the SQL server and its database.  I am using integrated windows authentication.

I login to the SQL server directly using the same domain admin account and I am able to see the database and its view tables in SQL Management Studio.

Please advise where I should look.  

Thanks.
0
I have a table with Register_ Year, Date of Birth.  I need to calculate Age using 01-10-Register_ Year and Date of Birth.  Can anyone help with this in Oracle
0

Query Syntax

50K

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.