Query Syntax

51K

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 working on a query. Getting this ERROR. please help out!!

SELECT
A.ID AS QUOTE_ID

FROM (
SELECT
PRICE_LIST.ID AS IDEX
FROM (
SELECT
LIST AS PP_ID from TABLE 1
) QUOTE-----------
LEFT OUTER JOIN TABLE 2 PRICE_LIST
ON QUOTE.PP_ID = PRICE_LIST.Id) QUOTE_PRICEPLAN

**************************************************************************

I am getting an error Invalid column name 'ID'.
PLEASE Help me on this at the earliest.
0
Introducing Cloud Class® training courses
LVL 12
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Hi!

I was trying to review the execution plan of a Query and saw the Nested Loop parameter "No join predicate" . How could I correct this Warning?


CREATE PROCEDURE [dbo].prd_3   
@sistema int,  
@aplicativo int,  
@codi_enc int,  
@codi_usu int    
As  

SELECT  per.codi_pfl AS 'ID',
                per.vNOMB_PFL AS 'DESCRIPCION',
                api.vNOMB_APL
FROM SEG_PERFIL AS per
INNER JOIN SEG_PERFIL_OPCION AS opc ON per.CODI_PFL = opc.CODI_PFL
INNER JOIN SEG_OPCION AS opi ON opi.CODI_OPC = opc.CODI_OPC
INNER JOIN SEG_APLICACION AS api ON api.CODI_APL = opi.CODI_APL
AND opi.CODI_SIS = api.CODI_SIS
INNER JOIN SEG_ENCARGATURA_APLICACION eapp ON api.codi_sis =eapp.CODI_SIS
AND api.codi_apl=eapp.[CODI_APL]
WHERE api.CODI_SIS = @sistema
  AND api.codi_apl= @aplicativo
  AND eapp.CODI_ENC=@codi_enc
  AND per.codi_pfl NOT IN (148,
                           158,
                           159)---perfil  no a listar
AND  EXISTS
    (SELECT 1
     FROM SEG_ENCARGATURA_PERFIL
	 inner join SEG_PERFIL  ON
	 per.CODI_PFL = SEG_ENCARGATURA_PERFIL.CODI_PFL
     WHERE CODI_USU = @codi_usu)
  AND eapp.CODI_USU=@codi_usu
	GROUP BY
	per.codi_pfl,
                per.vNOMB_PFL,
                api.vNOMB_APL

ORDER BY per.vNOMB_PFL
GO

Open in new window


I'd really appreciate your help!
0
Experts:

I need some assistance with determining a query (in MS-Access) which outputs a text value (e.g., night, morning, afternoon, evening).   Please see attached MDB.

The values are based on the following times:
00:00 to 05:59 AM =  Night
06:00 to 11:59 AM =  Morning
12:00 to 05:59 PM =  Afternoon
06:00 to 11:59 PM =  Evening

In my query, I converted times into a 24-hour format to show which is the equivalent of:
00:00 to 05:59 =  Night
06:00 to 11:59 =  Morning
12:00 to 17:59 =  Afternoon
18:00 to 23:59 PM =  Evening

Based on the module basConversion, I would like to produce the "night, morning, afternoon, and evening" output.

My question:   How do I modify the query to accomplish this task?

Thank you,
EEH
BetweenTimes.accdb
0
Hi Experts,
Attached is my database.  I have box# 8968 and 8969 also 89680, when I try to update the pallet# and location, for some reason it picks up extra records than it suppose to be.  I'm trying to figure it out but have no idea why it happen.  (for example: box# 8968 & 8969 have 30 records of each, so total should be 60 but it gave me 61 records because it include 89680 which I only entered from 8968 to 8969).  many thanks,
Box-FE.mdb
0
Hello all.

I am trying to pass on parameters contained in my initial search form, onto the pagination script in my results page.  I'm not sure how to do this properly.

My set up currently is flawed in that I lose my initial search criteria when I click on the [Next], [Last] etc buttons in my pagination script.  I guess this is because I need to pass the parameter values from my initial search into the pagination? Currently my simple pager code looks like this:

<ul class="pagination justify-content-end">
					  
					 <?php
					 if($pageno <= 1){?>
						<li class="page-item disabled"><a class="page-link" href="#">&#x276e; &#x276e; First </a></li>
					<?php } ?>

					<?php
					if($pageno > 1){?>
						<li class="page-item"><a class="page-link" href="?pageno=1"> &#x276e; &#x276e; First </a></li>
					<?php } ?> 
					  
					  
					  <li class="page-item"><a class="page-link" href="<?php if($pageno <= 1){ echo '#'; } else { echo "?pageno=".($pageno - 1); } ?>"> &#x276e; Previous</a></li>
					  
					  <li class="page-item"><a class="page-link" href="<?php if($pageno >= $total_pages){ echo '#'; } else { echo "?pageno=".($pageno + 1); } ?>"> Next &#x276f;</a></li>
					  
					 
					 <?php
					 if($pageno >= $total_pages){?>
						<li class="page-item disabled"><a class="page-link" href="#">Last &#x276f; &#x276f;</a></li>
					<?php } ?>

					<?php
					if($pageno < $total_pages){?>
						<li class="page-item"><a class="page-link" href="?pageno=<?php 

Open in new window

0
In my quest to create a poor man's address correction routine I created about 25 stored procedures similar to the one below. The only thing that changes are the strings used in the search. I have wrapped all these sp's into a VS program plus an SSIS job. What I just discovered is that the only changes that are sticking are the ones in the last sp that is called. If I run them individually the changes stick. I have a COMMIT TRAN at the end of each one.  What must I change to get the changes to stick as each sp is called? It must have something to do with all these sps called inside one session.


CREATE PROCEDURE [dbo].[apd_UpdateAddressesCASESB02]

AS

BEGIN

      SET NOCOUNT ON

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

SET @TranCount = @@TRANCOUNT

IF @TranCount = 0
      BEGIN TRAN

BEGIN TRY

UPDATE mt
 SET [MOD_ESRI_LOCATION] = chg99.[MOD_ESRI_LOCATION]
 FROM [dbo].[cases] mt
 CROSS APPLY (
     SELECT CASE WHEN PATINDEX('%BLVD%', mt.[MOD_ESRI_LOCATION]) >= 4 THEN SUBSTRING(mt.[MOD_ESRI_LOCATION],1,PATINDEX('%BLVD%',mt.[MOD_ESRI_LOCATION])+1)
         ELSE mt.[MOD_ESRI_LOCATION] END  AS [MOD_ESRI_LOCATION]
 ) AS chg01
 CROSS APPLY (
     SELECT  CASE WHEN PATINDEX('%BSMT%', chg01.[MOD_ESRI_LOCATION]) >= 4 THEN SUBSTRING(chg01.[MOD_ESRI_LOCATION],1,PATINDEX('%BSMT%',chg01.[MOD_ESRI_LOCATION])-1)
         ELSE chg01.[MOD_ESRI_LOCATION] END AS [MOD_ESRI_LOCATION]
 ) AS chg02
 CROSS APPLY (
     SELECT CASE WHEN …
0
Hello,
I have Table1 with date fields
From date  null
To      Date null
Howevr wen  run this query below it inserts 1900-01-01 in the column

INSERT INTO [TABLE1] 

SELECT 
         , IET.[From]
         , IET.[To]
 FROM [TABLE2] IET


WHERE [InvoiceItemGUID] NOT IN 
    (SELECT [InvoiceItemGUID] FROM TABLE1 )

Open in new window


Thanks
0
Hi,
I need to know how to convert the following Query code to Microsoft SQL Server Stored Procedure.

public string db_get_unhandled_items_CommandText(int? fromIdPerson, int? toIdPerson, int? fromIditem, int? toIditem)
{
    string IdPerson_cond = get_cond_str(fromIdPerson, toIdPerson, "aa.IdPerson");
    string Iditem_cond = get_cond_str(fromIditem, toIditem, "aa.Iditem");

    return @"
        select bb.Iditem,CONCAT('http://www.mysite.com/Items', replace(r.SourceURL, '\', '/')) urlSource, r.IdResource
        from [MYDB].[dbo].tblitem bb
          inner join [MYDB].[dbo].tblitemPerson aa
            on aa.Iditem = bb.Iditem AND " + Iditem_cond + @"
            inner join [MYDB].[dbo].tblResource r
            on bb.IdPrimaryAsset = r.IdAsset
            left join [dev].[AW_IR].[tblitem_labels] t2 on t2.Iditem = bb.Iditem
        where t2.Iditem is null AND " + IdPerson_cond + @"
        order by Iditem";
}

private static string get_cond_str(int? from, int? to, string Id_column)
{
    string query;
    if (from == null)
    {
        if (to == null)
        {
            query = "1 = 1";
        }
        else
        {
            query = Id_column + " <= " + to;
        }
    }
    else
    {
        if (to == null)
        {
            query = Id_column + " >= " + from;
        }
        else
        {
            query = Id_column + " >= " + from + " AND " + Id_column + " <= " + to;
        }
    }
    return query;
}
0
Need to omit the timestamp from my results in Oracle. Screenshot attached of my results and my statement is below. Any help will be greatly appreciated.

SELECT        BA_VIEW_WO_SUMMARY.WO_NUMBER, BA_VIEW_WO_SUMMARY.STATUS AS WO_STATUS, BA_VIEW_WO_QUOTE.STATUS AS QUOTE_STATUS, 
                         BA_VIEW_WO_SUMMARY.DEPT_NAME AS SHOP, BA_VIEW_WO_SUMMARY.PN, BA_VIEW_WO_SUMMARY.SERIAL_NUMBER, 
                         BA_VIEW_WO_SUMMARY.DESCRIPTION, BA_VIEW_WO_SUMMARY.CUSTOMER_PO, TRUNC(BA_VIEW_WO_SUMMARY.DUE_DATE) AS NEED_BY, BA_VIEW_WO_SUMMARY.CUSTOMER_NAME, 
                         BA_VIEW_WO_SUMMARY.PBH_CUSTOMER, CAST(BA_VIEW_WO_SUMMARY.ENTRY_DATE AS DATE) AS WO_CREATION, 
                         CAST(BA_VIEW_WO_QUOTE.QUOTE_DATE AS DATE) AS QUOTE_CREATED, CAST(BA_VIEW_WO_QUOTE.SENT_DATE AS DATE) AS QUOTE_SENT,  BA_VIEW_WO_QUOTE.QUOTE_AMOUNT,
                         CAST(BA_VIEW_WO_QUOTE.APPROVED_DATE AS DATE) AS APPROVED_DATE,  
                         BA_VIEW_WO_SUMMARY.MANUAL_ECD AS ESD
FROM            BA_VIEW_WO_SUMMARY LEFT OUTER JOIN
                         BA_VIEW_REPAIR_ORDERS ON BA_VIEW_WO_SUMMARY.WO_NUMBER = BA_VIEW_REPAIR_ORDERS.WO_NUMBER LEFT OUTER JOIN
                         BA_VIEW_WO_QUOTE ON BA_VIEW_WO_SUMMARY.WO_NUMBER = BA_VIEW_WO_QUOTE.WO_NUMBER
                         WHERE        (BA_VIEW_WO_SUMMARY.SYSCM_AUTO_KEY = '1') AND (BA_VIEW_WO_SUMMARY.CLOSE_DATE IS NULL)

Open in new window

Capture.PNG
0
Summarize SQL values that need to be divided with remainders.

I need to summarize a group of SKU's and their weights. the challenge I have is that that the weight field has a value that needs to be divided by 100 to get the correct weight
i.e. Sku#1 has a value of 15200 which should actually be 152.00 pounds
Sku#2 has a value of 3 which should actually calculate to .03
Sku#3 has a value of 15 which should actually calculate to .15

The summarized value for the 3 Sku's should then = 152,18 pounds

How would I formulate the sku values to summarize as shown?

Thanks
0
Cloud Class® Course: Microsoft Office 2010
LVL 12
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

HELP WITH SQL SERVER 2008R2 STATEMENT

I have a table called SHIPPER with a field called CUST_ORDER_ID and PACKLIST_ID  Ex: DC180001

i have a view called CT_TRACKING_INFORMATION_VIEW that has a field called TRACKING_INFO and PACKLISTS that can contain multiple PACKLIST_ID Ex: DC180001, DC180003, DC180040

I need a select statement that pulls the SHIPPER record by ORDER_ID and the TRACKING_INFO field if the PACKLIST_ID is "in" the PACKLISTS field.
This works if i hard code the LIKE '%DC180001%'
SELECT SHIPPER.CUST_ORDER_ID, shipper.PACKLIST_ID, Tracking_Information FROM SHIPPER, CT_TRACKING_INFORMATION_VIEW WHERE CUST_ORDER_ID = 'CC181430' AND (CT_TRACKING_INFORMATION_VIEW.packlist_id LIKE '%DC180001%' OR CT_TRACKING_INFORMATION_VIEW.packlist_id IS NULL)

Open in new window


but i can't figure out how to make it use the shipper.PACKLIST_ID   i.e. This does not work:
SELECT SHIPPER.CUST_ORDER_ID, shipper.PACKLIST_ID, Tracking_Information FROM SHIPPER, CT_TRACKING_INFORMATION_VIEW WHERE CUST_ORDER_ID = 'CC181430' AND (CT_TRACKING_INFORMATION_VIEW.packlist_id LIKE '%' + shipper.PACKLIST_ID +'%' OR CT_TRACKING_INFORMATION_VIEW.packlist_id IS NULL)

Open in new window


ok, got this to work somewhat:
 WHERE     (dbo.CT_TRACKING_INFORMATION_VIEW.packlist_id LIKE '%' + s.PACKLIST_ID + '%') OR
                      (dbo.CT_TRACKING_INFORMATION_VIEW.packlist_id IS NULL)

Open in new window


but it returns 2 records for each Order 1 with Null TRACKING_INFORMATION and 1 with the actual tracking information.
Note all records will have tracking information, that is why i am including IS NULL.
what i need is for the select statement to bring back only 1 record and if it has tracking information to include it.
0
I clicked on the "Reports" tab of Server 2016 Windows Server Updates Services and was told that "The  "Microsoft Report Viewer 2012 Redistributable"  is required for this feature. Please close the console before installing this package."

So I clicked on the URL link and was directed to the website https://www.microsoft.com/en-us/download/details.aspx?id=35747 where I downloaded the "Microsoft Report Viewer 2012 runtime."

However, when I installed the "Microsoft Report Viewer 2012 runtime" ReportViewer.msi file I received a message that said "Setup is missing an installation prerequisite. -Microsoft System CLR Types for SQL Server 2012" (see the screenshot).

I have googled this and the results I have found have had me download several different MSI installation files without telling me exactly which one(s) I need to install. I want to make sure that I only install the component or program that I absolutely need and that I don't install anything extra.

So what is the best URL to visit to download and install the right "Microsoft System CLR Types for SQL Server 2012?"

Microsoft System CLR Types
0
Hello Experts:

It's been a while since I used "case statements" in MS-Access.

I need some assistance with using a wildcard (*) in a case statement.   Below is what I have:

Public Function Q8_If_Married_Convert(Q8_If_Married As Variant) As Variant

        Select Case Q8_If_Married
        
           Case "Resides with spouse":                                                                   Q8_If_Married_Convert = "Resides with spouse"
           Case "Separated":                                                                                   Q8_If_Married_Convert = "Separated"
           Case "Separated due to other reasons (e...g... deployed)":                    Q8_If_Married_Convert = "Separated due to other reasons"
           Case Else:                                                                                               Q8_If_Married_Convert = "No data available"
           
        End Select

End Function

Open in new window



In this example, the problem lies occurs for the 3rd statement.   Rather than having a hard-coded case equal to "Separated due to other reasons (e...g... deployed):", I prefer using the case "Separated due to other reasons*" (ending with the wild card).   The key challenge how the data entry person entered the reference to "deployment".   That is, in this case, too many periods were used in the "e.g." reference.   Alternatively, in other cases, the separation reason may not be "deployment" but could be "XYZ" reason.

Can anyone offer some assistance with a) recommendation how to tweak the case select statement or b) offering an alternative that allows me to convert all possible outcomes for the "Separated due to other reasons*" scenario?

Thank you,
EEH
0
Hi,

In my LAB I have installed the MBAM server and SQL DB's on a cluster. Reporting services also installed...
I can access the MBAM web page without problems.

GPO's configured as well.

However, after noticing that I can't find any recovery info of my test devices I wanted to check directly in the database.

There are no tables in the DB at all.

I then dropped the Recovery DB and created it again.
No errors whatsoever, but the DB still doesn't contain any tables.

Also not in the Event Viewer. It all looks fine. But DB remains empty.

Any ideas?

Screenshot:


0
Hi,

I've been working on improving this Query and I've been able to reduce a lot of Logical Reads through Indexing.  But I've been wondering if there's any way to improve the Query's OR conditional.


/****** Object:  StoredProcedure [dbo].[std_pro1]    Script Date: 6/19/2018 1:46:33 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE  procedure [dbo].std_pro1  
     @FLG_BUSQUEDA_AVANZ        smallint
    ,@FECH_DOC_TDOC_INI_TXT     varchar(10)
    ,@FECH_DOC_TDOC_FIN_TXT     varchar(10)
    ,@FECH_CREA_TDOC_INI_TXT    varchar(10)
    ,@FECH_CREA_TDOC_FIN_TXT    varchar(10)
    ,@FECH_ENV_TDOC_INI_TXT     varchar(10)
    ,@FECH_ENV_TDOC_FIN_TXT     varchar(10)
    ,@CODI_EDOC_TED             smallint
    ,@CODI_TDOC_TTD             numeric(9)
    ,@CODI_TMOV_TTM             numeric(9)
    ,@NRO_REG_TDOC              int
    ,@NUM_DOC_TDOC              varchar(40)
    ,@CODI_EATD_TED             numeric(9)
    ,@FLG_ANU_TDOC              char(1)
    ,@NOM_EMI_TSEG              varchar(250)
    ,@EMP_EMI_TSEG              varchar(100)
    ,@OFI_EMI_TSEG              int
    ,@CODI_EMI_TSEG             int
    ,@NOM_DEST_TSEG             varchar(250)
    ,@EMP_DEST_TSEG             varchar(100)
    ,@OFI_DEST_TSEG             int
    ,@CODI_DEST_TSEG            int
    ,@ASUN_DOC_TDOC             varchar(2000)
    ,@USU_EMI_TSEG              int
    ,@ENC_EMI_TSEG              int
    ,@USU_DEST_TSEG             int
    ,@ENC_DEST_TSEG             

Open in new window

0
I am calling a table valued function which is returning multiple columns in select subquery, yet I know that select query returns single column at a time only.
So how is it possible to do

subquery
     SELECT TOP (1) CRFCA.Name, CRFCA.Rate, CRFCA.ChemicalFamily, CRFCA.WHP, CRFCA.qty, CRFCA.totalMixer
            From fnChemicalRecordForChemicalApplication(@companyID) as CRFCA           
            WHERE .ChemicalApplicationID = chemicalApplication.ChemicalApplicationID
            ) AS value8

Open in new window


Function
   create function fnChemicalRecordForChemicalApplication(@companyID int)
returns table
as
return (
		SELECT  chemicalApplicationChemical.ChemicalApplicationID as ChemicalApplicationID, chemical.Name as Name, chemicalApplicationChemical.Rate as Rate,                                               chemical.ChemicalType as ChemicalFamily, chemical.Whp as WHP, chemicalApplicationChemical.Quantity as qty, chemicalApplicationChemical.TotalMixture as totalMixer
                FROM ChemicalApplicationChemical AS chemicalApplicationChemical              
                INNER JOIN Chemical AS chemical ON chemical.ChemicalID = chemicalApplicationChemical.ChemicalID
				WHERE  (chemical.CompanyID = @companyID)
)

Open in new window

0
In general, when you run an aggregate query in Access...how would you select the Min or Max of the results?
For example, I have the following sql
Lets try this again...I've changed the SQL slightly
SELECT DLPReport.[Employee - Key] AS EmpID, DLPReport.[Cost Center], Sum(DLPReport.[Total Comp]) AS [SumOfTotal Comp]
FROM DLPReport
GROUP BY DLPReport.[Employee - Key], DLPReport.[Cost Center]
HAVING (((DLPReport.[Employee - Key])=846843));

Open in new window

This gives me three rows of data in the results:
EmpID      Cost Center      SumOfTotal Comp
800003      4001              3,190.53
800003      4002               5,146.86
800003      4003                      -5.28

How would I return only the MAX of the [SumofTotal Comp], which is the middle record with 5,146.86?
0
I have this bit of sql code. Can anyone tell me what the "VALUE_DATE_OFFSET_NO,-20" and "VALUE_DATE_OFFSET_NO,20" gives me?
No comments in the code. What is this trting to acheive?
Is it saying to subtract 20 from the column and add 20 to the column for the declared variables?

DECLARE @MinCapacityOffset INT
DECLARE @MaxCapacityOffset INT


-- Look up the minimum and maximum offsets specified in the Capacity standards table.
SELECT            @MinCapacityOffset = MIN(ISNULL(TCAPACITY.VALUE_DATE_OFFSET_NO,-20)),
            @MaxCapacityOffset = MAX(ISNULL(TCAPACITY.VALUE_DATE_OFFSET_NO,20))
FROM            TCAPACITY
0
Hello expert,

In previous question titled 'DECLARE CURSOR IS SELECT BEGIN'
The Cursor/Loop form was used to display a list of names and Contact IDs

Informative but not too useful.

In this question, more complex, there are attached texts to create three
tables
TMP_SOURCE_180615 has fields Contact_ID, FirstName and LastName

TMP_SOURCE_180617 has fields Contact_ID, Contact_Phone, FirstName and LastName

TMP_SOURCE_180618 has fields Contact_ID, LastName, and Contact_Phone

PS File names too long for display. Have truncated the names to the last six digits..

Ususally a script is attempted before asking a question because I don't
believe in just asking a completely open ended question like 'How do I do this?'

But have tried a couple of things and it is obvious that I am not even close.

However within the context of the script marked 'Declare Cursor TMP_SOURCE_180615'
copied herewith below what is the approach and tactic:

Where the Contact_ID is a certain value in TMP_SOURCE_180615
Fetch the CONTACT_PHONE that has that CONTACT_ID value from TMP_SOURCE_180617
and insert the CONTACT_PHONE value that matches the Contact_ID in TMP_SOURCE_180618
in the matching CONTACT_Phone field.

Again would like to make question more specific but am in uncharted waters and cannot
find a tutorial that addresses this issue.

Will double point on answer.

Allen in Dallas

---- Declare Cursor TMP_SOURCE_180615 ------
SET SERVEROUTPUT ON
DECLARE
      CURSOR …
0
Cloud Class® Course: Microsoft Windows 7 Basic
LVL 12
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

I've got two MSSQL (2014) tables:
  create table PARENT( ID int );
  create table CHILD( PARENTID int, CHILDID int, AGE int )
/* there are 3 parents */
  insert into PARENT values( 1 );
  insert into PARENT values( 2 );
  insert into PARENT values( 3 );
/* there are 4 children to parent 1, aged 5,6,7,8 */
  insert into CHILD values( 1, 10, 5 );
  insert into CHILD values( 1, 11, 6 );
  insert into CHILD values( 1, 12, 7 );
  insert into CHILD values( 1, 13, 8 );
/* there are 2 children to parent 2, aged 15,16 */
  insert into CHILD values( 2, 20, 15 );
  insert into CHILD values( 2, 21, 16 );
/* there are no children to parent 3 */

Open in new window


I can read out all parents and their children (and the children's age) with:
  select p.ID, c.CHILDID, c.AGE
    from PARENT p
    left join CHILD c on c.PARENTID=p.ID
  order by p.ID, c.AGE desc

Open in new window


Now I however would like to get a list with one row only per parent, including the ID and AGE of the three oldest children in separate columns.
If there are less than three children for a parent, the corresponding ID and AGE columns for the child should be null.

Any ideas to do this?

I may get such a list for only parents with at least three children doing like this - but that is not good enough :-(
select
  p.ID, 
  c1.CHILDID, c1.AGE,
  c2.CHILDID, c2.AGE,
  c3.CHILDID, c3.AGE
from PARENT p
cross apply (select ROW_NUMBER() over (order by AGE desc) as ROWNO, * from CHILD where PARENTID=p.ID) c1
cross apply (select ROW_NUMBER() over (order by AGE desc) as ROWNO, * from CHILD where PARENTID=p.ID) c2
cross apply (select ROW_NUMBER() over (order by AGE desc) as ROWNO, * from CHILD where PARENTID=p.ID) c3
where 
  c1.ROWNO=1 and c2.ROWNO=2 and c3.ROWNO=3
order by p.ID

Open in new window


Thanks,
/Stefan
0
Microsoft Access query syntax is giving me unexpected results.
I have an invoice date field.
If the query is run on Monday, I want to see invoices from Friday and the weekend (ie, on Monday I want to see invoices from Friday, Saturday and Sunday).
If the query is run any other day, I want to see invoices from yesterday. (ie on Thursday only want to see invoices from Wednesday)

This query provides no results. If I remove the greater than sign, then I get results, but only for the exact day ( I want to include the weekend details when run on Monday).
     IIf(Weekday(Date())=2,>Date()-4,>Date()-1)

This query does work, but it isn't what I want because it excludes the weekend invoices on Monday.
     IIf(Weekday(Date())=2,Date()-4,Date()-1)

What am I missing about the '>' function ?
0
Hello expert,

Found some old SQL scripts that use DECLARE CURSOR IS SELECT BEGIN LOOP END LOOP END
that need updating but am not familiar with this form.

Have looked online for a simple example so I could experiment with this model and figure it out.
Not finding an example that is simple, if it exists.

So have set out to edify myself.

First, created a table using script  so it is portable. Attached herewith: PL SQL Create Table explicit.txt
Creates a table with three records and twenty rows.

Second, Have been experimenting with code listed herewith below;
from http://www.oracletutorial.com/plsql-tutorial/plsql-cursor-for-loop/

The Oracle SQL Developer always returns
Error starting at line : 1 in command
which is not too informative.

Think the issue is in the syntax in the PUT_LINE
but can't quite make it work.

Just working to get a simple example to do something
on which a base can be built.

Thanks.

Allen in Dallas


-----------------begin cursor script------------------
DECLARE
  CURSOR cur_name
  IS
    SELECT
      CONTACT_ID, FIRST_NAME, LAST_NAME
    FROM
      TMP_SOURCE_180615
    ORDER BY
      LAST_NAME DESC;
BEGIN
  FOR TMP_SOURCE_180615 IN cur_name
  LOOP
    DBMS_OUTPUT.PUT_LINE( TMP_SOURCE_180615.FIRST_NAME,
  TMP_SOURCE_180615.LAST_NAME);
  END LOOP;
END;
PL-SQL-CREATE-TABLE-explicit.txt
0
Hi

How do I create a UNIQUE constraint on two columns in a SQL table?
Can I use SQL code to do this?

If I have a table called Table1.  I only want unique records for the combination of Column2 and Coumn3.
What SQL would I use to create this constraint?

Can this be done after the tables have been created?
0
Hi EE,

Is there an equivalent of this query for SQL server 2000.

SELECT OBJECT_NAME(ius.object_id) as TableName,

       si.name as IndexName,

       si.index_id as IndexID,

       ius.user_seeks,

       ius.user_scans,

       ius.user_lookups,

       ius.user_updates,

       ius.last_user_seek,

       ius.last_user_scan,

       ius.last_user_lookup,

       ius.last_user_update

  FROM sys.dm_db_index_usage_stats ius

  JOIN sys.indexes si

    ON ius.object_id = si.object_id

   AND ius.index_id  = si.index_id

WHERE database_id = db_id()

Order by ius.user_seeks DESC

Open in new window


If not do I have any options for tracking, database usage in SQL server 2000.

Any advice is welcome.

Thak you.
0
Hello Experts!

I'm getting the following error though the data gets inserted correctly.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?, ?, ?, ?, ?, ?, ? )' at line 2

My code is as follows:

<?php

if(isset($_POST["add_staff"]))
{
	
	$fullname = $_POST["initials"];
	$username = $_POST["user_name"];
	$password = $_POST["password"];
	$role = $_POST["role"];
	$class_assigned = $_POST["class_assigned"];
	$subject_taught = implode(",", $_POST["subject_taught"]);
	$class_taught = implode(",", $_POST["class_taught"]);
	

	$query = '';
	
   $query = "INSERT INTO tbl_users(fullname, username, password, role, class_assigned, subject_taught, class_taught) VALUES(?, ?, ?, ?, ?, ?, ? )";
  	
  	$stmt = $conn->prepare($query);
    $stmt->bind_param("sssssss", $fullname, $username, $password, $role, $class_assigned, $subject_taught, $class_taught);

	$stmt->execute();

	if ($conn->query($query) === TRUE) {
		$success_msg = '<div class="alert alert-success">
		<button type="button" class="close" data-dismiss="alert">
		<i class="ace-icon fa fa-times"></i>
		</button>
		<h4><i class="ace-icon fa fa-check"> Success</i></h4>
		<p>Staff Information was successfully <span class="label label-warning">added.</span></p></div>';

	} else {
		$error_msg = '<div class="alert alert-danger">
		<button type="button" class="close" data-dismiss="alert">
		<i class="ace-icon fa 

Open in new window

0

Query Syntax

51K

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.