Hi,
What is the difference b/w Shape command and the Joins of tables..
Even with Shape we can relate two entity by specifying the "Relate" command
plz can u tell how they functionally differ..
thanks in aadv..
Venu
Hi all,I am trying to show the data as the shape query, where in the left part of the table is unique and the other right fields in the grid are many in number. That is a one to many relation ship. Can I use shape query using ADO.net or Oledb please tell me how? Or else if there is any other alternative please let me know.Thanks and regards.
View 3 Replies View Relatedi use shape command in vb6 success but when transfer to sql query analyzer the error come out "[Microsoft][ODBC SQL Server Driver]Syntax error or access violation"
can you help me to solve this problem
I need to all a user to create and store cross sections of some shapes. Some shapes can be represented by the following tables.
tbRectangular
Entry | SectionName | Width | Height
tbDoubleTee
Entry | SectionName | Width | Height | Flang | Legs
tbTSection
Entry | SectionName | X1 | Y1 | X2 | Y2
tbISection
Entry | SectionName | X1 | Y1 | X2 | Y2 | X3 | Y3
tbCircular
Entry | SectionName | Diamter
I have posted a similar question here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=33396.
Are there any thoughts on ways this should be done?
Mike B
Help please!
I got a problem in migrating ms access to sql server. In my vb code im using shape function and it doest work with sql server. Does sql server has shape function or are they have the same syntax? Any idea for these problem..??? Many thanks and God Blessed...
Thanks,
Jacsme
Hi,This is my basic sql shape query:------------------------------------------------------------SHAPE {select * from tbl1}APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TOfield)------------------------------------------------------------With this query i get a RecordSet (RS1), who handle all the recordsfrom table tbl1, and a secondary RecordSet (RS2) who handle all therecords from table tbl2, who applies to the criteria that field1=1.It is possible that RS2 will be empty (zero records) since there is norecord in tbl2 who applies to that criteria.My wish is to design a query, that will collect only the records fromtbl1, that will have records from tbl2 who applies to the criteria -that RS2 won't be empty !I want to influence on the main part of the query (RS1), through thecriteria that is being used in the secondery query (RS2).I hope that my question is clear enough. thanks !
View 3 Replies View RelatedHi I created a DMX query to retrieve predictions based on previous customer purchases and wanted to filter out my input data by only purchases made in the current year. I keep receiving this error:
Code Snippet
===================================
Internal error: An unexpected error occurred (file 'dmxinit.cpp', line 1343, function 'DMXNodeInput::InitFromASTOpenRowset'). (Microsoft SQL Server 2005 Analysis Services)
------------------------------
Program Location:
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.IExecuteProvider.Execute(ICommandContentProvider contentProvider, AdomdPropertyCollection commandProperties, IDataParameterCollection parameters)
at Microsoft.AnalysisServices.AdomdClient.AdomdCommand.Execute()
at Microsoft.AnalysisServices.Controls.QueryResultGridStorage.ThreadProc()
And, here's my query:
Code Snippet
SELECT FLATTENED
(SELECT *
FROM PredictAssociation([PredictTable],
10,
INCLUDE_NODE_ID,
INCLUDE_STATISTICS
)
WHERE $NODEID <> ''
)
FROM
[Mining Model]
NATURAL PREDICTION JOIN
SHAPE {
OPENQUERY( [datasrc],
'SELECT ''1234'' AS [Customer_D_SID]'
)
} APPEND ({
SHAPE {
OPENQUERY( [datasrc],
'SELECT [Product_D_SID],[Customer_D_SID], [Transaction_Date]
FROM [Base_Sales_F]
WHERE [Customer_D_SID] = ''1234'' '
)
} APPEND ({
OPENQUERY( [datasrc],
'SELECT [Calendar_D_SID],[CALENDAR_YR_NBR]
FROM [dbo].[Calendar_D]
WHERE [CALENDAR_YR_NBR] >= ''2007'' '
)
} RELATE [Calendar_D_SID] TO [Transaction_Date]) AS B
} RELATE B.[Customer_D_SID] TO [Customer_D_SID]) AS [PredictTable]
AS T
I figured the only way to associate the calendar table with the sales table was to use a nested shape statement... is this wrong? Thanks for any help!
Hi there,
I have a derived column shape in which I replace a column with an expression.
The expression is an IF statement - a true result sets a value for the column and a false result just uses the existing value of the column (ie it replaces it with itself)
Like this.
ColumnName DerivedColumn Expression
ColumnA Replace 'ColumnA' ColumnB != ColumnC ? "y" : ColumnA
But whenever, the condition is NOT met, ColumnA is set to NULL!!!!
Does this mean that the column value is deleted before the Expression is applied?
If this is how it is meant to work, then does anyone have a way of doing what I want to do without creating extra columns in the dataset??
Cheers.
I have a table with 257 mil records with latitude and longitude data.
My goal is to find the closest intersecting values from a locations table (88 rows) and update any of the 257 mil records that are applicable with the location_Name and Location_Group_Name.
The query I have works but doesn't perform well on such a big data set.
CREATE TABLE #Positions -- Base table 257 mil rows. Actual table has 20 columns
(
IDBigInt PRIMARY KEY,
LatitudeDec(10,6),
LongitudeDec(10,6),
[Code] ....
Attached you will find the tables, test data, a function to measure distance and some queries that work but are too slow for this much data.
Hi,I am running SQL Server 2000 SP3 on Windows Server 2003 and since recentlyhave a strange problem executing shape queries from COM+ components usingADO.Until 4 days ago, they worked, then from one moment to the next (I must havechanged something, but I have no clue what other than restoring a 1.2 GBdatabase) they started failing with this error:Microsoft OLE DB Provider for SQL Server error '80040e14'Syntax error or access violationI have no problem executing non-shape queries, it is just the shape queriesthat fail.Any clues what may have gone wrong? Or how I can fix it?Cheers,Rsa Myh
View 1 Replies View Related
Hi there,
I'm getting an error in ascript transform shape.
When I try to write a value to an output column I'm getting an error saying the object reference cannot be set.
So my code looks like this - Row.OutputColumn = True
Any ideas??
InfoPath 2003
I have an Infopath form to edit the values in the NotifyMembers table. I want to list the email addresses alphabetically when the query returns data to the form. Unfortunately, I use EmailAddressID as an int in the table, so I need to join? the NotifyMember table with the EMailAddress table, which has a column EMail which is the address. Both have EmailAddressID as common fields.
I can't get the Shape command (edit SQL in the Datasource) to be happy with any syntax that I have tried.
For (non-working) example:
select "GroupCode","EmailAddressID","DelBit" from "dbo"."NotifyMembers" as "NotifyMembers" Join "dbo"."NotifyMembers"."EmailAddressID" On "dbo"."EmailAddress"."EmailAddressID" = "dbo"."NotifyMembers"."EmailAddressID" order by "dbo"."EmailAddress"."Email"
Any help appreciated..
We find that a delete command on a table where the rows to be deleted involve an inner join between the table and a view formed with an outer join sometimes works, sometimes gives error 625.
If the delete is recoded to use the join key word instead of the = sign
then it alway gives error 4425.
625 21 0 Could not retrieve row from logical page %S_PGID by RID because the entry in the offset table (%d) for that RID (%d) is less than or equal to 0. 1033
4425 16 0 Cannot specify outer join operators in a query containing joined tables. View '%.*ls' contains outer join operators.
The delete with a correleted sub query instead of a join works.
Error 4425 text would imply that joins with view formed by outer joins should be avoided.
Any ideas on the principles involved here.
SQL Server 2000Howdy All.Is it going to be faster to join several tables together and thenselect what I need from the set or is it more efficient to select onlythose columns I need in each of the tables and then join them together?The joins are all Integer primary keys and the tables are all about thesame.I need the fastest most efficient method to extract the data as thisquery is one of the most used in the system.Thanks,Craig
View 3 Replies View RelatedHi,
Why is it that SQL joins (*=) run a little faster as opposed to ANSI joins(LEFT JOIN...)? Aren't they supposed to be almost identical?
The issue is this: we are promoting using ANSI syntax for the obvious reason (future versions of SQL Server may not support SQL Server syntax; portability, etc.)
However, the problem is the speed. What have others done about this? Do you use ANSI syntax or SQL syntax? HOw true is it that future SQL Server versions may discontinue support for the '*=" and "=*' join operators.
Angel
I have four tables which I want to return results for an advanced search function, the tables contain different data, but can be quite easily joined,
Table A Contains a Main Image, this image is displayed in the results
Table B Contains an Icon, this image is displayed in the results
Table C doesn't have an image in it but has a child table with a number of images associated to the table, in the UNION ALL statement I would Like to do a Join to get say the top Image from this child and print it for the row associated with table C.
Select title, description, image from tableA
UNION ALL
Select title, description, icon as image from tableB
UNION ALL
title, description, ( inner Join SELECT top(1)
from imageTableC where imagetableC.FK = tableC.PK)
as image from tableC
Could someone show me the syntax to do this, I have all the information printing to the screen, bar this table C image.
Hi All,
Im having a problem with a statement i cannot seem to get 2 left joins working at the same time 1 works fine but when i try the second join i get this error:-
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx'.
My SQL statment is as follows :-
SELECT children_tutorial.*,schools.schoolname,regions.rname FROM children_tutorial LEFT JOIN schools ON children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx
I am using an Access database i have tried all sorts to get it working and its driving me mad!! any help would be really appreciated.
When I add a constraint to the where part of the SQL statement below it will only show the nc_department.department where siteid equals it. How do I pull and display all of those departments regardless of where the siteid = the selected item? In other words doesnt the Left outer Join supposed to grab all contents from the left table regardless?
SQL = "SELECT nc_department.order_id, nc_department.department, Count(nonconformance.department_id) as 'events', ISNULL(SUM(nonconformance.nc_wafer_qty),0) as wafers FROM nc_department LEFT OUTER JOIN nonconformance ON nc_department.department_id = nonconformance.department_id WHERE nc_department.active = '1' AND nonconformance.site_id = '" & siteid.SelectedItem.Value & "' GROUP BY nc_department.department , nc_department.order_id"
thanks in advance
JOIN MUDDLE
Soory if this is a bit basic, but I'm chasing my tail:
I have two select statements:
SELECT MyRows from Table1 where X = 1
SELECT OtherRows From Table 2 Where y = 3
I want to produce a LEFT JOIN between the result of each query, and return the resultant rows
I know this is not correct syntax:
(SELECT MyRows from Table1 where X = 1)
LEFT JOIN
(SELECT OtherRows From Table 2 Where y = 3)
ON
Tabel1.Row1 = Table2.Row2
but I think it illustrates what I want to do
If I do the WHERE after the join I only get where there is a righthand table
I cant get the syntax right, an example with the correct grammar would be very much appreciated
I am trying to figure out which Option is the best/fastest and why?
I have simplified the query a lot. The actual query consists of lots of tables.
OPTION A (the search for the specific id is in the JOIN)
select * from table A left join table B on a.id = b.id and a.id in (1,2,3)
OR
OPTION B(the search for specific id is in the WHERE clause)
select * from table A left join table B on a.id = b.id
where a.id in (1,2,3)
Could somebody please tell me why we should never mix old-style and ANSI-style joins in the same query? What would be the consiquences?
Thank you
Instead of inserting into, how can I use a join of multiple
tables to update a table.
This is the join that inserts.
INSERT INTO [table] (MyID, material, dollars)
SELECT l.MyID, material, dollars
FROM Tab_Client_Input l left outer JOIN
Tab_Special_tox r ON l.MyID = r.MyID
Thanks in advance
can somebody give a good explanation of uisng a self join with an example
that would really help
thks
Hi Folks,
I writing a store procedure, the first three parts work pretty well. The last select statement has about 8 outer joins in it. every time I run the store procedure, I get an error message for the last part. Below are the error message and the store procedure:
Store Procedure:
--Create Procedure dbo.IMS_Donation
--AS
Select Distinct D_VST_ID as 'DRWLOC_ID', D_VST_INSTID as 'DRWLOC_INSTID'
Into Donor_Visit1
From DNR_VST_DB_REC
Where D_VST_DATE Between 20010101 AND 20040512
AND D_VST_DONTYP in ('AP', 'WB', 'RP', 'E2', 'E1')
AND D_VST_STATUS = 'DN'
ORDER BY D_VST_ID
GO
SELECT DRWLOC_ID as 'COUNT_ID', DRWLOC_INSTID as 'COUNT_INSTID',
count(*) as 'COUNT_VISITS'
INTO Donor_Visit2
FROM DNR_VST_DB_REC, Donor_Visit1
Where D_VST_ID = DRWLOC_ID
AND NOT EXISTS (Select R_DCC_ID
From REC_DCC_DB_REC
Where R_DCC_ID = DRWLOC_ID
AND R_DCC_INSTID = DRWLOC_INSTID
AND R_DCC_CALLCD = 'DC')
GROUP BY DRWLOC_ID, DRWLOC_INSTID
GO
SELECT DVT1.DRWLOC_ID as'COMP_ID', CMP.l_CMP_UNITNO as 'COMP_UNITID',
CMP.L_CMP_INSTID as 'COMP_INSTID', count(*) as 'COMP_COMPTOT'
INTO Donor_Visit3
FROM LAB_CMP_DB_REC CMP, Donor_Visit1 DVT1, DNR_VST_DB_REC VST, CMP_VST_Jct CVT
WHERE CMP.L_CMP_INSTID = DVT1.DRWLOC_INSTID
AND VST.D_VST_ID = DVT1.DRWLOC_ID
AND VST.D_VST_UNITNO = CVT.L_CMP_UNITNO
AND CMP.L_CMP_UNITNO = CVT.L_CMP_UNITNO
AND CMP.L_CMP_STATCD != 'MOD'
AND CMP.L_CMP_CMPCD NOT IN ('INC', 'EMTY')
AND VST.D_VST_DATE BETWEEN 20010101 AND 20040512
AND VST.D_VST_STATUS = 'DN'
GROUP BY DVT1.DRWLOC_ID, CMP.L_CMP_UNITNO, CMP.L_CMP_INSTID
GO
SELECT DISTINCT
NAM.N_NAM_ID AS 'ID1',
NAM.N_NAM_INSTID AS 'INSTID1',
NAM.N_NAM_FNAME AS 'FNAME1',
NAM.N_NAM_MINITIAL AS 'MINITIAL1',
NAM.N_NAM_LNAME AS 'LNAME1',
PER.N_PER_BIRTH AS 'BIRTH1',
ADR.N_ADR_ADDR1 AS 'ADDR1',
ADR.N_ADR_ADDR2 AS 'ADDR2',
ADR.N_ADR_CITY AS 'CITY1',
ADR.N_ADR_STATE AS 'STATE1',
SUBSTRING(ADR.N_ADR_ZIP, 1,5) AS 'ZIP1',
PER.N_PER_EMAIL AS 'EMAIL1',
PER.N_PER_GENDER AS 'GENDER1',
PHNA.N_PHN_AREACD AS 'AREAD1',
PHNA.N_PHN_PREFIX AS 'PREFIXD1',
PHNA.N_PHN_NUMBER AS 'NBRD1',
PHNA.N_PHN_EXTENTN AS 'EXTD1',
PHNB.N_PHN_AREACD AS 'AREAD2',
PHNB.N_PHN_PREFIX AS 'PREFIXD2',
PHNB.N_PHN_NUMBER AS 'NBRE2',
PHNB.N_PHN_EXTENTN AS 'EXTD2',
BTY.D_BTY_ABO AS 'ABO1',
BTY.D_BTY_RHESUS AS 'RHI',
VST.D_VST_DATE AS 'FIRST1',
DV2.COUNT_VISITS AS 'COUNT',
SUM(DTS.D_DTS_DONSUM) AS 'AWARD',
ELG.D_ELG_RWBDTE AS 'ELIG1'
--INTO Donor_Visit4
From Donor_Visit2 DV2
RIGHT OUTER JOIN DNR_DTS_DB_REC DTS
ON DV2.COUNT_INSTID = DTS.D_DTS_INSTID
RIGHT OUTER JOIN NAT_PER_DB_REC PER
ON DV2.COUNT_INSTID = PER.N_PER_INSTID
RIGHT OUTER JOIN DNR_BTY_DB_REC BTY
ON DV2.COUNT_INSTID = BTY.D_BTY_INSTID
RIGHT OUTER JOIN NAT_PHN_DB_REC PHNA
ON DV2.COUNT_INSTID = PHNA.N_PHN_INSTID
RIGHT OUTER JOIN NAT_PHN_DB_REC PHNB
ON DV2.COUNT_INSTID = PHNB.N_PHN_INSTID
RIGHT OUTER JOIN DNR_DTS_DB_REC DNT
ON DV2.COUNT_ID = DNT.D_DTS_ID
RIGHT OUTER JOIN NAT_PER_DB_REC PER1
ON DV2.COUNT_ID = PER1.N_PER_ID
RIGHT OUTER JOIN DNR_BTY_DB_REC BTY1
ON DV2.COUNT_ID = BTY1.D_BTY_ID
LEFT OUTER JOIN NAT_PHN_DB_REC PHNA1
ON DV2.COUNT_ID = PHNA1.N_PHN_ID
RIGHT OUTER JOIN NAT_PHN_DB_REC PHNB1
ON DV2.COUNT_ID = PHNB1.N_PHN_ID
LEFT OUTER JOIN NAT_PHN_DB_REC PHNA2
ON PHNA2.N_PHN_PHTYP = 'D'
LEFT OUTER JOIN NAT_PHN_DB_REC PHNB2
ON PHNB2.N_PHN_PHTYP = 'E',
--LEFT OUTER JOIN DNR_DTS_DB_REC DTS1
--DTS1.D_DTS_CNTTYP <> 'N',
DNR_ELG_DB_REC ELG, NAT_NAM_DB_REC NAM, NAT_ADR_DB_REC ADR, DNR_VST_DB_REC VST
WHERE DV2.COUNT_INSTID = VST.D_VST_INSTID
AND DV2.COUNT_INSTID = ELG.D_ELG_INSTID
AND DV2.COUNT_INSTID = N_NAM_INSTID
AND DV2.COUNT_INSTID = N_ADR_INSTID
AND DV2.COUNT_INSTID = VST.D_VST_INSTID
--AND DV2.COUNT_INSTID = ELG.D_ELG_ID
AND NAM.N_NAM_SEQNO = 0
AND VST.D_VST_DATE = (SELECT MIN(VSTB.D_VST_DATE)
FROM DNR_VST_DB_REC VSTB
WHERE VST.D_VST_INSTID = VSTB.D_VST_INSTID
AND VSTB.D_VST_STATUS = 'DN'
AND VST.D_VST_ID = VSTB.D_VST_ID)
AND NOT EXISTS (SELECT R_DRC_ID
FROM REC_DRC_DB_REC
WHERE R_DRC_ID = COUNT_ID
AND R_DRC_INSTID = COUNT_INSTID
AND R_DRC_RESPCD = '15')
GROUP BY
NAM.N_NAM_ID,
NAM.N_NAM_INSTID,
NAM.N_NAM_FNAME,
NAM.N_NAM_MINITIAL,
NAM.N_NAM_LNAME,
PER.N_PER_BIRTH,
ADR.N_ADR_ADDR1,
ADR.N_ADR_ADDR2,
ADR.N_ADR_CITY,
ADR.N_ADR_STATE,
ADR.N_ADR_ZIP,
PER.N_PER_EMAIL,
PER.N_PER_GENDER,
PHNA.N_PHN_AREACD,
PHNA.N_PHN_PREFIX,
PHNA.N_PHN_NUMBER,
PHNA.N_PHN_EXTENTN,
PHNB.N_PHN_AREACD,
PHNB.N_PHN_PREFIX,
PHNB.N_PHN_NUMBER,
PHNB.N_PHN_EXTENTN,
BTY.D_BTY_ABO,
BTY.D_BTY_RHESUS,
VST.D_VST_DATE,
DV2.COUNT_VISITS,
DTS.D_DTS_DONSUM,
ELG.D_ELG_RWBDTE
Error Message:
(845 row(s) affected)
(844 row(s) affected)
(396 row(s) affected)
Server: Msg 9002, Level 17, State 6, Line 2
The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space.
Server: Msg 1105, Level 17, State 1, Line 2
Could not allocate space for object '(SYSTEM table id: -109901351)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full.
Microsoft SQL Server 2000:
I am not a SQL programmer, but I am trying to use data collected in our Job Costing system to feed data to a crystal report that summarizes the current department name and the last date each job was scanned into a department.
I used aliases of the Process table to extract the max date for each department.
This statement works fine, but sometimes it hangs and locks my process table. I am not sure what event causes the lock, but I think it has to do with the users aborting the report during the SQL extract or multiple users trying to report at the same time (8am when they first arrive at work).
I showed this to a consultant and he showed me that the MAX statement in the 3rd to last join was eating most of the execution time. Also he told me I am using too many joins and should look into Table Views. The statement completes in 15 to 45 seconds, depending on the workload.
What can I do to improve the performance of this code and to avoid the locks?
SELECT
OH.JobNumber,
OH.PlantID,
OH.CreateOpr,
OC.ComponentNumber,
PJN.ProductionCode as ProductionMax,
P2.ProcessCode as MaxCC,
PS.Description AS MaxCCDesc,
PJ1.ProductionCode,
P.ProcessCode as ProdCC,
P51.CreateDatim AS SchCCDate,
P53.CreateDatim AS TypCCDate,
P55.CreateDatim AS OPrCCDate,
P57.CreateDatim AS HPrCCDate,
P59.CreateDatim AS CRmCCDate,
P61.CreateDatim AS CCeCCDate,
P63.CreateDatim AS PRmCCDate,
P65.CreateDatim AS BinCCDate,
P67.CreateDatim AS JbOCCDate,
P69.CreateDatim AS OnDCCDate,
P71.CreateDatim AS ShpCCDate,
PS1.Description AS CCDesc,
P.CreateDatim AS CCDate,
OQT.Quantity,
OH.JobDescription,
OH.FormNumber,
OH.JobDescription,
OH.USERDEFINED1 AS JobType,
OH.CustAccount,
OH.CustName,
(select OrderHeader.DueDate from OrderHeader where OrderHeader.JobNumber = OH.JobNumber and OH.NoDueDate = 0) as DueDate,
OH.ProofDate,
OH.OrderDate,
OH.SalesRepCode,
OH.PONumber,
OH.PrevPONumber,
OH.NoDueDate,
OC.UserDefined1,
OC.Description as ComponentDescription
FROM OrderComponent OC
INNER JOIN OrderHeader OH ON OC.JobNumber = OH.JobNumber
INNER JOIN OrderQtyTable OQT ON OC.JobNumber = OQT.JobNumber
and OC.ComponentNumber = OQT.ComponentNumber
and OC.QtyOrdIndex = OQT.QuantityLineNo
LEFT JOIN ProductionJobNumber PJ1 ON PJ1.JobNumber = OH.JobNumber and PJ1.ComponentNumber = OC.ComponentNumber
LEFT JOIN Production P ON PJ1.ProductionCode = P.Code
LEFT JOIN Production P51 ON PJ1.ProductionCode = P51.Code AND P51.ProcessCode = 9151
LEFT JOIN Production P53 ON PJ1.ProductionCode = P53.Code AND P53.ProcessCode = 9153
LEFT JOIN Production P55 ON PJ1.ProductionCode = P55.Code AND P55.ProcessCode = 9155
LEFT JOIN Production P57 ON PJ1.ProductionCode = P57.Code AND P57.ProcessCode = 9157
LEFT JOIN Production P59 ON PJ1.ProductionCode = P59.Code AND P59.ProcessCode = 9159
LEFT JOIN Production P61 ON PJ1.ProductionCode = P61.Code AND P61.ProcessCode = 9161
LEFT JOIN Production P63 ON PJ1.ProductionCode = P63.Code AND P63.ProcessCode = 9163
LEFT JOIN Production P65 ON PJ1.ProductionCode = P65.Code AND P65.ProcessCode = 9165
LEFT JOIN Production P67 ON PJ1.ProductionCode = P67.Code AND P67.ProcessCode = 9167
LEFT JOIN Production P69 ON PJ1.ProductionCode = P69.Code AND P69.ProcessCode = 9169
LEFT JOIN Production P71 ON PJ1.ProductionCode = P71.Code AND P71.ProcessCode = 9171
LEFT JOIN Process PS1 ON P.ProcessCode = PS1.ProcessCode
LEFT JOIN ProductionJobNumber PJN ON PJN.ProductionCode =
(select MAX(ProductionJobNumber.ProductionCode)
From ProductionJobNumber
where OH.Jobnumber = ProductionJobNumber.JobNumber
AND OC.ComponentNumber = ProductionJobNumber.ComponentNumber)
LEFT JOIN Production P2 ON PJN.ProductionCode = P2.Code
LEFT JOIN Process PS ON P2.ProcessCode = PS.ProcessCode
WHERE OH.JobStatus = 'IN PROCESS'
i have two tables each table having 2 column
table1 table2
eid ename eid ename
1 A 3 C
2 B 4 D
i shld use a select query to get the following output
eid1 eid2
1 3
2 4
there is no relation btw the 2 tables except that eid column of both tables r of same data type
the count of eid column in both table are also same
can a blind join be done
SQL Novice
What i want to happen is Table_A will hold a recid and an update field. within the if statement it needs to JOIN with Table_B ON recid and check the value of one/two possible fields to determine if that recid goes to Table_F or Table_G
I need to get this INNER JOIN embedded in a nested if along with more INNER JOINs
INSERT BACKFILE_AP
SELECT WORK_INTRO.RECID, WORK_INTRO.UPDATED
FROM WORK_INTRO INNER JOIN SIF_DT1
ON WORK_INTRO.RECID = SIF_DT1.RECID
WHERE SIF_DT1.ROUTE_CODE = 'BACKFILE'
DELETE WORK_INTRO
FROM WORK_INTRO INNER JOIN SIF_DT1
ON WORK_INTRO.RECID = SIF_DT1.RECID
WHERE SIF_DT1.ROUTE_CODE = 'BACKFILE'
Heya -
Very new here to SQL, but when I do this following query
SELECT ProjMgr, SUM(Fee) AS Fee
FROM dbo.PR
WHERE (ProjMgr = '00138')
GROUP BY ProjMgr
I return these results:
ProjMgrFee
001389145297
Simple enough, so I flex my newbie SQL muscles and put in the last name instead of the ProjectManager's employee number
SELECT dbo.EM.LastName, SUM(dbo.PR.Fee) AS Fee
FROM dbo.PR INNER JOIN
dbo.EM ON dbo.PR.ProjMgr = dbo.EM.Employee
WHERE (dbo.PR.ProjMgr = '00138')
GROUP BY dbo.EM.LastName
And get these results
LastNameFee
Boulet9145297
Okay, NOW I'm getting cocky. I try to bring in an amount indicating held labor from another table that will have multiple instances of the same number, called WBS1, and show a sum of values contained over months of history in a sum. Before I do that, I add the table, and then I'm stuck - look what happens to my fee column...any ideas?
SELECT dbo.EM.LastName, SUM(dbo.PR.Fee) AS Fee
FROM dbo.PR INNER JOIN
dbo.EM ON dbo.PR.ProjMgr = dbo.EM.Employee INNER JOIN
dbo.LD ON dbo.PR.WBS1 = dbo.LD.WBS1
WHERE (dbo.PR.ProjMgr = '00138')
GROUP BY dbo.EM.LastName
RESULT
LastNameFee
Boulet5371502314
Hi All,
I had a query like
Select * from Fact1
UNION
Select * from Fact2
JOIN
(Select top(col1),... from Fact3
INNER JOIN Table1
group by Col2
) ON....
The above one was executing in 5 minutes
I changed the inner query to say
Select Quarter,top(col1),... from Fact3
INNER JOIN View1
group by Quarter,Col2
I have made sure that the inner query is returning the same set of rows before and after modification. But still the modified query runs for ever. I am refering 2 databases(both SQL Server 2000 databases, on the same server- including the current). Please also note that I had a inner join to a table which I have changed to a view in the inner query.
I suppose this is due to the change in the execution plan. What should I do to make this work?
Any ideas?
Prakash.P
The secret to creativity is knowing how to hide your sources!
I am very new to SQL and I have a question about inner joins. By using a lookup table and a row function, I have the following question and code. I am not sure if it right or not. I would appreciate any assistance:
For every type of food in the l_foods table, list the following:
The full name of the supplier from the l_suppliers table. The description of food from the l_foods table The price from the l_foods table plus the price_increase from the l_foods table; rename it to total_price
When price increase is null, assume that the 10 cents will be added to the price. Sort this information ty the total price.
SELECT a.supplier_name,
b.description,
b.price_increase AS total_price
FROM l_suppliers AS a, l_foods AS b;
Orange- l_foods table
Blue - l_supplier table
Red - within both tables.
That is far as I got without error.
Select * from
table1
left join
table2
on table.id=table2.fid
where
table2.column3 >5
is there any significance in the left join
or inner join is enough
Hi all,
What is happening internally when a join is performed ?.
Why there are two outer joins as both of them performing the
same opertion?.Use left join key word and swap the tables , its will
perform a right join .Is that correct?
Thanks in advance
Hi all,
What is happening internally when a join is performed ?.
Why there are two outer joins as both of them performing the
same opertion?.Use left join key word and swap the tables , its will
perform a right join .Is that correct?
Thanks in advance