Mdx Query Dim?split?left?

How To Split The Field In The Sql Query?
      I have the field LocationID (string)which has values  like
          "AZ001","AZ002","IN002","IN004"  first 2 will be always alphabets and remaining 3 will be numbers,
I want to split it  like "AZ" ," 001"
                              "AZ","002"
                              "IN" "002"
                              "IN" "004"
now i will populate dropdownlist with  unique values of "AZ" "IN"
according to  first dropdownlist how i will populate second dropdownlist?
So how to write sql query for splitting? and then populating the dropdownlist ?
 
 
 
 

View Replies !

---

Conditional Split Query
 
Hi, 
I have the following table in MsAccess

EmployeesA
empId integer,
empName varchar(60),
empAge integer,
empStatus char(1)  - can be N,D or S - New, Deleted or Shifted
 

and the following in Sql2005
 
EmployeesB
Id smallint,
Name varchar(60),
Age int,
Status char(1) - Bydefault 'N'
 
I have written a Foreach File package that populates the sql server tables (EmployeesB) from Access(EmployeesA). However i want to check for a condition now.
 
If empStatus = N in EmployeesA, then insert a new record in EmployeesB
If empStatus = D in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status field in EmployeesB as 'D'
If empStatus = S in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status as 'S' in EmployeesB and insert a new row.
 
How do I do it for each table each row in EmployeesA using a foreach file loop?
 
Thanks,
ron

View Replies !

---

Slow Query On Primary Key Using LEFT
My table consists of about 1.4 Million Records. The PK is a CHAR field and ranges in size from 3 - 25 characters. I need to pull a recordset using the LEFT function.

Example: SELECT blah WHERE LEFT(myPK, 8) = 'AIRBILLNU'

This query takes about 115927ms to run and the server is 100% CPU bound, it should only bring up 2 records. Seems like the index is not being used. I know the DB design is probably not the greatest, we probably should have had an INT PK and IX on the other field which is now the PK. I cannot do anything about that at this point.

Is there anything I can do to speed up this query.?

Thanks,Adrian

View Replies !

---

Left Outer Join Query?
Hello all,

I am stuck in a bit of a mess, and i really don't know how to get out of it.

I am a dot.net developer, working with MS SQL server 2000.

I am trying to write a query which essentially gives the amount of stock available for a particular stock_code.

select Stock_code, description from stock

Now what i want to do is, for each one of the stock_code that apears in the table above i want to look into another table called pop_item, and get the closest delivery_date for that particular stock_code. So i have top 1  and order it by delivery_date desc.

Individually for a particular stock_code, i can do this no problem. And here is my code for stock_code='E0016/6'

select top 1
 stock_code, delivery_date, order_number,qty_order-qty_delivered as onorder
from pop_item
where
stock_code='E0016/6' and
qty_order>qty_delivered
order by delivery_date desc

But I can't seem to be able to do this for all the stock_code, and not a specific one, cause even though i try and left outer join it, i can't access the outer stock_code from the first query into the next...

i.e

select stock.Stock_code, description, tempp.stock_code, tempp.delivery_date, tempp.onorder from stock

left outer join

(select top 1
 stock_code, delivery_date, order_number,qty_order-qty_delivered as onorder
from pop_item
where

--Can't say this(stock_code= stock.stock_code and )
qty_order>qty_delivered
order by delivery_date desc) as tempp

on tempp.stock_code=stock.stock_code

Now my question is, is there anyway to access stock.stock_code within the second query? Casue the whole query on top returns only one value for delivery_date, only of the highest delivery date in the whole of pop_item. which make sense... but i don;t know how to get around this...

OOOOOOOOOOOOOOOOOOhhhhhhhhhhhhhhhhhhhhhhhhhhhhh!

Hope someone can help me.

Regards,

Munira

ps- should i be using a cursor, can i call cursors from asp.net. every where i read about cursors they adivice us not to use them.

 

View Replies !

---

Query With A Lot Of Left Joins - Rewrite?
Hello,
 
I have a query with 11 left joins.  Some hits against tables with small amounts of reference data, whereas others are not so small.  Should I rewrite this in another way, as performance is a requirement on this one?  Or, should I do it another way?
 
How would I rewrite left joins?  Any examples?

 
Thanks.

View Replies !

---

Left Join Help (Probably) - Query Merging Anyway
I have this database running (ignore that the ERD below was done in Access, this is being made in Microsoft SQL Server 2005).


What I need to do is if you look at the users table and the orderContents table I need to make a query that:

Collects the users' names and any products they have bought (preferably shown by title not ID) as well as still showing the users that have not ordered any products.

I get the feeling there's a left join involved, but can't quite see how to do it.


Thank you in advanced for any help.

View Replies !

---

Query Split Column In 2 Columns In SQL
I like to push 1 column into 2 different columns just to show it on the screen. So no import in another table ore something like that.
I have a table like this:
Select Name from Cars;
Result:
Col1
BMWMercedesFordAudi
But i like to make a query so it is displayed like this:
Col1                Col2
BMW               FordMercedes         Audi
So i can bound a table directly to that column!Is this possible with SQL, and how can i build it.Thanks.

View Replies !

---

Query To Split A Database Column ?
 

How can i write a query to split a database column and shows 2 new columns.  In my database column
I have 2 mixing items and need to split out to 2 columns.  Normally I have to write a query and change parameter
and run another query. 
For example a database column with average number and range number. 
Thanks
Daniel
 

View Replies !

---

Intermittently Slow Query - Left Join
Here's a little background on the query. I have a list of documents by an id number in one table and the description of the sheets in another table. It's a one to many relationship, so for each description, there may be multiple entries in the documents table that it applies to. For example:


Descriptions table:

ID | Title
Doc1 | Document 1
Doc2 | Document 2


Documents table:

ID | Parent
Doc1 | 10400
Doc2 | 10400
Doc1 | 20189
Doc3 | 20189

View:

ID | Parent | Description
Doc1 | 10400 | Document 1
Doc2 | 10400 | Document 2
Doc1 | 20189 | Document 1
Doc3 | 20189 | (null)


So the query I am using uses a left join to combine the data from the one table into the other. There might not be an entry for the description, so for some Document entries, the description field may be blank. For some reason, certain queries take about 2 minutes longer than others who retreive 5 times the information.

In SQL Manager, is says "Executing Query. Waiting for response from data source." After about 20 seconds it says "Retrieving Data..." then about a minute later, it finally comes up with the data. I can select another parent that has a lot more items and it comes up in about 3 seconds max.

It's running on SQL Server 2005 with 2GB of RAM.

Any suggestions on tracking down the reason for the slowness would be great.

Thanks in advance!!!

-Dan

View Replies !

---

Two Left Outer Join In A Single Query
How to use two left outer join in a single query?

I was trying to run the follwoing query.It is giving me error

"select woancestor.wonum,wplabor.laborcode, wplabor.quantity,wplabor.laborhrs
from wplabor,workorder left outer join woancestor on workorder.wonum=woancestor.wonum
and wplabor left outer join labtrans on wplabor.laborcode=labtrans.laborcode
where woancestor.ancestor='572099' and workorder.istask=1
and workorder.wonum=wplabor.wonum"

Error is "Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'left'."

But the following query works fine

"select woancestor.wonum,wplabor.laborcode, wplabor.quantity,wplabor.laborhrs
from wplabor,workorder left outer join woancestor on workorder.wonum=woancestor.wonum
where woancestor.ancestor='572099' and workorder.istask=1
and workorder.wonum=wplabor.wonum"

please help me

View Replies !

---

Left Join Insert Query Error
I have been working on this for a little bit and have gotten to this point. Below is the query in blue, the error in red. Now, from what I gather the error is telling me I can't insert a duplicate product code into tblProduct. Isn't that what the left join is exactly not doing, as in, the left join is inserting all records from Complete_records into tblProduct where the code is null(does not exist) in tblProduct? Or, is this an issue where the identity number, productID in tblProduct, isn't starting at the next number in turn?

Thanks.

Set Identity_Insert tblProduct on
DECLARE @MaxId int

SELECT @MaxID=MAX(productID)
FROM tblProduct

SELECT IDENTITY(int,1,1) AS ID,Complete_products.APNum, Complete_products.Title, Complete_products.CategoryID, Complete_products.Mountable, Complete_products.price,
Complete_products.Height, Complete_products.Width, Complete_products.IRank, Complete_products.frameable, Complete_products.Typ INTO #Temp
FROM Complete_products LEFT OUTER JOIN
tblProduct AS tblProduct_1 ON Complete_products.APNum = tblProduct_1.productCode
where tblProduct_1.productCode IS NULL

INSERT INTO tblProduct
(productID,productCode, productName, productNavID, CanBeMounted, productRetailPrice, productHeight, productWidth, Rank, CanBeFramed, ProductType)
SELECT @MaxID + ID, APNum, Title, CategoryID, Mountable, price,
Height, Width, IRank, frameable, Typ
FROM #Temp

(236752 row(s) affected)
Msg 2601, Level 14, State 1, Line 13
Cannot insert duplicate key row in object 'dbo.tblProduct' with unique index 'IX_tblProductt_productCode'.
The statement has been terminated.

View Replies !

---

Split Fields And Display Query Result
Hi,
I'm having a problem in spliting the fields
I need to ru the following query to join two tables and getting the output as shown.

Query:
select cusl.user_name,
pmts.bill_ref_info, pmts.payee_acid, pmts.cust_acid, pmts.txn_amt,pmts.pmt_id
from cusl, pmts
where cusl.ubp_user_id = pmts.ubp_user_id and pmts.ubp_user_id= 'testinglive'

Output:
user_name bills_ref_info payee_acid cust_acid txn_amt
SAMEER ALLA0210181#123456#Amita 378902010021095 383702070051411 1.000 16318
SAMEER BARB0GNFCOM#6788990#Vikram Kalsan 378902010021095 383702070051411 1.000 16327
SAMEER BKID0000200#378902010099678#Vikram 378902010021095 383702070051411 1.000 14031
SAMEER undefined#123456789123456#Vikram 378902010021095 383702070051411 1.000 13918


Now I need to display the second field which is a #-separated field as individual fields alongwith tghe other fields that are shown on execution of the query.
Can this be done? Please guide me on this...

View Replies !

---

Query Optimizer Problems With Many LEFT/RIGHT OUTER JOINs
I am having an issue with large queries using Microsoft SQL Server 2005 - 9.00.2221.00 (X64).

I have a query with many INNER/LEFT OUTER/RIGHT OUTER joins which is taking very very very long to run.  This looks exactly like this problem described in  http://support.microsoft.com/kb/318530. However, this doc says it was fixed in SP1, which is already installed.


Basically I have a query:

SELECT ....
FROM TABLEA

INNER JOIN TABLEB ...
LEFT OUTER TABLEC...
LEFT OUTER TABLED...
RIGHT OUTER TABLEF...
LEFT OUTER TABLEJ..
LEFT OUTER TABLEH...
LEFT OUTER TABLEI...
RIGHT OUTER TABLEK...
LEFT OUTER TABLEM..
... 17 joined tables in all......
WHERE TABLEB.field1 = 'abc'


The query plan for this is using TABLEA as the "main" table and joining everything else to it.  The problem is, TABLEA has 117 MILLION records.  TABLEB has 10,000 records which match the WHERE.    I stopped this query after it ran for 62 HOURS.

If I simply change the query to:

SELECT ....
FROM TABLEB

INNER JOIN TABLEA ...
LEFT OUTER TABLEC...
LEFT OUTER TABLED...
RIGHT OUTER TABLEF...
LEFT OUTER TABLEJ..
LEFT OUTER TABLEH...
LEFT OUTER TABLEI...
RIGHT OUTER TABLEK...
LEFT OUTER TABLEM..
... 17 joined tables in all......
WHERE TABLEB.field1 = 'abc'

The query runs in 15 mins.  The query plan now uses TABLEB and the WHERE clause to join all the other tables.

The problem is, this query is generated from a report writter, and I have no control over the way it creates the SQL code. 

Any ideas how I can fix this?

View Replies !

---

Insert Query Fails (if Form Fields Left Empty)
Dear All,
I have created a table in my SQL server database, the problem i am facing is my insert query fails if i leave any form field empty (leave it blank). On my back-end table, only one field is mandatory, and others have been set with the constraint "allow null".
As per our business requirement, except one value is complusory while others are optional. If I enter all values in the form it works perfectly fine. Can you see in the code below - where am i possibly going wrong ?
<script language="VB" runat="server" >      Sub Page_Load(Src As Object, e As EventArgs)                      If Page.IsPostBack Then                        Dim ConLath As SqlConnection            Dim comLath As SqlCommand            Dim insertcmd                        conLath = New SqlConnection("Data Source=SQLas;Initial Catalog=settle;User ID=sa;Password=password")            ConLath.Open()            insertcmd = "Insert into His_set values (@t_d,@s_p,@p_s,@v_oq,@i_oq,@v_qn,@i_qn,@v_qw,@i_qw)"                        comLath = New SqlCommand(insertcmd, ConLath)                                    comLath.Parameters.Add(New SqlParameter("@t_d", SqlDbType.DateTime, 12))            comLath.Parameters("@t_d").Value = trade_date.Text            comLath.Parameters.Add(New SqlParameter("@s_p", SqlDbType.Decimal, 8))            comLath.Parameters("@s_p").Value = sett_price.Text            comLath.Parameters.Add(New SqlParameter("@p_s", SqlDbType.Decimal, 8))            comLath.Parameters("@p_s").Value = post_close.Text            comLath.Parameters.Add(New SqlParameter("@v_oq", SqlDbType.Int, 8))            comLath.Parameters("@v_oq").Value = vol_oq.Text            comLath.Parameters.Add(New SqlParameter("@i_oq", SqlDbType.Int, 8))            comLath.Parameters("@i_oq").Value = oi_oq.Text            comLath.Parameters.Add(New SqlParameter("@v_qn", SqlDbType.Int, 8))            comLath.Parameters("@v_qn").Value = vol_qn.Text            comLath.Parameters.Add(New SqlParameter("@v_qw", SqlDbType.Int, 8))            comLath.Parameters("@v_qw").Value = vol_qw.Text            comLath.Parameters.Add(New SqlParameter("@i_qn", SqlDbType.Int, 8))            comLath.Parameters("@i_qn").Value = oi_qn.Text            comLath.Parameters.Add(New SqlParameter("@i_qw", SqlDbType.Int, 8))            comLath.Parameters("@i_qw").Value = oi_qw.Text
                        Try                comLath.ExecuteNonQuery()                            Catch ex As SqlException                If ex.Number = 2627 Then                    Message.InnerHtml = "ERROR: A record already exists with " _                       & "the same primary key"                Else                    Message.InnerHtml = "ERROR: Could not add record, please " _                       & "ensure the fields are correctly filled out"                    Message.Style("color") = "red"                End If            End Try
            comLath.Dispose()            ConLath.Close()                                                        End If   End Sub
</script>
 

View Replies !

---

Left Join Vs Left Outer Join Syntax Generates Different Execution Plans
 

Anyone know why using

SELECT *
FROM a LEFT OUTER JOIN b
   ON a.id = b.id
instead of

    SELECT *
    FROM a LEFT JOIN b
       ON a.id = b.id
 
generates a different execution plan?
 
My query is more complex, but when I change "LEFT OUTER JOIN" to "LEFT JOIN" I get a different execution plan, which is absolutely baffling me! Especially considering everything I know and was able to research essentially said the "OUTER" is implied in "LEFT JOIN".
 
Any enlightenment is very appreciated.
 
Thanks
 

View Replies !

---

Values In LEFT JOIN Need To Be Empty Strings (was &"SQL Query Question&")
Hello,

I have a SQL database where I am attempting to perform a complicated query that I cannot seem to figure out. I am using SQL Server.

I have 4 tables (TableA, TableB, TableC, and TableD). TableA and TableB are guaranteed to have a relationship.

TableC and TableD are guaranteed to have a relationship.

The trick is, I need to link between TableA and TableC essentially using a LEFT JOIN. I need to retrieve all of the values from TableA regardless and the information from TableC and TableD if there is a link, if there isn't a link, then the values from TableC and TableD need to be empty strings.

Does anyone know how I can do this? I've been trying for the last 5 hours without any luck. I feel I'm close, but there is something I feel I'm overlooking.

Thank you SO much for your help!

View Replies !

---

Select Where Left In Left
hello

for MS SQL 2000, i cannot get it

i am having names like AB_12 I want to get all rows with left part similar , AB im that case


SELECT id, name
FROM Users
WHERE LEFT(name, CHARINDEX('_', name) - 1) AS name IN
(
SELECT LEFT(name, CHARINDEX('_', name) - 1) AS ns
FROM Users
GROUP BY LEFT(name, CHARINDEX('_', name) - 1)
HAVING (COUNT(*) > 1)
)



does not work



is there any way to use a variable ?

declare @nm nvarchar
set @nm = SELECT LEFT(name, CHARINDEX('_', name) - 1) AS ns
FROM Users


thank you for helping

View Replies !

---

Multiple Left Joins (2 Left Joins 1 Big Headache)
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.

View Replies !

---

Left Join Vs Left Outer Join
Hi,
 
Whats the diference between a left join and a  left outer Join

View Replies !

---

Left Join Vs Left Outer Join
Is there any difference between left join and left outer join in sql server 2000?please reply with example if any?
Thanks in advance

View Replies !

---

Getting Left _
how can I can update a table :: I must get all the left part of a columns part1_part2I dont know how many characterssomething like :UPDATE Users SET Users.Info = left(Users.name, _ ??? or split('_'))in that case Users.name = part1i must update only the one with a '_' and do nothing for the othersthank you

View Replies !

---

Pad Left
Hi,

I am trying to pad a field from the left with zeros that is defined in a table as "[char] (9)". The field can have from 1 to 9 chars populated. In this instance the value in the field is "133000", which I am trying to pad so that it will be "000133000". I have made several failed attempts to do this, the latest of which was this:

select
right(replicate('0',9)+ convert(varchar(9),a.GS06GRP_CTL_NBR),9)
fromTable1 a

Thanks,
Jeff

View Replies !

---

SQL LEFT JOIN Help
I'm trying to join 2 tables. I thought I was getting the correct results but it turns out I'm not.
My Query:
SELECT IVINVA, IVORDN, IVCSLN, IVRESR, IVCITM, CONVERT(varchar(12),CAST(IVIAMT as money),1) AS ExtPrice, CONVERT(varchar(12),CAST(IVPIVC as money),1) AS DistPrice, IVCSUM, IVQYCS, IVDESC, OIRESR, OIDPCT, CONVERT(varchar(12),CAST(IVPIVC - (OIDPCT / 100 * IVPIVC) as money),1) AS NetPrice FROM INVDET1_TBL LEFT JOIN ORDDIS_TBL ON ORDDIS_TBL.OIORDN = INVDET1_TBL.IVORDN AND ORDDIS_TBL.OIRESR = INVDET1_TBL.IVRESR WHERE IVORDN = '0859919' AND IVINVA = '00324024'
 Basically, my problem lies in the seonc condition of the LEFT JOIN. I needed to set the two tables equal my item number, because in some situations I need that logic to get the correct result. It most other cases, that item column in the ORDDIS_TBL is NULL, thus giving me the wrong results. In that case, I would want the JOIN to only be ORDDIS_TBL.OIORDN = INVDET1_TBL.IVORDN, and not include the second part. Is there a way I can condition this with an If statement, If ORDDIS_TBL.OIRESR is Null then do this join, if not, then do this? I'm confused how to get the proper result here.

View Replies !

---

Left/right Join
i'm shure it's some smal stiupid mistake bat I can't find it, PLZ help.

1)
select komorka from #plantemp
--result

komorka

09
10
I-P
II-P
III-P
SI/1
SI/2
SI/3


2)
select komorka,ustalenia from analiza_1 a where a.koniec between '20040701'and '20040731'

komorka ustalenia
SI/1788138.9300
SI/246638.4900
SI/216218.4000
08.0000



3)
select p.komorka,isnull(sum(ustalenia),0)
from #plantemp p left join analiza_1 a on p.komorka=a.komorka
where a.koniec between '20040701'and '20040731'
group by p.komorka

komorka ustalenia (sum)

08.0000
SI/1788138.9300
SI/262856.8900




I need all rows from table 1 bat right and left join gives me the same results, WHY

View Replies !

---

Left Join
There are two tables:
tblIndices:
IndexID, Name
1index1
2index2
3index3
.
.
.


tblBasketConstituents
ID, ParentIndexID, ChildIndexID, Weight
121 20
223 80
313 50
412 50

As you can see the ParentIndexID and ChildIndexID fields refer to tblIndices.IndexID
I would like a stored procedure as follows:
show all index names and show the wights for the indexID you passed.
This is what I have so far and it is not correct yet. Not sure what the syntax should be.


alter PROCEDURE [dbo].[uspBasketIndices_Get]

@IndexIDint

AS

select
i.IndexID,
i.[Name],
bc.Weight
from
tblIndices as i left join tblBasketConstituents as bc on i.IndexID = bc.ParentIndexID
and i.IndexID = @IndexID
order by
i.[Name]

View Replies !

---

How Many 'LEFT JOIN' Is Too Many?
Any one know any facts and figures about maximum Left Joins allowed (or recommended) in one query?

I am running a MS SQL 2000 my database is full of relational data and most of my foreign keys (INT data type) are a Clustered Indexed, Usually I will only be pulling one record from collection of about a dozen tables, but the Database is expected to grow fast and become big.

Right now I have a Stored Proc that has eight(8) LEFT JOINs in it. My worry is that this query will kill me as the database approaches 50,000 records.

Lito

View Replies !

---

Left Joins And Where
To the wise,

This may be a very simple problem but it's been racking may brains for a while and I just can't seem to think it through clearly.

I'm trying to return a query which uses a left join and where. I'm hoping to get a result set which shows - let us says all the departments in a company. I would like to see all the department but only the names of department heads that earn 20.000+.

In MS Access I used a subquery. My subquery returned only departments with department heads that earned 20.000+ - I then left joined the departments table to that query - no problem.

With MSSQL I've tried IN, ANY, ALL but my result sets only returns the departments that earn 20.000+ and the employees for those particular departments.

I'm thinking there must be some way of doing this without having to use a union clause.

Thanks for taking the time to read this message through.

Sincerely,


Arthur Lambiris

View Replies !

---

Left Trim
What is the syntax of left triming characters seperated by space.

e.g elsie reed
and i want elsie to go to one field and reed in another field i do not want to use numbers because they don't all have the same numbers but are all seperated by spaces

View Replies !

---

Left Join Help
I need to make a left join from the freezefile f, to sped s, instead of having f.studentid = s.id in the where clause. Any help??

select f.studentid, f.studentname, f.sex, fs.mealstatus, s.except, s.lre, r.description, g.testid, g.scale_la, g.scale_ma, t.test_name, t.year
from freezefile f, fsapps fs, sped s, regtb_exception r, gqe_scores g, test_info t
where
f.type = 'ADM'
and
s.except = r.code
and
t.test_name = 'ISTEP'
and
t.year = 2004
and
g.testid = t.testid
and
f.studentid = fs.id
and
f.studentid = s.id
and
f.studentid = g.studentid

View Replies !

---

Left Joins
My understanding of relevant topics as well as SQL Books Online definition of left outer joins is that each record in the left table will be retrieved and where no associated right record exists then null values will be displayed for records in the right hand table but I've obviously misunderstood and would be grateful if someone could show me how to produce the required effect.

My scenario is pretty simple: 2 tables DiningTables and Reservations with columns as follows:

DiningTables: TBL_ID and TBL_Location - TBL_ID is the primary key

Reservationss::RES_TBL_ID  and RES_Diner_Name - RES_TBL_ID is the primary key

There are 8 records in DiningTables and 4 records in Reservations and the objective is obtain the following output:

TBL_ID   RES_Diner_Name

1                         Jones

2                        Smith

3                         Bloggs

4                         Mack

5                          null

6                           null

7                          null

8                           null

The SQL query I used is

SELECT     Reservations.RES_Diner_Surname, DiningTables.TBL_ID
FROM         DiningTables LEFT OUTER JOIN
                      Reservations ON DiningTables.TBL_ID = Reservations.RES_TBL_ID

 

That query generates 11 rows as follows:

TBL_ID   RES_Diner_Name

1                        Jones

1                        Smith

1                        Bloggs

1                        Mack

2                          null

3                           null

4                          null

5                           null

6                           null

7                           null

8                           null

I'm clealry missing something incredibly obvious and I kinda feel like the village idiot and would be extremely grateful for a clue!!

 

View Replies !

---

Report From Right To Left
Report from right to left
Hi.

Is there a way to define the report from right to left?
and I mean all the report and not just the textBox and stuff?

For example:
Right now, When I have a table that spread over the whole page, If I change the visibility  of a  column from the center to False - The table shrink to the left (and there is a spare room in the right side of the page).

I tried changing the lenguage to hebrew but no good.

Any ideas?

View Replies !

---

Substring From The Left?!?
Hi,
we are using Moss 2007 and in one of our lists we add a hyperlink.
when we display this field into a report it is showed as: hyperlinkurl, name
 
for example: http://www.site.com,testsite
 
What we want to is that the piece after the , is displayed as a value and the string before the , is used as hyperlink.
 
Now we have tested a few things like:
 
 

=Trim(Right(Fields!Variant_Locatie.Value, Len(Fields!Variant_Locatie.Value) - InStr(Fields!Variant_Locatie.Value, ", ")))
 
and this give's us the string part AFTER the ,    so that part is correct!
 
but I can't find out how to do the same thing but then for using everything BEFORE the ,
 
 
...
 
anyone got an idea? just changing Right with Left give's us (for example): http://thisisa
instead of http://thisisatest.com (he's counting the number of characters from the right to the , and then displays the characters starting from the left but only the number of characters he counted previously) ...
 
urgent plz!

View Replies !

---

Left Or Right Joins
Hi All,
 
This may be a stupid question. But I just want to make sure i'm going in right direction.
I wrote the following query. My purpose is to retrieve all the members who submitted loan applications.
I just want to make sure my query is right? Can anyone veryfy this query?
Do I have to use loanApplication table first instead of members. Also Do I need to use right outer joins instead of left outer joins?
 
 



Code Block
SELECT     Member.CUMemberId, LoanApplication.SubmittedOn, Member.LastName, Member.FirstName, Member.MiddleName, LoanApplication.Amount,
                      LoanApplication.Decision, LoanApplication.Term, Rate.InterestRate, LoanApplication.Status, Member.CuStatus
FROM         Member INNER JOIN
                      MemberLogon ON Member.Id = MemberLogon.MemberFK INNER JOIN
                      LoanApplication ON Member.LastLoanApplicationFK = LoanApplication.Id AND Member.Id = LoanApplication.MemberFK LEFT OUTER JOIN
                      Account ON Member.Id = Account.MemberFK AND LoanApplication.LoanFK = Account.Id LEFT OUTER JOIN
                      Rate ON LoanApplication.RateFK = Rate.Id
WHERE     (LoanApplication.Status = 'Submitted')
 
 



Thanks
 
 

View Replies !

---

Anything Left Of A Character?
if i had many variables like 12-232
and 232131-232

and wanted only that left of the - symbol from that variable how could i do that?

View Replies !

---

Left Join
Hi all. My query works fine, it generates reports but not my expected result.

select d.fullname, p.nickname, p.birthdate, p.birthplace,
p.gender, p.civilstatus, p.religion, p.nationality, p. weight, p.height,
p.haircolor, p.eyecolor, p.complexion, p.bodybuilt, p.picture, p.dialectspoken,
d.mobilephone, d.prprovince,[Age] = dbo.F_AGE_IN_YEARS( birthdate, getdate() ),
c.name, c.address, c.telno, c.email, c.occupation, ed.year1, ed.year2, ed.degree, sch.schname
from hremployees as e
inner join psdatacenter as d on e.empdcno = d.dcno
inner join pspersonaldata as p on e.empdcno = p.dcno
left join hrappempcharrefs as c on e.empdcno = c.empdcno
left join hrappempeducs as ed on e.empdcno = ed.empdcno
left join hrsetschools as sch on ed.schoolcode = sch.schcode

the above query gives a 77 records

if i ran "select * from hremployees" generates 60 records

i think the error is in the left joining.
hrappempcharrefs, hrappempeducs and hrsetschools must be left joined to hremployees.

thanks
-Ron-

View Replies !

---

Left Function
i have a query

select message from ticket

but i want to only get the first 100 characters

select left(message,100) from ticket

is giving me an error - please advise?

View Replies !

---

Report From Right To Left
Hi.

Is there a way to define the report from right to left?
and I mean all the report and not just the textBox and stuff?

For example:
Right now, When I have a table that spread over the whole page, If I change the visibility of a column from the center to False - The table shrink to the left (and there is a spare room in the right side of the page).

I tried changing the lenguage to hebrew but no good.

Any ideas?

View Replies !

---

Name Split
Quick question.

I've got a CHAR (70) field called NAME that has a first and last name separated by a space. I want to split it into two fields FIRST and LAST -- with all the characters to the left of the space a first name and all the characters to the right of the space as last name. I couldn't find a string function that would let me do this simply (it may be right in front of me and I missed it).

Thanks in advance.

Ray

View Replies !

---

Name Split
I need to split a column of Full Names into First name and Last name columns. Has someone come across this before and if so can you give me an idea of how to overcome it?

View Replies !

---

Split Value In Sql
Hi to all

I have one problem regarding sp and pass value in sp
I am gating a value like Abc,Def,Ghi,

Now I want to split the whole pass value by “,�
And fire one for loop to store value in database
This things is done in asp.net web form but I want to do all process in sp
So please guide me how I am write sp .
The purpose is pass value one time so connection time is decrees and give fast perforce

View Replies !

---

To Split Or Not To Split
I have a database with a "large" table containing date based information Basically they're reservations. I've thought about creating a new table and adding any records from past years to this table. For the most part only current reservation need to be searchable, but in some circumstances it would be useful to be able to search through the archive too. so, my questions!!!

Is 8,000 or so rows of data "large" and unwieldly in SQL terms?

Would splitting this data into 2 tables - one small table for current and future reservations and one larger archive table then using a UNION SELECT query to make archive information seachable be a significant improvment on server resources/load or am I making the whole thing more complicated than it need be as 8,000 rows of data is nothing to worry about.............

What did they say about a little bit of knowledge being a dangerous thing?

Thanks in advance of any guidance to a neophyte!!?

View Replies !

---

SPLIT() UDF
SQL UDF split()

The objective of this article is to help the SQL developers with an UDF that can be used within a stored procedures or Function to split a string (based on given delimiter) and extract the required portion of the string.

Scripting languages like VB script and Java script have in-built split() functions but there is no such function available in SQL server. In my experience this function is really handy when you’re working on an ASP application with SQL server as backend, whereby you’ll need to pass the ASP page submitted values to the SQL stored procedure.

To give a simple example, in a typical Monthly reporting ASP page – the users would select a range of months and extract the information pertaining to this date range. Classic implementation of this model is to have an ASP page to accept the input parameters and pass the values to the SQL stored procedure (SP). The SP would return a result set which is then formatted in the ASP page as results.

If the date range is continuous ie. JAN07 to MAR07 then the SP can typically accept a ‘From’ and ‘To’ range variables. But I’ve encountered situations whereby the users select 3 months from the current year and 2 months from previous year (non-continuous date ranges). In such scenario the SP cannot have a date range as input parameters.

Typically an ASP programmer would do is by having a single date input parameter in the SP and call the SP within a loop in the ASP page. This is an inefficient way of programming as contacting the database server within an ASP loop could cause performance overhead especially if the table being queried is an online transaction processing table.

Here is how I handled the above situation.

1.Declared one string input parameter of type varchar(8000) (if you’re using SQL 2005 then it is advisable to use Varchar(Max))
2.Pass the ASP submitted values as string, in this case the months selected by user would be supplied to the SP as a string
3.Within the Stored Procedure I’ll call the split() function to extract each month from the string and query the corresponding data

The basic structure of the stored procedure is as pasted below:-

CREATE PROCEDURE FETCH_SALES_DETAIL (
@MONTH VARCHAR(MAX)
)
AS
BEGIN
DECLARE @MONTH_CNT INT,@MTH DATETIME
SET @MONTH_CNT=1
WHILE DBO.SPLIT(@MONTH,',',@MONTH_CNT) <> ''
BEGIN
SET @MTH = CAST(DBO.SPLIT(@MONTH,',',@MONTH_CNT) AS DATETIME)
--<<Application specific T-SQLs>>-- (BEGIN)
SELECT [SALES_MONTH],[SALES_QTY],[PRODUCT_ID],[TRANSACTION_DATE]
FROM SALES (NLOCK)
WHERE [SALES_MONTH]= @MTH
--<<Application specific T-SQLs>>--(END)

SET @MONTH_CNT=@MONTH_CNT+1
END
END

Dbo.SPLIT() function takes 3 parameters
1)The main string with the values to be split
2)The delimiter
3)The Nth occurrence of the string to be returned

The functionality of the UDF is as explained STEP by STEP:

1.Function Declaration
CREATE FUNCTION [dbo].[SPLIT]
(
@nstring VARCHAR(MAX),
@deliminator nvarchar(10),
@index int
)

RETURNS VARCHAR(MAX)

Function is declared with 3 input parameters:-
@nstring of type VARCHAR(MAX) will hold the main string to be split
@deliminator of type NVARCHAR(10) will hold the delimiter
@index of type INT will hold the index of the string to be returned
2.Variable Declaration
DECLARE @position int
DECLARE @ustr VARCHAR(MAX)
DECLARE @pcnt int

Three variables are needed within the function. @position is an integer variable that will be used to traverse along the main string. @ustr will store the string to be returned and the @pcnt integer variable to check the index of the delimiter.
3.Variable initialization
SET @position = 1
SET @pcnt = 1
SELECT @ustr = ''
Initialize the variables
4.Main functionality
WHILE @position <= DATALENGTH(@nstring) and @pcnt <= @index
BEGIN
IF SUBSTRING(@nstring, @position, 1) <> @deliminator BEGIN
IF @pcnt = @index BEGIN
SET @ustr = @ustr + CAST(SUBSTRING(@nstring, @position, 1) AS nvarchar)
END
SET @position = @position + 1
END
ELSE BEGIN
SET @position = @position + 1
SET @pcnt = @pcnt + 1
END
END

4.1The main while loop is used to traverse through the main string until the word index is less than or equal to the index passed as input parameter.
4.2Within the while loop each character within the string is verified against the delimiter and if it does not match then local word count variable is checked against the input index parameter
4.3If the values are same ie., the input variable index and the word being processed in the while loop are the same then the word is stored in the @ustr variable. If the values does not match then the @position variable is incremented.
4.4If the character matches with the delimiter then the word count variable @pcnt is incremented along with the @position variable

5.Return the value
RETURN @ustr

I hope this article would benefit those who are looking for a handy function to deal with Strings.

Feel free to send your feedback at dearhari@gmail.com

View Replies !

---