help with aliases to get around 918 error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

help with aliases to get around 918 error

Author

Topic

PureBluff
Starting Member

7 Posts

Posted - 2015-03-26 : 10:12:17

hi all. Very new to SQL, trying to write a query from 2 tables and join them only they share a column name of the same name.

I can define both aliases seperatrly but get an error when trying both, if anyone could advise....

select
 case_HDR.case_nbr as "test"
Case_DTL.case_nbr as "test2"
From
Case_HDR, case_dtl

Appreciate I need to join them also but trying to work in stages.

Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-26 : 10:14:29

please post some sample data and desired output using that data

PureBluff
Starting Member

7 Posts

Posted - 2015-03-26 : 10:31:11

Hi,

Not overly sure what you need, at the minute I'm trying to join a lot of tables as the information required is across 4 tables,

I believe I need to find a common value between

Table1 <> Table2
Table2 <> Table3
Table3 <> Table4

I can pull all of this information sperately (obviously) I'm just working on joining, only the content in my original post is the only common value shared between those tables & they have the same column name.

Could you give an exmple of what I should be providing as an example? I know that's a bit silly, but I am literally learning on my feet as before we had an app to query the DB and all joins were performed by that, to a degree

PureBluff
Starting Member

7 Posts

Posted - 2015-03-26 : 10:49:34

End result I need;

case_nbr, Prod_Code, Current_QTY from case_dtl
locn_id from locn_hdr
case_nbr, locn_id from case_hdr {for joining purposes}

To display;

case number, Prod_code, Qty, Locn_Id

after my query

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-26 : 10:53:45

1. CREATE TABLE statements for the Case_HDR and case_dtl tables:
2. Sample data for both tables in the form of INSERT INTO statements
3. Desired output from a query executed against those tables using that data.

PureBluff
Starting Member

7 Posts

Posted - 2015-03-26 : 11:09:46

Ok, I'll try and sort that out tomorrow, need to research what you're asking =D

Is there anything notably wrong with this code, as it returns 904, thanks

select
 case_dtl.case_nbr, case_dtl.actl_qty, case_dtl.sku_id 

from case_dtl

left join case_hdr on case_hdr.sku_id=case_dtl.sku_id

I wrongly thought it'd pull my fields in my Select statement and join the case_hdr table to it, but that returns a 904 error and now I'm confuddled.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-26 : 11:18:11

This error:

Database Cannot Be Autostarted During Server Shutdown Or Startup. (Microsoft SQL Server, Error: 904).

??

If not, then what error? Please post the whole error message

PureBluff
Starting Member

7 Posts

Posted - 2015-03-26 : 11:19:04

Failed to SQLBuilder.GetBusObjXML. Please contact your system administrator.

#Source: OraOLEDB Number: -2147217900 Description: ORA-00904: "CASE_HDR"."SKU_ID": invalid identifier SQL State: Native Error: 904

PureBluff
Starting Member

7 Posts

Posted - 2015-03-26 : 11:25:51

quote:
Originally posted by PureBluff

Failed to SQLBuilder.GetBusObjXML. Please contact your system administrator.

#Source: OraOLEDB Number: -2147217900 Description: ORA-00904: "CASE_HDR"."SKU_ID": invalid identifier SQL State: Native Error: 904

These are the table names and colums that I need to link, as I understand it, and the fact they're called the same column, I need to use an alias (correct me if I'm way off the mark)

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-03-26 : 11:43:42

In future, please provide DDL and test data like the following:


CREATE TABLE #case_hdr
(
	case_nbr int NOT NULL
	,locn_id int NOT NULL
);
INSERT INTO #case_hdr
VALUES (1, 1)
	,(2, 1)
	,(3, 2);

CREATE TABLE #case_dtl
(
	case_nbr int NOT NULL
	,Prod_code varchar(10) NOT NULL
	,Current_QTY int NOT NULL
);
INSERT INTO #case_dtl
VALUES (1, 'Prod1', 6)
	,(1, 'Prod2', 7)
	,(2, 'Prod3', 4)
	,(3, 'Prod4', 2);

With the above test data, try the following:


SELECT D.case_nbr, D.Prod_code, D.Current_QTY AS QTY, H.locn_id
FROM #case_dtl D
	JOIN #case_hdr H
		ON D.case_nbr = H.case_nbr;

To try on your system, just remove the #'s.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-26 : 11:47:19

quote:
Originally posted by PureBluff

Failed to SQLBuilder.GetBusObjXML. Please contact your system administrator.

#Source: OraOLEDB Number: -2147217900 Description: ORA-00904: "CASE_HDR"."SKU_ID": invalid identifier SQL State: Native Error: 904

You that this is a SQL Server forum right?

PureBluff
Starting Member

7 Posts

Posted - 2015-03-26 : 11:52:58

Ok, I'm not authorised to create tables in the TB, I literally can query it only.

Thanks for your help, looks like I'll need to find another way.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-26 : 12:06:19

You don't have to create the tables, just post the CREATE TABLE statements.

btw, You that this is a SQL Server forum right?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources