Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
283 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 49

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 49

Accepted Solution

by:
PortletPaul earned 2000 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 49

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

715 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