Solved

Just got my query working for one function. Now i need to get my other function working just like it

Posted on 2013-05-24
5
274 Views
Last Modified: 2013-05-28
Here is the query i have working on my search and populate function on my aspx webpage

declare @Seek as varchar(20)
set @seek = 'AR42154'

select
*
from amipartnumbers as APN 
left join (
            select *
            from JD
            cross apply (
                values 
                 (1, OEMPartNumer )
                ,(2, OEMSubNumber )
                ,(3, OEMSubNumber2)
                --,(4, OEMSubNumber3)
                --,(5, OEMSubNumber4)
              ) as xapply (lvl, OEMRef)
           ) JDT on APN.OEMItem = JDT.OEMRef
where OEMPartNumer = @Seek
or OEMSubNumber    = @seek
or OEMSubNumber2   = @seek
--or OEMSubNumber3   = @seek
--or OEMSubNumber4   = @seek

Open in new window


NOW, i need to get this working on my UPDATE query that imports users csv file and updates it with this query and returns it back to user.

This is the query i had before but it doesnt match all my numbers. only some of them

UPDATE imports 
SET imports.AMIPartNumber = 
coalesce(AMIA.Item,AMIB.Item,AMIC.Item,AMID.Item,AMIE.Item), 
Imports.AMIDescription = 
coalesce(AMIA.Description,AMIB.Description,AMIC.Description,AMID.Description,AMIE.Description) 
FROM imports as I 
LEFT JOIN JD as JDA 
ON I.OEMPartNumber = JDA.OEMPartNumber 
LEFT JOIN amipartnumbers as AMIA 
ON JDA.OEMPartNumber = AMIA.OEMItem 
LEFT JOIN JD as JDB 
ON I.OEMPartNumber = JDB.OEMSubNumber 
LEFT JOIN amipartnumbers as AMIB 
ON JDB.OEMSubNumber = AMIB.OEMItem 
LEFT JOIN JD as JDC 
ON I.OEMPartNumber = JDC.OEMSubNumber2 
LEFT JOIN amipartnumbers as AMIC 
ON JDB.OEMSubNumber2 = AMIC.OEMItem 
LEFT JOIN JD as JDD 
ON I.OEMPartNumber = JDD.OEMSubNumber3 
LEFT JOIN amipartnumbers as AMID 
ON JDB.OEMSubNumber2 = AMID.OEMItem 
LEFT JOIN JD as JDE 
ON I.OEMPartNumber = JDE.OEMSubNumber4 
LEFT JOIN amipartnumbers as AMIE 
ON JDB.OEMSubNumber2 = AMIE.OEMItem ;

select * from imports

Open in new window

0
Comment
Question by:bignadad
  • 3
  • 2
5 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39194279
as always, please try as a select before trying it as an update, may be quite wrong (I'm too tired really), but try it:
--UPDATE imports 
--SET
--  imports.AMIPartNumber = AMI.Item
--, Imports.AMIDescription = AMI.Description

select
  AMI.Item
, AMI.Description
, I.OEMPartNumber

FROM imports as I 
INNER JOIN (
            select
              JDT.OEMRef
            , APN.Item
            , APN.Description
            from amipartnumbers as APN 
            inner join (
                        select OEMref
                        from JD
                        cross apply (
                            values 
                             (1, OEMPartNumer )
                            ,(2, OEMSubNumber )
                            ,(3, OEMSubNumber2)
                            ,(4, OEMSubNumber3)
                            ,(5, OEMSubNumber4)
                          ) as xapply (lvl, OEMRef)
                       ) JDT on APN.OEMItem = JDT.OEMRef
            ) AMI on I.OEMPartNumber = AMI.OEMRef

Open in new window

0
 
LVL 2

Author Comment

by:bignadad
ID: 39194975
ive tried messing around with that query but i keep getting error on AMI.Item and AMI.Description in SET.

tried to change to table name and didnt work. and tried to change to APN and didnt work
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39196158
Hi. Could I suggest that instead of you needing to mess around with any query, that we all use the right tables and fields names? (and that the tables are accurately defined).

i.e. If I look back over the past couple of related questions I get confused about the table structures (and names)
e.g.
>>is it JD of JDSubs,
>>how many SubNumber[n] fields do really exist in JD/JDSubs
>>is there a field called "OEMPartNumer" or should it be "OEMPartNumBer"? :)

http://sqlfiddle.com/#!3/ff223/1 related to the earlier proposal for an update
http://sqlfiddle.com/#!3/8ac12/48 relates to the transposition (using cross apply)

There are differences in both of those for names/structures - it would help us, and especially you, if these discrepancies were removed. Once this is done any code proposed should not need much messing about with (I hope).

I'll have a crack at this using the latest sqlfiddle plus the import table, and get back with any progress (if any) - but it would be easier if we used the real definitions.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39196169
righty-oh, have something working, see http://sqlfiddle.com/#!3/76902/8
/* http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_28138157.html */
/* Just got my query working for one function. Now i need to get my other function working just like it */

/* at this stage the imports table has null in [AMIPartNumber] */
select
*
from imports
;
/*
   this displays the matches found by normalizing (transposing) table JD
   using cross apply
*/

select
*
from imports
left join (
            select *
            from JD
            cross apply (
                values 
                 (1, OEMPartNumer )
                ,(2, OEMSubNumber )
                ,(3, OEMSubNumber2)
              ) as xapply (lvl, OEMRef)
           ) JDT on imports.OEMPartNumber = JDT.OEMRef
;

/* so moving on from there we should be able to update table imports [AMIPartNumber] */

UPDATE imports
SET imports.AMIPartNumber =JDT.OEMPartNumer
from imports
left join (
            select *
            from JD
            cross apply (
                values 
                 (1, OEMPartNumer )
                ,(2, OEMSubNumber )
                ,(3, OEMSubNumber2)
              ) as xapply (lvl, OEMRef)
           ) JDT on imports.OEMPartNumber = JDT.OEMRef
;

/* and to show the effect of that update */
select
*
from imports
;

Open in new window

{edit, a correction}
0
 
LVL 2

Author Closing Comment

by:bignadad
ID: 39202285
Going to work on getting tables normalized.
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ASP.NET Content Page 3 29
Select question from MySQL 1 13
MS SQL Delete Duplicate Rows Only 2 18
How can I do Unit Testing with Session ? 1 11
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

838 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question