SQL Help

Hi experts,

I need SQL syntax help please.

Here is my business scenario; I need to get a count by distinct employee type of 4 different measures. If the count of employees by each measure does not equal zero/null then show the total count. If the count of employees by each measure equals zero or null, show zero.

I'd like to use PHP to echo the results in a table that would take the following format:

distinct_type | MeasureA | MeasureB | MeasureC | MeasureD
Emp Type X | 1 | 0 | 1 | 1
Emp Type Y | 0 | 1 | 0 | 1
Emp Type Z | 1 | 1 | 0 | 0

Something like...I know I am way off...

(SELECT DISTINCT employee.type AS 'distinct_type' FROM mydatabase ORDER BY employee.type)
UNION ALL
(SELECT employee.type, COUNT(*) AS 'MeasureA' FROM my database WHERE employee.type=distinct_type AND measures.a=1 GROUP BY employee.type ORDER BY employee.type ASC)
UNION ALL
(SELECT employee.type, COUNT(*) AS 'MeasureB' FROM my database WHERE employee.type=distinct_type AND measures.b=1 GROUP BY employee.type ORDER BY employee.type ASC)
UNION ALL
(SELECT employee.type, COUNT(*) AS 'MeasureC' FROM my database WHERE employee.type=distinct_type AND measures.c=1 GROUP BY employee.type ORDER BY employee.type ASC)
UNION ALL
(SELECT employee.type, COUNT(*) AS 'MeasureD' FROM my database WHERE employee.type=distinct_type AND measures.d=1 GROUP BY employee.type ORDER BY employee.type ASC)

2 Comments

OWASP: Forgery and Phishing

LVL 13

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

error message on openquery sql missing right paren ?
OLE DB provider "OraOLEDB.Oracle" for linked server "PROD" returned message "ORA-00907: missing right parenthesis".

I dont see the error ?

SELECT
PROJECTID
,LINEITEMID
,CUSTOMERPRICE
,PRICE
,SKU
,CSPIDENTIFICATION
,PRODUCTVARICOMMENTS
FROM OPENQUERY (PROD, 'select
PROJECTID
,LINEITEMID
,CUSTOMERPRICE
,PRICE
,SKU
,CSPIDENTIFICATION
,PRODUCTVARICOMMENTS
FROM XREF.XREF_RESULTS
where lastmodified >= to_date(''''01/01/2019'''',''''MM/DD/YYYY'''')')

Thanks
fordraiders

2 Comments

Good evening everyone,

I have a table with several data (> 2000) of this type:

Reference | Date | Product | Quantity
BC0001 | 2019-09-11 | Bread | 7
BC0001 | 2019-09-11 | Caviar | 5
BC0002 | 2019-09-12 | Oil | 2
BC0003 | 2019-09-13 | Bread | 4
BC0003 | 2019-09-13 | Egg | 12

Open in new window

And another table where I have to reinsert this unique data like this:

Reference | Date
BC0001 | 2019-09-11
BC0002 | 2019-09-12
BC0003 | 2019-09-13

Open in new window

I wish I could execute a SQL query to add in my table2 a single line of my table1 as shown in the example above.

Is there a SQL function to simplify my task?
Thank you for your help

Regards,
Jaber

9 Comments

LVL 6

Question by

bfuchs

1d agoSolvedPrivate

Correction of date field

hi Experts,

We have a column DOB that was mistake defined as text and we are planning to convert to Date column.
However there are many records with invalid dates in it.
Wondering if there is a function that will correct this when something is obvious, like only missing slashes or removing spaces etc...
See attached for sample data.

Thanks
Untitled.png

11 Comments

I have an unbound form frmLogRptCPUSelection that uses a combo box cboComputerDevice to select a device using SQL <SELECT tblComputer.cID, tblComputer.cName
FROM tblComputer ORDER BY tblComputer.cName;> Once the selection is made I have a "Print" button on the form that has the following code in the Print button Click event.

I also have a report rptEventLogByComputer that is based on a query qryComputerLogCPU. One problem is that cboComputerDevice is the value of cID not cName.
I can get the cName with

Dim strID As String
Dim strcName As String

strID = Me.cboComputerDevice
strcName = DLookup("[cName]", "tblComputer", "[cID] = " & strID)

I have tried putting [Forms]![frmLogRptCPUSelection]![cboComputerDevice] as the critera in the query but it does not work.

Now how do I pass strcName to qryComputerLogCPU so that the report will work?

5 Comments

problem getting open query to run

i keep getting an error when trying to run this openquery ?

INSERT INTO
[Metrics].[dbo].[EXTRACT_CUSTOMERPRICE_staging]
([PROJECTID]
,[LINEITEMID]
,[CUSTOMERPRICE]
,[WWGSKUPRICE]
,[WWGSKU])
SELECT
[PROJECTID],
[LINEITEMID],
[CUSTOMERPRICE],
[PRICE],
[SKU]
FROM OPENQUERY (XREF_PROD, 'select
[PROJECTID],
[LINEITEMID],
[CUSTOMERPRICE],
[PRICE],
[SKU]
FROM xref.xref_results')

OLE DB provider "OraOLEDB.Oracle" for linked server "XREF_PROD" returned message "ORA-00936: missing expression".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "select
[PROJECTID],
[LINEITEMID],
[CUSTOMERPRICE],
[PRICE],
[SKU]
FROM xref.xref_results" for execution against OLE DB provider "OraOLEDB.Oracle" for linked server "XREF_PROD".

Thanks
fordraiders

3 Comments

I enter the date value in this format.
oracle gives me
ORA-01843: not a valid month error.
NOTE:(you can better understand the question in the pictures.
Please see the pictures.

but
I enter the date value in this format.
Oracle is running.

)
I want to change the date format.
I don't want to use this date (13.jun.2014),
I want to use this date = (13.06.2014),
how can I do this?

Result of query 'select sysdate from dual'

17/09/2019 17:46:00

Open in new window

2 Comments

Problem with my query, when adding a value that has a single quote to a database field.

eg. //-- values
$value1 = "Peter";
$value2 = "O'Leary";
$value3 = "Contact Person";
//-- query
$stmt = $mysqli->prepare("INSERT INTO table1 (field1, field2, field3) VALUES (?,?,?)");
$stmt->bind_param('sss', $value1, $value2, $value3);
$stmt->execute();
$newId = $mysqli->insert_id;
$stmt->close();

This does not insert anything, and $newId has no value.

So it seems I have a really silly question... I'm sure it's something really obvious, but I'm dead tired and can't figure it out.

I've tried...
$value2 = htmlspecialchars("O'Leary", ENT_QUOTES, 'UTF-8');
$value2 = mysqli_real_escape_string($mysqli, "O'Leary");

6 Comments

i have 64 names i need to enter into this table via code if possible.
I have a table with these names in a field caled "employee_names"

Or is there another way to add their names or re-design the table ?

Thanks
fordraiders
EMPLOYEE_SCORE.jpg

3 Comments

I am trying to filter a result set based on a DATETIME column

select * FROM [ReportServer].[dbo].[ExecutionLogStorage]  where timestart>=2018-12-31

Open in new window

here is the what is displayed typically for a TimeStart value

TimeStart
2018-08-03 09:08:24.937

Open in new window

however the filter is not correct, all rows in the table are currently returned.
Any guidance appreciated, thanks

5 Comments

LVL 23

Question by

Thomas Grassi

3d agoSolved

SQL 2014 Fails to start after disk failure

SQL 2014 Cluster
Windows 2012 R2 Servers VM
VMware 6.5

I have two Node SQL Cluster Servers all disks were iSCSI connected to a NAS device Seagate Black Armor.

The Black Armor died and I lost two Dries. but all my SQ Drives were on the two drives that are still working.

But each of the Server could not connect to the drives. After remove the target node and adding it back I was able to get most of the drives back.

I have three SQL instances running on both Nodes.

I have a new NAS device and setup all the LUNS and Targets now both servers have al the drives again.

I copied the data from the old NAS to the new NAS

In the cluster I was able to get the DTC back online

and one of the three instances are back online.

I had to add all the permissions back to the new drives folders

The other two instances I can get the SQL server service to start but it stops shortly after

I tried to run a SQL repair from the install DVD that failed

Feature failure reason:
An error occurred during the setup process of the feature.

Error details:
§ Error installing SQL Server Database Engine Services Instance Features
Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.
Error code: 0x851A001A
Visit …

55 Comments

Question by

sj77

4d agoSolved

Python Loop Help

I have a python script on python 3.7.2 that I have pieced together. The script will first accept user input for a string and store into a variable and then iterate through each function call.

Depicted here:

while True:
    uin = input("Please enter a uin or 'enter' to quit: ")
    if not uin:
        break
    startTime = datetime.now()
    selectCustomer(uin)
    selectVehicle(uin)
    selectHRO(uin)
    selectHLABOR(uin)
    selectHPARTS(uin)
    selectINV(uin)
    selectSUPPLIER(uin)
    selectLABOR_OP(uin)
    selectSOURCE(uin)
    selectRO(uin)
    selectLABOR(uin)
    selectPARTS(uin)
    selectEHRO(uin)
    selectEHLABOR(uin)
    selectEHPARTS(uin)
    endTime = datetime.now() - startTime
    print('It took', endTime, ' seconds to run...')

Open in new window

Each function (for the most part is exactly identical and is composed of:

opening up a SQL connection
running a query
storing the output in an ordered dictionary
creating a JSON file and naming it with the user input (in our case UIN)
closing connection and file

Depicted here:

def selectEHPARTS(uin, output_dir=r"C:\Code\TestData"):
    driver = 'DRIVER={SQL Server};'
    host = 'SERVER=SQL2;'
    database = 'DATABASE=ROWSTAGE;'

    connectionstr = driver + host + database
    conn = pyodbc.connect(connectionstr)
    cursor = conn.cursor()

    cursor.execute("SELECT STORE_UIN, RO_NO, PARTNO, PARTDESC, ITEM_NO,

Open in new window

…

29 Comments

I'm getting the following Event Viewer errors when trying to start the SQL Server (SQLEXPRESS) Service:

SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

TDSSNIClient initialization failed with error 0x2740, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Only one usage of each socket address (protocol/network address/port) is normally permitted.

TDSSNIClient initialization failed with error 0x2740, status code 0xa. Reason: Unable to initialize the TCP/IP listener. Only one usage of each socket address (protocol/network address/port) is normally permitted.

Server TCP provider failed to listen on [ 'any' <ipv4> 49172]. Tcp port is already in use.

I've tried restarting the Server but get the same result.

3 Comments

LVL 1

Question by

JDCam

5d agoSolved

Oracle Decode in Join

Experts,

I need to add a decode to a JOIN due to inconsistent values used in a table.
In table CL no value is stored as an asterix
in table PI no value is a proper null

...and DECODE(CL.INVT_LEV4,'*',null,CL.INVT_LEV4) = PI.INVT_LEV4

Open in new window

The decode above looks correct to me, but returns no data
If I use the same in a select the columns the proper (null) result.

Why is the join not working?

6 Comments

I'm trying to run a subquery that will only be valid if only 1 row exists in my subquery.

For example:
select o.*
from orders o
where o.a = 'a'
and o.c = 1
and exists (select a.a
from address a
where o.a = a.a
and only 1 row exists)

If I'm supposed to use a "having (a) = 1" I really don't know the syntax for it in a subquery.

Also, I know I could use a proc to easily solve this but unfortunately I don't have access to implement that so I need to do it in a SQL statement. If it's even possible, that is.

Would a case statement work for this?

Please note that I'm working on Oracle 12c if that helps any.

Thanks,
Larry

2 Comments

What version of SQL Server introduced JSON support?
For some reason I'm not finding it any of the JSON docs.microsoft.com pages.

Thanks in advance.
Jim

SQL
JSON

2 Comments

I am trying to write a SQL statement whereby I have a field named FormElementReference, which is the name of the question, and an associated field called value which holds the answer to that question.

I've written a case statement to try and achieve this, and store the separate values in separate fields for export to SSRS.

What I currently get is an row for each answer value.

Screenshot showing SQL with a row for each value.

SELECT DISTINCT
FormResults.FormResultKey AS [FormResults FormResultKey]
,FormResults.FormID
,FormResults.FormReference
,UserForename + ' ' + UserSurname as [Full Name]
,FormResults.FormResultDate

,CASE WHEN FormResultDetails.FormElementReference = 'Date' then value else null end AS JobDATE
,CASE WHEN FormResultDetails.FormElementReference = 'Time' then value else null end AS TIMEFrom
,CASE WHEN FormResultDetails.FormElementReference = 'TEB' then value else null end AS TIMETo
,CASE WHEN FormResultDetails.FormElementReference = 'TEA' then value else null end AS TotalWorked
,CASE WHEN FormResultDetails.FormElementReference = 'JNO' then value else null end AS JobNumbers
,CASE WHEN FormResultDetails.FormElementReference = 'AA' then value else null end AS JobAddress
,CASE WHEN FormResultDetails.FormElementReference = 'Reason' then value else null end AS JobReason
,CASE WHEN FormResultDetails.FormElementReference = 'Managers' then value else null end AS ManagerAuthorised

FROM
FormResultAnswers
LEFT OUTER JOIN Documents
ON …

4 Comments

Hello:

In SQL Reporting Services (SSRS), I have an "If, Then" formula shown in the first of two formulas pasted below.

I have this formula as an "expression" in a cell. I want to multiply this cell times a value in another cell and have the product (multiplication result) appear in another cell. I keep getting an error message that does not explain itself. But, the error is clearly letting me know that I;'m not doing this correctly.

Specifically, I'm trying to tell SSRS to multiply this formula times another field.

This other field contains the second formula pasted below.

What formula do I use to accomplish this multiplication?

Thank you!

John

=If(sum(Fields!TRXQTY.Value)=0,0,sum(Fields!DEBITAMT.Value-Fields!CRDTAMNT.Value))
/ IIF(sum(Fields!TRXQTY.Value)=0,1,sum(Fields!TRXQTY.Value))

=Sum(Fields!TRXQTY.Value*(Fields!DEBITAMT.Value-Fields!CRDTAMNT.Value))

3 Comments

LVL 6

Question by

DP230

6d agoSolved

Cannot start Veeam due to unavailable SQL server?

Dear Experts, I could not start Veeam. It said that "SQL server is not available". We checked and started all of SQL and Veeam services but it did not help.

Can you please help? many thanks!

7 Comments

Amazon Web Services

LVL 13

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Hi Experts, I have one simple question on SQL Server Stored procedure. I have staging table with 300k+ rows, my requirement is read each row from the staging table and compare with key value in the target table and do update/insert/delete . But if there is any error i have to log each error to a log table.

I have tried this in two ways , but both took more than an hour which is not at all acceptable.
1) open cursor , fetch row, perform insert/update/delete , close the cursor
2) open loop , read row to temp table, perform insert/update/delete, exit the loop when all the rows are complete.

Please let me know what is the best way to read row by row and log the error if any issues and continue the load.

5 Comments

We need to get data from MS SQL Server to Excel a few times a day. Is it possible to get this done automatically?

Thanks
Jack

5 Comments

calculate price markup percentage
divided by zero error.

,[NetCostPerUnit] - [SellingPricePerUnit] / [NetCostPerUnit] * 100 as MarkupPercentage2

I'm running this calculation
but keep getting a error "divided by zero error."

Thanks
fordraiders

7 Comments

So this may be hard to word -

I have a table that I query and due to the way that the software is setup it gives me results that are duplicated on hours and minutes...

What I am trying to do is if it shows me that it has "duplicates in essence" I need the one with the HIGHER Billable rate per user....

See data below - I can answer any questions needed

Project	Hours	Minutes	Team Member	Responsibility	Billable Rate	Billable Ammount	ProjID
18180 OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	2	0	Corey 	Designer	85.00	170.00	2049
18180 OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	2	0	Corey 	Project Manager	170.00	340.00	2049
18180 OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	5	0	Corey 	Designer	85.00	425.00	2049
18180 OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	5	0	Corey 	Project Manager	170.00	850.00	2049
18180 OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	10	0	Corey 	Designer	85.00	850.00	2049
18180 OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	10	0	Corey 	Project Manager	170.00	1700.00	2049
18180 OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	16	0	Corey 	Designer	85.00	1360.00	2049
18180 OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	16	0	Corey 	Project Manager	170.00	2720.00	2049
18180 OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	0	5	Jag 	Designer	85.00	7.083	2049
18180 OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	5	0	Jag 	Designer	85.00	425.00	2049

Open in new window

SELECT proj_id,Project,Hours, Minutes, userid as [Team Member], Expr1 as Responsibility, rate AS [Billable Rate], rate * Hours + Minutes * (rate / 60) AS [Billable Ammount]
FROM dbo.OCI_QB_TEST WHERE project like('18180%') and CONVERT(VARCHAR(5),timespent,108) <> '00:00' and task <> '16'
union all
select '', '','', '', '', 'Total','' , sum(rate * Hours + Minutes * (rate / 60) ) from dbo.OCI_QB_TEST

WHERE project like('18180%') and CONVERT(VARCHAR(5),timespent,108) <> '00:00' and task <> '16'

13 Comments

This task includes combining three different columns, which include multiple stipulations. I have managed to incorporate most of them but they just threw me a new one.

For the third column, they now want to blank out the results if the results include the number 230001 or 230002 (eap.PROC_CODE). So where now it displays a 12 digit number starting with E, some zeroes to add up to 11 digits, and the number 230001, they want that to be blank. Otherwise continue with the original statement which is:

,COALESCE('RX' + RIGHT('00000000000' + convert(varchar(11), htr.ERX_ID),11),
'S' + RIGHT('00000000000' + convert(varchar(11), orsupply.PRIMARY_EXT_ID),11),
'E' + RIGHT('00000000000' + convert(varchar(11), eap.PROC_CODE),11)) AS 'COSTITEM'

The RX column has 13 digits total, the S and E columns have 12 digits total.

14 Comments

I have this stored procedure that calls a function(JOBMESSAGE) in another database to get messages pertaining to a job. The problem I have now is the client has decided to put this other database on another server and another instance of SQL. I can't simply prefix the call with the other sql instance name as I get a message about this not being allowed. How do I adjust this sp to be able to call this function in the other instance?

CREATE PROCEDURE [dbo].[GetJobMessage]
(
@JOBNUMBER VARCHAR(21),
@COSTTYPE VARCHAR(1),
@JOBMESSAGE VARCHAR(255) OUTPUT

)
AS
BEGIN

SET NOCOUNT ON;

DECLARE @TranCount int
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int

SET @TranCount = @@TRANCOUNT

IF @TranCount = 0
      BEGIN TRAN GetJobMessage
ELSE
      SAVE TRAN GetJobMessage

-- Get job status message, if one exists
BEGIN TRY
SELECT @JOBMESSAGE = [JOBDB].[dbo].JobMessage(@JOBNUMBER,@COSTTYPE)

END TRY
BEGIN CATCH
      ROLLBACK TRAN GetJobMessage
      SELECT      @ErrMsg = ERROR_MESSAGE(),
                  @ErrSeverity = ERROR_SEVERITY()
      RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH

END

COMMIT TRAN GetJobMessage
RETURN 0 -- No errors

SET ANSI_NULLS ON

GO

10 Comments

SQL

Related Topics

SQL

Related Topics