SQL

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 trying to group records by a specific field (parent) and display the child records inside of an accordion for that parent record. I first tried to use GROUP BY but that only returned one child record. Also, as the accordions work with id numbers, I have to increment that id for each accordion that is displayed.

I changed my SQL to ORDER BY instead of GROUP BY but now it obviously doesn't group them.

SELECT `edition`, `rep`, `rep_name`, `serial` FROM `vouchers` as `v`
INNER JOIN `reps` as `r` ON r.`rep_id` = v.`rep`
WHERE `sp_id` = :sp_id ORDER BY `edition`

Open in new window


I managed to get the accordion ID bit working but can't figure out how to get it to show the $value->edition (parent) as the accordion title and the $value->serial (child) records inside the accordion.

<div class="m-accordion m-accordion--bordered" id="m_accordion_2" role="tablist">
            <?php 
                $i = 0;
                    foreach ($data['voucher_history'] as $history=>$value):
                $i++;
            ?>
    <div class="m-accordion__item">
        <div class="m-accordion__item-head collapsed" role="tab" id="m_accordion_2_item_1_head" data-toggle="collapse" href="#m_accordion_2_item_<?php echo $i; ?>_body" aria-expanded="    false">
            <span class="m-accordion__item-icon">
                <i class="fa flaticon-user-ok"></i>
            </span>
            <span class="m-accordion__item-title">
                <?php echo sanitize($value->edition) . '

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Hi

Is there a better way to deal the situation below:

(1) I have a back- end with 64 tables and currently it is occupying a total of 2.75 MB, now I want to split it to increase the total spare size to 8 GB, I know some might say the SQL is the answer, from experience it is not due to the following challenges:

(1) In most cases the DELETE action query fail to delete data in SQL back-end via ODBC connection, while others work like insert or update queries
(2) Some sub-forms with formula fail also to calculate especial the calculated controls.
(3) I have not seen any issue relating to my VBA code except the above two!

So for now I would not want to continue experiment this SQL Server thing against MS Access because there could be some other hidden issues not revealed, even using C# sharp it is a lot of challenges to make it work!


The main issues are here!

(1) The payroll shares the employee details with the sales accounting, if I move all payroll related tables into a new database can the sales accounting work???????
(2) The Point of sales shares the products & warehouse with the sales accounting & inventory, now if I move the Point of sales tables to a new database won't it affect the others??????
(3) The Fixed register share the accounts codes to all can this be moved without problems??

Currently my FE has 13.5 MB and BE is at 2.75 MB


Regards

Chris
0
Hello expert,

Using Oracle SQL Developer ver 18.1
working on learning about PL/SQL Loops.
At
https://www.tutorialspoint.com/plsql/plsql_loops.htm
The code
DECLARE
   i number(1);
   j number(1);
BEGIN
   << outer_loop >> 
   FOR i IN 1..3 LOOP
      << inner_loop >> 
      FOR j IN 1..3 LOOP
         dbms_output.put_line('i is: '|| i || ' and j is: ' || j);
      END loop inner_loop;
   END loop outer_loop;
END;
/

is offered and says the result will be

i is: 1 and j is: 1
i is: 1 and j is: 2
i is: 1 and j is: 3
i is: 2 and j is: 1
i is: 2 and j is: 2
i is: 2 and j is: 3
i is: 3 and j is: 1
i is: 3 and j is: 2
i is: 3 and j is: 3  

PL/SQL procedure successfully completed.

But all that is output is

 PL/SQL procedure successfully completed.

Is there something that needs to be changed to get the
more complete result?

Thanks.

Allen in Dallas
0
I am new to triggers for Teradata.   from the staging table if the submit quantity changes on records where the type code is either 'stock' or 'ship' update the Inventory table quantity.

This is the message I am getting:
Expected ORDER/REFERENCING/FOR/WHEN keywords or a triggered action block

REPLACE TRIGGER stgInvChng_Trg
AFTER UPDATE OF (SbmtQty) ON stgInvChng
NEW AS after row
FOR EACH ROWWHEN(afterrow.TypeCd IN ('Stock’,'Ship' ))
( Update i
FROM stgInvChng s, Invtry i
 SET InvQty = SbmtQty
Where
New.Itme = s.Item AND
s.Item = i.item);
0
I have the following SQL code:

select DISTINCT CAST(enc_timestamp AS DATE) as [Date of Visit], p.person_nbr AS [Medical Record], enc_nbr AS [Encounter Number],
'Family Medicine' as [Location], p.last_name AS [Last Name], p.first_name AS [First Name], CAST(p.date_of_birth AS DATE) as [Date of Birth],
p.zip AS [Patient Zip Code], 'ICD10' as [ICD Designation], pd.icd9cm_code_id, pd.encounter_dx_priority from patient_encounter pe
JOIN person p ON p.person_id = pe.person_id
JOIN payer_mstr pm ON pm.payer_id = pe.cob1_payer_id
JOIN patient_procedure pp ON pp.enc_id = pe.enc_id
JOIN patient_diagnosis pd ON pd.enc_id = pe.enc_id
where CAST(enc_timestamp AS DATE) >= dateadd(day,datediff(day,1,GETDATE()),0) 
and pe.billable_ind = 'Y' and pe.clinical_ind = 'Y' and pe.practice_id = '0001' 

Open in new window


The data is good, however the output method is not where I want it to be.  A patient will appear in multiple rows with this code, on the same EncounterNumber but with multiple diagnoses.  For example (I'm including the DX Priority however I don't really need that in my output--I just know that I need it in my code to determine the solution);

DATEOFVISIT       MEDICALRECORD     ENCOUNTERNUMBER     LOCATION     FIRSTNAME     LASTNAME     DOB     ZIP    ICD DES     ICD CODE     ENCOUNTER DX PRIORITY
2018/07-11                 1111                           3423423432                  Clinic               Test                 Patient           08/77  55        10                   …
0
I have a SQL statement like this

SELECT DISTINCT SiteAddress,sitecity,SiteState,SiteZip,SiteCountryCode

 FROM table
WHERE (Deleted =0
AND SiteCountryCode = 'USA')
or (SiteAddress != NULL
and SiteAddress != 'NULL')

Open in new window


SiteAddress is nvarchar(256)

Howcome this SQL brings back NULL in siteaddress?

s1.png
0
In SQL Server 2012, I am unable to connect to  SQL Server using Windows Authentication (via SQL Server Management Studio). I can sign on with the SA user using SQL Server Authentication and with another SQL Server login that I added. Just can't sign on with Windows Authentication.

According to the Server Properties, the server is set up for mixed mode (SQL Server and Windows Authentication mode).

The message that is thrown when I attempt to login with Windows Authentication is that the Login failed for User MXSVA\Administrator.

Any ideas?
0
I need to run a query where I count then group by whether a field name has particular set of characters.

So here are the sample field values:
AAR_GUAM HS_2018-07-20(Gibson)1.3(inf)
AAR_KADENA ES_2018-07-19(Johnson,Gibson)
AAR_KADENA ES_2018-07-26(Gibson)1.1-1.5-1.7(inf)
AAR_KUBASAKI HS_2018-07-26(Gibson)

I have the query for counting the number of AAR reports if the name (for example Gibson) appears in the report name.
SELECT tblAAR.AARFileName, Count(*) AS AAR
FROM tblAAR
WHERE (((tblAAR.AARFileName) Like "*" & [TempVars]![tvUserLname] & "*"))
GROUP BY tblAAR.AARFileName;

Open in new window


But I need to separate out those files names that "(inf)" and those that don't and create a separate count for both of them. A little stuck on how I can do this...

Any help would be appreciated!

Thanks!
0
Hi All;


I have just tried to run my application with SQl Server back end on a different machine with a different network, now I'm getting the error attached .I'm not too sure what it is I asked the IT Manager he assured me that he installed the correct ODBC drivers. Could it be the code below not correctly done:

Option Compare Database
Option Explicit

Function ShowConnectInfo()
  Dim dbs As DAO.Database
  Dim tdf As DAO.TableDef
  Set dbs = CurrentDb()
For Each tdf In dbs.TableDefs
    If (tdf.Connect <> vbNullString) Then
        tdf.Connect = "ODBC;DRIVER={sql server};DATABASE=MDCAccounting;SERVER=USER\SQLEXPRESS;Trusted_Connection=Yes;"
Debug.Print tdf.Connect
        tdf.RefreshLink
    End If

 Next tdf

Set tdf = Nothing
  Set dbs = Nothing
End Function

Regards

Chris
Sql-Server-error.docx
0
I am joining the below contact table with another table (id_customer).  There are 4 records.  
ContactI need the data to display as

415  Name 1 ....    593  Name 3 ...
592  Name 2 ....    628  Name 4 ...

If there are nulls then the record would have nulls.

So basically there are 2 buyers and 2 engineers.  I want a distinct record next to each other.
0
Simple Misconfiguration =Network Vulnerability
Simple Misconfiguration =Network Vulnerability

In this technical webinar, AlgoSec will present several examples of common misconfigurations; including a basic device change, business application connectivity changes, and data center migrations. Learn best practices to protect your business from attack.

I need an SQL regex to capture ICD10 code range from:  F1 thru F9.99, i was trying the following: t10."ICD-10 CODE" LIKE 'F[1-9]%' but it is not working out for me.
0
Hi Guys,

Does anyone know the syntax in MSSQL to  get all the characters left from the first non-alphanumeric character:

What I have so far is below:

The LABEL value is 'Joe Bloggs [Duplicate - do not use]'

SUBSTRING([LABEL],0,CHARINDEX('[',[LABEL],0))

Open in new window


This yields  'Joe Bloggs ' which is ok but the issue is that someone could put any other character therefore it wouldn't work if someone puts ^ or / etc.

Could someone please kindly help me withthis?
0
I have a table in an Access 2007 database that has a bunch of different statuses for one row of information. I am trying to match this table's data to another table which separates each status into a different row. Now, my original plan was to separate the first table's records into different rows based on their statuses and append them to a new table that can be used to match data with the original table. That is, I would create a query that focused on one status and assign it a letter that was associated with the status, such as "P" for pending. Then, I would append that data, with the letter replacing the original status column, into a new table that could be used to match data with the second table.

The only problem with this plan is that it would require multiple appending tables. I'm wondering if anyone out there has made a query that could break a row of data into separate rows and assign them data so that I would only have to build and append one query instead of several separate ones. If not, does anyone have any suggestions as to how I could make a single query that could do all this work? Or am I better off building multiple appending queries like my original plan?

Please let me know if my explanation was unclear and you need more information!
0
Hi
I use the following code to hide databound ASP.net columns. Is there a way to automatically hide all databound columns that were pulled
into my GridView using my SQL select statement. I only want my template fields to be visible. As you can see there are 12 template fields.
I am trying to find a cleaner way of doin this where I don't need to know the number of template fields

    Private Sub GridView1_RowCreated(sender As Object, e As GridViewRowEventArgs) Handles GridView1.RowCreated

        Try

            'Data Rows
            If e.Row.RowType = DataControlRowType.DataRow Or e.Row.RowType = DataControlRowType.Header Then

                'There are 12 template fields with index 0 to 11
                For i As Integer = 12 To e.Row.Cells.Count - 1
                    e.Row.Cells(i).Visible = False
                Next

            End If

        Catch ex As Exception
            Response.Write(ex.Message & " s34")
        End Try
    End Sub

Open in new window

0
Hi Expert!

Just a small help!

I have seen a code for SQL table re-linker here see below:

Dim sConnect As String
sConnect = "ODBC;DRIVER={sql server};DATABASE=YourDatabaseName;SERVER=YourServerName;Trusted_Connection=Yes;"

Dim tdf As DAO.TableDef
Dim dbs As DAO.Database

Set dbs = CurrentDB
Set tdf = dbs.TableDefs("YourTableName")

tdf.Connect = sConnect


Now I have 64 tables to be re-linked as you can see above there is one provision for a table only(Set tdf = dbs.TableDefs("YourTableName")
), then how do I accommodate all the 64 tables there on MS Access start-up form Open event??????????


Regards

Chris
0
I'm looking for MySQL query that will replace values in a certain column matching certain value
so in this example I'd like to replace only all ABC  values under only Column1 with say 123 and leave XYZ unchanged, CBD
ID | Column1| Column2
1 | ABC           | blah
2 | ABC           | blah
3 | XYZ            | blah
4 | CBD          | blah

Thanks!
0
Hi all.

I have the following query that contains a subquery. I want to only display records whose count (from the subquery) is greater than 1.

The problem is that the count it's displaying is twice as many as the original count. So for example, it's showing record ABC as having a count of 4, when in reality the count is 2. When I use the subquery (SQ) as it's own query it correctly shows a count of 2, but when I add it to the query below (as a subquery) it doubles the count. What am I doing wrong?

SELECT DISTINCT a.GPOContractID, b.EntityCode, COUNT(SQ.EntityCode) AS Count
FROM         ContractAssignmentHeader AS a INNER JOIN
                      ContractAssignmentDetail AS b ON a.CANumber = b.CANumber INNER JOIN
                          (SELECT DISTINCT ContractAssignmentHeader.CANumber, ContractAssignmentHeader.GPOContractID, ContractAssignmentDetail.EntityCode
                            FROM          ContractAssignmentHeader INNER JOIN
                                                   ContractAssignmentDetail ON ContractAssignmentHeader.CANumber = ContractAssignmentDetail.CANumber
                            WHERE      (ContractAssignmentDetail.Status = N'Active') AND (ContractAssignmentHeader.ContractType = N'GPO')) AS SQ ON 
                      a.GPOContractID = SQ.GPOContractID AND b.EntityCode = SQ.EntityCode
GROUP BY a.GPOContractID, b.EntityCode
HAVING     (COUNT(SQ.EntityCode) > 1)
ORDER BY a.GPOContractID

Open in new window


Thank you in advance.
0
I have the following table in a MS SQL DB:

CREATE TABLE [dbo].[Forms](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[FormNumber] [int] NOT NULL,
	[Blob] [varbinary](max) NULL,
	[FileName] [nvarchar](1000) NULL,
	[FormName] [nvarchar](1000) NULL,
 CONSTRAINT [PK_Forms] PRIMARY KEY CLUSTERED 
(	[Id] ASC
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Open in new window

The contents of the table look like this:
1	19000	 Form19000.pdf	Order Form 1
2	19001	 Form19001.pdf	Order Form 2
3	19002	 Form19002.pdf	Order Form 3

Open in new window

I have information in all the columns except the BLOB column.  I would like to load the blob of a file for each row with its corresponding file.  How would I do this for a table that has several hundred rows using T-SQL?

I know that I can manually load each row with the following statement but how would I do this for the whole table:
UPDATE dbo.Forms
SET Blob =
	(Select * from OPENROWSET(bulk 'C:\Forms\Form19000.pdf', single_blob) as a)
WHERE Id = 1

Open in new window

0
I've been asked to do some research on what the advantages are by moving an MS Access front-end to a .net platform. We have a front-end that runs on a SQL backend currently with about 15-20 users. I've done some research and for this size user base & database I really can't seem to find any major benefits by moving the front-end to .net. Can anyone shed some light on if this makes sense or not? I seem to think it doesn't because of the size of the database and the amount of users but I'm open to opinions.
0
Keep up with what's happening at Experts Exchange!
LVL 12
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

hi,
I'm trying to execute this query and the following error happens

select a.abc ,bdf ,cgh , b.xyz
from tablea a , tableb b
where a.abc in ( select distinct xyz from tableb);

this gives error-
ora - 01652 unable to extend temp segment by 640 in tablespace oratemp
0
Hi all;

I have got a link table manager on the net and I have tried to modify it to see if it can be used to create a less DSN connection because I will be shipping my application to other people as well with different networks. The original code before amendments  is listed below:


'//Name     :   CreateDSNConnection
'//Purpose  :   Create a DSN to link tables to SQL Server
'//Parameters
'//     stServer: Name of SQL Server that you are linking to
'//     stDatabase: Name of the SQL Server database that you are linking to
'//     stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'//     stPassword: SQL Server user password
Function CreateDSNConnection(stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String) As Boolean
    On Error GoTo CreateDSNConnection_Err

    Dim stConnect As String
   
    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "Description=myDSN" & vbCr & "SERVER=" & stServer & vbCr & "DATABASE=" & stDatabase & vbCr & "Trusted_Connection=Yes"
    Else
        stConnect = "Description=myDSN" & vbCr & "SERVER=" & stServer & vbCr & "DATABASE=" & stDatabase & vbCr
    End If
   
    DBEngine.RegisterDatabase "myDSN", "SQL Server", True, stConnect
       
    '// Add error checking.
    CreateDSNConnection = True
    Exit Function
CreateDSNConnection_Err:…
0
SQl for Oracle SQL Developer.  I am trying to count the number of items by a date field between two dates but it does not like my date syntax.
and EVENT_DT > TO_DATE('01-JAN-2017', 'DD-MON-YYYY') and
and EVENT_DT > fROM_DATE('31-DEC-2017', 'DD-MON-YYYY')

The first date works but not when I add the FROM Date.
0
Hello,

is it supported to put SQL server on hyper-v replica

I know that Exchange does not support the Hyper-V Replica feature,

but for SQL I have not found clear answer.
Thank you
0
I need to count how many calls per hour. My table has 3 fields... Date, Time, and Origin (see screenshot of sample data). I need to know how many In calls per hour per day, and how many Out calls per hour per day. After that, I need to sum up all that data (it's 30 days worth) and get the average number of In calls per hour, and average number of Out calls per hour. So, I'll end up with a report that shows all 30 days, with data that looks like this:

6/13/2018  IncomingPerHour  20
6/13/2018  OutgoingPerHour  12
6/14/2018  IncomingPerHour  23
6/14/2018  OutgoingPerHour  7
etc.

Screenshot of my source table:

sample
0
i have SQL data file which is 5 gb and sql log file is 71 gb..  in E Drive size is 100gb..
There a job full Daily Full backup are happening, .bak file is only 191 MB

After realizing the above I did one Transcational log backup to g drive(got temp space), it came around 18gb, after that I took again full backup the size of backup was same around 192MB

is some thing wrong with transaction log, there is no open transcations, i have verfied? is my full backup is 192 Mb only?

im shortage of space as well to take hourly transaction log on E drive, the db is in full recovery model

Version is sql server 2012 std
0

SQL

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.